OGG 抽取程序註冊時報OGG-08221 ORA-00001錯誤總結

潇湘隐者發表於2024-03-22

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]

1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=390982163154682&id=2861271.1&_afrWindowMode=0&_adf.ctrl-state=dq53pb6ig_80

相關文章