OGG: Not Able To Register Extract After DB Refresh

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)