ORA-08104: this index object 762389 is being online built or rebuilt

Try to rebuild an index online, get ORA-08104:

SQL> ALTER INDEX TESTUSER.tbl_idx_01 rebuild online;
ALTER INDEX TESTUSER.tbl_idx_01 rebuild online
*
ERROR at line 1:
ORA-08104: this index object 762389 is being online built or rebuilt

Subscribe to get access

Read more of this content when you subscribe today.

Datapump ORA-31626 ORA-31638 ORA-31632 ORA-00942

Datapump expdp finished, but generated errors :

ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user TETSUSER
ORA-31632: master table "TESTUSER.SYS_EXPORT_SCHEMA_01" not found, 
           invalid, or inaccessible
ORA-00942: table or view does not exist
SQL> SELECT owner_name, job_name, operation, job_mode,
            state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE STATE      ATTACHED_SESSIONS
---------- -------------------- ---------- -------- ---------- -----
TESTUSER      SYS_EXPORT_SCHEMA_01  EXPORT    SCHEMA   COMPLETING 0

Subscribe to get access

Read more of this content when you subscribe today.

SYS.SYS$SERVICE_METRICS_TAB Size Grows Big and Affects AQ Performance

SYMPTOMS

1)Table SYS.SYS$SERVICE_METRICS_TAB keeps growing everyday.

SQL> select count(*) from SYS$SERVICE_METRICS_TAB;

COUNT(*)
----------
46946104

SQL> SELECT TRUNC(enq_time), COUNT(*)
  FROM sys$service_metrics_tab
 GROUP BY TRUNC(enq_time)
 ORDER BY 1 ;

TRUNC(ENQ   COUNT(*)
--------- ----------
07-JAN-19      41052
08-JAN-19      63338
09-JAN-19      63096
10-JAN-19      63338
11-JAN-19      62128
12-JAN-19      63338
13-JAN-19      63316
14-JAN-19      63316
...
..
.

2) Oracle background process ora_cjq — Job scheduler coordinator slows and hangs when running below SQL with “enq: TQ – DDL contention” contention.

begin dbms_aqadm_sys.remove_subscriber (
                            queue_name => 'SYS.SYS$SERVICE_METRICS', 
                 subscriber => sys.aq$_agent(:1, NULL, NULL)); end;

3) Oracle background process Qnnn: Queue cleanup processes hang with “library cache pin” contention.

4) oraagent.bin@host program also has “library cache pin” contention”.

5) Manageability Monitor Process MMON background slave process ora_m000 has “library cache lock” contention.

Subscribe to get access

Read more of this content when you subscribe today.

ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi]

There are a lot ORA-00600 [kwqitnmphe:ltbagi] errors in alert log, this error is caused by a QMON slave background process:

*** 2019-10-05 18:43:28.563
*** SESSION ID:(1906.11789) 2019-10-05 18:43:28.563
*** CLIENT ID:() 2019-10-05 18:43:28.563
*** SERVICE NAME:(SYS$BACKGROUND) 2019-10-05 18:43:28.563
*** MODULE NAME:(Streams) 2019-10-05 18:43:28.563
*** ACTION NAME:(QMON Slave) 2019-10-05 18:43:28.563

kwqitmmit during kwqitmmit:time mgr IOT proc: Error 600 in Queue 
                                Table SYS.SYS$SERVICE_METRICS_TAB
kwqicaclcur: Error 600
Cursor Session Number : 1904
Cursor Session Serial : 24687
Cursor Pin Number : 24585
kwqitmmit during kwqitmmit:end proc: Error 600 in Queue Table 
                                        SYS.SYS$SERVICE_METRICS_TAB
kwqitptm: error 600 encountered during serving 10008981

*** 2019-10-05 18:43:28.564
ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi], 
                        [1], [0], [], [], [], [], [], [], [], [], []
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi], [1], 
                             [0], [], [], [], [], [], [], [], [], []

CAUSES

There are still historical messages without a current Subscriber (a.k.a. orphaned entries in the IOT history table).

SQL> select count(*) from AQ$_SYS$SERVICE_METRICS_TAB_H;

COUNT(*)
----------
6268766

SQL> select sub_hist.NAME, sub_hist.SUBSCRIBER#, sub_hist.MSGID, 
            msg.QUEUE, msg.MSG_STATE
       from
            AQ$_SYS$SERVICE_METRICS_TAB_H sub_hist,
            AQ$SYS$SERVICE_METRICS_TAB msg
       where
            sub_hist.MSGID = msg.MSG_ID and
            NOT EXISTS (select sub.SUBSCRIBER_ID 
                         from aq$_SYS$SERVICE_METRICS_TAB_s sub 
                     where sub_hist.subscriber# = sub.SUBSCRIBER_ID);

no rows selected

WORKAROUND

1)Purge all the messages in queue table SYS$SERVICE_METRICS_TAB.

DECLARE
     po dbms_aqadm.aq$_purge_options_t;
BEGIN
     po.block := FALSE;
     DBMS_AQADM.PURGE_QUEUE_TABLE(
            queue_table => 'SYS.SYS$SERVICE_METRICS_TAB',
            purge_condition => NULL,
            purge_options => po);
END;
/

2)Recompile any invalid objects including possible queue and rule sets.

SQL> select owner, object_name, object_type 
       from dba_objects 
      where owner='SYS' and status!='VALID';

OWNER         OBJECT_NAME            OBJECT_TYPE
------------  -------------------   --------------
SYS          SYS$SERVICE_METRICS_N    RULE SET
@?/rdbms/admin/utlrp.sql
SQL> select owner, object_name, object_type 
       from dba_objects 
      where owner='SYS' and status!='VALID';

no rows selected

Invalid Queue Rule Set After DBMS_AQADM.PURGE_QUEUE_TABLE

In 11.2, after manually purged the message queue, some queue rule sets might become Invalid.

before purging the queue.

SQL> select object_type, object_name       
      from dba_objects       
      where owner='SYS' and status!='VALID'; 

no rows selected

Purge the queue table.

SQL> DECLARE         
       po dbms_aqadm.aq$_purge_options_t;      
     BEGIN         
       po.block := FALSE;         
       DBMS_AQADM.PURGE_QUEUE_TABLE
            ( queue_table => 'SYS.SYS$SERVICE_METRICS_TAB',
          purge_condition => NULL,         
            purge_options => po); 
     END; 

/  

PL/SQL procedure successfully completed.

Check the Invalid objects again.

SQL> select object_type, object_name       
       from dba_objects      
      where owner='SYS' and status!='VALID'; 

OBJECT_TYPE           OBJECT_NAME
 -------------------  --------------------------
 RULE SET             SYS$SERVICE_METRICS_N

Recompile the invalid objects.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select object_type, object_name
from dba_objects
where owner='SYS' and status!='VALID';

no rows selected