Forum Stats

  • 3,855,178 Users
  • 2,264,465 Discussions
  • 7,905,912 Comments

Discussions

Scheduled jobs running in the wrong environment

user502212
user502212 Member Posts: 14
edited Apr 12, 2018 2:50PM in Multitenant

Assume we start with a multi-tenant database (version 12.1.0.2) where the CDB is set to Pacific time zone and the PDB is set to the Central time zone. Run the following setup scripts in the PDB:

CREATE TABLE CHECK_TIME (   DATE_PDB   DATE,   DATE_CDB   DATE,   THESOURCE  VARCHAR2(50 CHAR) );

CREATE OR REPLACE PROCEDURE check_time_sp IS

BEGIN  

INSERT INTO check_time (date_pdb, date_cdb,thesource)   VALUES (CURRENT_DATE, SYSDATE,'SP');  

COMMIT;

END;

CREATE OR REPLACE PROCEDURE check_time_test IS

BEGIN  

INSERT INTO check_time (date_pdb, date_cdb,thesource)   VALUES (CURRENT_DATE, SYSDATE,'SCHEDULED');  

COMMIT;

END;

BEGIN   SYS.DBMS_SCHEDULER.CREATE_JOB     (        job_name        => 'CHECK_TIME_UPDATE'       ,start_date      => TO_TIMESTAMP_TZ('2018/01/01 12:00:00.000000 US/Central','yyyy/mm/dd hh24:mi:ss.ff tzr')       ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'       ,end_date        => NULL       ,job_class       => 'DEFAULT_JOB_CLASS'       ,job_type        => 'STORED_PROCEDURE'       ,job_action      => 'check_time_test'       ,comments        => NULL     );   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'RESTARTABLE'      ,value     => FALSE);   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'LOGGING_LEVEL'      ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'MAX_FAILURES');   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'MAX_RUNS');   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'STOP_ON_WINDOW_CLOSE'      ,value     => FALSE);   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'JOB_PRIORITY'      ,value     => 1);   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'SCHEDULE_LIMIT');   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'AUTO_DROP'      ,value     => FALSE);   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'RESTART_ON_RECOVERY'      ,value     => FALSE);   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'RESTART_ON_FAILURE'      ,value     => FALSE);   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'STORE_OUTPUT'      ,value     => TRUE);   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE     ( name      => 'CHECK_TIME_UPDATE'      ,attribute => 'RAISE_EVENTS'      ,value     => SYS.DBMS_SCHEDULER.JOB_FAILED + SYS.DBMS_SCHEDULER.JOB_BROKEN + SYS.DBMS_SCHEDULER.JOB_SCH_LIM_REACHED);

END;

Now if we run this SQL:

INSERT INTO check_time (date_pdb, date_cdb,thesource) VALUES (CURRENT_DATE, SYSDATE,'MANUAL');

commit;

We see that the times tagged MANUAL accurately reflect the CDB and PDB times (2 hours difference)

Now run the stored procedure:

begin

check_time_test();

end;

We see that the times tagged SP accurately reflect the CDB and PDB times (2 hours difference).

Everything is good so far.

Now enable or run the scheduled job CHECK_TIME_UPDATE (which fires the stored procedure check_time_test).  We see that the times tagged SCHEDULED no longer reflect the CDB and PDB times (2 hours difference).  The PDB date reflects the CDB time.

Why?  I thought the stored procedure check_time_test would run under the PDB environment and that the CURRENT_DATE = PDB Central time.  Do we have something set wrong?

FYI - I have a daily job that runs and have verified that it indeed does trigger off PDB time.

This discussion has been closed.