本实验中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)
