RUs contain now all available DST patches

At first, Happy New Year everybody. There was silence on the blog for some weeks but I took an extended longer time off. Well, and once you return, there are some emails in your inbox which need some work. You’ll see several new blog posts getting published in the coming days. Back to normal, yesterday night RU 19.18.0 among other patch bundles has been released. And there’s a huge fantastic surprise in it: RUs contain now all available DST patches.

Photo by Lukas Blazek on Unsplash

What’s the story?

There are some good news. We discussed this internally for a long long time. So let me give you some background information at first.

A long while ago with the flexibility of the CDB architecture, we ran into more and more time zone issues. Even though since 12.2.0.1 a CDB could hold PDBs with different time zone settings, it was still required that the matching DST file at least existed in the target home. A typical example causing trouble for you is when you unplug a PDB from a CDB with DST V.38, and you want to plug it into a CDB where only DST V.37 has been installed in the home, to be precise in $ORACLE_HOME/oracore/zoneinfo.

At next, we hit the same problem with AutoUpgrade’s unplug/plug capabilities. Or even before already when AutoUpgrade got used between two servers. When your source database got patched to let’s say DST V.38, and you just took a plain 19c home having DST V.32 as this was the default at build date in 2019, then we could not see the target environment. And the upgraded failed.

Or take it a step further – and this is a topic I received emails at least twice a month about. You just wanted to move a database from one VM in our Cloud to another VM – and you patched DST before. But the target did not have the matching DST file included by default.

And so on, and so on.

The massive constraint is that time zone does not allow any sort of downgrade or reversion. Once your database is on a given DST version, you can’t lower it anymore.

Now our idea for a longer while was to have all DST files included in Release Updates. This way, at first, you don’t need to download and install the most recent DST patch. But the main focus was on allowing flexibility and removing a typical pitfall.

What? A surprise?

We ship now all available DST patches with an RU and deploy them to your $ORACLE_HOME/oracore/zoneinfo directory.

And before you start venting, read on. There is an important distinction between delivering a patch and applying it.

Hence, don’t be scared – you’ll easily recognize that it has ZERO impact on any of your databases when you apply an RU. And it took me a bit of patience to explain this several times. When we discuss time zone patching we need to differ between two parts:

1. Applying the DST patch or files to your $ORACLE_HOME/oracore/zoneinfo
2. Adjusting a database and change the DST version

The first part, applying the DST patch, is absolutely non-interruptive, it can be even done online while all databases are up and running. Why is that? Simply because there will be files copied to the home which aren’t actively used by the database. You could copy them in for ages already, and many customers I know did so.

The second part, adjusting the database, is interruptive. When you adjust the time zone version of your database, at first the dictionary will be adjusted and we require the database in STARTUP UPGRADE mode, then as next steps potential time zone data will be checked and adjusted as well if necessary. This isn’t downtime officially, but in fact it means that locks get used and data is not accessible for DMLs. So basically, this means downtime to most customers as well.

Now, with Release Updates starting from 19.18.0 we do only part 1: We copy in all the missing and newly available DST files.

Nothing gets interrupted. Nothing needs to be done. No action is required on your side.

This is really important to mention. In this case, we just ease your DBA life – regardless whether you are sailing on-prem or in clouds or in a hybrid universe.

Certainly, you could adjust DST

Of course, as you know, we have scripts in $ORACLE_HOME/rdbms/admin which allow you to adjust the DST level of your database to a newer one. So you could adjust time zone – but you don’t have to.

-rw-r--r--. 1 oracle dba  8317 Feb 24  2017 utltz_countstats.sql
-rw-r--r--. 1 oracle dba  7423 Feb 24  2017 utltz_countstar.sql
-rw-r--r--. 1 oracle dba 34210 Oct 15  2021 utltz_upg_check.sql
-rw-r--r--. 1 oracle dba 21793 Oct 15  2021 utltz_upg_apply.sql

Instead of the scripts you could use DBMS_DST directly by yourself but the scripts are way more convenient and do some error handling. See the documentation for further information.

Did one say “Upgrade”?

As you know, AutoUpgrade does this change for you unless you change the parameter

• upg1.timezone_upg=no

By default, AutoUpgrade will change it to newest available level. So if you don’t want time zone to be upgraded, you need to explicitly set the above parameter in your AutoUpgrade’s configuration file.

Yes, this is the one of the two situations where you need to be aware of this change. When you run AutoUpgrade to upgrade to Oracle Database 19.18.0 or newer, and you did not tick off the timezone upgrade parameter, then it will upgrade your database to DST V.40, the newest available one right now.

So please be aware of this.

For everybody else, this is only a convenience making things easier. Again, no timezone change happens just because the patch files get copied to your home.

And how about a new database?

Above I wrote “two situations”. So there is another one.

When you create a new database, regardless whether this is a non-CDB or a CDB, it will pick the newest available time zone file by default. So once you applied 19.18.0 or a newer RU, and you create a new database, it will be created with the newest available DST file.

If you’d like to avoid this and have your database still created with an older DST file then you can use the workaround I describe here. We mainly used this workaround for Transportable Tablespace databases to avoid time zone issues during transport. But you can certainly use this w/a for every new database creation.

Summary

We think that this change will make things a lot easier.

Don’t get puzzled please. Deploying the DST binary files does not mean that your database gets a TZ upgrade massage. You’ll have to trigger this by yourself. But having the available DST files in the Oracle Home will get you way more flexibility, and it will help avoiding the typical TZ mismatch issues we see every other week.

For the CDB architecture, there is no issue as well. In fact, having the files is a big relief. Since Oracle Database 12.2.0.1 you can have mixed time zone versions in PDBs. And the CDB$ROOT does not have to be on the highest value interestingly. So a PDB can be plugged in with DST V.40 while the CDB$ROOT and PDB$SEED are on DST V.32. The important point is that the files need to be available in the Oracle Home. Which they are from now on.

One final comment:
Of course, you will find the patches in the 21c RU as well. This is not a 19c-only thing.

I will apply 19.18.0 and 21.9.0 this week and see if there is more to say.

Further Links and Information

• Does PDB$SEED get time zone patched or not?
• How to patch all PDBs with a new time zone file
• utltz Scripts usage – Oracle Globalization Support Guide
• Create an Oracle database with non-default time zone

–Mike