After database is refreshed, the following errors occur:
GGSCI> register extract DTCAP database
2024-05-16 01:12:23 ERROR OGG-08221 Cannot register or unregister EXTRACT DTCAP because of the following SQL error: OCI Error ORA (status = 26668-ORA-26668: GoldenGate process OGG$DTCAP exists
ORA-06512: at "SYS.DBMS_XSTREAM_GG_ADM", line 145
ORA-06512: at "SYS.DBMS_XSTREAM_GG_ADM", line 186
ORA-06512: at line 1
).
This is because dictionary tables were not cleaned up properly when OGG process was removed.
set linesize 130 col session_name format a20 col global_db_name format a45 SQL> select SESSION#,CLIENT#,SESSION_NAME,DB_ID,GLOBAL_DB_NAME from system.LOGMNR_SESSION$; SESSION# CLIENT# SESSION_NAME DB_ID GLOBAL_DB_NAME ---------- ------- ------------ ---------- --------- 1 0 OGG$CAP_DTCAP 576966795 PROD <----OLD PROD DB
Because current DB ID is different from old PROD database one:
SQL> select name, dbid from v$database;
NAME DBID
--------- ----------
TEST 2095324014
Drop the Extract
SQL>SELECT CAPTURE_NAME, CAPTURE_TYPE, STATUS, to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN FROM DBA_CAPTURE;
CAPTURE_NAME CAPTURE_TY STATUS REQ_SCN OLDEST_SCN
-------------------------------------------------------------------------------------------------------------------------------- ---------- -------- ---------------- ----------------
OGG$CAP_DTCAP LOCAL DISABLED 6217502668722 6215549732638
SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE('OGG$CAP_DTCAP');
PL/SQL procedure successfully completed.
SQL> SELECT CAPTURE_NAME, CAPTURE_TYPE, STATUS, to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN FROM DBA_CAPTURE;
no rows selected
Drop queue tables from log miner.
set linesize 100
col owner format a20
col name format a25
col queue_table format a20
select owner, name, queue_table from dba_queues where owner = 'C##OGGADMIN';
OWNER NAME QUEUE_TABLE
-------------------- ------------------------- --------------------
C##OGGADMIN OGG$Q_DTCAP OGG$Q_TAB_DTCAP
C##OGGADMIN AQ$_OGG$Q_TAB_DTCAP_E OGG$Q_TAB_DTCAP
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table =>'C##OGGADMIN.OGG$Q_TAB_DTCAP', force => TRUE);
PL/SQL procedure successfully completed.
SQL> select queue_table, owner from dba_queues where owner ='C##OGGADMIN';
no rows selected
OR
Stop and clean the queues.
set linesize 100
col owner format a20
col name format a25
col queue_table format a20
select owner, name, queue_table from dba_queues where owner = 'C##OGGADMIN';
OWNER NAME QUEUE_TABLE
-------------------- ------------------------- --------------------
C##OGGADMIN OGG$Q_DTCAP OGG$Q_TAB_DTCAP
C##OGGADMIN AQ$_OGG$Q_TAB_DTCAP_E OGG$Q_TAB_DTCAP
BEGIN
DBMS_AQADM.STOP_QUEUE(queue_name => 'C##OGGADMIN.OGG$Q_DTCAP');
DBMS_AQADM.STOP_QUEUE(queue_name => 'C##OGGADMIN.AQ$_OGG$Q_TAB_DTCAP_E');
DBMS_AQADM.DROP_QUEUE(queue_name => 'C##OGGADMIN.OGG$Q_DTCAP');
DBMS_AQADM.DROP_QUEUE(queue_name => 'C##OGGADMIN.AQ$_OGG$Q_TAB_DTCAP_E');
END;
BEGIN
EXEC DBMS_STREAMS_ADM.REMOVE_QUEUE(
queue_name => C##OGGADMIN.OGG$Q_DTCAP',
cascade => true,
drop_unused_queue_table => true);
END;
/
BEGIN
EXEC DBMS_STREAMS_ADM.REMOVE_QUEUE( queue_name => 'C##OGGADMIN.AQ$_OGG$Q_TAB_DTCAP_E',
cascade => true,
drop_unused_queue_table => true);
END;
/
SQL> select queue_table, owner from dba_queues where owner ='C##OGGADMIN';
no rows selected
SQL> select SESSION#,CLIENT#,SESSION_NAME,DB_ID,GLOBAL_DB_NAME
from system.LOGMNR_SESSION$;
no rows selected
Not Able To Register Extract After DB Refresh (Doc ID 2593741.1)