DST_UPGRADE_STATE = DATAPUMP(1) causing issue in Oracle DB upgrade.

Yesterday, I was busy upgrading my UAT database from 12.1.0.2 to 12.2.0.1. As a prerequisite when I ran preupgrade.jar into 12.1 RDBMS home it gave me below warning:


-- CHECK/FIXUP name: pending_dst_session
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- + Complete any pending DST update operation before starting the database
-- upgrade.
--
-- There is an unfinished DST update operation in the database. It's
-- current state is: DATAPUMP(1)
--
-- There must not be any Daylight Savings Time (DST) update operations
-- pending in the database before starting the upgrade process.
-- Refer to My Oracle Support Note 1509653.1 for more information.
--
fixup_result := dbms_preup.run_fixup('pending_dst_session');

I tried querying DATABASE_PROPERTIES to get current DST_UPGRADE_STATE output, it was shown as below:


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 18
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE DATAPUMP(1)

I followed below MOS notes to resolve the issues, but all ended without any luck.

Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)

Then I thought of ignoring this error & proceeded with DB upgrade. DB was successfully upgraded. But I once again stuck during step of DST upgrade from 18 to 26.

It was not allowing me to upgrade DST version from 18 to 26 as DST_UPGRADE_STATE was not NONE.

Then I googled it & found below steps to resolve it:


1. ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;
2. exec dbms_dst.unload_secondary;
3. ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, OFF’;


I did check DST_UPGRADE_STATE post implementing it.


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 18
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE NONE

Now my database was ready for DST upgrade as DST_UPGRADE_STATE is NONE. 🙂

Hope u will find this post very useful.

Cheers

Regards,
Adityanath

Advertisements