Oracle_CDC整理3-参考及错误处理

 

1.  参考文档

http://blog.csdn.net/chentaocba/article/details/7435131

http://www.cnblogs.com/myrunning/p/5329139.html

 

Oracle 10.2 CDC：http://docs.oracle.com/cd/B19306_01/server.102/b14223/cdc.htm

Oracle 11g CDC：http://docs.oracle.com/cd/B28359_01/server.111/b28313/cdc.htm#CHDEHIIE

DBMS_CDC_PUBLISH：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_cdcpub.htm#ARPLS023

DBMS_CDC_SUBSCRIBE：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_cdcsub.htm#ARPLS024

Oracle 11.2 CDC:

https://docs.oracle.com/cd/E11882_01/server.112/e25554/cdc.htm#DWHSG016

 

 

2.  错误处理

2.1.  ORA-31466: 未找到发布内容

执行订阅表信息时，提示ORA-31466：未找到发布内容，排查发现是没有将变更表cdc_employee_info的查询权限赋予订阅者用户cdc_subscriber导致。

解决方法：

SQL> conn cdc_publisher/cdc_publisher

Connected.

SQL> grant select on cdc_employee_info to cdc_subscriber;

Grant succeeded.

2.2.  激活订阅之后，对源表进行操作，捕获不到数据

监控SYS用户的employee_info表，没有出现任何异常，就是捕获不到数据，后来我替换给backupuser用户的employee_info表，按照以上步骤操作，就能正常捕获到数据了，官网也没查到相关的文档说明，很奇怪，还需要进一步研究。

2.3.  ORA-26723 创建变更集报错

报错内容：

ORA-26723: 用户 "SYS" 要求角色 "DV_STREAMS_ADMIN/DV_XSTREAM_ADMIN/DV_GOLDENGATE_ADMIN"

ORA-06512: 在 "SYS.DBMS_LOGREP_UTIL", line 197

ORA-06512: 在 "SYS.DBMS_LOGREP_UTIL", line 240

ORA-06512: 在 "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 391

ORA-06512: 在 line 1

ORA-06512: 在 "SYS.DBMS_CDC_PUBLISH", line 719

ORA-06512: 在 "SYS.DBMS_CDC_PUBLISH", line 927

ORA-06512: 在 line 2

 

处理办法就是关闭dba vault。

1)      关闭数据库；

2)      在所有节点上以oracle用户执行：oracle> chopt disable dv

Writing to /oracle/app/oracle/product/11.2.0/install/disable_dv.log...

/usr/bin/make -f /oracle/app/oracle/product/11.2.0/rdbms/lib/ins_rdbms.mk dv_off ORACLE_HOME=/oracle/app/oracle/product/11.2.0

/usr/bin/make -f /oracle/app/oracle/product/11.2.0/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/11.2.0

2.4.  ORA-31489: DDL_MARKERS 值无效

同步模式中，创建变更记录表，执行报错：

ORA-31489: DDL_MARKERS 值无效

ORA-06512: 在 "SYS.DBMS_CDC_PUBLISH", line 1002

ORA-06512: 在 line 2

处理：

ddl_markers默认是y,但同步得用n;

 

2.5.  启动发布订阅日志显示

数据库alert日志

Logminer Bld: Done

Streams Capture: CDC$C_CDC_SCOTT_TEST CAPTURE Created

