Oracle DataGuard switchover
2013/07/11
Tag.Data Guard,primary,standby,switchover
切换前primary site和standby site状态检查
-
Primary site.
-
确认primary site和standby site没有出现日志裂隙(log file gap)
SQL> select status,gap_status from v$archive_dest_status where dest_id=2; STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP -
确认primary可以转换成standby角色
SQL> select switchover_status from v$database; SWITCHOVER_STATUS
--------------------
TO STANDBY--注意:
switchover_status应为to standby/session active/not allowed
a. to standby 表示可以转换
b. session active 表示还有活动的session,通过v$session确认活动会话
c. not allowed 表示不能转换 -
查看当前会话数
SQL> select count(*) from v$session where username is not null; COUNT(*)
----------
1 -
查看primary其他信息
SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY
-
-
Standby site.
-
查看standby site端switchover状态
SQL> select switchover_status,database_role from v$database; SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED PHYSICAL STANDBY -
确认standby site没有日志应用延迟
SQL> select delay_mins from v$archive_dest where dest_id=2; DELAY_MINS
----------
0如果有延迟设置.在standby site禁用延迟
alter database recover managed standby database nodelay; -
查看standby其他信息
SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- -------------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
-
-
切换
--注意:
现将primary将为standby,再将standby升为priamry
-
primary切到standby
SQL> select database_role from v$database; DATABASE_ROLE
----------------
PRIMARY SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY SQL> alter database commit to switchover to physical standby; Database altered.========================================
SQL> shutdown immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> shutdown immediate;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> select open_mode from v$database;
ERROR:
ORA-03114: not connected to ORACLE SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options========================================
[oracle@TENCENT64 /u]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 11 19:57:50 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance.
SQL>=========================================
SQL> startup nomount;
ORACLE instance started. Total System Global Area 1603411968 bytes
Fixed Size 2226912 bytes
Variable Size 503317792 bytes
Database Buffers 1090519040 bytes
Redo Buffers 7348224 bytes SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select database_role from v$database; DATABASE_ROLE
----------------
PHYSICAL STANDBY SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- -------------------- --------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY观察alert日志可以发现primary已经成功切换成Physical Standby database
Thu Jul 11 19:58:56 2013
Successful mount of redo thread 1, with mount id 3458571643
Physical Standby Database mounted. -
standby切到primary
SQL> select database_role from v$database; DATABASE_ROLE
----------------
PHYSICAL STANDBY SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- -------------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY SQL> alter database commit to switchover to primary; Database altered. SQL> shutdown immediate;
ORA-01109: database not open Database dismounted.
ORACLE instance shut down. SQL> startup;
ORACLE instance started. Total System Global Area 1603411968 bytes
Fixed Size 2226912 bytes
Variable Size 486540576 bytes
Database Buffers 1107296256 bytes
Redo Buffers 7348224 bytes
Database mounted.
Database opened. SQL> select database_role from v$database; DATABASE_ROLE
----------------
PRIMARY SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE RESOLVABLE GAP SQL> / OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY观察alert日志可以发现standby已经成功切换成Primary
Standby became primary SCN: 1362898
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary
最后,修改primary site和standby site的tnsnames.ora
-
--END--