Cleaning Up Old GoldenGate / Streams Metadata in Oracle

When working with Oracle GoldenGate Integrated Extract, Downstream Mining, or LogMiner Server, we often encounter broken, partially removed, or abandoned Oracle Streams objects. These stale objects can prevent new Extract registration, cause OGG errors (e.g., OGG-00303, OGG-08232), or block mining server creation.

In this post, I’ll walk through a real-world scenario where we cleaned an entire Oracle database of all leftover OGG/Streams objects and restored the environment to a clean state for a fresh GoldenGate deployment.

The same steps apply whether you’re working on:

• Downstream mining
• Integrated Extract
• PDB/CDB OGG deployments
• Troubleshooting broken
• REGISTER EXTRACT operations
• Fixing inconsistent Streams metadata

Check for Existing Capture Processes

SQL>   SELECT capture_name, queue_owner, queue_name, status
FROM dba_capture;
  2
CAPTURE_NAME
--------------------
QUEUE_OWNER
--------------------------------------------------------------------------------
QUEUE_NAME
--------------------------------------------------------------------------------
STATUS
------------
OGG$CAP_EXT_AVUK
C##GGADMIN
OGG$Q_EXT_AVUK
DISABLED

This told us that the capture metadata was removed, but internal objects were still left behind.

Identifying leftover Streams/OGG Objects

SQL> SELECT object_name, object_type FROM dba_objects WHERE object_name LIKE 'OGG$%';

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
OGG$EXT_AVUK         APPLY
OGG$MINCDC           APPLY
OGG$Q_EXT_AVUK       QUEUE
OGG$Q_EXT_AVUK_N     RULE SET
OGG$Q_EXT_AVUK_R     RULE SET
OGG$Q_MINCDC         QUEUE
OGG$Q_MINCDC_N       RULE SET
OGG$Q_MINCDC_R       RULE SET
OGG$Q_TAB_EXT_AVUK   TABLE
OGG$Q_TAB_MINCDC     TABLE

10 rows selected.

These objects are automatically created for Integrated Extract and Downstream Mining, but they do not get cleaned automatically if someone drops Extracts manually or through OGG CLI.

Identifying Queue Ownerships

SQL> SELECT owner, name FROM dba_queues WHERE name IN ('OGG$Q_EXT_AVUK','OGG$Q_MINCDC');

OWNER
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
C##GGADMIN
OGG$Q_EXT_AVUK

C##GGADMIN
OGG$Q_MINCDC

This is important because queue cleanup must be performed from the owner schema.

Drop Queue Tables

SQL> ALTER SESSION SET CURRENT_SCHEMA = C##GGADMIN;
Session altered.
SQL> BEGIN
  DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'C##GGADMIN.OGG$Q_TAB_EXT_AVUK', force => TRUE);
  DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'C##GGADMIN.OGG$Q_TAB_MINCDC', force => TRUE);
END;
/
  2    3    4    5
PL/SQL procedure successfully completed.
SQL> 

This single step removed:

• Queue tables
• AQ queues
• Rule sets
• Apply metadata
• Streams internal plumbing

Now verify still any OGG objects leftover

SQL> SELECT object_name, object_type
FROM dba_objects
WHERE object_name LIKE 'OGG$%';
  2    3
no rows selected

SQL>

After cleaning up now we can able to configure and register the extracts. Below adding few more interesting queries that helps in debugging the issue.

SELECT object_name FROM dba_objects WHERE object_name LIKE 'OGG$%';
SELECT capture_name FROM dba_capture;
SELECT apply_name   FROM dba_apply;
SELECT queue_table  FROM dba_queue_tables WHERE owner='C##GGADMIN';
SELECT name         FROM dba_queues       WHERE owner='C##GGADMIN';
SELECT rule_set_name FROM dba_rule_sets   WHERE owner='C##GGADMIN';

Conclusion

Cleaning up Oracle Streams and GoldenGate metadata is an essential DBA task when working with integrated capture, LogMiner-based mining, or downstream deployments. Leftover OGG$ objects can cause some of the most confusing OGG errors—including failed extract registrations and mining server failures.