DATA GUARD switch切换

1.切换时间，停止业务

 

2.[查看完成的归档文件]（此操作主库上）

    SQL> select THREAD#,SEQUENCE#,applied from v$archived_log order by 2;

 

     THREAD#  SEQUENCE# APPLIED

  ---------- ---------- ---------

           1         30 YES

           1         31 YES

           1         31 NO

           1         32 YES

           1         32 NO

           1         33 YES

           1         33 NO

           1         34 YES

           1         34 NO

 

 

 

3. 主备切换，需要先在主库切换

  1）检测主库的状态

    SQL>  select database_role,protection_mode,switchover_status from v$database;

  

    DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS

    ---------------- -------------------- --------------------

    PRIMARY          MAXIMUM PERFORMANCE  TO STANDBY

 

    SQL> select open_mode from v$database;

  

    OPEN_MODE

    ----------

    READ WRITE

 

  2）主库切换为备份库

    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; ［在主库执行这个命令的时候会很慢的］

   

    Database altered.

 

  3）重启原主库到mount状态

    SQL> shutdown immediate

 

    shutdown时候可能会出现下面情况

 

    ORA-01092: ORACLE instance terminated. Disconnection forced

 

    这个时候要重新连接conn / as sysdba

    在启动数据库到mount状态

 

    SQL> startup mount

        ORACLE instance started.

 

        Total System Global Area  830930944 bytes

        Fixed Size                  2232920 bytes

        Variable Size             494931368 bytes

        Database Buffers          331350016 bytes

        Redo Buffers                2416640 bytes

        Database mounted.

  4）检测原先主库的状态

     SQL> select DATABASE_ROLE, PROTECTION_MODE,SWITCHOVER_STATUS FROM V$DATABASE;

 

     DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS

     ---------------- -------------------- --------------------

     PHYSICAL STANDBY MAXIMUM PERFORMANCE  RECOVERY NEEDED

 

 

 

4.在备份库上做检测

   1）在备库上检测现在该库的当前属性

     SQL> select database_role,protection_mode,switchover_status from v$database;

 

     DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS

     ---------------- -------------------- --------------------

     PHYSICAL STANDBY MAXIMUM PERFORMANCE  TO PRIMARY

 

   2）备库切换到主库

     SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

 

          Database altered.

 

   3）查看当前库的状态

     SQL> select open_mode from v$database;

     OPEN_MODE

     ----------

     MOUNTED

 

   4）启动原备用数据库到open状态

     SQL> alter database open;

    

     Database altered.

 

   5）在原备库上检测现在该库的当前属性

 

     SQL> select DATABASE_ROLE, PROTECTION_MODE,SWITCHOVER_STATUS FROM V$DATABASE;

  

     DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS

     ---------------- -------------------- --------------------

     PRIMARY          MAXIMUM PERFORMANCE  RESOLVABLE GAP

 

 

 

5.在当前备库（原主库）上

 

   1)启动备用数据库到open状态

     SQL> alter database open;

 

     Database altered.

  

   2）在当前备库（原主库）应用当前日志

     SQL> alter database recover managed standby database using current logfile disconnect from session;

     

     Database altered.

 

   3）查看当前备库（原主库）的属性

     SQL> select DATABASE_ROLE, PROTECTION_MODE,SWITCHOVER_STATUS FROM V$DATABASE;

    

     DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS

     ---------------- -------------------- --------------------

     PHYSICAL STANDBY MAXIMUM PERFORMANCE  NOT ALLOWED

 

 

 

6.在当前主库（原备库）上

 

    1）再检查一次该库的当前属性

 

     SQL> select DATABASE_ROLE, PROTECTION_MODE,SWITCHOVER_STATUS FROM V$DATABASE;

  

     DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS

     ---------------- -------------------- --------------------

     PRIMARY          MAXIMUM PERFORMANCE  TO STANDBY

 

   2）更新主库数据，提交并在备库上查看数据更新情况，如果无需切换日志就可以个同步看到数据更新情况，那么lgwr模式下切换成功。

 

  

  

   3）接下来查看当前主库（原备库）的日志使用情况

     SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS      FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------   ------------- --------- ------------ ---------

         1          1         37   52428800        512          1 YES INACTIV     1238007       14-OCT-15       1238010 14-OCT-15

         2          1         38   52428800        512          1 YES INACTIV     1238010       14-OCT-15       1238068 14-OCT-15

         3          1         39   52428800        512          1 NO  CURRENT     1238068       14-OCT-15    2.8147E+14

  

   4）当前主库（原备库）切换日志

     SQL> alter system switch logfile;

 

     System altered.

 

     SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS      FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ----------- ------------- --------- ------------ ---------

         1          1         40   52428800        512          1 NO  CURRENT     1239817       14-OCT-15    2.8147E+14

         2          1         38   52428800        512          1 YES INACTIV     1238010       14-OCT-15       1238068 14-OCT-15

         3          1         39   52428800        512          1 YES INACTIV     1238068       14-OCT-15       1239817 14-OCT-15

  

7.在当前备库（原主库）上查看日志应用情况，如果和主库同步，那么归档模式下切换成功。

    SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC  STATUS  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- ---  ------- ------------- --------- ------------ ---------

         1          1         40   52428800        512          1 YES  CURRENT       1239817 14-OCT-15      1212993 14-OCT-15

         3          1         39   52428800        512          1 YES  CLEARING      1238068 14-OCT-15      1239817 14-OCT-15

         2          1          0   52428800        512          1 YES  UNUSED        1238010 14-OCT-15      1238068 14-OCT-15

 

[查看完成的归档文件]

    SQL> select THREAD#,SEQUENCE#,applied from v$archived_log order by 2;

       THREAD#  SEQUENCE# APPLIED

---------- ---------- ---------

         1         38 YES

         1         39 YES

         1         40 YES

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31387888/viewspace-2124312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31387888/viewspace-2124312/