Oracle 19C OGG基础运维-08Error code [942]
Oracle 19C OGG基础运维-08Error code [942]
问题：
源端：删除dept_bak表
目标端：在数据同步时找不到dept_bak表，进程ABENDING，同步中断。
错误日志如下：
Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist], no error handler present.
目标端详细日志如下：
[oracle@cjcos02 ogg]$ tail -f ggserr.log
2020-04-10T20:48:13.291+0800 INFO OGG-00484 Oracle GoldenGate Delivery for Oracle, rep_01.prm: Executing DDL operation.
2020-04-10T20:48:13.919+0800 ERROR OGG-00519 Oracle GoldenGate Delivery for Oracle, rep_01.prm: Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist], no error handler present.
2020-04-10T20:48:20.064+0800 INFO OGG-02333 Oracle GoldenGate Delivery for Oracle, rep_01.prm: Reading /ogg/dirdat/dp000000004, current RBA 10,276, 0 records, m_file_seqno = 4, m_file_rba = 11,586.
2020-04-10T20:48:20.065+0800 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_01.prm: PROCESS ABENDING.
查看进程状态
GGSCI (cjcos02) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REP_01 00:03:15 00:22:09
重启进程问题依旧存在
GGSCI (cjcos02) 17> stop rep_01
REPLICAT REP_01 is already stopped.
GGSCI (cjcos02) 17> start rep_01
GGSCI (cjcos02) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REP_01 00:03:15 00:22:09
问题原因：
一开始源端extract进程和目标端replicat进程参数文件中没有通过*来匹配所有表，而是指定了固定的表。
例如：
---extract
TABLE cjcpdb.cjc.emp;
TABLE cjcpdb.cjc.dept;
TABLE cjcpdb.cjc.bonus;
TABLE cjcpdb.cjc.salgrade;
TABLE cjcpdb.cjc.dummy;
---replicat
MAP cjcpdb.cjc.emp, TARGET chenpdb.chen.emp;
MAP cjcpdb.cjc.dept, TARGET chenpdb.chen.dept;
MAP cjcpdb.cjc.bonus, TARGET chenpdb.chen.bonus;
MAP cjcpdb.cjc.salgrade, TARGET chenpdb.chen.salgrade;
MAP cjcpdb.cjc.dummy, TARGET chenpdb.chen.dummy;
再此期间在源端创建了一张测试表dept_bak;
源端：
create table dept_bak as select * from dept;
由于之前的配置，这个操作不会同步到目标端。
后来，将源端extract进程和目标端replicat进程参数文件修改为通过*来匹配用户下所有表。
例如：
---extract
TABLE cjcpdb.cjc.*;
---replicat
MAP cjcpdb.cjc.*, TARGET chenpdb.chen.*;
重启extract和relicat进程后，发现源端比目标端多了一张测试表dept_bak，
在源端手动删除这张表，由于目标端没有这张表，drop命令在同步到目标端时发生错误提示找不到表，最终导致目标端恢复进程replicat挂起。
解决方案：目标端修改参数，跳过942错误
GGSCI (cjcos02) 19> edit param rep_01
GGSCI (cjcos02) 20> view param rep_01
replicat rep_01
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg@chenpdb,password oracle
assumetargetdefs
reperror default,discard
discardfile ./dirrpt/replzl.dsc,append,megabytes 50
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR 942 IGNORE
MAP cjcpdb.cjc.*, TARGET chenpdb.chen.*;
---MAP cjcpdb.cjc.emp, TARGET chenpdb.chen.emp;
---MAP cjcpdb.cjc.dept, TARGET chenpdb.chen.dept;
---MAP cjcpdb.cjc.bonus, TARGET chenpdb.chen.bonus;
---MAP cjcpdb.cjc.salgrade, TARGET chenpdb.chen.salgrade;
---MAP cjcpdb.cjc.dummy, TARGET chenpdb.chen.dummy;
重启replicat进程后恢复正常
GGSCI (cjcos02) 23> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_01 00:00:00 00:00:02
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
