一、需求，前段时间，墨天伦有个小伙伴咨询了这个问题，搞了测试环境测试下。

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

巴特西

Oracle-DG 主库将log_archive_dest_state_2远程归档线程参数设置为defer,为什么dg还是处于实时同步状态？

一、需求，前段时间，墨天伦有个小伙伴咨询了这个问题，搞了测试环境测试下。

二、测试

2.1 正常同步

2.2 远程归档线程参数置为defer

2.3 什么情况下，defer+什么才能让主备之间数据同步中断！

最新文章

热门文章