[Oracle] DataGuard switchover

Oracle DataGuard switchover

2013/07/11

Tag.Data Guard,primary,standby,switchover

切换前primary site和standby site状态检查

• Primary site.

  1. 确认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

  2. 确认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 表示不能转换

  3. 查看当前会话数

     SQL> select count(*) from v$session where username is not null;

     COUNT(*)
     ----------
     1

  4. 查看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.

  1. 查看standby site端switchover状态

     SQL> select switchover_status,database_role from v$database;

     SWITCHOVER_STATUS DATABASE_ROLE
     -------------------- ----------------
     NOT ALLOWED PHYSICAL STANDBY

  2. 确认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;

  3. 查看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

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

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