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;
由于在逻辑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;