OGG-02024 Oracle GoldenGate Capture for Oracle, An attempt to gather information about the logmining server configuration from the Oracle database failed

OGG-02024  Oracle GoldenGate Capture for Oracle,  An attempt to gather information about the logmining server configuration from the Oracle database failed

OGG-00662  Oracle GoldenGate Capture for Oracle, OCI Error retrieving bind info for query (status = 100).

I mistakenly deleted an Integrated Extract process UODSEXT without unregistering the Extract first. The actual procedure to delete an Integrated Extract is, first it should be Unregistered and then it should be deleted.

If it is a Normal Extract, we can just delete it as usual.

I created the Integrated Extract process again and tried to register it but the below error occured.,

GGSCI (OGGSRV1) 4> info allProgram Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING TRADP 00:00:00 00:00:09
EXTRACT RUNNING TRAEXT 00:00:04 00:00:02
EXTRACT STOPPED UODSDP 00:00:00 66:58:19
EXTRACT STOPPED UODSEXT 00:00:00 66:58:43

2014-12-30 02:06:48 ERROR OGG-02024 Oracle GoldenGate Capture for Oracle, uodsext.prm: An attempt to gather information about the logmining server configuration from the Oracle database failed.
2014-12-30 02:06:48 ERROR OGG-00662 Oracle GoldenGate Capture for Oracle, uodsext.prm: OCI Error retrieving bind info for query (status = 100).
2014-12-30 02:06:48 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, uodsext.prm: PROCESS ABENDING.

Since I did not unregister it and deleted the Integrated Process, I thought to unregister it and then register it again. But that too did not work. I was not able to unregister the Extract Process.

GGSCI (OGGSRV1) 22> unregister extract uodsext database
ERROR: Cannot register or unregister EXTRACT UODSEXT because of the following SQL error: OCI Error retrieving bind info for query (status = 100).

As Integrated Extract is tightly bind with the Oracle Database through Logminer, unlike Classic Extract, we should be careful with the Integrated Extract while creating and Deleting it.
While Registering the Extract Process, a Logminer session will be attached and a Capture Process will be created. So For each Integrated Extract there will be a Capture process and a Logminer Session attached to it.

I just queried the dba_capture and found the naming convention of the Capture Process by seeing the other Capture Process which was created.

SQL> select capture_name,queue_name,rule_set_name from dba_capture;Capture
Name QUEUE_NAME RULE_SET_NAME
————— —————————— ——————————
OGG$CAP_TRAEXT OGG$Q_TRAEXT RULESET$_376

From the above output,
OGG$CAP_TRAEXT here TRAEXT is my Integrated Name so I created a very simple Capture Process with the below command. Just forget about the RULE_SET_NAME. For Each Capture Process the RULE_SET will vary.

BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => ‘OGG$Q_UODSEXT’,
capture_name => ‘OGG$CAP_UODSEXT’);
END;
/
GGSCI (OGGSRV1) 5> info allProgram Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING TRADP 00:00:00 00:00:09
EXTRACT RUNNING TRAEXT 00:00:04 00:00:02
EXTRACT STOPPED UODSDP 00:00:00 66:58:19
EXTRACT STOPPED UODSEXT 00:00:00 66:58:43

From the above output We can see that the Integrated Extract Process and the Datapump are in Stopped status.
There is only one Capture TRAEXT which is listed in the output.

SQL> select capture_name,queue_name,rule_set_name from dba_capture;Capture
Name QUEUE_NAME RULE_SET_NAME
————— —————————— ——————————
OGG$CAP_TRAEXT OGG$Q_TRAEXT RULESET$_376

1 row selected.

SOLUTION

Connect as GoldenGate admin User and Create a simple Capture Process manually as said before.,

