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

posted @ 2015-09-03 22:58  Renolei  阅读(450)  评论(0)  编辑  收藏  举报