Friday, 22 April 2022

Upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state

Error while upgrading Timezone patch

SQL> DECLARE
            l_tz_version PLS_INTEGER;
        BEGIN
            l_tz_version := DBMS_DST.GET_LATEST_TIMEZONE_VERSION;
            DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
            DBMS_DST.begin_prepare(l_tz_version);
        END;
        /
  2    3    4    5    6    7    8  DECLARE
*
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 1382
ORA-06512: at line 6

SQL> SELECT property_name, property_value
        FROM   database_properties
        WHERE  property_name LIKE 'DST_%'
        ORDER BY property_name;
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         34
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              DATAPUMP(1)

SQL> select name, value$ from sys.props$ where name like '%DST%';
NAME                                           VALUE$
-------------------------         ----------------------------
DST_SECONDARY_TT_VERSION 14
DST_PRIMARY_TT_VERSION 34
DST_UPGRADE_STATE DATAPUMP(1)

Solution:
SQL> exec dbms_dst.unload_secondary;
PL/SQL procedure successfully completed.

SQL> select property_value from sys.database_properties where property_name = 'DST_UPGRADE_STATE';
PROPERTY_VALUE
--------------------
NONE