CAPTURE CDC$C_CDC_SCOTT_TEST: Start SCN: 1924579 (0x1d5de3.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: First SCN: 1924579 (0x1d5de3.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: Required Checkpoint SCN: 0 (0x0000.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: Captured SCN: 0 (0x0000.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: Applied SCN: 0 (0x0000.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: Capture Type: LOCAL

CAPTURE CDC$C_CDC_SCOTT_TEST: Logminer Id: 1

CAPTURE CDC$C_CDC_SCOTT_TEST: Source Database: ORCL.SITE

knlciAlterCapture: start scn is changed to 1925382 (0x1d6106.00000000) for CAP CDC$C_CDC_SCOTT_TEST.

Thu Feb 08 20:03:32 2018

Streams APPLY AP01 for CDC$A_CDC_SCOTT_TEST started with pid=31, OS id=13370

APPLY CDC$A_CDC_SCOTT_TEST: Apply User: CDC_PUBLISHER

APPLY CDC$A_CDC_SCOTT_TEST: Apply Tag: 0

APPLY CDC$A_CDC_SCOTT_TEST: Parameter Set by User: COMMIT_SERIALIZATION Value: NONE

APPLY CDC$A_CDC_SCOTT_TEST: Parameter Set by User: DISABLE_ON_ERROR Value: Y

APPLY CDC$A_CDC_SCOTT_TEST: Parameter Set by User: DISABLE_ON_LIMIT Value: Y

Thu Feb 08 20:03:32 2018

Streams CAPTURE CP01 for CDC$C_CDC_SCOTT_TEST started with pid=32, OS id=13372

CAPTURE CDC$C_CDC_SCOTT_TEST: Start SCN: 1925382 (0x1d6106.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: First SCN: 1924579 (0x1d5de3.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: Required Checkpoint SCN: 0 (0x0000.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: Captured SCN: 0 (0x0000.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: Applied SCN: 0 (0x0000.00000000)

CAPTURE CDC$C_CDC_SCOTT_TEST: Capture Type: LOCAL

CAPTURE CDC$C_CDC_SCOTT_TEST: Logminer Id: 1

CAPTURE CDC$C_CDC_SCOTT_TEST: Source Database: ORCL.SITE

CAPTURE CDC$C_CDC_SCOTT_TEST: Parameter Set by User: DISABLE_ON_LIMIT Value: Y

CAPTURE CDC$C_CDC_SCOTT_TEST: Parameter Set by User: MAXIMUM_SCN Value: INFINITE

Thu Feb 08 20:03:32 2018

Streams Apply Reader for CDC$A_CDC_SCOTT_TEST started AS01 with pid=41 OS id=13374

Thu Feb 08 20:03:32 2018

Streams Apply Server for CDC$A_CDC_SCOTT_TEST started AS02 with pid=42 OS id=13376

 

Thu Feb 08 20:03:32 2018

Propagation Sender/Receiver (CCA) for Streams Capture  and Apply CDC$A_CDC_SCOTT_TEST with pid=43, OS id=13378 started.

APPLY CDC$A_CDC_SCOTT_TEST: Source Database: NA

APPLY CDC$A_CDC_SCOTT_TEST: Applied Message Number: NA

APPLY CDC$A_CDC_SCOTT_TEST: Message Create Time: NA

First applied SCN for apply w/ object number 91923 and subscriber sequence number 1 is updated to SCN: 0 (0x0000.00000000)

First applied SCN of the Streams path from capture (CDC$C_CDC_SCOTT_TEST) to propagation () to apply (CDC$A_CDC_SCOTT_TEST) is set to SCN: 1924579 (0x1d5de3.00000000)

Streams CAPTURE CP01 for CDC$C_CDC_SCOTT_TEST with pid=32, OS id=13372 is in combined capture and apply mode.

Capture CDC$C_CDC_SCOTT_TEST is handling 1 applies.

Starting persistent Logminer Session with sid = 1 for Streams Capture CDC$C_CDC_SCOTT_TEST

LOGMINER: Parameters summary for session# = 1

LOGMINER: Number of processes = 3, Transaction Chunk Size = 1

LOGMINER: Memory Size = 30M, Checkpoint interval = 1000M

LOGMINER: SpillScn 0, ResetLogScn 1286002

LOGMINER: summary for session# = 1

LOGMINER: StartScn: 1925373 (0x0000.001d60fd)

LOGMINER: EndScn: 0

LOGMINER: HighConsumedScn: 1925382 (0x0000.001d6106)

LOGMINER: session_flag: 0x0

LOGMINER: Read buffers: 16

LOGMINER: Memory LWM: limit 10M, LWM 24M, 80%

LOGMINER: Memory Release Limit: 1M

LOGMINER: LowCkptScn: 0 (0x0000.00000000)

LOGMINER: HighCkptScn: 0 (0x0000.00000000)

LOGMINER: SkipScn: 1924579 (0x0000.001d5de3)

Thu Feb 08 20:03:39 2018

LOGMINER: session#=1 (CDC$C_CDC_SCOTT_TEST), reader MS00 pid=46 OS id=13385 sid=138 started

Thu Feb 08 20:03:39 2018

LOGMINER: session#=1 (CDC$C_CDC_SCOTT_TEST), builder MS01 pid=47 OS id=13387 sid=202 started

Thu Feb 08 20:03:39 2018

LOGMINER: session#=1 (CDC$C_CDC_SCOTT_TEST), preparer MS02 pid=48 OS id=13389 sid=15 started

LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 63, /oracle/app/oracle/fast_recovery_area/ORCL/archivelog/2018_02_08/o1_mf_1_63_f7rh2v7s_.arc