scheduler ORA-01001: invalid cursor ORA-06512: at "SYS.DBMS_AQ_INV" ORA-08102: index key not found, obj

I enabled the event JOB_OVER_MAX_DUR. for our scheduled jobs and all by a sudden the database jammed! It was dumping lika an horse!

from the trace-files:

----- Error Stack Dump -----

ORA-01001: invalid cursor

ORA-06512: at "SYS.DBMS_AQ_INV", line 208

ORA-08102: index key not found, obj# 12482, file 1, block 28009 (2)

ORA-06512: at "SYS.DBMS_AQADM", line 1471

ORA-06512: at line 1

----- Current SQL Statement for this session (sql_id=14ur5x3gfybjy) -----

 delete from "SYS"."AQ$_SCHEDULER$_EVENT_QTAB_H" where msgid = :1 and subscriber# = :2 and  name = '0' and address# = 0

From the alert.log:

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

ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_AQ_INV", line 208
ORA-08102: index key not found, obj# 12482, file 1, block 28009 (2)
ORA-06512: at "SYS.DBMS_AQADM", line 1471
ORA-06512: at line 1
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1085
ORA-06512: at line 1
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1085
ORA-06512: at line 1
Dumping diagnostic data in directory=[cdmp_20130918123540], requested by (instance=1, osid=13808 (J001)), summary=[abnormal process termination].

First stop the scheduler:

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');

I tried to rebuild the index:

SQL> Alter index sys.scheduler$_event_qtab_hist rebuild online;

No difference!

I tried to rebuild the table:

SQL> alter TABLE SYS.AQ$_SCHEDULER$_EVENT_QTAB_H move online;

alter TABLE SYS.AQ$_SCHEDULER$_EVENT_QTAB_H move online

                *

ERROR at line 1:

ORA-24005: Inappropriate utilities used to perform DDL on AQ table

SYS.AQ$_SCHEDULER$_EVENT_QTAB_H

No luck there!

But wait!!! An index on an index-organized table!?! Where did that one come from?

SQL> drop index SYS.SCHEDULER$_EVENT_QTAB_HIST;

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

All works great again!

The script to reproduce the index is as follows:

CREATE INDEX SYS.SCHEDULER$_EVENT_QTAB_HIST ON SYS.AQ$_SCHEDULER$_EVENT_QTAB_H

(SUBSCRIBER#, NAME, ADDRESS#)

LOGGING

TABLESPACE SYSTEM

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MAXSIZE          UNLIMITED

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            FREELISTS        1

            FREELIST GROUPS  1

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

NOPARALLEL;

ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation , DBMS_AUDIT_MGMT.init_cleanup

I was trying to enable audit trail management in our database

SQL> BEGIN
  2    IF NOT DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
  3      DBMS_AUDIT_MGMT.init_cleanup(
  4        audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  5        default_cleanup_interval => 12 /* hours */);
  6    END IF;
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1039
ORA-06512: at line 3

from the manual:

"The INIT_CLEANUP procedure sets up the audit management infrastructure and sets a default cleanup interval for the audit trail records. The procedure also moves the audit trail tables out of the SYSTEM tablespace."

What I could se the SYSAUX tablespace was allowed to grow 2Gb and the AUD$ table was only 230Mb!

Oh, maybe the DBMS_AUDIT_MGMT was looking at the real free space in the datafile (already allocated disk space)!?!

SQL> ALTER DATABASE DATAFILE '/oradata/db1_sysaux01.dbf' RESIZE 4G;

Then all by a sudden the init_cleanup runs fine!

DBMS_SCHEDULER.STOP_JOB ORA-27486 insufficient privilege

Trying to stop scheduled job as an non-dba user: myUser

exec DBMS_SCHEDULER.STOP_JOB('myJob',force => true);


ORA-27486 insufficient privilege 
ORA-06512 at "SYS.DBMS_ISCHED", line 199
ORA-06512 at "SYS.DBMS_SCHEDULER", line 557


The user is lacking the system privilege: MANAGE SCHEDULER

solution:

grant MANAGE SCHEDULER to myUser;

I was trying to apply path 11.2.0.2.11 on Grid and database homes:

# opatch auto /home/oracle/software/p16742320_112020_Linux-x86-64 -oh /oracle/11.2.0.2 -ocmrf /tmp/ocm.rsp


The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16459322/custom/server/16459322 failed  for /oracle/11.2.0.2
The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893 failed  for /oracle/11.2.0.2

In the log /opt/oracle/grid/OPatch/crs/../../cfgtoollogs/opatchauto2013-09-10_09-09-27.log

2013-09-10 09:09:39: The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893 failed  for /oracle/11.2.0.2
2013-09-10 09:09:39: The opatch version check failed with following error
2013-09-10 09:09:39: bash: /oracle/11.2.0.2/OPatch/opatch: Permission denied

I discovered I had unzipped the opatch-software as root and that caused the problem. Change to oracle:oinstall :

# chown -R oracle:oinstall /oracle/11.2.0.2/OPatch

Now all is working just fine:

# opatch auto /home/oracle/software/p16742320_112020_Linux-x86-64 -oh /oracle/11.2.0.2 -ocmrf /tmp/ocm.rsp

Detected Oracle Clusterware install

Using configuration parameter file: /opt/oracle/grid/crs/install/crsconfig_params

Successfully unlock /oracle/11.2.0.2

patch /home/oracle/software/p16742320_112020_Linux-x86-64/16459322  apply successful for home  /oracle/11.2.0.2

patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893  apply successful for home  /oracle/11.2.0.2