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

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?

Photo by Luis Cortes on Unsplash

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:

• https://mikedietrichde.com/hands-on-lab/hol-18c-upgrade/#TZ

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 …

https://support.oracle.com/epmos/faces/DocumentDisplay?id=412160.1

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 12.2.0.1.14
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/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/12.2.0.1'

Patching component oracle.oracore.rsf, 12.2.0.1.0...
Patch 28125601 successfully applied.
Log file location: /u01/app/oracle/product/12.2.0.1/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 (12.1.0.1 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 12.1.0.2 and 12.1.0.1). 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 12.2.0.1. As I applied TZ Patch DST V.32 now, I need to update my databases.

The Scripts

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.

• ?/rdbms/admin/utltz_countstats.sql
  Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.
  .
• ?/rdbms/admin/utltz_countstar.sql
  Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.
  .
• ?/rdbms/admin/utltz_upg_check.sql
  Time zone upgrade check script
  .
• ?/rdbms/admin/utltz_upg_apply.sql
  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 12.2.0.1 .
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 catcon.pl.

$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

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 root container:

$ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.2.0.1/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 seed:

$ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.2.0.1/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 CDB$ROOT:

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.

Summary

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.

Lesson learned:
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.

Further Reading

See: How to patch all PDBs with a new time zone file? (Dec 18, 2018)

–Mike