Scheduler Jobs Getting Dropped And Not Getting Enabled After Manual Run After Upgrade To 12.2. (Doc ID 2430704.1)

 In this Document
Symptoms
Changes
Cause
Solution
References


Applies to:

Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

 Dbms_scheduler jobs get dropped if auto_drop is set to true and disabled, not enabled after running the manual run using DBMS_SCHEDULER.run_job and use_current_session=FALSE

 Issue is specific to RAC

SQL>
SQL> set echo on;
SQL> set time on;
SQL>
SQL>
SQL> begin
     dbms_scheduler.create_job (
     job_name => 'SQLHC.TEST_JOB8',
     job_type => 'PLSQL_BLOCK',
     job_action => 'BEGIN dbms_output.put_line(''Hello''); END;',
     start_date => systimestamp,
     end_date =>NULL,
     repeat_interval=> 'FREQ=MINUTELY; INTERVAL=05',
     auto_drop => FALSE,
     enabled => TRUE);
    commit;
    end;
    /

PL/SQL procedure successfully completed.

 


 SQL>
 SQL> column REQ_START_DATE Format a35
 SQL> column OUTPUT Format a20
 SQL> column owner Format a10
 SQL> column job_name Format a15
 SQL> column LAST_START_DATE Format a35
 SQL> column NEXT_RUN_DATE Format a35
 SQL> column repeat_interval Format a35
 SQL> set pagesize 200 linesize 200
 SQL>

 SQL> select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

OWNER  JOB_NAME ENABL SCHEDULE_TYP LAST_START_DATE                                     NEXT_RUN_DATE                                    JOB_STYLE     AUTO_   REPEAT_INTERVAL                                           
  ------- ------------ ------- ------------       -----------------------------------                 -----------------------------------              -------------   -------   -----------------------------------                       
SQLHC  TEST_JOB8 TRUE   CALENDAR        01-AUG-18 06.47.50.783978 PM +05:30  01-AUG-18 06.52.49.374076 PM +05:30 REGULAR      FALSE   FREQ=MINUTELY; INTERVAL=05                                

SQL>
SQL> select JOB_NAME,STATUS,REQ_START_DATE,output from dba_scheduler_job_run_details where job_name='TEST_JOB8' order by 3 desc;

JOB_NAME        STATUS                         REQ_START_DATE                      OUTPUT                                                                                                               
--------------- ------------------------------ ----------------------------------- --------------------                                                                                                 
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.47.49.374076 PM +05:30 Hello   

                                                                                                           


SQL>
SQL>  begin
      dbms_scheduler.disable(name => 'SQLHC.TEST_JOB8');
      end;
      /

PL/SQL procedure successfully completed.

SQL>
SQL> select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

OWNER  JOB_NAME  ENABL SCHEDULE_TYP LAST_START_DATE                                   NEXT_RUN_DATE                                  JOB_STYLE    AUTO_   REPEAT_INTERVAL                                           
-------- ------------- -----     ------------        -----------------------------------             -----------------------------------            ------------- -----       -----------------------------------                       
SQLHC  TEST_JOB8   FALSE CALENDAR       01-AUG-18 06.47.50.783978 PM +05:30 01-AUG-18 06.52.49.374076 PM +05:30 REGULAR  FALSE FREQ=MINUTELY; INTERVAL=05    

                          

SQL>
SQL>  begin
      dbms_scheduler.enable(name => 'SQLHC.TEST_JOB8');
      end;
      /

PL/SQL procedure successfully completed.

SQL>
SQL> select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

OWNER    JOB_NAME  ENABL SCHEDULE_TYP LAST_START_DATE                     NEXT_RUN_DATE                                                   JOB_STYLE     AUTO_   REPEAT_INTERVAL                                           
---------- ------------ ----- ------------           ----------------------------------- -----------------------------------                            -------------- -----        -----------------------------------                       
SQLHC    TEST_JOB8  TRUE  CALENDAR       01-AUG-18 06.47.50.783978 PM +05:30 01-AUG-18 06.52.49.374076 PM +05:30 REGULAR       FALSE     FREQ=MINUTELY; INTERVAL=05    

Run the job manually:                          

SQL>
SQL>BEGIN
    DBMS_SCHEDULER.run_job(job_name => 'SQLHC.TEST_JOB8',use_current_session => FALSE);
    END;
    /

PL/SQL procedure successfully completed.

SQL>
SQL> select JOB_NAME,STATUS,REQ_START_DATE,output from dba_scheduler_job_run_details where job_name='TEST_JOB8' order by 3 desc;

JOB_NAME        STATUS                         REQ_START_DATE                      OUTPUT                                                                                                               
--------------- ------------------------------ ----------------------------------- --------------------                                                                                                 
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.49.40.532991 PM +05:30 Hello                                                                                                                
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.47.49.374076 PM +05:30 Hello       

                                                                                                       

SQL>
SQL> select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

OWNER   JOB_NAME   ENABL SCHEDULE_TYP LAST_START_DATE                                    NEXT_RUN_DATE                                      JOB_STYLE  AUTO_  REPEAT_INTERVAL                                           
---------- ------------- ----- ------------         -----------------------------------                -----------------------------------                ------------- -----     -----------------------------------                       
SQLHC   TEST_JOB8   TRUE                           01-AUG-18 06.49.40.537526 PM +05:30  01-AUG-18 06.52.49.374076 PM +05:30 REGULAR    FALSE    FREQ=MINUTELY; INTERVAL=05   

 

