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:
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
Hi Mike,
what happens when you plug a PDB into a new CDB that has different time zone versions? This will be the rule for upgrades if the old CDB has an older time zone version than the new CDB. Will the PDB stay on the old version?
Regards
Kay.
Hi Kay,
as long as the installation has the time zone file equal or higher than your PDB, all is fine, the COMPATIBILITY check will pass.
Your PDB can have a different time zone setting as the CDB$ROOT.
BUT … if the PDB has a higher time zone setting than the installation your are plugin it into, it will fail.
But this gives me food for another time zone blog post!
Cheers,
Mike
Thank you for this note !!! very well explained, I’m using your steps for our MAA 19c upgrade on Cloud. A small tip, if you remove -n 1 catcon will pick a number of sqlplus processes to start based on cpu_count and the number of PDBs