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