ERROR: Cannot register or unregister EXTRACT UODSEXT because of the following SQL error: OCI Error 26,665.
The status of the UODSEXT extract was in STARTING for a long time. As I discussed in my previous post, This Capture process was checking for an Archivelog which was not available or deleted.
Now I thought to stop the Extract Process, UnRegister it and again Register. I am sharing my experience on it where I faced few problems and which was also resolved.
GGSCI (OGGSRV1) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED UODSDP 00:00:00 22:27:53
EXTRACT ABENDED UODSEXT 00:42:20 175:01:08
EXTRACT RUNNING UTILDP 00:00:00 00:00:01
EXTRACT RUNNING UTLEXT 00:00:03 00:00:02
ERROR: EXTRACT UODSEXT must be registered with the database to perform this operation.
I tried to Register the Extract Process and got the below error.,
ERROR: Cannot register or unregister EXTRACT UODSEXT because of the following SQL error: OCI Error 26,665.
I checked the state of the Integrated Capture Process and it was ‘WAITING FOR DICTIONARY REDO’.
SQL> set lines 200 pages 2000
SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A20
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
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# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;
Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
——————– ——————– ——- ——– ——————– ———- ———–
OGG$CAP_UODSEXT TNS 1221 923 WAITING FOR DICTIONA 0 0
RY REDO
Checked the Information of the Session with ID 1221.,
Session Serial
ID Number STATUS ACTION PROGRAM EVENT State
——- ——– ——– ——————————————— ——————– ——————————————— ——————–
1221 923 ACTIVE OGG$CAP_UODSEXT – Capture extract@OGGSRV1 Streams capture: waiting for archive log WAITING
I tried to stop the Capture process using the below command and checked the status, but there was no improvement. The session was still active and it waiting for the archive log.
SQL> exec DBMS_CAPTURE_ADM.STOP_CAPTURE(‘OGG$CAP_UODSEXT’);
PL/SQL procedure successfully completed.
Session Serial
ID Number STATUS ACTION PROGRAM EVENT State
——- ——– ——– ——————————————— ——————– ——————————————— ——————–
1221 923 ACTIVE OGG$CAP_UODSEXT – Capture extract@OGGSRV1 Streams capture: waiting for archive log WAITING
Then I tried to Drop the Capture process using the below command and ended up with the below error.,
BEGIN DBMS_CAPTURE_ADM.DROP_CAPTURE(‘OGG$CAP_UODSEXT’); END;*
ERROR at line 1:
ORA-01338: Other process is attached to LogMiner session
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 732
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 703
ORA-06512: at “SYS.DBMS_CAPTURE_ADM”, line 566
ORA-06512: at line 1
The above error clearly states that, a Logminer Session is holding the Extract Process and not getting released. So, I just killed that session with SID 1221 and checked the status.
After killing the session, I again tried to stop and drop the Capture process and at last the Capture process was dropped successfully.,
There was no Capture process with name OGG$CAP_UODSEXT. It was totally removed / dropped.
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
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# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;no rows selected
So I started proceeding with Unregister the Extract Process UODSEXT, still I was not able to do it,
ERROR: EXTRACT UODSEXT must be registered with the database to perform this operation.
This is because, we had externally killed and dropped the Logminer process which was attached to the Capture Process. This means, a logminer server process will be attached whenever an Extract is registered to the Database and it will be dettached whenever it is unregistered. This is called Integrated Extract Process.
Now I tried to Register the Extract process and it got registered without any error.,
Extract UODSEXT successfully registered with database at SCN 7345485133524.
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
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# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
——————– ——————– ——- ——– ——————– ———- ———–
OGG$CAP_UODSEXT CP04 646 16955 LOADING (step 66 of 0 0
66)
The State here is LOADING. This means the Extract process is getting integrated with the Database.
I just waited for few minutes and again checked the status of the Extract Process.,
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
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# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
——————– ——————– ——- ——– ——————– ———- ———–
OGG$CAP_UODSEXT CP04 646 16955 WAITING FOR INACTIVE 1 1
DEQUEUERS
Now you can after the state of the Capture process has been changed.
WAITING FOR INACTIVE DEQUEUERS – Waiting for the capture process’s queue subscribers to start. The capture process stops enqueuing LCRs if there are no active subscribers to the queue.
This is because, I only started the Extract Process and DataPump Process is yet to be started.
Now I am going to UnRegister the Capture Process
Successfully unregistered EXTRACT UODSEXT from database.
Check if the background process (Logminer Process) Still Exists.,
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
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# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;no rows selected
Hope this cleared you, reason and solution for the error
ERROR: EXTRACT UODSEXT must be registered with the database to perform this operation.
ERROR: Cannot register or unregister EXTRACT UODSEXT because of the following SQL error: OCI Error 26,665.
Loading ...
Total Users : 1816359