转载

在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

正文到此结束