模拟同步失败

备库创建唯一性索引

SQL> create unique index idx_scott_t_1 on scott.t(user_id); Index created.

主库插入重复数据

SQL> insert into scott.t select * from scott.t;

36 rows created. SQL> commit; Commit complete.

备库自动停止同步

Mon Sep 29 17:22:32 2014 LOGSTDBY: SQL Apply about to stop with ORA-1 LOGSTDBY: SQL Apply finished logging error information LOGSTDBY Apply process AS02 server id=2 pid=41 OS id=2535 stopped Errors in file  /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_as02_2535.trc: ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated Mon Sep 29 17:22:36 2014 Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_lsp0_2507.trc: ORA-26808: Apply process AS02 died unexpectedly. ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated Mon Sep 29 17:22:36 2014 LOGSTDBY Analyzer process AS00 server id=0 pid=39 OS id=2531 stopped Mon Sep 29 17:22:36 2014 LOGSTDBY Apply process AS01 server id=1 pid=40 OS id=2533 stopped Mon Sep 29 17:22:36 2014 LOGSTDBY Apply process AS03 server id=3 pid=42 OS id=2537 stopped Mon Sep 29 17:22:36 2014 LOGSTDBY Apply process AS04 server id=4 pid=43 OS id=2539 stopped Mon Sep 29 17:22:36 2014 LOGSTDBY Apply process AS05 server id=5 pid=44 OS id=2541 stopped Mon Sep 29 17:22:40 2014 LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2518 sid=46 stopped Mon Sep 29 17:22:40 2014 LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2514 sid=44 stopped Mon Sep 29 17:22:40 2014 LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2516 sid=34 stopped LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 1114702, ResetLogScn 995548 LOGMINER: summary for session# = 1 LOGMINER: StartScn: 0 (0x0000.00000000) LOGMINER: EndScn: 0 (0x0000.00000000) LOGMINER: HighConsumedScn: 1114646 (0x0000.00110216) LOGMINER: session_flag: 0x1 LOGMINER: Read buffers: 16 Mon Sep 29 17:22:43 2014 LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2554 sid=34 started Mon Sep 29 17:22:43 2014 LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2556 sid=44 started Mon Sep 29 17:22:43 2014 LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2558 sid=45 started LOGMINER: Turning ON Log Auto Delete Mon Sep 29 17:22:45 2014 LOGSTDBY Analyzer process AS00 started with server id=0 pid=40 OS id=2560 Mon Sep 29 17:22:45 2014 LOGSTDBY Apply process AS01 started with server id=1 pid=41 OS id=2562 Mon Sep 29 17:22:45 2014 LOGSTDBY Apply process AS04 started with server id=4 pid=44 OS id=2568 Mon Sep 29 17:22:45 2014 Mon Sep 29 17:22:45 2014 LOGSTDBY Apply process AS05 started with server id=5 pid=45 OS id=2570 Mon Sep 29 17:22:45 2014 LOGSTDBY Apply process AS03 started with server id=3 pid=43 OS id=2566 LOGSTDBY Apply process AS02 started with server id=2 pid=42 OS id=2564 LOGMINER: Begin mining logfile for session 1 thread 1 sequence 42, /u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_4_b2l8vov0_.log LOGSTDBY: SQL Apply about to stop with ORA-1 LOGSTDBY: SQL Apply finished logging error information LOGSTDBY Apply process AS01 server id=1 pid=41 OS id=2562 stopped Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_lsp0_2507.trc: ORA-26808: Apply process AS01 died unexpectedly. ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated LOGSTDBY Analyzer process AS00 server id=0 pid=40 OS id=2560 stopped LOGSTDBY Apply process AS02 server id=2 pid=42 OS id=2564 stopped LOGSTDBY Apply process AS03 server id=3 pid=43 OS id=2566 stopped LOGSTDBY Apply process AS04 server id=4 pid=44 OS id=2568 stopped LOGSTDBY Apply process AS05 server id=5 pid=45 OS id=2570 stopped Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_as01_2562.trc: ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=2554 sid=34 stopped LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=2558 sid=45 stopped LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=2556 sid=44 stopped

trace文件/u01/app/oracle/diag/rdbms/orcl2/orcl/trace/orcl_as02_2535.trc里有这个事务相关信息

ERROR INFORMATION: PRIMARY TXN xid: 0x0003.003.0000033b ( XIDUSN = 3, XIDSLT = 3, XIDSQN = 827) start scn: 0x0000.0011024e (1114702) commit scn: 0x0000.00110250 (1114704)

视图里有可读性强的错误信息

SQL> select event, status from DBA_LOGSTDBY_EVENTS; EVENT                            STATUS -------------------------------- ----------------------------------------------------------- insert into "SCOTT"."T"          ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated values     "USERNAME" = 'SYS',     "USER_ID" = 0,     "CREATED" = '17-SEP-11'

。。。。。。。。。。。

根据上面的xidusn，xidslt，xidsqn跳过事务

SQL> exec dbms_logstdby.skip_transaction ( 3,  3,  827); PL/SQL procedure successfully completed.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered.

或者直接从视图里拼出语句

SQL> select event, status, 'exec dbms_logstdby.skip_transaction ('||XIDUSN||', '||XIDSLT||', '||XIDSQN||');' from dba_logstdby_events where XIDUSN is not null; EVENT              STATUS -------------------------------------------------- ---------------------------------------------------------------------------------------------------- 'EXECDBMS_LOGSTDBY.SKIP_TRANSACTION('||XIDUSN||','||XIDSLT||','||XIDSQN||');' ------------------------------------------------------------------------------------------------------------------------------------------------------------------- insert into "SCOTT"."T"          ORA-00001: unique constraint (SYS.IDX_SCOTT_T_1) violated values     "USERNAME" = 'SYS',     "USER_ID" = 0,     "CREATED" = '17-SEP-11' exec dbms_logstdby.skip_transaction (3, 3, 827);

。。。。。。。。。。。。。

还有一个办法，就是全同步单表

1 在备库上创建到主库的dblink，要求dblink在主库那边的用户能够读表、锁表及SELECT_CATALOG_ROLE

create database link dg_orcl connect to system identified by oracle using 'dg_orcl';   

2 调用存储过程

exec dbms_logstdby.instantiate_table('SCOTT', 'T', 'DG_ORCL');