SQL> conn ggadmin/******@oraodspp
Connected.
SQL>
SQL> BEGIN
2 DBMS_CAPTURE_ADM.CREATE_CAPTURE(
3 queue_name => ‘OGG$Q_UODSEXT’,
4 capture_name => ‘OGG$CAP_UODSEXT’);
5 END;
6 /PL/SQL procedure successfully completed.

SQL>

So now a simple Capture Process is created. Check if this listed in the output. Issue the below query to check it.,

SQL> select capture_name,queue_name,rule_set_name from dba_capture;Capture
Name QUEUE_NAME RULE_SET_NAME
—————————— —————————— ——————————
OGG$CAP_TRAEXT OGG$Q_TRAEXT RULESET$_376
OGG$CAP_UODSEXT OGG$Q_UODSEXT

2 rows selected.

Again tried to Register the Extract Process UODSEXT but it ended up with the error that “ERROR: This EXTRACT UODSEXT is already registered with the database.”

GGSCI (OGGSRV1) 4> register extract UODSEXT database
ERROR: This EXTRACT UODSEXT is already registered with the database.

Start the Capture Process which is created manually.

SQL> exec DBMS_CAPTURE_ADM.START_CAPTURE(‘OGG$CAP_UODSEXT’);PL/SQL procedure successfully completed.

Now the Capture process is started. Issue the below command to check if the Process is getting started or not.

SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A15
SQL> COLUMN STATE HEADING ‘State’ FORMAT A27
SQL> COLUMN STATE_CHANGED HEADING ‘State|Change Time’
SQL> COLUMN CREATE_MESSAGE HEADING ‘Last Message|Create Time’
SQL>
SQL> SELECT CAPTURE_NAME,
2 STATE,
3 TO_CHAR(STATE_CHANGED_TIME, ‘HH24:MI:SS MM/DD/YY’) STATE_CHANGED,
4 TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, ‘HH24:MI:SS MM/DD/YY’) CREATE_MESSAGE
5 FROM V$STREAMS_CAPTURE;Capture State Last Message
Name State Change Time Create Time
————— ————————— —————– —————–
OGG$CAP_TRAEXT WAITING FOR TRANSACTION 00:38:13 01/02/15 00:38:13 01/02/15
OGG$CAP_UODSEXT INITIALIZING 00:38:10 01/02/15

2 rows selected.

Yes, now the Capture Process OGG$CAP_UODSEXT is getting Initialized which can be seen from the above output.

Later the State of the Capture has been changed which we can clearly see in the below result.,

SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A15
SQL> COLUMN STATE HEADING ‘State’ FORMAT A27
SQL> COLUMN STATE_CHANGED HEADING ‘State|Change Time’
SQL> COLUMN CREATE_MESSAGE HEADING ‘Last Message|Create Time’
SQL>
SQL> SELECT CAPTURE_NAME,
2 STATE,
3 TO_CHAR(STATE_CHANGED_TIME, ‘HH24:MI:SS MM/DD/YY’) STATE_CHANGED,
4 TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, ‘HH24:MI:SS MM/DD/YY’) CREATE_MESSAGE
5 FROM V$STREAMS_CAPTURE;Capture State Last Message
Name State Change Time Create Time
————— ————————— —————– —————–
OGG$CAP_TRAEXT WAITING FOR TRANSACTION 01:08:38 01/02/15 01:08:39 01/02/15
OGG$CAP_UODSEXT WAITING FOR INACTIVE DEQUEU 00:38:14 01/02/15 00:35:49 01/02/15
ERS

2 rows selected.

Now the Integrated Capture is manually registered and it can be Unregistered now.
UnRegister the Integrated Extract Process UODSEXT.

GGSCI (OGGSRV1) 6> unregister extract uodsext database
Successfully unregistered EXTRACT UODSEXT from database.

The Extract has been Unregistered Successfully. In the below output we can clearly see that the Capture Process which was assigned to the Integrated Extract UODSEXT does not exists.

SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A15
SQL> COLUMN STATE HEADING ‘State’ FORMAT A27
SQL> COLUMN STATE_CHANGED HEADING ‘State|Change Time’
SQL> COLUMN CREATE_MESSAGE HEADING ‘Last Message|Create Time’
SQL>
SQL> SELECT CAPTURE_NAME,
2 STATE,
3 TO_CHAR(STATE_CHANGED_TIME, ‘HH24:MI:SS MM/DD/YY’) STATE_CHANGED,
4 TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, ‘HH24:MI:SS MM/DD/YY’) CREATE_MESSAGE
5 FROM V$STREAMS_CAPTURE;Capture State Last Message
Name State Change Time Create Time
————— ————————— —————– —————–
OGG$CAP_TRAEXT WAITING FOR TRANSACTION 01:23:00 01/02/15 01:23:01 01/02/15

1 rows selected.

SQL> select capture_name,queue_name,rule_set_name from dba_capture;Capture
Name QUEUE_NAME RULE_SET_NAME
—————————— —————————— ——————————
OGG$CAP_TRAEXT OGG$Q_TRAEXT RULESET$_376

1 rows selected.

Now, let us check if we can able to register the Extract and also check if a Logminer Process is attached to the Integrated Extract / Capture process.,

GGSCI (OGGSRV1) 7> register Extract UODSEXT database
Extract UODSEXT successfully registered with database at SCN 7346945951684.

Cool… The Extract UODSEXT has been registered successfully.

SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A30
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A7
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A40
SQL> COLUMN TOTAL_MESSAGES_CAPTURED HEADING ‘Redo|Entries|Evaluated|In Detail’ FORMAT 999999999
SQL> COLUMN TOTAL_MESSAGES_ENQUEUED HEADING ‘Total|LCRs|Enqueued’ FORMAT 9999999999
SQL>
SQL> SELECT c.CAPTURE_NAME,
2 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(‘)+1,4) PROCESS_NAME,
3 c.SID,
4 c.SERIAL#,
5 c.STATE,
6 c.TOTAL_MESSAGES_CAPTURED,
7 c.TOTAL_MESSAGES_ENQUEUED
8 FROM V$STREAMS_CAPTURE c, V$SESSION s
9 WHERE c.SID = s.SID AND
10 c.SERIAL# = s.SERIAL#;
Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
—————————— ——- ——- ——– —————————————- ———- ———–
OGG$CAP_TRAEXT TNS 142 1 WAITING FOR TRANSACTION 174017334 164878673
OGG$CAP_UODSEXT CP04 777 31047 LOADING (step 66 of 66) 0 02 rows selected.

Here you can see the new Capture Name OGG$CAP_UODSEXT has been created.

We can clearly see, the process is getting Loaded. Later, it got loaded and was waiting for the Logs.,

SQL> SELECT c.CAPTURE_NAME,
2 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(‘)+1,4) PROCESS_NAME,
3 c.SID,
4 c.SERIAL#,
5 c.STATE,
6 c.TOTAL_MESSAGES_CAPTURED,
7 c.TOTAL_MESSAGES_ENQUEUED
8 FROM V$STREAMS_CAPTURE c, V$SESSION s
9 WHERE c.SID = s.SID AND
10 c.SERIAL# = s.SERIAL#;Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
—————————— ——- ——- ——– —————————————- ———- ———–
OGG$CAP_TRAEXT TNS 142 1 WAITING FOR TRANSACTION 174047954 164905462
OGG$CAP_UODSEXT CP04 777 31047 WAITING FOR INACTIVE DEQUEUERS 1 1

2 rows selected.

Now you can Start the Integrated Extract Process UODSEXT and there will be no hurdles in it.

 

CONCLUSION:

Always be careful while Playing with Integrated Extract Processes. Its configuration is entirely different from the regular Classic Extract Process.

 

Hope this helped you a lot…..!!!!!!!!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *