(unsupported) DST_UPGRADE_STATE is DATAPUMP(1) but no data pump jobs are running. How to fix?

This blog post contains unsupported commands. Do not try to use them without the supervision of Oracle Support!

I have just run across an Oracle Database who had a broken configuration in database_properties.

The database was in process of being upgraded to 18c, but the DST upgrade step was not working because of wrong entries in the view DATABASE_PROPERTIES:

The MOS note Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1) states that I had to check note How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1) to solve the problem.

In fact, there should haveĀ  been an orphan data pump job trying to import the timezone file. But in my case, no jobs at all, do data pump job tables.

Second, the secondary time zone being lower than the primary one was, to me, sign of an old upgrade went wrong.

Trying to begin a new prepare phase was failing with:

Trying to end the old one was failing as well:

Trying to unload the secondary was failing as well:

I double-checked ALL the notes to clean-up the situation and made sure that there was nothing actually running regarding a DST upgrade.

I am pretty evil trying unsupported stuff. So I have decided to check the underlying table:

Fixed tables are not writable, but sys.props$ is, and it was containing the same bad data:

So I did what I knew was wrong, after taking a guaranteed restore point. Do not try this at home without the supervision of Oracle Support!

Trying again:

The rest of the upgrade procedure went smoothly.

Ludovico

The following two tabs change content below.

Ludovico

Oracle ACE Director and Computing Engineer at CERN
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Computing Engineer at CERN, the European Organization for Nuclear Research, in Switzerland.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.