Thinking Out Loud

May 24, 2016

Using DBMS_STREAMS_ADM To Cleanup GoldenGate

Filed under: 12c,GoldenGate — mdinh @ 5:33 am

This is really messed up. I chose GoldenGate because I did not want to mess around with streams.

When using Integrated Capture or Delivery, then knowing streams is a prerequisites.

Apologies as the format is not pretty.

The QUEUE table was indeed missing and this is what I get for monkeying around.

To resolve the issue –  exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

GGSCI (arrow.localdomain as ggs_admin@hawk) 3> unREGISTER EXTRACT e_hawk DATABASE

2016-05-23 19:16:32  ERROR   OGG-08221  Cannot register or unregister EXTRACT E_HAWK because of the following SQL error: 
OCI Error ORA-24010: QUEUE "GGS_ADMIN"."OGG$Q_E_HAWK" does not exist
ORA-06512: at "SYS.DBMS_APPLY_ADM_INTERNAL", line 468
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 724
ORA-06512: at line 1 (status = 24010).

GGSCI (arrow.localdomain as ggs_admin@hawk) 4> exit


ARROW:(SYS@hawk):PRIMARY> select * from dba_capture;

CAPTURE_NAME                   QUEUE_NAME                     QUEUE_OWNER
------------------------------ ------------------------------ ------------------------------
RULE_SET_NAME                  RULE_SET_OWNER                 CAPTURE_USER
------------------------------ ------------------------------ ------------------------------
 START_SCN STATUS   CAPTURED_SCN APPLIED_SCN USE  FIRST_SCN
---------- -------- ------------ ----------- --- ----------
SOURCE_DATABASE
----------------------------------------------------------------------------------------------------
SOURCE_DBID SOURCE_RESETLOGS_SCN SOURCE_RESETLOGS_TIME LOGMINER_ID NEGATIVE_RULE_SET_NAME
----------- -------------------- --------------------- ----------- ------------------------------
NEGATIVE_RULE_SET_OWNER        MAX_CHECKPOINT_SCN REQUIRED_CHECKPOINT_SCN LOGFILE_ STATUS_CH
------------------------------ ------------------ ----------------------- -------- ---------
ERROR_NUMBER
------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
VERSION                                                          CAPTURE_TY LAST_ENQUEUED_SCN
---------------------------------------------------------------- ---------- -----------------
CHECKPOINT_RETENTION_TIME
-------------------------
START_TIME                                                                  PURPOSE
--------------------------------------------------------------------------- -------------------
CLIENT_NAME
----------------------------------------------------------------------------------------------------
CLIENT_S OLDEST_SCN FILTERED_SCN
-------- ---------- ------------
OGG$CAP_E_HAWK                 OGG$Q_E_HAWK                   GGS_ADMIN
                                                              GGS_ADMIN
    256229 DISABLED       346591      346586 NO      256229
HAWK
 3171223736                    1             912525304           3
                                           346420                  346586 IMPLICIT 23-MAY-16


11.2.0.4.0                                                       LOCAL
                        0
22-MAY-16 04.21.31.000000 PM                                                GoldenGate Capture
E_HAWK
DISABLED     346586       255600


ARROW:(SYS@hawk):PRIMARY> exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG$CAP_E_HAWK');

PL/SQL procedure successfully completed.

ARROW:(SYS@hawk):PRIMARY> select * from dba_capture;

CAPTURE_NAME                   QUEUE_NAME                     QUEUE_OWNER
------------------------------ ------------------------------ ------------------------------
RULE_SET_NAME                  RULE_SET_OWNER                 CAPTURE_USER
------------------------------ ------------------------------ ------------------------------
 START_SCN STATUS   CAPTURED_SCN APPLIED_SCN USE  FIRST_SCN
---------- -------- ------------ ----------- --- ----------
SOURCE_DATABASE
----------------------------------------------------------------------------------------------------
SOURCE_DBID SOURCE_RESETLOGS_SCN SOURCE_RESETLOGS_TIME LOGMINER_ID NEGATIVE_RULE_SET_NAME
----------- -------------------- --------------------- ----------- ------------------------------
NEGATIVE_RULE_SET_OWNER        MAX_CHECKPOINT_SCN REQUIRED_CHECKPOINT_SCN LOGFILE_ STATUS_CH
------------------------------ ------------------ ----------------------- -------- ---------
ERROR_NUMBER
------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
VERSION                                                          CAPTURE_TY LAST_ENQUEUED_SCN
---------------------------------------------------------------- ---------- -----------------
CHECKPOINT_RETENTION_TIME
-------------------------
START_TIME                                                                  PURPOSE
--------------------------------------------------------------------------- -------------------
CLIENT_NAME
----------------------------------------------------------------------------------------------------
CLIENT_S OLDEST_SCN FILTERED_SCN
-------- ---------- ------------
OGG$CAP_E_HAWK                 OGG$Q_E_HAWK                   GGS_ADMIN
                                                              GGS_ADMIN
    256229 DISABLED       346591      346586 NO      256229
HAWK
 3171223736                    1             912525304           3
                                           346420                  346586 IMPLICIT 23-MAY-16


11.2.0.4.0                                                       LOCAL
                        0
22-MAY-16 04.21.31.000000 PM                                                GoldenGate Capture
E_HAWK
DISABLED     346586       255600


ARROW:(SYS@hawk):PRIMARY>


ARROW:(SYS@hawk):PRIMARY> exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

PL/SQL procedure successfully completed.

ARROW:(SYS@hawk):PRIMARY> select * from dba_capture;

no rows selected

ARROW:(SYS@hawk):PRIMARY>
Advertisements

1 Comment »

  1. […] Using DBMS_STREAMS_ADM To Cleanup GoldenGate […]

    Pingback by Cloning Goldengate Integrated Capture and DB | Thinking Out Loud — October 10, 2017 @ 10:10 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: