12.2 Scheduler Job Disappeared After Creation

Hi all,
Just sharing an experience here. Recently in a migrated 12.2 environment I created a Scheduler Job and it simply disappeared. How come?
Well, seems since 12.2 the AUTO_DROP attribute is set to true by default. I absolutely don’t know why. So I had to recreate the job and set auto_drop to false, simple like this.

I couldn’t find any related bug on MOS, but I’d relate this as a bug, once the default behavior have changed with no prior information.
So, now on, I strongly suggest you to check on your job AUTO_DROP attribute after job creation.

Here is a test case that reproduces the problem:

In a 12.2 database:

begin 
  DBMS_SCHEDULER.create_job (
    job_name        => 'TEST_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN null; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'SYSTIMESTAMP + INTERVAL ''10'' SECOND',
    end_date        => NULL,
    enabled         => false,
    comments        => 'Test job');
end;
/
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';
-- Job should appear here

begin 
  dbms_scheduler.enable('TEST_JOB'); 
  dbms_scheduler.run_job('TEST_JOB',false); 
end;
/
 
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';

-- There is no job now in 12.2.

You can take the AUTO_DROP easily from the query above, or using getting the job_ddl and you’ll see the AUTO_DROP set to true, as in the bottom of this page by Tim Hall.

Hope it helps!
Cheers!

ORA-00600 [ipc_recreate_que_2]

Hi all,
So this week I started receiving this error in a client environment. This was happening due Bug 26803191 – Getting ORA-00600 [ipc_recreate_que_2] instead of ORA-27515 – superseded (Doc ID 26803191.8)
In summary, failures due to low memlock limit are leading ORA-00600 [ipc_recreate_que_2] instead of ORA-27515, patching ensure that ORA-27515 is raised on failures due to low memlock limit instead of this ORA-600.

As workaround, the memlock limited can be changed, as also described in MOS ORA-00600: Internal Error Code, Arguments: [ipc_recreate_que_2] When Running Exachk (Doc ID 2480088.1).

Before:

[boesing@exa1dbadm01 trace]$ grep memlock /etc/security/limits.conf |grep oracle
# - memlock - max locked-in-memory address space (KB)
oracle soft memlock 237809520
oracle hard memlock 237809520

After:

[boesing@exa1dbadm01 ~]$ grep memlock /etc/security/limits.conf
# - memlock - max locked-in-memory address space (KB)
oracle soft memlock unlimited
oracle hard memlock unlimited
* soft memlock 32768
* hard memlock 32768

Hope it helps,
Cheers!

ORA-00600: internal error code, arguments: [13011], [7907] on SYS.DBMS_AQ_INV

Hi all,

So a couple weeks ago I was facing the following on a client environment. It was appearing every 10 mins on alert.log:

ORA-00600: internal error code, arguments: [13011], [7907], [12679954], [1], [13461738], [0], [], [], [], [], [], []
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1248

I could find several references to ORA-600 [13011], always related to some internal corruption. As this table is related to Advanced Queueing, decided to check on Scheduler Job table structures:

SQL> analyze table SYS.SCHEDULER$_EVENT_LOG validate structure cascade; 

Table analyzed. 

SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade; 
analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade 
* 
ERROR at line 1: 
ORA-01499: table/index cross reference failure - see trace file

SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure;

Table analyzed.

Ahá!
Also on the generated trace file:

2019-03-01 22:26:37.736 
SESSION ID:(39.32751) 2019-03-01 22:26:37.736 
CLIENT ID) 2019-03-01 22:26:37.736 
SERVICE NAME:(SYS$USERS) 2019-03-01 22:26:37.736 
MODULE NAME:(sqlplus.exe) 2019-03-01 22:26:37.736 
CLIENT DRIVER:(SQL*PLUS) 2019-03-01 22:26:37.736 
ACTION NAME) 2019-03-01 22:26:37.736 
CONTAINER ID:(1) 2019-03-01 22:26:37.736 
Table/Index row count mismatch 
table 273184 : index 275017, 1832 
Index root = tsn: 1 rdba: 0x00c0128a

So ANALYZE on table SCHEDULER$_JOB_RUN_DETAILS fails with CASCADE but succeeds without CASCADE. This means that there is a problem with one of the index of this table. It has gone out of sync with the table.

SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where table_name='SCHEDULER$_JOB_RUN_DETAILS';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild;

SQL> alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild online;

Index altered.

After this, error solved and no more recurrence of that ORA-600.

Also note I couldn’t find any document about this on MOS, so this is kind of exclusive by now. 🙂

Cheers!