Introduction

A client has reported na issue while trying to execute a impdp against a PDB that failed with the following error:

ORA-39065: unexpected master process exception in DISPATCH
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

I had identified the issue was the DST in UPGRADE state due a incomplete procedure executed early.

I managed to fix the issue by completing the DST upgrade executing the step 12 on the below procedure.

After that I have reviewed and tested the proper steps to upgrade the DST version in a specific PDB properly.

Please note this procedure causes downtime in the PDB.

About the process

The client was trying to upgrade the timezone file following the great post from Tim Hall “Upgrade the Database Time Zone File Using the DBMS_DST Package“, which describes how to upgrade a non-CDB or a CDB + PDBs.

The steps described in this post are essentially the same from the original oracle-base blog post. However, since the client went to upgrade only one specific PDB, I have used the post from Tim Hall and adapted the necessary steps to work in that scenario. I have tested this procedure for 2 PDBs in a RAC environment. It is important to note that for RAC databases, we need to make sure we close the PDB in ALL instances before open in UPGRADE mode.

Assumptions

To follow this post, please keep in mind these requirements:

• The latest DST file is in place in the Oracle Home
• Only one PDB will be upgraded
• The CDB will not be touched

Step by step

For the below commands, you only need to change the PDB name in the steps 1, 10 and 12.
For all others steps you can just copy and paste.
Note even if you are executing this procedure in a Single Instance, you can still using the INSTANCES=ALL syntax.

1) Connect in the PDB to be upgraded

alter session set container = C02PDB2;

2) Check the current Time Zone File version

SELECT * FROM v$timezone_file;

3) Check the latest Time Zone File version available

SELECT DBMS_DST.get_latest_timezone_version FROM DUAL;

4) Check the DST properties and State

col property_name FORMAT A30
col property_value FORMAT A20
SELECT property_name, property_value
  FROM database_properties
 WHERE property_name LIKE 'DST_%'
ORDER BY property_name;

5) Prepare for the Upgrade

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;
/

6) Empty the default tables that hold the affected tables list and errors.

TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

7) Find the tables affected by the upgrade:

EXEC DBMS_DST.find_affected_tables;

8) Check if you have tables affected by the upgrade:

SELECT count(*) FROM sys.dst$affected_tables;
SELECT count(*) FROM sys.dst$error_table;

9) If no tables are affected, then you can go ahead:

EXEC DBMS_DST.end_prepare;

10) Close the PDB in all instances and open in the local instance in Upgrade mode:

alter session set container = C02PDB2;
alter pluggable database C02PDB2 close immediate instances=all;
alter pluggable database C02PDB2 open upgrade;

11) Begin the upgrade:

SET SERVEROUTPUT ON
DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  SELECT DBMS_DST.get_latest_timezone_version
  INTO   l_tz_version
  FROM   dual;
  DBMS_OUTPUT.put_line('l_tz_version= ' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/

12) Reopen the PDB in NORMAL mode in all RAC instances:

alter pluggable database C02PDB2 close immediate instances=all;
alter pluggable database C02PDB2 open instances=all;

13) Now you can effectively upgrade the DST:

SET SERVEROUTPUT ON
DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/

14) Check the DST properties and State after the upgrade

col property_name FORMAT A30
col property_value FORMAT A20
SELECT property_name, property_value
  FROM database_properties
 WHERE property_name LIKE 'DST_%'
ORDER BY property_name;

The state should be NONE, if you see UPGRADE, PREPARE or anything else, then it means that the upgrade process was not completed successfully.

Example when I upgraded to version 44:

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         44
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE