primary同步数据到逻辑standby时报违反唯一约束（ora-00001）导致逻辑standby同步停止

﹏ヽ暗。殇╰゛Y 2022-08-12 03:58 172阅读 0赞

primary同步数据到逻辑standby时报违反唯一约束（ora-00001）导致逻辑standby同步停止  
由于在逻辑standby库中添加一条数据库到表中，在primary端再次添加此条数据，导致primay同步standby应用时报错ORA-00001: 违反唯一约束条件  
如下logical standbay端日志：  
LOGSTDBY Apply process AS05 started with server id=5 pid=40 OS id=5628  
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 78, D:\\ORCL2\\SREDO02.LOG  
Thu Sep 18 14:56:27 2014  
LOGSTDBY Apply process AS03 started with server id=3 pid=38 OS id=1160  
LOGSTDBY: SQL Apply about to stop with ORA-1  
LOGSTDBY: SQL Apply finished logging error information  
LOGSTDBY Apply process AS01 server id=1 pid=36 OS id=7112 stopped  
Thu Sep 18 14:56:31 2014  
Errors in file f:\\app\\administrator\\diag\\rdbms\\orcl2\\orcl2\\trace\\orcl2\_lsp0\_3536.trc:  
ORA-26808: 应用进程 AS01 意外停止。  
ORA-00001: 违反唯一约束条件 (TTS\_FND.FND\_ERRORS\_PK)  
Errors in file f:\\app\\administrator\\diag\\rdbms\\orcl2\\orcl2\\trace\\orcl2\_as01\_7112.trc:  
ORA-00001: 违反唯一约束条件 (TTS\_FND.FND\_ERRORS\_PK)  
LOGSTDBY Apply process AS02 server id=2 pid=37 OS id=6012 stopped  
LOGSTDBY Apply process AS04 server id=4 pid=39 OS id=5288 stopped  
LOGSTDBY Analyzer process AS00 server id=0 pid=35 OS id=6588 stopped  
LOGSTDBY Apply process AS03 server id=3 pid=38 OS id=1160 stopped  
LOGSTDBY Apply process AS05 server id=5 pid=40 OS id=5628 stopped  
LOGMINER: session\#=1, builder MS01 pid=33 OS id=5100 sid=76 stopped  
LOGMINER: session\#=1, reader MS00 pid=19 OS id=5932 sid=200 stopped  
LOGMINER: session\#=1, preparer MS02 pid=34 OS id=6008 sid=134 stopped  
  
应用失败后logical standby停止sql apply 导致其他操作也不能同步，问题解决办法如下：  
1.到表中DBA\_LOGSTDBY\_EVENTS 查询失败操作的sql  
select XIDUSN, XIDSLT, XIDSQN , status , event,event\_time from dba\_logstdby\_events order by event\_time;  
XIDUSN  XIDSLT  XIDSQN   status                                                      event   event\_time      
2          4    1244  "ORA-00001: 违反唯一约束条件 (TTS\_FND.PK\_FND\_REPORT\_VALUES)          2014/9/18 14:33:02  
  
2.在逻辑standby跳过失败的事物  
SQL> exec dbms\_logstdby.skip\_transaction (2,4,1244);  
  
3.因为primay端和logical standby数据我们要都可以改动为了防止以后出现类似的情况，我们跳过表DML操作  
sql> EXECUTE DBMS\_LOGSTDBY.SKIP (stmt => 'DML', schema\_name => 'username', object\_name => 'table\_name');  
  
4.开启实时sql apply  
SQL> alter database start logical standby apply immediate;