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
Adityanath's Oracle Blog 
Your solution really help us.
Otherwise, we will be stuck in Step 2 of 9 of dbua V18 upgrade.
Hello There,
Appreciate the feedback!!!
Regards,
Adi
Is there impact below command ?
exec dbms_dst.unload_secondary;
if any issue comes can revert ?
Way cool! Some very valid points! I appreciate you writing this write-up plus the rest of the website is extremely good.
I have been surfing online more than three hours today, yet I never found any interesting article like yours. It抯 pretty worth enough for me. In my view, if all web owners and bloggers made good content as you did, the internet will be a lot more useful than ever before.
I really appreciate this post. I抳e been looking everywhere for this! Thank goodness I found it on Bing. You have made my day! Thanks again
I抦 not that much of a internet reader to be honest but your sites really nice, keep it up! I’ll go ahead and bookmark your site to come back in the future. Many thanks
Hello There. I found your blog using msn. This is a very well written article. I抣l make sure to bookmark it and come back to read more of your useful info. Thanks for the post. I抣l certainly return.
Hey very nice blog!! Man .. Excellent .. Amazing .. I’ll bookmark your website and take the feeds also厈I am happy to find numerous useful info here in the post, we need work out more techniques in this regard, thanks for sharing. . . . . .
Really enjoyed this article, is there any way I can receive an email sent to me when you write a new post?
Thank you for the auspicious writeup. It in fact was a amusement account it.
Look advanced to more added agreeable from you!
By the way, how can we communicate?
I’m really enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you hire out a designer to create your theme? Excellent work!
Thank you, Adityanath. I wish Oracle documented this issue as well you.
Thanks Rich!!