RheoData Blog | dba

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.

dba_queues

Clean up old Extracts

Recent Posts

Beyond 'db_owner': Securing Your GoldenGate Extracts the Right Way

Don’t Retire Your Oracle Experts—Supercharge Them with Google Cloud

Six Levers for Snowflake Performance: Faster Queries and Lower Costs

Posts by Tag

Links