逻辑standby手动同步后引起主键重复导致实时应用停止一例

    昨天又因为用户手动进行大事务处理而使逻辑standby数据库同步异常慢，于是又手动同步了一下这些表，没想到今天早上一来检查发现数据库居然没有同步生产库，然后手动启动实时同步应用后没几秒又自动停止了，于是查看了一下日志文件。

   Wed Jun 29 10:10:14 2011
LOGSTDBY status: ORA-00001: unique constraint (ACC.PK_CLEARING_TRADE_201106) violated
LOGSTDBY Apply process P005 pid=30 OS id=622662 stopped
Wed Jun 29 10:10:14 2011
Errors in file /oracle/product/10.2.0/admin/his/bdump/his1_lsp0_1175684.trc:
ORA-12801: error signaled in parallel query server P005, instance his01:his1 (1)
ORA-00001: unique constraint (ACC.PK_CLEARING_TRADE_201106) violated
LOGSTDBY Apply process P006 pid=31 OS id=667898 stopped
LOGSTDBY Apply process P008 pid=33 OS id=1155198 stopped
LOGSTDBY Apply process P010 pid=35 OS id=995528 stopped
LOGSTDBY Apply process P009 pid=34 OS id=856312 stopped
LOGSTDBY Apply process P007 pid=32 OS id=671940 stopped
LOGSTDBY Apply process P012 pid=37 OS id=663662 stopped
LOGSTDBY Apply process P011 pid=36 OS id=700582 stopped
LOGSTDBY Analyzer process P004 pid=29 OS id=639226 stopped
Wed Jun 29 10:10:14 2011
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action
LOGSTDBY status: ORA-16111: log mining and apply setting up
Wed Jun 29 10:10:14 2011
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 4, Transaction Chunk Size = 1000
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: session# = 1, builder process P001 started with pid=26 OS id=1171624
LOGMINER: session# = 1, preparer process P003 started with pid=28 OS id=176296
LOGMINER: session# = 1, reader process P000 started with pid=25 OS id=1122546
LOGMINER: session# = 1, preparer process P002 started with pid=27 OS id=811134
Wed Jun 29 10:10:14 2011
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 3401, /archnfs/1_3401_715014709.arc
LOGSTDBY Apply process P005 started with pid=30 OS id=622662
LOGSTDBY Apply process P009 started with pid=34 OS id=856312
LOGSTDBY Apply process P006 started with pid=31 OS id=667898
LOGSTDBY Apply process P008 started with pid=33 OS id=1155198
LOGSTDBY Analyzer process P004 started with pid=29 OS id=639226
Wed Jun 29 10:10:14 2011
LOGMINER: Begin mining logfile for session 1 thread 2 sequence 4215, /archnfs/2_4215_715014709.arc
Wed Jun 29 10:10:14 2011
LOGMINER: Turning ON Log Auto Delete
LOGSTDBY Apply process P010 started with pid=35 OS id=995528
LOGSTDBY Apply process P011 started with pid=36 OS id=700582
LOGSTDBY Apply process P007 started with pid=32 OS id=671940
LOGSTDBY Apply process P012 started with pid=37 OS id=663662
Wed Jun 29 10:10:20 2011
LOGSTDBY status: ORA-00001: unique constraint (ACC.PK_CLEARING_TRADE_201106) violated
LOGSTDBY Apply process P005 pid=30 OS id=622662 stopped
Wed Jun 29 10:10:21 2011
Errors in file /oracle/product/10.2.0/admin/his/bdump/his1_lsp0_1175684.trc:
ORA-12801: error signaled in parallel query server P005, instance his01:his1 (1)
ORA-00001: unique constraint (ACC.PK_CLEARING_TRADE_201106) violated
LOGSTDBY Apply process P009 pid=34 OS id=856312 stopped
LOGSTDBY Analyzer process P004 pid=29 OS id=639226 stopped
LOGSTDBY Apply process P007 pid=32 OS id=671940 stopped
LOGSTDBY Apply process P006 pid=31 OS id=667898 stopped
LOGSTDBY Apply process P008 pid=33 OS id=1155198 stopped
LOGSTDBY Apply process P011 pid=36 OS id=700582 stopped
LOGSTDBY Apply process P012 pid=37 OS id=663662 stopped
LOGSTDBY Apply process P010 pid=35 OS id=995528 stopped

 

    原来是主键复复了，想了一下昨天的操作，我是先把备库的记录按条件删除，然后再从生产库查询出来插入到备库来同步的，但在恢复这些表的同步关系后估计是还有相应的日志文件没应用完，然后开启实时应用服务的时候又在应用这些日志，从而导致主键重复而不能插入数据。这样就自动把实时应用给停了。

    知道了原因后处理起来就简单了，把产生生产库数据的所有程序都停止后，实际上这会生产库不会再产生这些表的数据，然后跳过有问题的表不让这些表同步，开启实时应用让所有的归档日志文件完全应用完后再手动同步数据库的这些表，再恢复这些表的同步关系就可以了。