做DG的switchover并恢复到最初状态

本实验中dg1是主库，dg2是备库，DG当前正常运行在最大可用模式。
第一次计划将dg1转化为备库，dg2转为主库
第二次最终再切换回dg1是主库，dg2是备库。

环境检查：

因为目前DG是可以正常使用，所以只需要检查以下参数就可以 ：
切换前首先检查dg2备库上的参数设置是否正确。
SYS@dg2>show parameter archive_dest_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=dg1 LGWR SYNC AFFIRM V
                                                 ALID_FOR=(ONLINE_LOGFILES,PRIM
                                                 ARY_ROLE) DB_UNIQUE_NAME=dg1
SYS@dg1> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE
SYS@dg2>show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SYS@dg2>show parameter fal_server
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server                           string      DG1
SYS@dg2>show parameter fal_client
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      DG2
dg1上检查standby日志创建情况
SYS@dg1>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         4          1          0 NO  UNASSIGNED
         5          1         81 YES ACTIVE
         6          0          0 YES UNASSIGNED
         7          0          0 YES UNASSIGNED
SYS@dg1>show parameter fal_client
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      DG1
SYS@dg1>show parameter fal_server
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server      
#####################################################

一、开始进行DG主备数据库的切换

1.dg1上的操作-切换为备库

SYS@dg1>select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SYS@dg1>alter database commit to switchover to physical standby;
Database altered.
SYS@dg1>shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SYS@dg1>startup mount;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             327158172 bytes
Database Buffers           83886080 bytes
Redo Buffers                6103040 bytes
Database mounted.
SYS@dg1>select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
#######################################

2.dg2上的操作：--切换为主库

SYS@dg2>select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SYS@dg2>alter database commit to switchover to primary;
Database altered.
SYS@dg2>exit
SYS@dg2>startup mount force;
######################################

3.检查DG切换后的状态

DG2上的检查--已经切换到 PRIMARY
SYS@dg2>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PRIMARY          MAXIMUM AVAILABILITY
SYS@dg2>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SYS@dg2>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
             80
SYS@dg2>select sequence#,status,thread#,block# from v$managed_standby;
SEQUENCE# STATUS          THREAD#     BLOCK#
---------- ------------ ---------- ----------
        79 CLOSING               1      28673
        79 CLOSING               1      28672
        77 CLOSING               1          1
        80 CLOSING               1          1
        81 WRITING               1       2672
DG1上的检查--已经切换到物理STANDBY
SYS@dg1>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY
SYS@dg1>select open_mode from v$database;
OPEN_MODE
--------------------

MOUNTED

SYS@dg1>alter database recover managed standby database disconnect from session;
Database altered.

SYS@dg1>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            80
SYS@dg1>select sequence#,status,thread#,block# from v$managed_standby;
SEQUENCE# STATUS          THREAD#     BLOCK#
---------- ------------ ---------- ----------
        80 CLOSING               1          1
         0 CONNECTED             0          0
         0 CONNECTED             0          0
         0 CONNECTED             0          0
        81 WAIT_FOR_LOG          1          0
        81 IDLE                  1       2730
         0 IDLE                  0          0
         0 IDLE                  0          0
         0 IDLE                  0          0

4.在DG2--主库上切换redo日志，查看alert日志中的信息

从主库及备库日志可以看到，当前已经切换到序号82的归档日志。序号81的归档日志已经归档完成

[oracle@dg2 ~]$ cat alert_dg.log

Fri Aug 02 20:48:20 2013
LGWR: Standby redo logfile selected to archive thread 1 sequence 82
LGWR: Standby redo logfile selected for thread 1 sequence 82for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 82 (LGWR switch)
  Current log# 3 seq# 82 mem# 0: /u01/oradata/dg/redo03.log
Fri Aug 02 20:48:20 2013
Archived Log entry 85 added for thread 1 sequence 81 ID 0x6776262a dest 1:
序号81的归档归档日志已经应用。现在在等待序号82的日志。
[oracle@dg1 ~]$ cat alert_dg.log
Fri Aug 02 20:48:20 2013
Standby controlfile consistent with primary
RFS[1]: Selected log 4 for thread 1 sequence 82 dbid 1735160627 branch 821829622
Fri Aug 02 20:48:21 2013
Archived Log entry 157 added for thread 1 sequence 81ID 0x6776262a dest 1:
Fri Aug 02 20:48:24 2013
Media Recovery Log /u01/archivelog/arc_1_81_821829622.arc
Media Recovery Waiting for thread 1 sequence 82 (in transit)
####################################################

二、切换回dg1是主库，dg2是备库

1.dg1上的操作：--切换为主库

SYS@dg1>select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SYS@dg1>alter database commit to switchover to primary;
Database altered.
SYS@dg1>shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

2.DG2的操作---切换为备库

SYS@dg2>select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SYS@dg2>alter database commit to switchover to physical standby;
Database altered.
SYS@dg2>shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@dg2>startup mount;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             318769564 bytes
Database Buffers           92274688 bytes
Redo Buffers                6103040 bytes
Database mounted.
SYS@dg2>select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SYS@dg2>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY
SYS@dg2>select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SYS@dg2>alter database recover managed standby database disconnect from session;
Database altered.

3.dg1上的打开数据库并检查状态

SYS@dg1>startup;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             327158172 bytes
Database Buffers           83886080 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SYS@dg1>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SYS@dg1>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PRIMARY          MAXIMUM AVAILABILITY
SYS@dg1>alter system switch logfile;
System altered.

4.切换 redo日志的alert日志：

[oracle@dg1 ~]$ cat alert_dg.log
Fri Aug 02 20:59:53 2013
LGWR: Standby redo logfile selected to archive thread 1 sequence 89
LGWR: Standby redo logfile selected for thread 1 sequence 89for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 89 (LGWR switch)
  Current log# 2 seq# 89 mem# 0: /u01/oradata/dg/redo02.log
Fri Aug 02 20:59:53 2013
Archived Log entry 168 added for thread 1 sequence 88 ID 0x6776473b dest 1:

[oracle@dg2 ~]$ cat alert_dg.log
Fri Aug 02 20:59:52 2013
Standby controlfile consistent with primary
RFS[1]: Selected log 4 for thread 1 sequence 89 dbid 1735160627 branch 821829622
Fri Aug 02 20:59:52 2013
Archived Log entry 95 added for thread 1 sequence 88 ID 0x6776473b dest 1:
Fri Aug 02 20:59:54 2013
Media Recovery Log /u01/archivelog/arc_1_88_821829622.arc
Media Recovery Waiting for thread 1 sequence 89 (in transit)