{"id":1858,"date":"2019-04-30T16:34:29","date_gmt":"2019-04-30T14:34:29","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1858"},"modified":"2020-08-18T16:02:52","modified_gmt":"2020-08-18T14:02:52","slug":"dst-upgrade-ora-56938","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/dst-upgrade-ora-56938\/","title":{"rendered":"(unsupported) DST_UPGRADE_STATE is DATAPUMP(1) but no data pump jobs are running. How to fix?"},"content":{"rendered":"

This blog post contains unsupported commands. Do not try to use them without the supervision of Oracle Support!<\/strong><\/p>\n

I have just run across an Oracle Database who had a broken configuration in database_properties.<\/p>\n

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:<\/p>\n

SQL> SELECT version FROM v$timezone_file;\r\n\r\n       VERSION\r\n--------------\r\n            14\r\n\r\n\r\nSQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value\r\n2 FROM DATABASE_PROPERTIES\r\n3 WHERE PROPERTY_NAME LIKE 'DST_%'\r\n4 ORDER BY PROPERTY_NAME;\r\n\r\nPROPERTY_NAME               VALUE\r\n--------------------------- ------------------------------\r\nDST_PRIMARY_TT_VERSION      14\r\nDST_SECONDARY_TT_VERSION    4\r\nDST_UPGRADE_STATE           DATAPUMP(1)<\/pre>\n
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)<\/strong> states that I had to check note How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)<\/strong> to solve the problem.<\/p>\n
In fact, there should have\u00a0 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.<\/p>\n
Second, the secondary time zone being lower than the primary one was, to me, sign of an old upgrade went wrong.<\/p>\n
Trying to begin a new prepare phase was failing with:<\/p>\n
SQL> exec DBMS_DST.BEGIN_PREPARE(31);\r\nBEGIN DBMS_DST.BEGIN_PREPARE(31); END;\r\n\r\n*\r\nERROR at line 1:\r\nORA-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\r\nORA-06512: at \"SYS.DBMS_SYS_ERROR\", line 79\r\nORA-06512: at \"SYS.DBMS_DST\", line 1390\r\nORA-06512: at line 1<\/pre>\n
Trying to end the old one was failing as well:<\/p>\n
SQL> EXEC DBMS_DST.END_PREPARE;\r\nBEGIN DBMS_DST.END_PREPARE; END;\r\n\r\n*\r\nERROR at line 1:\r\nORA-56924: prepare window does not exist\r\nORA-06512: at \"SYS.DBMS_SYS_ERROR\", line 79\r\nORA-06512: at \"SYS.DBMS_DST\", line 1470\r\nORA-06512: at line 1<\/pre>\n
Trying to unload the secondary was failing as well:<\/p>\n
SQL> exec dbms_dst.UNLOAD_SECONDARY\r\nBEGIN dbms_dst.UNLOAD_SECONDARY; END;\r\n\r\n*\r\nERROR at line 1:\r\nORA-56938: no secondary time zone data file being loaded by on-demand or a datapump job\r\nORA-06512: at \"SYS.DBMS_DST\", line 1975\r\nORA-06512: at \"SYS.DBMS_SYS_ERROR\", line 79\r\nORA-06512: at \"SYS.DBMS_DST\", line 1950\r\nORA-06512: at line 1<\/pre>\n
I double-checked ALL the notes to clean-up the situation and made sure that there was nothing actually running regarding a DST upgrade.<\/p>\n
I am pretty evil trying unsupported stuff. So I have decided to check the underlying table:<\/p>\n
sys@ACCINT:SQL> select text from dba_views where view_name='DATABASE_PROPERTIES';\r\n\r\nTEXT\r\n--------------------------------------------------------------------------------\r\nselect name, value$, comment$\r\n  from x$props\r\n<\/pre>\n
Fixed tables are not writable, but sys.props$ is, and it was containing the same bad data:<\/p>\n
NAME                                          \r\n------------------------------------------\r\nVALUE$                                    \r\n------------------------------------------\r\nCOMMENT$                                  \r\n------------------------------------------\r\nDST_UPGRADE_STATE                         \r\nDATAPUMP(1)                               \r\nState of Day Light Saving Time Upgrade    \r\n                                          \r\nDST_PRIMARY_TT_VERSION                    \r\n14                                        \r\nVersion of primary timezone data file     \r\n                                          \r\nDST_SECONDARY_TT_VERSION                  \r\n4                                         \r\nVersion of secondary timezone data file   \r\n<\/pre>\n
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!<\/strong><\/p>\n
SQL> update props$ set value$=0 where name='DST_SECONDARY_TT_VERSION';\r\n\r\n1 row updated.\r\n\r\nSQL> update props$ set value$='NONE' where name='DST_UPGRADE_STATE';\r\n\r\n1 row updated.\r\n\r\nSQL> select * from props$ where name like 'DST%';\r\n\r\nNAME                                     \r\n-----------------------------------------\r\nVALUE$                                   \r\n-----------------------------------------\r\nCOMMENT$                                 \r\n-----------------------------------------\r\nDST_UPGRADE_STATE                        \r\nNONE                                     \r\nState of Day Light Saving Time Upgrade   \r\n                                         \r\nDST_PRIMARY_TT_VERSION                   \r\n14                                       \r\nVersion of primary timezone data file    \r\n                                         \r\nDST_SECONDARY_TT_VERSION                 \r\n0                                        \r\nVersion of secondary timezone data file  \r\n\r\n\r\n3 rows selected.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n<\/pre>\n
Trying again:<\/p>\n
SQL> exec DBMS_DST.BEGIN_PREPARE(31);\r\nA prepare window has been successfully started.\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\n
The rest of the upgrade procedure went smoothly.<\/p>\n
—<\/p>\n
Ludovico<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[321,326,3,308,330],"tags":[313,314,310,311,238,312],"class_list":["post-1858","post","type-post","status-publish","format-standard","hentry","category-aced","category-oracle","category-oracledb","category-oracle-database-18c","category-oracle-inst-upg","tag-dst","tag-rdbms_dst","tag-timezone","tag-tzfile","tag-upgrade","tag-utltz"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1858","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1858"}],"version-history":[{"count":1,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1858\/revisions"}],"predecessor-version":[{"id":1859,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1858\/revisions\/1859"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1858"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1858"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1858"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}