Today I received a question from a very experienced Oracle ACS engineer whether the PDB$SEED get time zone patched or not when a time zone patch gets applied. I’d say spontaneously “Of course, it does” but after thinking for a few seconds I started having doubts. Question is: Does the PDB$SEED get “time zone” patched or not?
Time zone scripts are in ?/rdbms/admin since Oracle 18c
First of all, since Oracle Database 18c the scripts to adjust time zone settings of the database are in
?/rdbms/admin. If you did our Hands-On Lab already you may know this:
And you know now how to adjust the settings after applying the patch.
But where do you learn about the newest available time zone patch?
Download the most recent time zone patch
Well, sometimes I think this is a secret. Even if you know MOS Note: 412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches because you visited one of our workshops, you may have fun finding the patch number. For me this note is one of my top candidates for notes which need a proper cleanup.
And please don’t ask me why the owner did not include a link to the most recent patch …
I’m looking for this one here in my environment for a test:
RDBMS – PROACTIVE DSTV32 UPDATE – TZDATA2018E(Patch 28125601) on Linux x86-64
I applied it with opatch.
$ $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 22.214.171.124.14 Copyright (c) 2018, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/126.96.36.199 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/188.8.131.52/oraInst.loc OPatch version : 184.108.40.206.14 OUI version : 220.127.116.11.4 Log file location : /u01/app/oracle/product/18.104.22.168/cfgtoollogs/opatch/opatch2018-12-17_15-22-27PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 28125601 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Backing up files... Applying interim patch '28125601' to OH '/u01/app/oracle/product/22.214.171.124' Patching component oracle.oracore.rsf, 126.96.36.199.0... Patch 28125601 successfully applied. Log file location: /u01/app/oracle/product/188.8.131.52/cfgtoollogs/opatch/opatch2018-12-17_15-22-27PM_1.log OPatch succeeded.
What does MOS say?
Rita, my colleague, sent me this excerpt from MOS Note:1509653.1 – Updating the RDBMS DST version in 12c Release 1 (184.108.40.206 and up) using DBMS_DST.
There are two interesting statements:
- The RDBMS DST version of PDB$SEED is the RDBMS_DST version at the CDB creation time (default is DSTv18 for 220.127.116.11 and 18.104.22.168). The RDBMS DST version of PDB$SEED can currently not be updated.
- A PDB has to be switched into UPGRADE mode.
The latter should be handled by the time zone adjustment scripts. The note mentions their existence as last topic – but does not name the scripts but instead only points to the Globalization Guide without link: “Section 4.7.3 Steps to Upgrade Time Zone File and Timestamp with Time Zone Data”.
Here’s the link to the 12.2 documentation by the way.
Anyway, this was Rita’s question: “Does the PDB$SEED get “time zone” patched or not?”
Checking my databases
This simple query reveals the current time zone version of my databases:
select VALUE$ from SYS.PROPS$ where NAME='DST_PRIMARY_TT_VERSION'; VALUE$ ------ 26
This is expected in Oracle Database 22.214.171.124. As I applied TZ Patch DST V.32 now, I need to update my databases.
The following scripts get delivered with Oracle Database 18c onward. You can either use them (which I will do here) or copy them from MOS Note: 1585343.1 – Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database into your environment.
Script to gives how much
TIMESTAMP WITH TIME ZONEdata there is in a database using stats info. No restart required.
Script to approximate how much
TIMESTAMP WITH TIME ZONEdata there is in a database using a
COUNT(*)for each table that has a TSTZ column. This script is useful when using
DBMS_DSTpackage or the scripts of
Time zone upgrade check script
Time zone apply script. Warning: This script will restart the database and adjust time zone data.
It may be intended not having the scripts adjust all containers in one action. You may want DST V32 only in PDBs A and B, whereas PDBs C and D should be still operating with DST V.26.
But the problem here is: Whenever you provision a new PDB, it will get the time zone version it had gotten during creation of the database. This could be years ago. And I’m not sure if that is really intended and wanted.
Workaround 1 – The Unsupported Approach
Rita and I both had the same idea: Use
_ORACLE_SCRIPT to adjust the
PDB$SEED. But this is an unofficial approach as the Multitenant team does not want you to change the
PDB$SEED. Even though this path will work fine and ensure that future PDBs will not have an outdated time zone version.
SQL> alter session set container=PDB$SEED; Session altered. SQL> alter session set "_oracle_script"=true; Session altered. SQL> shutdown Pluggable Database closed. SQL> startup Pluggable Database opened. SQL> select VALUE$ from SYS.PROPS$ where NAME='DST_PRIMARY_TT_VERSION'; VALUE$ ------ 26 SQL> start $OH18/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 126.96.36.199 . INFO: This database is a Multitenant database. INFO: This database is a PDB. INFO: Current PDB is PDB$SEED . 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. Session altered. SQL> start $OH18/rdbms/admin/utltz_upg_apply.sql Session altered. INFO: If an ERROR occurs, the script will EXIT SQL*Plus. INFO: The database RDBMS DST version will be updated to DSTv32 . INFO: This database is a Multitenant database. INFO: This database is a PDB. INFO: Current PDB is PDB$SEED . WARNING: This script will restart the database 2 times WARNING: WITHOUT asking ANY confirmation. WARNING: Hit control-c NOW if this is not intended. INFO: Restarting the database in UPGRADE mode to start the DST upgrade. Pluggable Database closed. Pluggable Database opened. INFO: Starting the RDBMS DST upgrade. INFO: Upgrading all SYS owned TSTZ data. INFO: It might take time before any further output is seen ... An upgrade window has been successfully started. INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data. Pluggable Database closed. Pluggable Database opened. INFO: Upgrading all non-SYS TSTZ data. INFO: It might take time before any further output is seen ... INFO: Do NOT start any application yet that uses TSTZ data! INFO: Next is a list of all upgraded tables: Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" Number of failures: 0 INFO: Total failures during update of TSTZ data: 0 . An upgrade window has been successfully ended. INFO: Your new Server RDBMS DST version is DSTv32 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this SQL*Plus session. INFO: Do not use it for timezone related selects. Session altered. SQL> shutdown Pluggable Database closed. SQL> startup open read only Pluggable Database opened. SQL> select VALUE$ from SYS.PROPS$ where NAME='DST_PRIMARY_TT_VERSION'; VALUE$ ------ 32
Done. Future new PDBs will be created with DST V.32 instead of V.26.
But workaround 2 seems to be a bit more solid as it uses the “vehicle” for executing scripts Multitenant provides.
Workaround 2 – The “better” approach
The vehicle to execute standard scripts in CDB environments is my good ol’ friend
$ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/188.8.131.52/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
-s option excutes the script in the “system” containers
Due to the
OPEN READ ONLY state of the
PDB$SEED the log will show errors. I don’t see any issues with it. But if you want to avoid these errors you need to open the
PDB$SEED in read/write mode as shown in Workaround 1 before.
This approach does not work for the “
apply” script as it will raise an error due to the underlying sql statements. Instead I run it now in two phases. At first for the
$ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/184.108.40.206/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -l /home/oracle -b utlzt_upg_apply_root -d /u01/app/oracle/product/18/rdbms/admin utltz_upg_apply.sql
and then for the
$ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/220.127.116.11/rdbms/admin/catcon.pl -n 1 -c 'PDB$SEED' -l /home/oracle -b utlzt_upg_apply_seed -d /u01/app/oracle/product/18/rdbms/admin utltz_upg_apply.sql
Final check, first in the
PDB$SEED, then in the
alter session set container=pdb$seed; select VALUE$ from SYS.PROPS$ where NAME='DST_PRIMARY_TT_VERSION'; VALUE$ -------------------------------------------------------------------------------- 32 alter session set container=cdb$root; select VALUE$ from SYS.PROPS$ where NAME='DST_PRIMARY_TT_VERSION'; VALUE$ -------------------------------------------------------------------------------- 32
At now let me provision a new PDB and check the time zone version as well:
create pluggable database PDB_NEW admin user adm identified by adm \ file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb_new'); alter session set container=pdb_new; startup select VALUE$ from SYS.PROPS$ where NAME='DST_PRIMARY_TT_VERSION'; VALUE$ -------------------------------------------------------------------------------- 32
This looks good.
The time zone adjustment scripts get deilivered since Oracle Database 18c in the standard ?/rdbms/admin directory and can be used for earlier database releases as well. But be careful when you use them in a Multitenant environment as the PDB$SEED does not get adjusted by default. And hence all your future newly provisionioned pluggable databases will have the time zone version which was valid during creation of the database – no matter how current your time zone patches are.
When you apply a time zone patch, use the adjust scripts also for the PDB$SEED desapite the fact that MOS Note:1509653.1 states “The RDBMS DST version of PDB$SEED can currently not be updated“. But use the official approach with catcon.pl.