Create a database with NON-DEFAULT Time Zone

One requirement of Transportable Tablespaces (and of course Full Transportable Export/Import as well) is to have identical database character sets AND identical time zone settings.

Problem

Source database has a lower time zone setting than the default target database in the destination home.

Lets assume you’d like to migrate an Oracle 11.2.0.4 off IBM AIX and migrate it into Oracle 12.1.0.2 on an Exadata meaning Oracle Linux 6. The source time zone version if it has never been upgraded would be TZ V14 – whereas the new Oracle 12.1.0.2 database you’d create would get TZ V18 by default.

Solution 1 – Upgrade Time Zone version in Source

You could now “simply” upgrade the Time Zone version in the source database meaning you’d apply the time zone patch matching the default time zone of the destination database home (see MOS Note: 412160.1 for the list of available Time Zone patches). Then you would have to adjust the data with the scripts provided in MOS Note:1585343.1

Or you could go a step further and apply the most recent available time zone patches to both homes which I’d recommend in this case. Then you’ll adjust the source database before initiating the transport steps including the copy operations.

I have put “simply” in quotation marks on purpose as often this may not be an option. Changing the source’s time zone version will incur downtime and a restart of the database. Furthermore in case of somebody has a very high amount of time zone dependent data it may take a while.

Solution 2 – Create Destination Database with lower Time Zone version

Therefore I’d consider the second solution as the more interesting and practical one: Create the destination database to transport to with a different time zone version than the default. In Oracle Database 12.1.0.2 the default would be TZ V18.

Step 1: Create database creation scripts with DBCA

I haven’t tried if it would work as well to let the DBCA directly create a custom database but I don’t trust Java applications using my environment variables so I prefer the script version. It does not work to use one of the seed databases in DBCA (DWH and OLTP and GENERAL PURPOSE) as those all use the default version of the release without an option to override it.

Step 2: Set environment variable ORA_TZFILE

The time zone files are located in $ORACLE_HOME/oracore/zoneinfo. By using the environment variable ORA_TZFILE we can override the default of using the highest numbered time zone file in this directory:

$ export  ORA_TZFILE=/u01/app/oracle/product/12.1.0.2/oracore/zoneinfo/timezone_14.dat

Step 3: Create the database with the “sh” script

DBCA has created the “create database” scripts and one named <SID>.sh. Start the creation by:

$ ./MIKE.sh

Step 4: Verify the correct time zone setting of your new database

The following query reflects the current time zone version of this database:

SQL> select value$ "TZ Version" from SYS.PROPS$ where name='DST_PRIMARY_TT_VERSION';

In my example the result will be 14 now instead of 18 (the default for an Oracle 12.1.0.2 database).

–Mike