在Dataguard环境中配置cascade redo transport

cascade redo transport用在多个standby的环境下，当standby与primary的距离较远需要通过WAN来传输Redo时，为减少传输过程中对primary的压力及网络带宽的占用，仅让其中的一个standby从primary直接接收redo，这个standby就称作cascading standby，而其余的standby从cascading standby接收redo，这些standby称作cascaded standby，cascading standby从中起到了redo转发的功能。比如DG环境中：A是主库、B、C、D都是A的备库，B与A的距离是500KM，B和C、B和D的距离都在50km，这时可以将B库作为cascading standby，把从A收到的redo转发给C、D，这时的C、D就是cascaded standby，因为它们从B库接收redo，而不是从A库直接接收。还有一个限制就是cascaded standby必须是一个physical standby

下面就来看看如何实现cascade redo transport

测试DG环境部署：
prmy:tstdb1
cascading standby:tstdb1_stdby1
cascaded standby:tstdb1_stdby2


###只列出与redo transport相关的参数
---tstdb1:
alter system set db_unique_name=tstdb1;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=tstdb1' scope=both;
alter system set log_archive_dest_2='service=tstdb1_stdby1 SYNC valid_for=(online_logfiles,primary_role) db_unique_name=tstdb1_stdby1' scope=both;
alter system set log_archive_dest_3='service=tstdb1_stdby2 ASYNC valid_for=(standby_logfiles,standby_role) db_unique_name=tstdb1_stdby2' scope=both;   <---role transition后才会用到的参数
alter system set fal_server=tstdb1_stdby1;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_dest_state_3=enable;
alter system set log_archive_config="dg_config=(tstdb1,tstdb1_stdby1,tstdb1_stdby2)";


---tstdb1_stdby1:
alter system set db_unique_name=tstdb1_stdby1;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=tstdb1_stdby1' scope=both;
alter system set log_archive_dest_2='service=tstdb1 SYNC valid_for=(online_logfiles,primary_role) db_unique_name=tstdb1' scope=both;   <---role transition后才会用到的参数
alter system set log_archive_dest_3='service=tstdb1_stdby2 ASYNC valid_for=(standby_logfiles,standby_role) db_unique_name=tstdb1_stdby2' scope=both;
alter system set fal_server=tstdb1;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_dest_state_3=enable;
alter system set log_archive_config="dg_config=(tstdb1,tstdb1_stdby1,tstdb1_stdby2)";


---tstdb1_stdby2:
alter system set db_unique_name=tstdb1_stdby2;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=tstdb1_stdby2' scope=both;
alter system set fal_server=tstdb1_stdby1;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_config="dg_config=(tstdb1,tstdb1_stdby1,tstdb1_stdby2)";


###在tstdb1_stdby1、tstdb1_stdby2上开启MRP
---tstdb1_stdby1:
alter database recover managed standby database using current logfile disconnect;


---tstdb1_stdby2:
alter database recover managed standby database using current logfile disconnect;


###DG状态查询：
---tstdb1: v$archive_dest里指向LOG_ARCHIVE_DEST_3的状态显示为PENDING，v$archive_dest_status里tstdb1_stdby2的recovery_mode显示为IDLE，表明tstdb1_stdby2的恢复不是通过tstdb1接收日志的
SELECT DESTINATION,db_unique_name,type,STATUS,database_mode,recovery_mode,ARCHIVED_THREAD#,ARCHIVED_SEQ#,applied_seq#,synchronization_status,gap_status FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
SYS@tstdb1-SQL> SYS@tstdb1-SQL> 
DESTINATION     DB_UNIQUE_NAME  TYPE       STATUS   DATABASE_MODE   RECOVERY_MODE   ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_SEQ# SYNCHRONIZATION_STAT GAP_STAT
--------------- --------------- ---------- -------- --------------- --------------- ---------------- ------------- ------------ -------------------- --------
                tstdb1          LOCAL      VALID    OPEN            IDLE                           1           574            0 CHECK CONFIGURATION
tstdb1_stdby1   tstdb1_stdby1   PHYSICAL   VALID    OPEN_READ-ONLY  MANAGED REAL TI                1           574          536 OK                   NO GAP
                                                                    ME APPLY


tstdb1_stdby2   tstdb1_stdby2   UNKNOWN    VALID    UNKNOWN         IDLE                           0             0            0 CHECK CONFIGURATION  LOG SWIT
                                                                                                                                                     CH GAP


SYS@tstdb1-SQL> select dest_name,status,target,archiver,schedule,destination,process,register,transmit_mode,affirm,applied_scn from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2','LOG_ARCHIVE_DEST_3');


