(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:

SQL> SELECT version FROM v$timezone_file;

       VERSION
--------------
            14


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

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:

SQL> exec DBMS_DST.BEGIN_PREPARE(31);
BEGIN DBMS_DST.BEGIN_PREPARE(31); END;

*
ERROR at line 1:
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 1390
ORA-06512: at line 1

Trying to end the old one was failing as well:

SQL> EXEC DBMS_DST.END_PREPARE;
BEGIN DBMS_DST.END_PREPARE; END;

*
ERROR at line 1:
ORA-56924: prepare window does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1470
ORA-06512: at line 1

Trying to unload the secondary was failing as well:

SQL> exec dbms_dst.UNLOAD_SECONDARY
BEGIN dbms_dst.UNLOAD_SECONDARY; END;

*
ERROR at line 1:
ORA-56938: no secondary time zone data file being loaded by on-demand or a datapump job
ORA-06512: at "SYS.DBMS_DST", line 1975
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1950
ORA-06512: at line 1

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:

sys@ACCINT:SQL> select text from dba_views where view_name='DATABASE_PROPERTIES';

TEXT
--------------------------------------------------------------------------------
select name, value$, comment$
  from x$props

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

NAME                                          
------------------------------------------
VALUE$                                    
------------------------------------------
COMMENT$                                  
------------------------------------------
DST_UPGRADE_STATE                         
DATAPUMP(1)                               
State of Day Light Saving Time Upgrade    
                                          
DST_PRIMARY_TT_VERSION                    
14                                        
Version of primary timezone data file     
                                          
DST_SECONDARY_TT_VERSION                  
4                                         
Version of secondary timezone data file   

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!

SQL> update props$ set value$=0 where name='DST_SECONDARY_TT_VERSION';

1 row updated.

SQL> update props$ set value$='NONE' where name='DST_UPGRADE_STATE';

1 row updated.

SQL> select * from props$ where name like 'DST%';

NAME                                     
-----------------------------------------
VALUE$                                   
-----------------------------------------
COMMENT$                                 
-----------------------------------------
DST_UPGRADE_STATE                        
NONE                                     
State of Day Light Saving Time Upgrade   
                                         
DST_PRIMARY_TT_VERSION                   
14                                       
Version of primary timezone data file    
                                         
DST_SECONDARY_TT_VERSION                 
0                                        
Version of secondary timezone data file  


3 rows selected.

SQL> commit;

Commit complete.

Trying again:

SQL> exec DBMS_DST.BEGIN_PREPARE(31);
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

The rest of the upgrade procedure went smoothly.

Ludovico

My feedback after upgrading EM12c 12.1.0.3 to 12.1.0.5

Today I’ve upgraded EM12c for a customer from the second-last version (12.1.0.3) to the last one (12.1.0.5) and the EM Repository from 11.2.0.3 to 12.1.0.2.

The upgrade path was not very easy: EM 12.1.0.3 is not compatible with a repository 12.1.0.2 and EM 12.1.0.5 requires a mandatory patch for the repository if 11.2.0.3 (or an upgrade to 11.2.0.4).

So I’ve done:

  • upgrade of the repository from 11.2.0.3 (in Data Guard configuration) to 11.2.0.4
  • upgrade of the EM from 12.1.0.3 to 12.1.0.5
  • upgrade of the repository from 11.2.0.4 to 12.1.0.2 (in Data Guard configuration), from Solaris to Linux

 

In my case, I was particularly concerned about my customer’s EM topology:

  • two OMS in load balancing
  • console secured with a custom SSL certificate
  • a good amount of targets (more than 800 total targets, more than 500 targets with status)
  • a lot of jobs and custom reports
  • a big, shared central software library
  • many other small customizations: auth, groups, metrics, templates…

I will not bother with the actual execution steps, every installation may differ, I strongly recommend to read the upgrade documentation (I know, it’s HUGE 🙁 ).

Just to resume, the upgrade guide is here: https://docs.oracle.com/cd/E24628_01/upgrade.121/e22625/toc.htm

in my case I had to read carefully the chapters 3, 4, 5, 6 and appendixes G and K.

By following every step carefully, I had no problems at all and at the end everything was working correctly: all the targets up, the load balancing working in SSL as expected, the jobs restarted and ran successfully…

It has been incredible to see how many operations the OUI has done without raising a single error!!

Ok, it’s not just a Click Next Next Next Next installation, there are a lot of steps to do manually before and afterwards, but still… very good impression.

It took a little more than one hour to upgrade the first OMS (this also upgrades the EM repository) and a little less than 20 minutes to upgrade the second one.

Let a couple of hours for checking everything before, staging the binaries, taking backups/snapshots, creating restore points… and one hours more for upgrading the central agents and cleansing the old installations.

About upgrading/moving the repository, check this good post by Maaz AnjumMIGRATE ENTERPRISE MANAGER 12.1.0.4.0 TO A PDB FROM A NON-CDB, even if you don’t plan to do it, it’s worth a read.

HTH

Ludo