Why are PDB scheduled jobs not running? or running at the wrong times?
If you found this problem in a Pluggable Database (PDB), then we might have the same issue. My finding is that AUTOTASK is always triggered at 14:00, which should be at 22:00 by default, according to predefined maintenance windows.
Furthermore, the wrongly triggered job could severely impact database performance during busy hours in some regions, such as in Asia and India. In the emergency of poor performance, we can formally stop the running job to solve the issue on the spot.
Given those symptoms, the first thought in my mind, it could be a time zone problem, so we should trace the issue from time zone.
CDB's Scheduler Time Zone
We first check CDB's scheduler default time zone as following.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
09-MAR-22 09.51.35.578500000 AM ASIA/HONG_KONG
ASIA/HONG_KONG is the correct time zone to the database and it's one of valid time zones in Oracle.
PDB's Scheduler Time Zone
Next, we check PDB's scheduler default time zone.
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
08-MAR-22 05.52.19.214059000 PM PST8PDT
Time zone PST8PDT (Pacific Time Zone) in this PDB is usually used in north America. The question is, why are they different between CDB and PDB? This is because all PDB follows the very original time zone setting of PDB$SEED.
PDB$SEED Scheduler Time Zone
Although we have the answer, we need to confirm that.
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB$SEED
SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
08-MAR-22 05.53.20.921072000 PM PST8PDT
It comes as no surprise, always PST8PDT.
Solution
To solve the problem, we should correct scheduler default time zone in PDB.