DEST_NAME                                          STATUS   TARGET  ARCHIVER   SCHEDULE DESTINATION                    PROCESS    REG TRANSMIT_MOD AFF APPLIED_SCN
-------------------------------------------------- -------- ------- ---------- -------- ------------------------------ ---------- --- ------------ --- -----------
LOG_ARCHIVE_DEST_1                                 VALID    PRIMARY ARCH       ACTIVE   USE_DB_RECOVERY_FILE_DEST      ARCH       YES SYNCHRONOUS  NO            0
LOG_ARCHIVE_DEST_2                                 VALID    STANDBY LGWR       ACTIVE   tstdb1_stdby1                  LGWR       YES PARALLELSYNC YES  1.2723E+13
LOG_ARCHIVE_DEST_3                                 VALID    STANDBY LGWR       PENDING  tstdb1_stdby2                  LGWR       YES ASYNCHRONOUS NO            0
                                                                                                                                                     
---tstdb1_stdby1: tstdb1_stdby2那行的recovery_mode虽然显示为RTA，但实际并非工作在RTA模式，因为cascaded standby必须要等到cascading standby生成完整的archivelog后才能进行recover
SELECT DESTINATION,db_unique_name,type,STATUS,database_mode,recovery_mode,ARCHIVED_THREAD#,ARCHIVED_SEQ#,applied_seq#,synchronization_status,gap_status FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 
DESTINATION     DB_UNIQUE_NAME  TYPE       STATUS   DATABASE_MODE   RECOVERY_MODE   ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_SEQ# SYNCHRONIZATION_STAT GAP_STAT
--------------- --------------- ---------- -------- --------------- --------------- ---------------- ------------- ------------ -------------------- --------
                tstdb1_stdby1   LOCAL      VALID    OPEN_READ-ONLY  MANAGED REAL TI                1           574            0 STATUS NOT AVAILABLE
                                                                    ME APPLY


tstdb1          tstdb1          UNKNOWN    VALID    UNKNOWN         IDLE                           0             0            0 STATUS NOT AVAILABLE NO GAP
tstdb1_stdby2   tstdb1_stdby2   PHYSICAL   VALID    OPEN_READ-ONLY  MANAGED REAL TI                1           574          333 STATUS NOT AVAILABLE NO GAP
                                                                    ME APPLY


                NONE            UNKNOWN    VALID    UNKNOWN         IDLE                           1           574          574 STATUS NOT AVAILABLE


---tstdb1_stdby2：SRLs没有处于ACTIVE状态的
SQL> col member format a50
SQL> col status format a10
SQL> set linesize 130
select f.group#,f.member,l.status from v$logfile f,v$standby_log l where f.group#=l.group# order by group#;SQL> 
    GROUP# MEMBER                                             ARC STATUS
---------- -------------------------------------------------- --- ----------
        11 /oradata06/teststdby2/testaaaaa/stdredo01a.log     NO  UNASSIGNED
        12 /oradata06/teststdby2/testaaaaa/stdredo02a.log     NO  UNASSIGNED
        13 /oradata06/teststdby2/testaaaaa/stdredo03a.log     YES UNASSIGNED
        14 /oradata06/teststdby2/testaaaaa/stdredo04a.log     YES UNASSIGNED


###在tstdb1上进行DML操作，观察到tstdb1_stdby2的结果滞后于tstdb1、tstdb1_stdby1，直到tstdb1执行下一次switch logfile，tstdb1_stdby2的结果才会更新
---tstdb1:
update scott.t0930_1 set username='AAA' where user_id=141;
commit;


SYS@tstdb1-SQL> select * from scott.t0930_1 where user_id=141;


USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
AAA                                   141 20150130 16:21:49


---tstdb1_stdby1:
SQL> select * from scott.t0930_1 where user_id=141;


USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
AAA                                   141 20150130 16:21:49


---tstdb1_stdby2:
SQL> select * from scott.t0930_1 where user_id=141;


USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
FFF                                   141 20150130 16:21:49


---tstdb1:
alter system switch logfile;


---tstdb1_stdby2:
SQL> select * from scott.t0930_1 where user_id=141;




USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
AAA                                   141 20150130 16:21:49


###执行switchover使tstdb1、tstdb1_stdby1的角色发生互换
---tstdb1:
alter database commit to switchover to physical standby with session shutdown;
shutdown abort
startup
alter database recover managed standby database using current logfile disconnect;


---tstdb1_stdby1:
alter database commit to switchover to primary with session shutdown;
alter database open;


switchover后DG的状态变为了
prmy:tstdb1_stdby1
cascading standby: tstdb1
cascaded standby:tstdb1_stdby2


###验证一下tstdb1_stdby2能否继续从tstdb1_stdby1接收archivelog
---tstdb1_stdby1:
update scott.t0930_1 set username='BBB' where user_id=141;
commit;


select * from scott.t0930_1 where user_id=141;
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
BBB                                   141 20150130 16:21:49


---tstdb1:
select * from scott.t0930_1 where user_id=141;
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
BBB                                   141 20150130 16:21:49


---tstdb1_stdby2:
select * from scott.t0930_1 where user_id=141;
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
AAA                                   141 20150130 16:21:49


---tstdb1_stdby1:
alter system switch logfile;


---tstdb1_stdby2:
select * from scott.t0930_1 where user_id=141;
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
BBB                                   141 20150130 16:21:49

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

转载于:http://blog.itpub.net/53956/viewspace-1813396/