Database Migration from non-CDB to PDB – The Time Zone Pitfall

There are several pitfalls when you plugin a non-CDB into a CDB environment. I’d like to highlight some of them – and show you potential workarounds as well. This is part of a series of blog posts to make your migration from non-CDB to PDB a bit smoother.

Photo by Allef Vinicius on Unsplash

Database Migration from non-CDB to PDB – The Time Zone Pitfall

Interestingly, there is no issue with different time zone settings within a single CDB. Your CDB$ROOT can be on DST V.32 whereas a PDB you plugin can be already on DST V.33. But only if you fulfill one important requirement: The patch for DST V.33 must be installed into the CDB’s home. If you did that, you won’t see a warning. But if you didn’t take care yet, you need to install it before plugging in.

Time Zone Settings

You will find the available (and installed) time zone files in $ORACLE_HOME/oracore/zoneinfo. Check the time zone setting your database is adjusted to with either query:

SELECT * FROM v$timezone_file;

FILENAME             VERSION    CON_ID
-------------------- ---------- ------
timezlrg_32.dat      32         0

or

select TZ_VERSION from registry$database;

TZ_VERSION
----------
	32

Do this for your non-CDB as well as in the CDB$ROOT of your future CDB. If they match or if the value in the CDB is higher than in the non-CDB, all is fine. If not, you need to apply the time zone patch to your CDB’s home before plugin.

• If TZ_VERSION of non-CDB or PDB is lower than CDB, you can plugin
• If TZ_VERSION of non-CDB or PDB is equal to CDB, you can plugin
• If TZ_VERSION of non-CDB or PDB is higher than CDB, you need to check in the CDB:

  SELECT DBMS_DST.get_latest_timezone_version
  FROM   dual;
  
  GET_LATEST_TIMEZONE_VERSION
  ---------------------------
  			 32

  • If GET_LATEST_TIMEZONE_VERSION is lower than TZ_VERSION of the non-CDB or PDB, you must patch the CDB first

Keep in mind that there’s no Time Zone Downgrade available. Once you lifted the time zone setting in your database, you can’t revert.

An Example

In my example, my 12.2.0.1 non-CDB has DST V.33 already whereas the CDB’s 19.3.0 installation has DST V.32. With “has” I mean: patch with time zone files is installed and present in $ORACLE_HOME/oracore/zoneinfo.

When I run the plug compatibility check, I see this WARNING which in my opinion must be flagged as an ERROR instead (I cut off the other warnings and errors for another paragraph below):

CON_ID NAME	TYPE	  MESSAGE					     STATUS
------ -------- --------- -------------------------------------------------- ---------
     1 DB12	...       ...

     1 DB12	WARNING   PDB requires time zone version file(s): primary ve PENDING
			  rsion 33 while CDB does not have such time zone ve
			  rsion file(s) installed.

The issue gets detected. And there’s a clear advice what you need to do.

But out of curiosity I still plugged in the non-CDB following this track to see what happens. The plugin operation works interestingly fine. But the subsequent and necessary upgrade fails as the upgrade procedure does not upgrade the database when the database’s (or PDB’s) time zone version is higher than the file in the home. You won’t get a proper warning unless you drill down to the logfiles in this case.

Actually the plugin shouldn’t have been allowed as there’s no plug-out back into non-CDB available. Unless I apply the time zone patch to the destination home, I can’t use this PDB. Once I apply it, the upgrade completes successfully. This can be very tricky if the time zone patch for the destination home does not exist yet. Be careful and check upfront!

Please see also this blog post about how to patch all PDBs with a time zone patch correctly.

Other migration techniques?

When you use other migration techniques such as export and import with Data Pump or Transportable Tablespaces, you must pay even more attention to this topic. When you export from a database with DST V.33, you can’t import into a database with DST V.32. This by the way applies also to migrations especially into the Oracle Autonomous Cloud. Check the time zone setting before you start your import. And open an SR if you need the time zone value to be raised.

The same applies when you attempt to use Transportable Tablespaces or Full Transportable Export/Import. Data Pump blocks the tablespace contents import when it detects different time zone settings. Yes, different is the keyword here. As the files in the tablespaces files are untouched by the import process – only the “description” of what’s in these tablespaces gets imported – the potentially existing time zone data would be logically corrupt. Hence, the attempt to plugin such a tablespace will be rejected. Plan this carefully upfront.

Summary and Recommendation

Time zone topics look pretty simple. But the devil is in the detail. You really need to take care. Especially for migrations where you use Data Pump and/or Transportable Tablespaces. Still, even for the plugin of a non-CDB or a PDB, time zone characteristics matter. Watch out – and my recommendation is to keep your entire landscape on equal time zone settings. If you need to create databases on purpose with a lower than the maximum time zone setting (for instance, a receiving CDB with DST V.26 in Oracle 19c), then please follow this advice: Create a database with non-default time zone.

A final word regarding time zone patches

Together with some customers, I’ve had a lovely time zone patch experience in the past months (DST Time Zone Patch for Oracle 18.5.0). I’m confident that time zone patches get now built on the base release and not on top of an RU anymore. Still, please double check this by yourself. The README of DST V.33 patch 28852325 does not look so promising:

Oracle Database 19 Release 19.3.0.0.190416DBRU

CORE Patch for Bug# 28852325 for Generic Platforms

This patch is non-RAC Rolling Installable.

Released: Thu May 30 15:18:31 2019
  
This document describes how you can install the CORE DST patch for bug#  28852325 on your Oracle Database 19 Release 19.3.0.0.190416DBRU

And it has another important line: It is non-RAC rolling installable. I by myself applied it while my database is up and running. But I guess, this is not the official way. Officially I read this as “applying a time zone patch requires downtime – regardless if you have a RAC or not”. Please take this into account as well.

Finally, I don’t see time zone patches being produced proactively. This can create a terrible situation. You have applied DST V.33 Patch 28852325 to your 19.3.0 home, you’d like to upgrade to 19.4.0 – but as of now, there’s no time zone patch available which would fit into a 19.4.0 home. Please open an SR and – if necessary – escalate it to get a matching patch.

Further Information and Links

• How to patch all PDBs with a new time zone file? – December 18, 2018
• Does the PDB$SEED get time zone patched or not? – December 17, 2018
• Data Pump: The Time Zone Pitfalls – May 14, 2019
• Create a database with non-default time zone – December 8, 2016
• AutoUpgrade – Advanced Config File Options – June 28, 2019
• MOS Note:412160.1 – Download newest DST patches
• DST V.33 Patch 28852325

Typical Plugin Issues and Workarounds

• Typical Plugin Issues and Workarounds
• The Compatible Pitfall
• The Time Zone Pitfall
• The Component Pitfall
• The Patch Level Pitfall
• Various Pitfalls
• The Fallback Challenge
• The Minimal Downtime Challenge

Related Posts

• Upgrade, plug in, convert (noncdb_to_pdb.sql) – 11.2.0.4 and higher
• Plugin, upgrade, convert (noncdb_to_pdb.sql) – 12.2.0.1 and higher
• Clone via NON$CDB, upgrade, convert – 12.1.0.2 and higher

–Mike