Oracle DST Upgrade Issue utltz_upg_apply.sql Hung Due to Scheduler Jobs when Upgrade from Oracle 12c to Oracle 19c

Oracle DST Upgrade Issue: utltz_upg_apply.sql Hung Due to Scheduler Jobs

Introduction
During an Oracle Database Time Zone (DST) upgrade, the utltz_upg_apply.sql script may appear to hang for an extended period while upgrading TIMESTAMP WITH TIME ZONE (TSTZ) data. In 19c environment, the upgrade process stalled because Oracle Scheduler jobs continued running in the background with Q00n process.This article explains the symptoms, investigation, resolution and root cause.

SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv44 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.

SCRIPT
-----------------------------------------------------------------------
?/rdbms/admin/utltz_upg_apply_patch.sql
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
...........
...........utltz_upg_apply_patch.sql execution was stalled
...........
ORA-12152: TNS:unable to send break message =>Kill the PMON process from another session.

SQL> SQL>

###############Database alert.log##############################
checking ser bit vector, q=70001005a94dd80, ser=25601
checking ser bit vector, q=70001005a94dd80, ser=26113
2026-01-04T11:52:37.935866+00:00
utltz_upg_check successfully found newer RDBMS DSTv44 and took 0 minutes to run.
2026-01-04T11:53:51.026420+00:00
utltz_upg_apply is ready to update to RDBMS DSTv44 and will now restart the database in UPGRADE mode.
Shutting down ORACLE instance (immediate) (OS id: 29032708)
Shutdown is initiated by sqlplus@node01 (TNS V1-V3).
Stopping background process SMCO
Shutting down instance: further logons disabled
2026-01-04T11:53:51.122756+00:00
Stopping background process CJQ0
Stopping background process MMNL
2026-01-04T11:53:53.191443+00:00
Stopping background process MMON
License high water mark = 20
2026-01-04T11:53:53.192386+00:00
Stopping Emon pool
2026-01-04T11:53:53.208628+00:00
Stopping all emon processes
2026-01-04T11:58:52.865905+00:00
Thread 1 advanced to log sequence 365148 (LGWR switch),  current SCN: 
  Current log# 2 seq# 365148 mem# 0: /orcl/redo011/redo_011.log
  Current log# 2 seq# 365148 mem# 1: /orcl/redo021/redo_021.log
2026-01-04T12:27:54.822151+00:00
GEN1 (ospid: 25100730_14877027): terminating the instance due to ORA error 472
2026-01-04T12:27:54.822440+00:00
Cause - 'Instance is being terminated due to fatal process PMON being dead.'=>PMON killed
2026-01-04T12:27:54.832133+00:00

Symptoms:
###################trace file shown below:##################
ksvkillslaves: detected hung proc 70001006d45b218 [Unix process pid: 11206986, image: oracle@node01 (Q002)]
----------------------------------------
SO: 0x70001006deeee40, type: process (2), map: 0x70001006d45b218
    state: LIVE (0x4532), flags: 0x1
    owner: 0x0, proc: 0x70001006deeee40
    link: 0x70001006deeee60[0x70001006deeee60, 0x70001006deeee60]
    child list count: 7, link: 0x70001006deeeeb0[0x70001006df03f70, 0x70001006bf7e760]
    pg: 0
SOC: 0x70001006d45b218, type: process (2), map: 0x70001006deeee40

Investigation:
The trace file indicates that Oracle's hung process detector (ksvkillslaves) identified a hung parallel execution slave process:
ksvkillslaves: detected hung proc
image: oracle@node01 (Q00n)
Oracle pid: 79

The process name Q00n is significant because Oracle uses Qnnn processes for:
Advanced Queuing (AQ)
Streams
Scheduler-related queue services
Job queue slave activities
Propagation processes

What Happened:
During execution of: @?/rdbms/admin/utltz_upg_apply.sql

Oracle's DBMS_DST package was attempting to update objects containing TIMESTAMP WITH TIME ZONE data. At the same time:
Scheduler jobs were running
AQ propagation/background queue slaves were active
Q00n was waiting for a resource or lock.

The DST upgrade session and Scheduler/AQ processes became interdependent. Oracle's hang manager detected the condition and logged:
ksvkillslaves: detected hung proc

Resolution:

Kill the database PMON process:

Kill the pmon process from another session and started the database in UPGRADE mode.

$sqlplus / as sysdba

SQL> startup upgrade;

ORACLE instance started.
Total System Global Area 6442389152 bytes
Fixed Size                  9113248 bytes
Variable Size            2046820352 bytes
Database Buffers         4378853376 bytes
Redo Buffers                7602176 bytes
Database mounted.
Database opened.
SQL>
SQL>


SQL> show parameter job
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

SQL> alter system set job_queue_processes=0 scope=both;
 
System altered.
 
SQL> sho parameter job
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0

SQL>

SQL>@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv44 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.

SCRIPT
-----------------------------------------------------------------------------
?/rdbms/admin/utltz_upg_apply_patch.sql
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 6442389152 bytes
Fixed Size                  9113248 bytes
Variable Size            2046820352 bytes
Database Buffers         4378853376 bytes
Redo Buffers                7602176 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6442389152 bytes
Fixed Size                  9113248 bytes
Variable Size            2046820352 bytes
Database Buffers         4378853376 bytes
Redo Buffers                7602176 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "APP_SCHEMA"."AQ$_LOG_QUEUE_TABLE_A"
Number of failures: 0
Table list: "APP_SCHEMA"."AQ$_LOG_QUEUE_TABLE_D"
Number of failures: 0
Table list: "MDSYS"."MESSAGES_TABLE"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv44 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.

Session altered.

SQL> alter system set job_queue_processes=1000 scope=both;

SQL> sho parameter job
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

Root Cause:

During an Oracle DST upgrade using utltz_upg_apply.sql, the upgrade process became stalled because active Oracle Scheduler jobs and associated Q00x background slave processes were running concurrently. Oracle's hang detection mechanism reported a hung Q00n process (ksvkillslaves: detected hung proc). Investigation revealed contention between DBMS_DST timezone conversion operations and Scheduler/AQ background activity. The issue was resolved by temporarily disabling job queue processes.

After stopping Scheduler execution, the DST upgrade resumed immediately and completed successfully. Scheduler functionality was restored after the upgrade by resetting JOB_QUEUE_PROCESSES to its original value.

Recommended Pre-DST Upgrade Checklist

-- Check running scheduler jobs
SELECT * FROM dba_scheduler_running_jobs;

-- Check current setting
SHOW PARAMETER job_queue_processes;

-- Disable before DST upgrade
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;

-- Run upgrade
@?/rdbms/admin/utltz_upg_apply.sql

-- Restore after completion
ALTER SYSTEM SET job_queue_processes=<original_value> SCOPE=BOTH;

Key Takeaway: 
If utltz_upg_apply.sql appears hung and Oracle trace files show Q00x processes with messages such as ksvkillslaves: detected hung proc, investigate Scheduler jobs, AQ processes, and JOB_QUEUE_PROCESSES settings. Temporarily setting JOB_QUEUE_PROCESSES=0 can eliminate the contention and allow the DST upgrade to complete successfully.

Why Setting JOB_QUEUE_PROCESSES=0 Fixed It causes Oracle to: Stop Scheduler jobs Disable job queue slaves Prevent AQ-related background workers from starting Remove contention on objects being processed by DBMS_DST Once the Scheduler/AQ workers stopped, the DST upgrade no longer competed for resources and was able to continue.