SCHEDULE_TYPE changed from CALENDAR to null.After this disable the job and try to enable multiple times with enable procedure is not make it enabled

                             

SQL>
SQL> select JOB_NAME,STATUS,REQ_START_DATE,output from dba_scheduler_job_run_details where job_name='TEST_JOB8' order by 3 desc;

JOB_NAME        STATUS                         REQ_START_DATE                      OUTPUT                                                                                                               
--------------- ------------------------------ ----------------------------------- --------------------                                                                                                 
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.49.40.532991 PM +05:30 Hello                                                                                                                
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.47.49.374076 PM +05:30 Hello                                                                                                                

SQL>
SQL> select JOB_NAME,STATUS,REQ_START_DATE,output from dba_scheduler_job_run_details where job_name='TEST_JOB8' order by 3 desc;

JOB_NAME        STATUS                         REQ_START_DATE                      OUTPUT                                                                                                               
--------------- ------------------------------ ----------------------------------- --------------------                                                                                                 
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.49.40.532991 PM +05:30 Hello                                                                                                                
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.47.49.374076 PM +05:30 Hello                                                                                                                

SQL>
SQL> select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

OWNER JOB_NAME  ENABL SCHEDULE_TYP LAST_START_DATE                     NEXT_RUN_DATE                       JOB_STYLE  AUTO_  REPEAT_INTERVAL                                           
----- --------- ------ -----       ----------------------------------- ----------------------------------- ---------  ------  ----------- -----                     
SQLHC TEST_JOB8  TRUE              01-AUG-18 06.49.40.537526 PM +05:30 01-AUG-18 06.52.49.374076 PM +05:30 REGULAR    FALSE   FREQ=MINUTELY; INTERVAL=05                                

SQL>
SQL> select JOB_NAME,STATUS,REQ_START_DATE,output from dba_scheduler_job_run_details where job_name='TEST_JOB8' order by 3 desc;

JOB_NAME        STATUS                         REQ_START_DATE                      OUTPUT                                                                                                               
--------------- ------------------------------ ----------------------------------- --------------------                                                                                                 
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.49.40.532991 PM +05:30 Hello                                                                                                                
TEST_JOB8       SUCCEEDED                      01-AUG-18 06.47.49.374076 PM +05:30 Hello                                                                                                                

SQL>
SQL>  begin
      dbms_scheduler.disable(name => 'SQLHC.TEST_JOB8');
      end;
      /

PL/SQL procedure successfully completed.

SQL>
SQL> select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

OWNER  JOB_NAME   ENABL SCHEDULE_TYP LAST_START_DATE                      NEXT_RUN_DATE                      JOB_STYLE AUTO_ REPEAT_INTERVAL                                           
----- --------    ----- ------------ ----------------------------------- ----------------------------------- --------- ----- -----------------------------------                       
SQLHC  TEST_JOB8  FALSE              01-AUG-18 06.49.40.537526 PM +05:30 01-AUG-18 06.52.49.374076 PM +05:30 REGULAR   FALSE FREQ=MINUTELY; INTERVAL=05                                

SQL>
SQL>  begin
     dbms_scheduler.enable(name => 'SQLHC.TEST_JOB8');
      end;
      /

PL/SQL procedure successfully completed.

SQL>
SQL> select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

OWNER JOB_NAME ENABL SCHEDULE_TYP LAST_START_DATE                     NEXT_RUN_DATE            JOB_STYLE  AUTO_     REPEAT_INTERVAL                                           
---- -------- ----- ------------ ----------------------------------- ------------------------- --------- ---------- -----------------------------------                       
SQLHC TEST_JOB8 FALSE  01-AUG-18 06.50.38.983734 PM +05:30 01-AUG-18 06.47.49.374076 PM +05:30  REGULAR   FALSE    FREQ=MINUTELY; INTERVAL=05                                

SQL>
SQL>
SQL> select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

OWNER      JOB_NAME        ENABL SCHEDULE_TYP LAST_START_DATE                     NEXT_RUN_DATE                       JOB_STYLE         AUTO_ REPEAT_INTERVAL                                           
---------- --------------- ----- ------------ ----------------------------------- ----------------------------------- ----------------- ----- -----------------------------------                       
SQLHC      TEST_JOB8       FALSE              01-AUG-18 06.50.38.983734 PM +05:30 01-AUG-18 06.47.49.374076 PM +05:30 REGULAR           FALSE FREQ=MINUTELY; INTERVAL=05    

 

Changes

 Upgrade to 12.2.0.1

Cause

 Bug 28430089 - JOBS NOT GETTING ENABLED AFTER DISABLING ,MANUAL RUN USING RUN_JOB PROCEDURE closed as duplicate of

 Bug 28402823 - 12.2.0.1 IN RAC DBMS_SCHEDULER SET SCHEDULE_TYPE TO NULL AFTER JOB RAN MANUALLY

Solution

 The issue happens only in RAC database.

 Download and apply the patch 28402823 

 Or

Use the below workaround:

 Set  INSTANCE_STICKINESS attribute to false as

 

exec dbms_scheduler.set_attribute('<jobname>', 'INSTANCE_STICKINESS', FALSE);

 

 In the above example

  

exec dbms_scheduler.set_attribute('SQLHC.TEST_JOB8', 'INSTANCE_STICKINESS', FALSE);

 

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more