Clean up old Extracts

For many using Oracle GoldenGate, there is a need to test out the desired configurations in a dev, test, or even a QA environment.  This means there will be times where you will add, change, or delete different aspects of the Oracle GoldenGate processes.  In my test environment for Oracle GoldenGate, I’ve built quite a few Extracts and Replicats that are needed for testing out solutions for customers.  I wasn’t planning on destroying my Oracle GoldenGate Hub, but Oracle had other plans since I was running my hub on OCI (story for another time).

After rebuilding my Oracle GoldenGate Hub, I began to setup my extracts and replicats.  After getting everything setup, nothing would replicat; started to look around and realized that the extract was an integrated extract and there may be some things hung in the database since they were not deleted correctly.

To identify what extracts are still registered with the Oracle Database, you will use the DBA_CAPTURE view.

set linesize 150
col capture_name format a20
select capture_name from dba_capture;

CAPTURE_NAME
——————–
OGG$CAP_L1EXT
OGG$CAP_LCEXT

As you can see, I’ve got two integrated extracts that need to be removed.  Before removing them, it is good to check for any hung log miner session as well.

set linesize 130
col session_name format a20
col global_db_name format a45
select SESSION#,CLIENT#,SESSION_NAME,DB_ID,GLOBAL_DB_NAME from system.LOGMNR_SESSION$;

SESSION# CLIENT# SESSION_NAME DB_ID GLOBAL_DB_NAME
———- ———- ——————– ———- ———————————————
6 0 OGG$CAP_LCEXT 1564695817 RDDEVDB.SUB06171836220.DEMOVNC.ORACLEVCN.COM
7 0 OGG$CAP_L1EXT 1564695817 RDDEVDB.SUB06171836220.DEMOVNC.ORACLEVCN.COM

Now that I know there are hung sessions in the log miner, these need to be cleaned up as well.

The following steps need to be performed to clean up the log miner and the extracts:

1. Drop the extracts

exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG$CAP_LCEXT');
exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG$CAP_L1EXT');

Verify that the extracts have been removed from the DBA_CAPTURE view.

2. Drop queue tables from log minder

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##GGATE';

OWNER NAME QUEUE_TABLE
——————– ————————- ——————–
C##GGATE OGG$Q_IEXT OGG$Q_TAB_IEXT
C##GGATE AQ$_OGG$Q_TAB_IEXT_E OGG$Q_TAB_IEXT
C##GGATE AQ$_OGG$Q_TAB_LCEXT_E OGG$Q_TAB_LCEXT
C##GGATE OGG$Q_LCEXT OGG$Q_TAB_LCEXT
C##GGATE AQ$_OGG$Q_TAB_L1EXT_E OGG$Q_TAB_L1EXT
C##GGATE OGG$Q_L1EXT OGG$Q_TAB_L1EXT

declare
v_queue_name varchar2(60);
begin
for i in (select queue_table, owner from dba_queues where owner = ‘C##GGATE’)
loop
v_queue_name := i.owner||’.’||i.queue_table;
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => v_queue_name, force => TRUE);
end loop;
end;

Verify that the queues that were allocated to the GoldenGate user has been cleaned up by querying the DBA_QUEUES view again.

If all the queues have been cleaned up, creating the extracts needed will succeed.