physical dataguard 切换测试
由于资源有限，stdbA、stdbB、stdbC都在同一台服务器上。
同步关系是：primary：stdbA standby：stdbB、stdbC
stdbA-->stdbB
stdbA-->stdbC
准备切换成：primary：stdbB standby：stdbA、stdbC
stdbB-->stdbA
stdbB-->stdbC
设置提示：
$ cat bin/login.sql
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 180
set pagesize 33
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select INSTANCE_NAME global_name from v$instance;
set sqlprompt '&gname> '
set termout on
####################end#################
在bash_profile上加上：
export SQLPATH=/home/oracle/bin
切换sid进入到不同的实例，如：
export ORACLE_SID=stdbA
目前 primary：stdbA standby：stdbB、stdbC
stdbA-->stdbB
stdbA-->stdbC
目前三个实例的switchover_status的状态如下：
stdbA> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
stdbB> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
stdbC> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
第一步：在primary stdbA上操作，将stdbA切换成standby
stdbA> alter database commit to switchover to physical standby with session shutdown;
Database altered.
stdbA> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
stdbA> startup mount
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 385876536 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14692352 bytes
Database mounted.
stdbA> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
stdbA> alter database recover managed standby database disconnect from session;
Database altered.
stdbA> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL STANDBY
在stdbB、stdbC上查看switchover_status
stdbB> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
stdbC> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
第二步：将stdbB 切换为主库
stdbB> alter database commit to switchover to primary;
Database altered.
stdbB> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
stdbB> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 385876536 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
稍等片刻，SWITCHOVER_STATUS 状态由 NOT ALLOWED 变成 TO STANDBY。
stdbB> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
stdbB> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
---------- ----------------
READ WRITE PRIMARY
至此，stdbB-->stdbA 的同步关系已搭建好。
第三步，建立stdbB-->stdbC的同步关系
在primary stdbB上配置 log_archive_config、log_archive_dest_3：
stdbB> alter system set log_archive_config='DG_CONFIG=(stdbA,stdbB,stdbC)' scope=both;
System altered.
stdbB> alter system set log_archive_dest_3='SERVICE=stdbC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbC REOPEN=60 lgwr async';
System altered.
顺便在stdbC上也配置上：
stdbC> alter system set log_archive_config='DG_CONFIG=(stdbA,stdbB,stdbC)' scope=both;
System altered.
stdbC> alter system set log_archive_dest_3='SERVICE=stdbB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbB REOPEN=60 lgwr async';
System altered.
在primary stdbB 上执行一些sql，并且执行alter system switch logfile;
查看当前的日志SEQUENCE#
stdbB> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 250 52428800 1 NO CURRENT 825642 08-MAY-13
2 1 248 52428800 1 YES INACTIVE 825159 08-MAY-13
3 1 249 52428800 1 YES INACTIVE 825162 08-MAY-13
然后在stdbC上查看日志是否已经传过来并且应用：
stdbC> select dest_id,archived,status,sequence#,applied,completion_time from v$archived_log where SEQUENCE#>242;
DEST_ID ARC S SEQUENCE# APP COMPLETIO
---------- --- - ---------- --- ---------
3 YES A 243 YES 08-MAY-13
3 YES A 244 YES 08-MAY-13
1 YES A 245 YES 08-MAY-13
1 YES A 246 YES 08-MAY-13
1 YES A 247 YES 08-MAY-13
1 YES A 248 YES 08-MAY-13
1 YES A 249 YES 08-MAY-13
可以看到stdbB上产生的日志已经传至stdbC，并且已经应用
若拿日志中出现日常，可以手工传过来并且注册logfile，如：
alter database register physical logfile '/home/oracle/app/oracle/flash_recovery_area/STDBC/archivelog/1_241_814374880.dbf'
