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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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:
|
1 2 3 4 |
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