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--