Oracle Stream Replication - 删除源库归档日志后出现问题

问题描述:
前段时间因为stream源库hdb库flash_recovery_area需要释放空间, 将归档日志做了部分清理;当时stream的capture和propagation进程都是关闭状态.
今天打开stream后发现,备库itods的HR Schema不同步数据.

问题分析:

1, 查看源库capture,propagation状态
dbms_capture_adm.start_capture,dbms_propagation_adm.start_propagation开启capture和propagation后,数据库一直没有同步数据,此时一直显示如下,capture_scn为3907982,于是决定重启实例.

select CAPTURE_NAME,CAPTURE_USER,START_SCN,CAPTURED_SCN,APPLIED_SCN,MAX_CHECKPOINT_SCN,status from dba_capture;

CAPTURE_NAME CAPTURE_USER START_SCN CAPTURED_SCN APPLIED_SCN MAX_CHECKPOINT_SCNSTATUS
------------ --------------------- ------------  ----------   ------------------ ----------
CAPTURE_HDB  STRMADMIN    1364525  3907982     3907982      3900541            ENABLED

重启实例后,capture状态如下.
SQL>select CAPTURE_NAME,CAPTURE_USER,START_SCN,CAPTURED_SCN,APPLIED_SCN,MAX_CHECKPOINT_SCN,status from dba_capture;

CAPTURE_NAME CAPTURE_USER START_SCN CAPTURED_SCN APPLIED_SCN MAX_CHECKPOINT_SCN STATUS
------------ ------------ --------- ----------- ----------- ------------------ --------
CAPTURE_HDB  STRMADMIN    1364525   1364525     3907982      3900541            ENABLED

发现此时capture_scn已经变成了起始的scn,数据亦无法同步.

2, 紧接着目标库的apply进程也出现终止.
SQL> select apply_name,ERROR_MESSAGE,status from dba_apply;

APPLY_NAME           ERROR_MESSAGE                                                           STATUS
-------------------- ----------------------------------------------------------------------- --------
HR_ITODS_APPLY       ORA-12801: error signaled in parallel query server P000                ABORTED           
                     ORA-00001: unique constraint (SYS.I_STREAMS_APPLY_SPILL_TXN) violated

目标库alter日志
Streams Apply Reader P000 pid=21 OS id=18973 stopped
Tue May 15 16:29:33 2012
Errors in file /u01/app/oracle/admin/itods/bdump/itods_p000_18973.trc:
ORA-00001: unique constraint (SYS.I_STREAMS_APPLY_SPILL_TXN) violated
Streams Apply Server P001 pid=22 OS id=18975 stopped
Tue May 15 16:29:33 2012
Errors in file /u01/app/oracle/admin/itods/bdump/itods_p001_18975.trc:
ORA-10388: parallel query server interrupt (failure)
Tue May 15 16:29:35 2012
Streams APPLY A001 with pid=19, OS id=18969 stopped
Tue May 15 16:29:35 2012
Errors in file /u01/app/oracle/admin/itods/bdump/itods_a001_18969.trc:
ORA-12801: error signaled in parallel query server P000
ORA-00001: unique constraint (SYS.I_STREAMS_APPLY_SPILL_TXN) violated

3, 使用streams_hc_10GR2.sql工具诊断
诊断发现,capture抓取到118号日志/u01/app/oracle/flash_recovery_area /HDB/archivelog/2012_03_31/o1_mf_1_ 118_%u_.arc(SCN区间为3888330-3908020),而3907982正好是位于这个日志文件之中.

4, 查看归档日志.可以看见
SQL> select name,sequence#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log
NAME                                                                        SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------------------------------------------------------------------------------------- --------- ------------- ------------
                                                                                           46       1364518      1364525
                                                                                           47       1364525      1365492
                                                                                           48       1365492      1392182
....省略
                                                                                          118       3888330      3908020
                                                                                          119       3908020      3923000
....省略这段蓝色(119-143)日志,Stream无法使用到!
                                                                                         139       4501308      4538548
                                                                                          140       4538548      4570636
                                                                                          141       4570636      4610435
                                                                                          142       4610435      4649364
                                                                                          143       4649364      4673731
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_04_14/o1_mf_1_144_7rkmc4yq_.arc   144       4673731      4729117
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_04_14/o1_mf_1_145_7rldowkv_.arc   145       4729117      4757728
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_04_17/o1_mf_1_146_7rson60m_.arc   146       4757728      4786636
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_04_17/o1_mf_1_147_7rtlqf7n_.arc   147       4786636      4822440
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_04_18/o1_mf_1_148_7rw4ks7r_.arc   148       4822440      4876951
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_04_18/o1_mf_1_149_7rwq1h66_.arc   149       4876951      4902440
....省略
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_05_15/o1_mf_1_177_7v3xpvm1_.arc   177       5297901      5298150
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_05_15/o1_mf_1_178_7v48otkk_.arc   178       5298150      5351175
/u01/app/oracle/flash_recovery_area/HDB/archivelog/2012_05_15/o1_mf_1_179_7v4jp2xm_.arc   179       5351175      5405597

159 rows selected.

5, 总结:删除的归档日志没有被stream的capture,propagation进程应用就被删除了,导致归档日志不完整,出现目标库无法连续应用归档日志.

备注:streams_hc_10GR2.sql该诊断工具在
https://support.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28bmDocType=BULLETIN&bmDocDsrc=KB&bmDocID=273674.1&viewingMode=1143&bmDocTitle=Streams%20Configuration%20Report%20and%20Health%20Check%20Script&from=BOOKMARK%29%29