Upgrading a single PDB to use a new DST file

(Credit to Mike C. for these notes)

When trying to refresh a db which was at a db level 2 below Prod I received the error below.

ORA-39002: invalid operation

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 41 into a target database with TSTZ version 39.

This is the first time I’ve tried to refresh Dev from Prod. Test is more in step with Prod so wasn’t a problem.

Prod is at 19.19 but that’s not available from the console anymore.

I created a new Dev at 19.23 and that did allow me to do an import however I didn’t really want to replace the original db system, although would have done if no other choice.

I upgraded Dev to 19.23 but suspected there’d be more work to do before an import could be done and so it proved.

Although general db patching does make the new DST files available it doesn’t set them in the CDB or PDB so the actions below were still required.

If I hadn’t patched to 19.23 I could have applied a DST patch (p35099667_190000_Linux-x86-64.zip ) using opatch to apply the files but this still would have needed the below. (think of the below as datapatch after patching)

The below can be done on individual PDB’s or the container. I’ve just done it on LIMSD pdb as that’s all we’ll use this for. Also I did it on a new temp empty PDB which would be cloned over LIMSD so as not to worry about user table upgrading. I was doing an import anyway.

Check which DST files are now available after upgrade or DST patch.

ls -l /u01/app/oracle/product/19.0.0.0/dbhome_1/oracore/zoneinfo

Check which file is set in the db.

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
39

Put pdb into upgrade mode

alter pluggable database PDB1 close immediate;

alter pluggable database PDB1 open upgrade;

alter session set container=PDB1;


Start an upgrade window

begin

dbms_dst.begin_upgrade

( new_version => 41,
error_on_overlap_time => false,
error_on_nonexisting_time => false
);

end;


Restart the db normally

alter pluggable database PDB2 close immediate;

alter pluggable database PDB2 open;

Start the upgrade


SQL> declare

failures binary_integer := -1;

begin

dbms_dst.upgrade_database

( num_of_failures => failures,

upgrade_data => false,

error_on_overlap_time => false,

error_on_nonexisting_time => false

);

end; 

End the upgrade

SQL> declare

failures binary_integer := -1;

begin

dbms_dst.end_upgrade(num_of_failures => failures);

end; 

PL/SQL procedure successfully completed.


Check again which file is set in the db.

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
41

DP import should now work in this PDB.