Oracle-DG 主庫將log_archive_dest_state_2遠程歸檔執行緒參數設置為defer,為什麼dg還是處於實時同步狀態？
一、需求，前段時間，墨天倫有個小夥伴諮詢了這個問題，搞了測試環境測試下。
Oracle-DG 主庫將log_archive_dest_state_2遠程歸檔執行緒參數設置為defer,為什麼dg還是處於實時同步狀態？
按照小夥伴的預期，正常情況下，此時DG連通性已經中斷。
二、測試
2.1 正常同步
Primary
SQL> create table b(id int); SQL> select dest_name,status,recovery_mode from v$archive_dest_status where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS RECOVERY_MODE ------------------------------ ------------------ ---------------------------------------------- LOG_ARCHIVE_DEST_2 VALID MANAGED REAL TIME APPLY
Standby SQL> select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby; PROCESS CLIENT_PROCESS SEQUENCE# STATUS BLOCK# BLOCKS ------------------ ---------------- ---------- ------------------------ ---------- ---------- MRP0 N/A 87 APPLYING_LOG 9 409600 SQL> select count(*) from b; COUNT(*) ---------- 0 # ps -ef|grep LOCAL=NO oracle 49788 1 0 06:45 ? 00:00:00 oraclec12 (LOCAL=NO) oracle 49792 1 0 06:45 ? 00:00:00 oraclec12 (LOCAL=NO) oracle 49794 1 0 06:45 ? 00:00:00 oraclec12 (LOCAL=NO) oracle 49799 1 0 06:45 ? 00:00:01 oraclec12 (LOCAL=NO) SQL> select s.sid,s.serial#,p.program,s.username,p.username,p.background,s.program,s.LAST_CALL_ET,s.LOGON_TIME,s.status from v$process p,v$session s where p.addr=s.paddr and p.spid in(49788,49792,49794,49799); SID SERIAL# PROGRAM USERNAME USERNAME BA PROGRAM LAST_CALL_ET LOGON_TIME STATUS ---------- ---------- ------------------------- ---------- ---------- -- ------------------------- ------------ ------------------- ---------------- 32 64176 oracle@c12 PUBLIC oracle oracle@c11 (TNS V1-V3) 0 2021-04-20 06:45:46 INACTIVE 34 13426 oracle@c12 PUBLIC oracle oracle@c11 (TNS V1-V3) 21 2021-04-20 06:45:42 INACTIVE 47 151 oracle@c12 PUBLIC oracle oracle@c11 (TNS V1-V3) 1042 2021-04-20 06:45:42 INACTIVE 49 10360 oracle@c12 PUBLIC oracle oracle@c11 (TNS V1-V3) 1041 2021-04-20 06:45:43 INACTIVE
2.2 遠程歸檔執行緒參數置為defer
alter system set log_archive_dest_state_2=defer; SQL> insert into b values(1); 1 row created. SQL> commit; SQL> select dest_name,status,recovery_mode from v$archive_dest_status where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS RECOVERY_MODE ------------------------------ ------------------ ---------------------------------------------- LOG_ARCHIVE_DEST_2 DEFERRED MANAGED REAL TIME APPLY # ps -ef|grep LOCAL=NO oracle 49788 1 0 06:45 ? 00:00:00 oraclec12 (LOCAL=NO) oracle 49792 1 0 06:45 ? 00:00:00 oraclec12 (LOCAL=NO) oracle 49794 1 0 06:45 ? 00:00:00 oraclec12 (LOCAL=NO) oracle 49799 1 0 06:45 ? 00:00:01 oraclec12 (LOCAL=NO) SID SERIAL# PROGRAM USERNAME USERNAME BA PROGRAM LAST_CALL_ET LOGON_TIME STATUS ---------- ---------- ------------------------- ---------- ---------- -- ------------------------- ------------ ------------------- ---------------- 32 64176 oracle@c12 PUBLIC oracle oracle@c11 (TNS V1-V3) 1 2021-04-20 06:45:46 INACTIVE 47 151 oracle@c12 PUBLIC oracle oracle@c11 (TNS V1-V3) 1098 2021-04-20 06:45:42 INACTIVE 49 10360 oracle@c12 PUBLIC oracle oracle@c11 (TNS V1-V3) 1097 2021-04-20 06:45:43 INACTIVE SYS@c12>select count(*) from b; COUNT(*) ---------- 1 SQL> alter system switch logfile; SQL> select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 87 1 SQL> truncate table b; dg無影響！ 主要原因是主備之間的session並未斷開,dg根據已經建立的主備連接session進行數據傳輸，因此單純關閉這個模式dg 還是無法斷開同步！
2.3 什麼情況下，defer+什麼才能讓主備之間數據同步中斷！
Standby $kill -- LOCAL=NO 的主庫發起的遠程session
$ lsnrctl stop
Primary SQL> alter system switch logfile; SQL> insert into b values(1); SQL> commit; SQL> alter system switch logfile; DEST_NAME STATUS RECOVERY_MODE ---------------------------------------- ------------------ ---------------------------------------------- LOG_ARCHIVE_DEST_1 VALID IDLE LOG_ARCHIVE_DEST_2 DEFERRED IDLE
Standby
SYS@c12>select * from b; 此時數據已經不同步了！ 也就是說 kill 主備之間已連接的session,參數defer是禁用重新發起的連接，但是不對已有連接處理。
no rows selected
如何恢復？
Primary
alter system set log_archive_dest_state_2=enable;
DEST_ID ERROR
-------------------
2 ORA-12541: TNS:no listener
$ lsnrctl start
alter system set log_archive_dest_state_2=defer;
alter system set log_archive_dest_state_2=enable;
SQL> alter system switch logfile;
Standby
SQL> select * from b;
ID
----------
1
