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.
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
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.
TZ_VERSIONof non-CDB or PDB is lower than CDB, you can plugin
TZ_VERSIONof non-CDB or PDB is equal to CDB, you can plugin
TZ_VERSIONof 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
GET_LATEST_TIMEZONE_VERSIONis lower than
TZ_VERSIONof 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.
In my example, my 126.96.36.199 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
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 188.8.131.52.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 184.108.40.206.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
- Upgrade, plug in, convert (noncdb_to_pdb.sql) – 220.127.116.11 and higher
- Plugin, upgrade, convert (noncdb_to_pdb.sql) – 18.104.22.168 and higher
- Clone via NON$CDB, upgrade, convert – 22.214.171.124 and higher