How to patch all PDBs with the a new time zone file?

Yesterday I wrote about how to adjust the time zone setting in the PDB$SEED as by default the time zone scripts won’t touch the PDB$SEED when you execute them. And in addition, MOS Note:1509653.1 tells you, that the PDB$SEED can’t be adjusted. But this leads to a weird mix of time zone settings across a Multitenant deployment. Which I’d guess is not desired. Following a tweet reply by Marco Mischke I realized: I explained how to patch the PDB$SEED – but I didn’t explain how to patch all PDBs with the a new time zone file?

Photo by Laureen Missaire on Unsplash

Of course you will need common downtime for this activity.

Check all containers for the current time zone version

First of all check all your containers for the current time zone setting.

alter system set "_exclude_seed_cdb_view"=false scope=both;

select value$, con_id from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by 2;

VALUE$ CON_ID
------ ------
26	    1
26	    2
26	    3
26	    4
26	    5

All containers are set on time zone V.26.

In this blog post I showed how to adjust CDB$ROOT and PDB$SEED when you have a newer time zone patch applied to your binary home:

• Does the PDB$SEED get “time zone” patched or not?

At first I run the check script in the root and seed containers:

$ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.2.0.1/rdbms/admin/catcon.pl -n 1 -s -l /home/oracle -b utltz_upg_check -d /u01/app/oracle/product/18/rdbms/admin utltz_upg_check.sql

The issue I see is the logging. As I have only one worker to avoid concurrency issues as I’m not sure if the script is able to deal with it I get only one logfile. In this logfile I have now to check for each container this part:

INFO: Database version is 12.2.0.1 .
INFO: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
WARNING: There are 3 open PDBs .
WARNING: They will be closed when running utltz_upg_apply.sql .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.

But I will need to run the “check” part in the PDBs as well. Make sure they are opened:

alter pluggable database all open;

And then execute:

$ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.2.0.1/rdbms/admin/catcon.pl -n 1 -S -l /home/oracle -b utltz_upg_check_pdbs -d /u01/app/oracle/product/18/rdbms/admin utltz_upg_check.sql

How to patch all PDBs with a new time zone file?

This is actually very simple and straight forward.

You patch the CDB$ROOT and the PDB$SEED as I demonstrated in this blog post.

And then you simply add this for all other PDBs – but make sure the PDBs are open unrestricted.

$ $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.2.0.1/rdbms/admin/catcon.pl -n 1 -S -l /home/oracle -b utltz_upg_apply_pdbs -d /u01/app/oracle/product/18/rdbms/admin utltz_upg_apply.sql

catcon: ALL catcon-related output will be written to [/home/oracle/utltz_upg_apply_pdbs_catcon_27023.lst]
catcon: See [/home/oracle/utltz_upg_apply_pdbs*.log] files for output generated by scripts
catcon: See [/home/oracle/utltz_upg_apply_pdbs_*.lst] files for spool files, if any
catcon.pl: completed successfully

Once this is completed, here’s a final check:

select value$, con_id from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by 2;

VALUE$ CON_ID
------ ------
32	    1
32	    2
32          3
32	    4
32	    5

Looks good.

–Mike