When you provision a new database in Oracle Cloud Infrastructure (OCI), it always comes with the latest timezone file installed.

But in a recent migration, we wanted a database with a specific timezone file version.

Here’s how you can get a new PDB with a custom timezone file.

Using OCI Tooling Doesn’t Work

The OCI tooling uses a template file to provision the database faster. But the template file comes with the latest timezone file. Timezone files are part of the Release Update, so the newer Release Update you’re on, the newer the timezone file is.

Using template files for provisioning means that you don’t get to choose which version of the timezone file you want in your database. Further, the usual hacks like removing timezone files from the Oracle home or using the environment variable ORA_TZFILE won’t work.

The Solution

I’m going to create a new PDB in an on-prem database, export that PDB to OCI and use that as my new PDB.

• I start by finding an existing CDB with the desired timezone file, or I create a new one.

  select version from v$timezone_file;
  
  VERSION
  ----------
  44
  

• I don’t need to check

  • The patch level: I’ll sort out any patch differences with Datapatch in OCI.
  • The components: CDBs in OCI have all components installed.

• I create a new empty PDB:

  CREATE PLUGGABLE DATABASE PDBTEMPLATE ADMIN USER ADMIN IDENTIFIED BY mys3cr3tpassw0rd!;
  

• I close and unplug my PDB:

  ALTER PLUGGABLE DATABASE PDBTEMPLATE CLOSE;
  ALTER PLUGGABLE DATABASE PDBTEMPLATE UNPLUG INTO '/home/oracle/pdbtemplate.pdb';
  DROP PLUGGABLE DATABASE PDBTEMPLATE INCLUDING DATAFILES;
  

• I transfer the PDB to my host in OCI. In my test, the size of the PDB was 600 MB.

• Now, I can create a new PDB using the archive file:

  CREATE PLUGGABLE DATABASE PDBNEW USING '/home/oracle/pdbtemplate.pdb';
  ALTER PLUGGABLE DATABASE PDBNEW OPEN READ WRITE;
  

  • The PDB probably opens with plug-in violations. I ignore this for now.

• I need to sort out any patching difference:

  $ORACLE_HOME/OPatch/datapatch -pdbs PDBNEW
  

• After a restart I check for plug-in violations:

  ALTER PLUGGABLE DATABASE PDBNEW CLOSE IMMEDIATE;
  ALTER PLUGGABLE DATABASE PDBNEW OPEN;
  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%');
  
  TYPE                       CAUSE                                                                                           MESSAGE                     ACTION
  __________ ___________________________ _________________________________________________________________________________________________ __________________________
     WARNING    is encrypted tablespace?    Tablespace SYSTEM is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.    Encrypt the tablespace.
     WARNING    is encrypted tablespace?    Tablespace SYSAUX is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.    Encrypt the tablespace.
  

  • The query removes any warnings about components missing in my PDB.
  • I can ignore the warning about missing encryption of SYSTEM and SYSAUX

• Finally, I create an encryption key (or rotate the key if the PDB is already encrypted):

  ALTER SESSION SET CONTAINER=PDBNEW;
  ADMINISTER KEY MANAGEMENT SET KEY
     FORCE KEYSTORE IDENTIFIED BY <keystore-password>
     WITH BACKUP;
  

• Last, let’s check the timezone file versions in my CDB:

  ALTER SESSION SET CONTAINER=CDB$ROOT;
  SELECT   CON$NAME, VALUE$ 
  FROM     CONTAINERS(SYS.PROPS$) 
  WHERE    NAME='DST_PRIMARY_TT_VERSION' 
  ORDER BY 1;
  
     CON$NAME    VALUE$
  ___________ _________
  CDB$ROOT    45
  PDBNEW      44
  

  • The CDB uses the latest timezone file version, 45.
  • My new PDB uses an older timezone file, 44.

That’s It!

This workaround enables you to create PDBs with a custom timezone file version. I can use the same approach if I want a PDB with a specific set of components installed.

If there is network connectivity between the source and target CDB, I could also clone the PDBTEMPLATE over a network link.

Happy migrating!

In a recent migration, Data Pump couldn’t validate a foreign key constraint because rows were missing in the parent table.

01-JUN-26 03:12:18.148: W-8 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
01-JUN-26 03:18:58.517: ORA-39083: Object type REF_CONSTRAINT:"APPUSER"."FK_CHILDTABLE_C001" failed to create with error:
ORA-02298: cannot validate (APPUSER.FK_CHILDTABLE_C001) - parent keys not found
ALTER TABLE "APPUSER"."CHILDTABLE" ADD CONSTRAINT "FK_CHILDTABLE_C001"
  FOREIGN KEY ("C001") REFERENCES "APPUSER"."PARENTTABLE" ("C001") ENABLE

In the source database, the constraint was validated. How come rows are now missing?

Data Pump Export

By default, a Data Pump export is not fully consistent. Instead, each table is consistent only within that object. Here’s an example:

If no users are connected to the system, the export is logically consistent even though the tables were exported as of different SCNs.

But imagine the following:

• There is a parent/child relationship between PARENT1 and CHILD1 enforced by a foreign key constraint.
• A user inserts data at SCN 125. So, in between the export of PARENT1 and CHILD1.
• Parent row is not exported because PARENT1 is exported as of SCN 120.
• Child row is exported because CHILD1 is exported as of SCN 130.

During import, Data Pump can’t create and validate the foreign key constraint because the parent rows are missing.

GoldenGate

In this specific migration, this wasn’t a real problem because Data Pump and GoldenGate work together.

• On export, Data Pump notes the SCN at which each table were exported.
• On import, Data Pump writes the SCNs into the target database.
• GoldenGate uses Automatic Per Table Instantiation to start the replication from the SCN at which the export was made.

Once GoldenGate has replicated the changes beyond SCN 125, we could create and validate the constraint.

Zero Downtime Migration (ZDM)

We were doing the migration using ZDM. We had to instruct ZDM to ignore the error using the response file parameter:

IGNOREIMPORTERRORS=ORA-02298,...

Other Solutions

Fully Consistent Export

You can instruct Data Pump to make a fully consistent export, so all tables are exported as of the same SCN. Using the example from above:

To do so, add the following parameter:

expdp ... flashback_time=systimestamp

In ZDM, you use the response file parameter:

DATAPUMPSETTINGS_DATAPUMPPARAMETERS_FLASHBACKTIME=SYSTIMESTAMP

This requires that there’s enough UNDO in your database. If your export runs for 4 hours before it reaches CHILD1, then you potentially need a lot of undo to read the table as it looked 4 hours ago.

On an big, active database there is a risk that your export now fails with:

ORA-31693: Table data object "APPUSER"."CHILD1" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU15_987654321$" too small

In which case you should start the export in an off-peak period or from a standby database.

Standby Database

If exporting from your primary database gives you problems with ORA-01555, consider doing it from a snapshot standby database.

If no one is using the standby database, then you don’t even have to perform a fully consistent export using FLASHBACK_SCN or FLASHBACK_TIME.

That’s It

Normally, seeing ORA-02298 during a Data Pump import is a serious problem.

However, if you’re doing an initial load then you can probably validate the constraint once replication starts.

Happy exporting!