Time Zones and Oracle Database Patching

I have worked with several clients who rely heavily on time zone data and processing it correctly. If you have columns in your database which are defined as TIMESTAMP WITH TIME ZONE, you need to understand what the relevance is relating to the time zone in your database.

Photo from Unsplash featuring a woman wearing a blue and red patterned dress holding a clock in front of her face

For example, if you need to measure the how long a flight between New York and London took between take-off and landing. You need to know the local time in each location, and that changes, and on different days (Daylight Savings Time – DST – change in the US is on a different day to the UK). If you don’t take into account daylight savings time, which (in 2024) changed on 10th March in California and on 31st March in the UK, you are going to get your calculations wrong. And the date of this changes. And there is an increasing number of countries (and even some parts of countries, like Hawaii in the USA) opting out of changing their daylight savings. So, you need to keep your database time zone up to date to take care of this.

This post is not about how to upgrade your time zone. It’s about why and when, and some pitfalls.

Is the time zone automatically updated during patching?

No. From RU 19.18, [and on 23ai (but not necessarily 21C!) ], all time zone files are installed up to the highest at the point of patch release. However, they are not APPLIED to the CDB or any PDB (including the PDB$SEED).

What is the latest time zone file?

In Oracle 19.18, it’s DST V.40
In Oracle 19.22, it’s DST V.42

Do we need to apply the latest time zone file to the CDB or PDB?

Well, that depends upon your application so you are going to need some analysis of the datatypes and the data within the schema.

If the schema has any columns definitions using the datatype “TIMESTAMP WITH TIME ZONE” then the answer is… maybe. You need to be storing data using named time zones (e.g. Europe/London) and not a simple numeric offset (e.g. -08:00). You also need to understand which named time zones you are using. For example, the somewhat old DST V.9 contains all the updates needed until 2040 if you are only using time zones in the US so you can delay the update for a while (ref: MOS Doc ID 412160.1)

If you do not use “TIMESTAMP WITH TIME ZONE” then the time zone functionality, and therefore patching, will have no effect on your application.

Can I have mixed time zones?

Yes. From 12.2 each PDB and the CDB can have a different time zone. Importantly, the CDB can be on a lower time zone than the PDB’s.

Impact of applying the time zone update

In Oracle 12C, 18C and 19C it is necessary to stop the PDB and perform a STARTUP UPGRADE to prepare for the upgrade, so you have a full outage – even on RAC and Exadata. The time zone cannot be upgraded “rolling”. And if you stop the CDB, you’re stopping every PDB.

From Oracle 21C and 23ai, there is a parameter “TIMEZONE_VERSION_UPGRADE_ONLINE” which sort-of allows an online upgrade of the time zone. What actually happens is that the new time zone is prepared but will only take effect on the next full database restart (again, not rolling!)
Once the database has been restarted, you need to perform a manual upgrade of the database. However, this will put an exclusive lock any tables being upgraded, so they will not be available for update. The online option has merit and splits the upgrade into smaller more manageable parts, but could still result in a significant downtime for you application (between 1 minute and many hours depending upon the tables being updated).

It is a good idea to practice the upgrade on a copy of Production to get an idea of timings!

Datapump Import Restrictions

A datapump import can only be imported into a PDB with a time zone that is the same or higher than the source PDB. If the PDB has a lower time zone level, then you will receive an error

e.g. ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 34

Transportable Tablespace Restriction

A transportable tablespace can only be attached to a target database with exactly the same time zone level as the source. Plugging into a different time zone level could cause a logical data corruption and is not allowed (as the files are imported unchanged)

How can I see what version we are using?

SELECT * FROM v$timezone_file;

FILENAME        VERSION CON_ID
timezlrg_38.dat 38      0

SELECT property_name, SUBSTR(property_value, 1, 30) value
 FROM database_properties
 WHERE property_name LIKE 'DST_%'
 ORDER BY property_name;

PROPERTY_NAME             VALUE
DST_PRIMARY_TT_VERSION       38
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE          NONE

From the CDB we can see all versions

alter session set "_exclude_seed_cdb_view"=FALSE;

SELECT sp.value$ DST_Version, sp.con_id, vp.name 
  FROM CONTAINERS(sys.props$) sp LEFT JOIN v$pdbs vp ON sp.con_id = vp.con_id 
 WHERE sp.name='DST_PRIMARY_TT_VERSION' 
 ORDER BY con_id;

DST_Version CON_ID  NAME
38               1
32               2  PDB$SEED
40               3  UTF8PDB1
32               5  WE8PDB2

How to apply the update?

There's a few different ways, so I'll mention 3 in brief:

1. DBMS_DST (see this blog post by Tim Hall: https://oracle-base.com/articles/misc/update-database-time-zone-file ) offewrs the most control - and typing!

2. Use the below Oracle scripts preferable combined with catcon.pl:

$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

$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

3. If you're on cloud/Exacc, use: dbaascli database upgradeTimeZone 
e.g. dbaascli database upgradeTimeZone --dbname ORCL --restartDB --skipClosedPDBs

A WARNING!

When you update the timezone, it effectively changes a Java class in the database. If the Java class in the CDB does not match the PDB, there is a chance that you will get an ORA-03113 when running Java in the PDB. That’s OK when you patch the PDB, but if you patch the CDB and skip some PDB’s, you might hit this and need to reset the Java system in the PDB. There are 2 fix programs available fixTZa and fixTZb – and you should probably raise an SR to get Oracle Support involved in resolving this!