(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

Principal Product Manager at Oracle
Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

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

  1. Support note
    ORA-39065 And ORA-56935 Errors When Running Impdp On 12c R2 Database (Doc ID 2471020.1)
    suggets this workaround:
    connect / as sysdba
    ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;
    exec dbms_dst.unload_secondary;

  2. Vegard got a really good solution:

    You don’t need to rollback to a restore point or something. Just use dbms_dst.unload_secondary after the alter session.

    Taken from his blog https://minimalistic-oracle.blogspot.com/2018/02/dstupgradestate-set-to-datapump1-when.html
    and quoting him directly đŸ™‚

    SYS@proddb01 SQL> r
    1 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
    2 FROM DATABASE_PROPERTIES
    3 WHERE PROPERTY_NAME LIKE ‘DST_%’
    4* ORDER BY PROPERTY_NAME

    PROPERTY_NAME VALUE
    —————————————- ——————–
    DST_PRIMARY_TT_VERSION 18
    DST_SECONDARY_TT_VERSION 14
    DST_UPGRADE_STATE DATAPUMP(1)

    According to https://blog.oracle-ninja.com/2013/09/17/stuck-timezone-upgrades-and-smart-scans the solution would be, as sysdba:

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

    I tried this, with the following result:

    SYSproddb01 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 0
    DST_UPGRADE_STATE NONE

  3. Yes unload succeed after setting the event :
    SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE ‘DST_%’
    4 ORDER BY PROPERTY_NAME;

    PROPERTY_NAME VALUE
    ——————————————————————————————————————————– ——————————
    DST_PRIMARY_TT_VERSION 18
    DST_SECONDARY_TT_VERSION 14
    DST_UPGRADE_STATE DATAPUMP(1)

    SYS > ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;

    SYS > exec dbms_dst.unload_secondary;

    Procedure PL/SQL terminee avec succes.

    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 0
    DST_UPGRADE_STATE NONE

  4. Hi Ludovico
    Just to avoid people to tweak Oracle internal tables đŸ™‚ :
    I think the workaround in note “Exadata: Database Performance Degrades when Database is in Timezone Upgrade Mode (Doc ID 1583297.1)” should have solved your problem. (it solved mine ..)

    • Hi Philippe,
      thank you for the comment. I wish I had found the note when I encountered the problem.
      So “exec dbms_dst.unload_secondary;” was failing, but then it succeeded after setting the event?

  5. Thank you for sharing your experiences Ludovico, the above information (DST_UPGRADE_STATE is DATAPUMP(1) but no data pump jobs are running) was very helpful..

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.