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.
Testing with Oracle Database 19c
Sometimes it is needed and useful to revisit blog posts. And since we prepare our next virtual classroom seminar right now, I tested my procedures with Oracle Database 19c RU 23.
Well, and at first I realized that I don’t need to run any of the steps below separately anymore.
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b tzcheck -d $ORACLE_HOME/rdbms/admin -n 1 -l /tmp utltz_upg_check.sql
This runs the check. Just make sure your PDBs are open.
Then I started the utltz_upg_apply.sql script:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b tzapply -d $ORACLE_HOME/rdbms/admin -n 1 -l /tmp utltz_upg_apply.sql
Not all PDBs open?
And this happens when you don’t have all your PDBs open. If you don’t have all of them open intentionally, then you need to pass a list of containers to catcon.pl to avoid the script exiting with errors.
catcon::wait_for_completion: unexpected error in next_proc()
catcon::catconExec_int: unexpected error in wait_for_completion
catcon::catconExec: an error (1) was encountered
catcon.pl: unexpected error encountered in catconExec
while running scripts against Containers at UPGRADE_LEVEL of 1; exiting
catcon::catconWrapUp: catconInit2 has not been run
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
RAISE_APPLICATION_ERROR(-20210, 'Stopping script - see previous message ...');
*
ERROR at line 8:
ORA-06550: line 8, column 5:
PLS-00201: identifier 'RAISE_APPLICATION_ERROR' must be declared
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored
You’ll correct this then by calling the script for an individual PDB:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -c PDB1 -b tzapply -d $ORACLE_HOME/rdbms/admin -n 1 -l /tmp utltz_upg_apply.sql
Please note that I trapped into this error because my PDB1 did not have SAVE STATE set. Hence, then the apply script runs, it restarts the entire CDB – but my PDB1 did not get started again due to the missing attribute.
Most of you may stop reading. But I leave the previous content just for reference, and in case you have older databases or want to still do it step-by-step.
Previous Instructions for Oracle 12.2.0.1
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 -c 'CDB$ROOT PDB$SEED' -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
Hello,
yeah but.. what if my cdb is upgraded to the latest tz version and one of my pluggable is not? What should I do if I need to upgrade just ONE of my i.e. 8 pdbs?
Do I really need to bounce the whole cdb?
Cheers
Hi Dario,
if you want to upgrade only a PDB, you can logon to it and use the scripts.
Cheers,
Mike
A bit of a kinda dumb question of sort, I think I know the answer. I assume the CDB cannot be at a lower timezone to the PDB though, right? So for example, you can’t have a CDB at 30 and a PDB at 35 or is that allowed?
Hi Edwin,
this in fact is not a dumb but a very serious and good question.
In fact, the CDB can be on a lower TZ version than the PDB as long as the patch is present in $ORACLE_HOME/oracore/zoneinfo.
See here, this summarized the topic:
https://mikedietrichde.com/2019/07/30/database-migration-from-non-cdb-to-pdb-the-time-zone-pitfall/
Cheers,
Mike
Has anyone asked if you can downgrade your timezone to what it was before you updated it?
Such a good point, Edwin – and I’m not even sure if I mentioned this ever on the blog. We mention it in our Virtual Classroom Seminars and in our slides.
1. Time zone can’t be downgraded
2. If you have such a situation, you must ensure that the higher time zone version patch is installed in your source home
For example, you upgrade from DST 14 to DST 35.
Now you want to downgrade.
You must apply DST 35 to your source home.
Otherwise, you can’t downgrade your database.
This does not apply when you flashback as in this case you’d wipe out all the time zone adjustments from your database, too.
Hope this helps – cheers,
Mike
Hi Mike,
how about patching RAC databases? Running upg_tzv_apply.sql inside a PDB wants the whole CDB to be in single-node-mode; CLUSTER_DATABASE has to be FALSE. This leads to a downtime of the whole CDB database, including all PDBs, which is not really acceptable in productive environments just to patch a single PDB with new DST file.
Do you have any suggestions about that?
Regards
Kay.
Hi Kay,
did you try putting the PDB you’d like to patch into UPGRADE mode, and then run the scripts with SQL*Plus in it only?
The home itself needs the time zone patch of course, but even though I haven’t tried it, I think it may work.
Cheers,
Mike
Hi Mike,
When I ran utltz_upg_check.sql on pdb then it gives warning pdb not started on single instance. So I stopped pdb on one instance and upgraded the time zone on other instance where pdb is in opened state.Finally I restarted PDB on other instance.
SQL> @?//rdbms/admin/utltz_upg_check.sql;
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues …
INFO: Database version is 19.0.0.0 .
INFO: This database is a Multitenant database.
INFO: This database is a PDB.
INFO: Current PDB is OPERAPDB .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen …
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
WARNING: This PDB is not started in a single instance!
WARNING: Start this PDB in one single instance only
WARNING: BEFORE running utltz_upg_apply.sql!
WARNING: This is REQUIRED!
Hi Vimal,
I guess this has to do with the fact that you can’t alter time zone from several instances at the same time, at least not with the automated script.
Cheers,
Mike
Hi !
If you remove -n 1 catcon you will be able to upgrade time zone file for multiple pdbs with one catcon command while some pdbs already have been upgraded to the last installed version. Example:
PDB101 – 34
PDB103 – 32
PDB105 – 32
Using -n 1 in this constellation will cauase PDB101 to throw: catcon.pl: unexpected error encountered in catconExec and the whole upgrade for all PDBs will fail. (ORA-56920: a prepare or upgrade window of a secondary time zone data file is in an active state).
Regards
Dieter