While working on a recent migration, I came across an interesting requirement.
We needed to create a new PDB in Oracle Cloud Infrastructure (OCI), but not with the latest timezone file. We needed a specific timezone file version to match the source environment.
At first, this looked simple. But OCI provisioning doesn't give you an option to select the timezone file version.
So I had to look for another approach.
Why OCI Provisioning Doesn't Help
When you create a database using the OCI tooling, Oracle generally provisions it using a template.
That template already contains the timezone file version associated with the Oracle Release Update.
So, if your OCI database is running a newer RU, you'll normally get the newer timezone file as well.
Things that might normally work in an on-prem environment, such as removing timezone files from the Oracle Home or setting:
ORA_TZFILE
don't really help here.
So the question becomes:
How can we get a PDB with an older or specific timezone file version into OCI?
The Approach
The workaround is actually quite straightforward:
Create the PDB somewhere that already has the required timezone file version, unplug it, move it to OCI, and plug it into the OCI CDB.
For example, I had an existing CDB with timezone file version 44:
SELECT version
FROM v$timezone_file;
VERSION
-------
44
I then created an empty PDB:
CREATE PLUGGABLE DATABASE PDBTEMPLATE
ADMIN USER ADMIN IDENTIFIED BY "mys3cr3tpassw0rd!";
After that, I closed and unplugged the PDB:
ALTER PLUGGABLE DATABASE PDBTEMPLATE CLOSE;
ALTER PLUGGABLE DATABASE PDBTEMPLATE
UNPLUG INTO '/home/oracle/pdbtemplate.pdb';
DROP PLUGGABLE DATABASE PDBTEMPLATE INCLUDING DATAFILES;
The resulting PDB archive was relatively small in my test environment, around 600 MB, so transferring it to the OCI host was easy.
Create the PDB in OCI
Once the archive was available on the OCI host, I created the new PDB from it:
CREATE PLUGGABLE DATABASE PDBNEW
USING '/home/oracle/pdbtemplate.pdb';
ALTER PLUGGABLE DATABASE PDBNEW OPEN READ WRITE;
At this point, you may see some plug-in violations.
Don't panic. Some of them are expected because the source and target environments aren't exactly the same.
Run Datapatch
The next step is to make sure the PDB has the required SQL patch changes for the OCI environment.
I used:
$ORACLE_HOME/OPatch/datapatch -pdbs PDBNEW
After the patching completed, I restarted the PDB and checked the plug-in violations again:
ALTER PLUGGABLE DATABASE PDBNEW CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE PDBNEW OPEN;
Then:
SELECT TYPE,
CAUSE,
MESSAGE,
ACTION
FROM PDB_PLUG_IN_VIOLATIONS
WHERE NAME = 'PDBNEW'
AND STATUS != 'RESOLVED'
AND NOT (
CAUSE = 'OPTION'
AND TYPE = 'WARNING'
AND MESSAGE LIKE '%PDB installed version NULL%'
);
In my case, the remaining warnings were related to encryption of the SYSTEM and SYSAUX tablespaces.
OCI expects tablespaces to be encrypted, so this needs to be handled according to the target environment's encryption requirements.
Set the Encryption Key
Finally, I created/rotated the encryption key for the PDB:
ALTER SESSION SET CONTAINER=PDBNEW;
ADMINISTER KEY MANAGEMENT SET KEY
FORCE KEYSTORE IDENTIFIED BY <keystore-password>
WITH BACKUP;
The Important Part — Check the Timezone Version
Now comes the part we actually wanted to achieve.
I checked the timezone versions from the CDB root:
ALTER SESSION SET CONTAINER=CDB$ROOT;
SELECT CON$NAME,
VALUE$
FROM CONTAINERS(SYS.PROPS$)
WHERE NAME = 'DST_PRIMARY_TT_VERSION'
ORDER BY 1;
The result looked like this:
CON$NAME VALUE$
--------- ------
CDB$ROOT 45
PDBNEW 44
And that's exactly what I wanted.
The OCI CDB is using timezone file version 45, while my newly created PDB is using version 44.
So the PDB retained the timezone file version from the source environment.
One More Option
If the source and target CDBs have network connectivity, you don't necessarily have to move the PDB archive manually.
You can also consider cloning the PDB over a database link.
That can make the process much easier when you're dealing with larger PDBs.
Final Thoughts
This is one of those Oracle migration requirements that looks difficult at first because OCI provisioning doesn't provide a simple option to select the timezone file version.
The workaround is to create the PDB from an environment that already has the timezone version you need and then plug that PDB into the OCI CDB.
The same basic approach can also be useful when you need a PDB with a specific set of components or configuration characteristics that aren't available through the standard OCI provisioning workflow.
For migration projects, small details like timezone file versions can become important, especially when the source and target environments are running different Oracle Release Updates.
Sometimes the easiest solution isn't changing the OCI database — it's bringing the right PDB to OCI.
#Oracle #OracleDatabase #OCI #OracleCloud #PDB #Multitenant #DatabaseMigration #OracleDBA #TimeZone #CloudMigration
