GoldenGate GGSCI Register / Unregister Extract Failed with OGG-08221 OCI Error 24,034

advertisements

_____________________________________________________________________________________________________________________

Problem Description 

GoldenGate GGSCI Register or Unregister Extract failed with following error. 

GGSCI (Node1 as PPS_OWNER@KPMUAT1) 27>register extract EXT_CPIF database

2016-09-21 14:21:51  ERROR   OGG-08221  Cannot register or unregister EXTRACT EXT_CPIF because of the following SQL error: OCI Error 24,034.

Solution Description

The issue is because there is already a subscriber present with the queue. You have to remove the subscriber using following package. 

1. Find out the respective queue name using below sql query

SQL> SELECT capture_name, queue_owner, queue_name FROM dba_capture;

CAPTURE_NAME                   QUEUE_OWNER                    QUEUE_NAME
------------------------------ ------------------------------ ---------------
OGG$CAP_EXT_CPIF               PPS_OWNER                      OGG$Q_EXT_CPIF

2. Execute the following PLSQL package to find out the subscriber. Provide the queue name with its owner(highlighted below in red). It will provide you the subscriber detail as output which is highlighted in yellow.

SQL> set serveroutput on
Declare
   subs dbms_aqadm.aq$_subscriber_list_t;
   nsubs BINARY_INTEGER;
   i BINARY_INTEGER;
Begin
   subs := dbms_aqadm.queue_subscribers('PPS_OWNER.OGG$Q_EXT_CPIF');
   nsubs :=subs.COUNT;

   FOR i IN 0 .. nsubs-1 LOOP
   dbms_output.put_line(subs(i).name || ','||nvl(subs(i).address, 'Local subscriber') ||','||subs(i).protocol );
   END LOOP;
End;
/
OGG$EXT_CPIF,Local subscriber,0

PL/SQL procedure successfully completed.

advertisements

 

3. Remove the subscriber using following PL SQL code. Pass the above subscriber code and queue name as argument in the following code.

SQL> declare
 subscriber sys.aq$_agent;
begin
 subscriber := sys.aq$_agent ('OGG$EXT_CPIF',null,null);
 DBMS_AQADM.REMOVE_SUBSCRIBER(
    queue_name => 'PPS_OWNER.OGG$Q_EXT_CPIF',
    subscriber => subscriber);
end;

/ 
PL/SQL procedure successfully completed.

4. Now try to register the extract.

GGSCI (Node1 as PPS_OWNER@KPMUAT1) 29> register extract EXT_CPIF database

2016-09-21 14:28:18  INFO    OGG-02003  Extract EXT_CPIF successfully registered with database at SCN 19407324.

_____________________________________________________________________________________________________________________

Website Stats