primary同步数据到逻辑standby时报违反唯一约束(ora-00001)导致逻辑standby同步停止
来源:互联网 发布:犀牛软件 垃圾袋 编辑:程序博客网 时间:2024/05/20 14:42
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;
0 0
- primary同步数据到逻辑standby时报违反唯一约束(ora-00001)导致逻辑standby同步停止
- primary主库上创建表空间无法同步到物理standby和逻辑standby
- 逻辑 standby
- 建立逻辑standby
- DG - 逻辑standby管理
- DG - 逻辑Standby创建
- 逻辑Standby之failover
- dataguard 创建逻辑STANDBY
- 创建逻辑standby数据库
- logical standby 同步缓慢
- 逻辑standby的高级管理
- DG - 逻辑standby角色转换
- standby log的异常状态导致DG数据同步异常显示failure destination
- 物理standby转换为逻辑standby的步骤
- 创建逻辑备用库(Logical Standby Database)
- oracle 10g 逻辑standby 切换步骤
- 不能在逻辑STANDBY上创建JOB
- 配置DG为逻辑standby模式
- Netty数据包大小的确定
- opencv 学习之 亮度检测
- Netty使用protobuf作为通信协议
- Netty中文件上传的实现
- Netty的超时机制
- primary同步数据到逻辑standby时报违反唯一约束(ora-00001)导致逻辑standby同步停止
- Netty中队列(部分)的处理机制
- 阻塞式Http Server与非阻塞式(异步)HttpServer 示例(基于apache http包构建)
- Lua学习笔记
- 希特勒的演讲
- hdu 1907——John & hdu 2509——Be the Winner
- k-近邻算法的初步研究
- 关联映射
- Lua与Java相互交互的例子