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?
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
PDB$SEED when you have a newer time zone patch applied to your binary home:
At first I run the check script in the
$ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/184.108.40.206/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 220.127.116.11 . 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/18.104.22.168/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.
And then you simply add this for all other PDBs – but make sure the PDBs are
$ $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/22.214.171.124/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