How to Clean Up GoldenGate Integrated Extract if the Delete Extract fails?

When removing an Integrated Extract, it has to first be un-registered from the database.

    ggsci> dblogin userid ogg_user password ****;
    Logged into the database.

    ggsci> stop KC1AULEP
    Process stopped

    ggsci> unregister extract KC1AULEP
    Extract unregistered

    ggsci> delete KC1AULEP
    Extract deleted.

If the process above was not un-registered from the database then it has to be manually cleared using the DBMS_CAPTURE_ADM.DROP_CAPTURE procedure.

First let’s query the DBA_CAPTURE view to identify the processes which still exist in the database but the extracts have already been dropped.

    sqlplus / as sysdba

    SQLPLUS>SELECT capture_name
    FROM dba_capture;
    CAPTURE_NAME
    ——————–
    GGS$CAP_KC1AULEP
    GGS$CAP_KSDAULNO
    GGS$CAP_KSDAULNP
    GGS$CAP_KRETSCOL
    GGS$CAP_KRETNCOL

Cleaning Up GoldenGate Integrated Extract Using DBMS_CAPTURE_ADM.DROP_CAPTURE

Then clean up the Capture process.

    SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE (‘GGS$CAP_KC1AULEP’);
    PL/SQL procedure successfully completed.

    SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE (‘GGS$CAP_KSDAULNO’);
    PL/SQL procedure successfully completed.

    SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE (‘GGS$CAP_KSDAULNP’);
    PL/SQL procedure successfully completed.

    SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE (‘GGS$CAP_KRETSCOL’);
    PL/SQL procedure successfully completed.

    SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE (‘GGS$CAP_KRETNCOL’);
    PL/SQL procedure successfully completed.

The previouly deleted Extract has successfully been dropped from the data dictionary!


Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.

Comments

  1. kumarsaec@gmail.com'Kumaravel Vivekanandam says

    HI Natik,

    This was very useful in fixing the issue I had. I deleted the extract before unregistering it and it was preventing RMAN from deleting the archive logs after backing them up as there was an integration between goldengate and RMAN. I appreciate you sharing your experience.

    Thanks,
    Kumar.

Leave a Reply

Your email address will not be published.