Skip to content

Scheduled Jobs Running at Wrong Time

  • by

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 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.

1. 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.

2. 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.

3. 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.

Leave a Reply

Your email address will not be published.