OGG部署時,抽取進程(Extract)註冊到資料庫時遇到下麵錯誤: REGISTER EXTRACT ***** DATABASE ERROR OGG-08221 Cannot register or unregister EXTRACT because of the following SQL ...
OGG部署時,抽取進程(Extract)註冊到資料庫時遇到下麵錯誤:
REGISTER EXTRACT ***** DATABASE
ERROR OGG-08221 Cannot register or unregister EXTRACT because of the following SQL error: OCI Error ORA (status = 1-ORA-00001: unique constraint (SYSTEM.LOGMNR_SESSION_UK1) violated
關於這個錯誤,Oracle官方文檔Unable To Register OGG Extract (Doc ID 2861271.1)[1]有相關的介紹和分析,如下所示:
SYMPTOMS
Unable to register OGG extract
GGSCI 5> register extract database container ()
ERROR OGG-08221 Cannot register or unregister EXTRACT because of the following SQL error: OCI Error ORA (status = 1-ORA-00001: unique constraint (SYSTEM.LOGMNR_SESSION_UK1) violated
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 617
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 249
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 589
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_IVK", line 177
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 179
CAUSE
possible clean up required on queue or CAPTURE, APPLY process related to
clean up required for the session created for in logminer tables
SOLUTION
sqlplus c##ggs_owner/
delete from system.logmnr_spill$ where session# = 10;
delete from system.logmnr_age_spill$ where session# = 10;
delete from system.logmnr_log$ where session# = 10;
delete from system.logmnr_restart_ckpt$ where session# = 10;
delete from system.logmnr_restart_ckpt_txinfo$ where session# = 10;
delete from system.logmnr_filter$ where session# = 10;
delete from system.logmnr_parameter$ where session# = 10;
delete from system.logmnr_global$ where session# = 10;
delete from system.logmnr_session$ where session# = 10;
commit;
Then register again
官方文檔解釋,抽取進程REGISTER到資料庫出現失敗的可能原因有下麵兩個:
1:可能是因為進程相關的CAPTURE、APPLY的隊列沒有清理乾凈 2:需要清理乾凈進程創建的與logmnr相關的會話。
千萬不要直接拷貝官方文檔的SQL語句,你首先需要通過下麵SQL找出logmnr相關的會話ID信息
SET LINESIZE 720;
COL SESSION_NAME FOR A16
COL GLOBAL_DB_NAME FOR A12
SELECT SESSION#,CLIENT#,SESSION_NAME,DB_ID,GLOBAL_DB_NAME FROM SYSTEM.LOGMNR_SESSION$;
然後用具體的會話ID替換上面的會話10後,執行SQL語句,然後就可以重新註冊抽取進程了。
sqlplus c##ggs_owner/ --用實際的ogg用戶替換當前用
delete from system.logmnr_spill$ where session# = xxx;
delete from system.logmnr_age_spill$ where session# = xxx;
delete from system.logmnr_log$ where session# = xxx;
delete from system.logmnr_restart_ckpt$ where session# = xxx;
delete from system.logmnr_restart_ckpt_txinfo$ where session# = xxx;
delete from system.logmnr_filter$ where session# = xxx;
delete from system.logmnr_parameter$ where session# = xxx;
delete from system.logmnr_global$ where session# = xxx;
delete from system.logmnr_session$ where session# = xxx;
commit;
參考資料
1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=390982163154682&id=2861271.1&_afrWindowMode=0&_adf.ctrl-state=dq53pb6ig_80