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