Fixing DST UPGRADE STATE DATAPUMP when upgrade Oracle 12c to Oracle 19c
Fixing Oracle 19c DST Upgrade Failure when DST_UPGRADE_STATE Is DATAPUMP
Issue Summary:
While performing an Oracle 19c Daylight Saving Time (DST) / Time Zone file upgrade validation using utltz_upg_check.sql, the script failed because the database was not in a clean DST state.
The issue occurred when running: @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
The script reported that the current DST_UPGRADE_STATE was DATAPUMP(7) instead of NONE. Oracle requires DST_UPGRADE_STATE to be NONE before running DST upgrade preparation or apply scripts. The Oracle 19c DST upgrade process uses the DBMS_DST package to manage timezone file upgrades and prepare/upgrade windows.
Error occurred while executing:
@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
ERROR: Current DST_UPGRADE_STATE is DATAPUMP(7 !
ERROR: DST_UPGRADE_STATE in DATABASE_PROPERTIES needs to be NONE
ERROR: before running utltz_upg_check.sql.
ERROR: Do the checks as documented in point 3
ERROR: of Note 977512.1 for 11gR2 or note 1509653.1 for 12c .
DECLARE
*
ERROR at line 1:
ORA-20034: Stopping script - see previous message ...
ORA-06512: at line 125
INFO: Now detecting new RDBMS DST version.
ERROR: something went wrong during DBMS_DST.BEGIN_PREPARE
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 65
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1382
ORA-06512: at line 42
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
ERROR: Something went wrong during DBMS_DST.FIND_AFFECTED_TABLES.
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 35
ORA-56924: prepare window does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1535
ORA-06512: at line 17
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 8
Session altered.
Investigations:
This means Oracle detected active or leftover secondary timezone file usage associated with Data Pump.The database properties showed:
col PROPERTY_NAME for a40
SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- --------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE DATAPUMP(7)
Under normal database operation, Oracle recommends that DST_UPGRADE_STATE should be NONE. Internal Oracle best-practice content also highlights that a database should not remain in PREPARE, UPGRADE, or DATAPUMP state during normal operations.
Why This Blocks DST Upgrade
The utltz_upg_check.sql script starts a DST prepare window to validate timezone upgrade readiness.However, because the database was already showing: DST_UPGRADE_STATE = DATAPUMP(7)
This indicated that a secondary timezone file was still loaded due to a previous Data Pump operation or interrupted timezone-related activity.
Oracle could not start a new prepare window and failed with:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading
of a secondary time zone data file is in an active state
The DBMS_DST package is responsible for managing this upgrade state and the timezone prepare/upgrade workflow.
Solution:
Step 1: Check Current DST Properties
Run the following query:
COL property_name FOR A40
SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- --------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE DATAPUMP(7)
This confirmed that the root cause of database stuck in DATAPUMP DST state is database property DST_UPGRADE_STATE was set to: DATAPUMP(7)
Step 2: Enable Event 30090
Before unloading the secondary timezone file, enable event 30090:
SQL>ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, LEVEL 32';
Session altered.
Step 3: Unload the Secondary Timezone File
SQL> EXEC DBMS_DST.UNLOAD_SECONDARY;
Succeed in unloading the secondary timezone data file.
PL/SQL procedure successfully completed.
SQL>col PROPERTY_NAME for a40
SQL>SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- -----------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE DATAPUMP(6)
3 rows selected.
Step 4: Repeat DBMS_DST.UNLOAD_SECONDARY
In this case, the unload operation had to be executed multiple times because the DATAPUMP counter reduced one level at a time. Repeated executions showed the following progression:
SQL> EXEC DBMS_DST.UNLOAD_SECONDARY;
Succeed in unloading the secondary timezone data file.
PL/SQL procedure successfully completed.
SQL>col PROPERTY_NAME for a40
SQL>SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- --------------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE DATAPUMP(5)
3 rows selected.
SQL> EXEC DBMS_DST.UNLOAD_SECONDARY;
Succeed in unloading the secondary timezone data file.
PL/SQL procedure successfully completed.
SQL>col PROPERTY_NAME for a40
SQL>SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- -----------------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE DATAPUMP(4)
3 rows selected.
SQL> EXEC DBMS_DST.UNLOAD_SECONDARY;
Succeed in unloading the secondary timezone data file.
PL/SQL procedure successfully completed.
SQL>col PROPERTY_NAME for a40
SQL>SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- ------------------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE DATAPUMP(3)
3 rows selected.
SQL> EXEC DBMS_DST.UNLOAD_SECONDARY;
Succeed in unloading the secondary timezone data file.
PL/SQL procedure successfully completed.
SQL>col PROPERTY_NAME for a40
SQL>SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- ------------------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE DATAPUMP(2)
3 rows selected.
SQL> EXEC DBMS_DST.UNLOAD_SECONDARY;
Succeed in unloading the secondary timezone data file.
PL/SQL procedure successfully completed.
SQL>col PROPERTY_NAME for a40
SQL>SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE DATAPUMP(1)
3 rows selected.
SQL> EXEC DBMS_DST.UNLOAD_SECONDARY;
Succeed in unloading the secondary timezone data file.
PL/SQL procedure successfully completed.
SQL>col PROPERTY_NAME for a40
SQL>SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
PROPERTY_NAME VALUE
---------------------------------------- --------------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
3 rows selected.
At this point, the secondary timezone file was fully unloaded and the DST state was clean.
Step 5: Disable Event 30090
Once DST_UPGRADE_STATE became NONE, disable the event:
SQL> ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, OFF';
Session altered.
Step 6: Re-run DST Upgrade Check
Now run the DST check script again:
SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv44 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
