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.

  2. gopikrish_6@yahoo.co.in'karthik says

    Here another issue RMAN deleted the archives later restored it and register with database too using below commands.

    Still OGG are reading old sequencs #534551 now OGG as running stage but it cant move ( data not getting transfer from source to target)

    The extract was struck on the below archive

    thread_1_seq_534551.3076.986126299

    GGSCI (icdushalmodb01 as [email protected]) 63> info EX_P01 showch debug

    EXTRACT EX_P01 Last Started 2018-09-06 21:53 Status RUNNING
    Checkpoint Lag 08:22:09 (updated 86:50:39 ago)
    Process ID 15844
    Log Read Checkpoint Oracle Integrated Redo Logs
    2018-09-02 22:43:00 Seqno 534551, RBA 953914540
    SCN 1.1687742197 (5982709493)

    You re-stored and registered the archives from 534551 till 534740.Still not seeing any data movement for the extract

Leave a Reply

Your email address will not be published.