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
原文地址:https://www.cnblogs.com/lvcha001/p/14679252.html