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

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

Sql server 2008 R2

Trying to shrink the logfile for out Team Foundation 2010 database:



BACKUP LOG  Tfs_DefaultCollection TO DISK='NUL:'


DBCC SHRINKFILE(Tfs_DefaultCollection_log, 1)

I get the followin error message:

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

Trying the solution with sp_repldone:

USE Tfs_DefaultCollection

GO

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1

Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

exec sp_replicationdboption @dbname = N'Tfs_DefaultCollection', @optname = N'merge publish', @value = N'true'

GO

Msg 20028, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 60

The Distributor has not been installed correctly. Could not enable database for publishing.

The replication option 'merge publish' of database 'Tfs_DefaultCollection' has been set to false.

Running opentran show we have some unreplicated data, that's really strange because there is no replication setup for this database:

dbcc opentran

Transaction information for database 'Tfs_DefaultCollection'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (193362:85316:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sp_removedbreplication Tfs_DefaultCollection

Msg 1205, Level 13, State 57, Procedure sp_MSrepl_clean_replication_bit, Line 25

Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

alter database Tfs_DefaultCollection set offline with rollback immediate

Msg 1205, Level 13, State 68, Line 1

Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

I founbd and killed one of my own sessions...

alter database Tfs_DefaultCollection set offline with rollback immediate

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Failed to restart the current database. The current database is switched to master.

sp_removedbreplication Tfs_DefaultCollection

alter database Tfs_DefaultCollection set online with rollback immediate

Now running opentran again:

dbcc opentran

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

BACKUP LOG  Tfs_DefaultCollection TO DISK='NUL:'

DBCC SHRINKFILE(Tfs_DefaultCollection_log, 1)

Success!

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

On a newly installed Oracle 11.2.0.2 instance I tried to enable archive logging:


SQL> startup mount force


SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

WTF!!!

SQL> alter database recover;

Database altered.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

!?!?!?

What happens if we do a new shutdown and startup mount (without force this time)?

SQL> alter database open;

Database altered.

SQL> shutdown

SQL> startup mount

SQL> alter database archivelog;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/11.2.0.2/dbs/arch

Oldest online log sequence     45

Next log sequence to archive   46

Current log sequence           46

SQL> alter database open;

Database altered.

All well again!  :)

oracle tablespace usage SQL script

This SQL could be usefull if you would lika a list of your tablespaces and be able to see how full they are:


SELECT tablespace_name,
       ROUND (used_bytes / (1024 * 1024)) used_mb,
       ROUND (max_bytes / (1024 * 1024)) max_mb,
       ROUND ( (used_bytes) / max_bytes * 100) percent_full
  FROM (SELECT ddf.tablespace_name,
               NVL (dfs.used_bytes, 0) used_bytes,
               ddf.max_bytes
          FROM (  SELECT tablespace_name, SUM (bytes) used_bytes
                    FROM dba_segments
                GROUP BY tablespace_name) dfs,
               (  SELECT tablespace_name,
                         SUM (GREATEST (bytes, maxbytes)) max_bytes
                    FROM dba_data_files
                GROUP BY tablespace_name
                union
                  SELECT tablespace_name,
                         SUM (GREATEST (bytes, maxbytes)) max_bytes
                    FROM dba_temp_files
                GROUP BY tablespace_name) ddf
         WHERE ddf.tablespace_name = dfs.tablespace_name(+));





TABLESPACE_NAME                   USED_MB     MAX_MB PERCENT_FULL
------------------------------ ---------- ---------- ------------
SYSAUX                               1478      32768            5
UNDOTBS1                               70       4096            2
USERS                                  44       4096            1
SYSTEM                                669      32768            2
UNDOTBS2                               18       4096            0
TEMP                                    0       4096            0

The script also includes the temporary tablespaces like TEMP and calculates the MAX_MB from the maxsize of the datafiles and not tha actual size.