Oracle time zone version files

For Oracle Transportable Tablespaces process, one of the requirements is that both databases (source and target) should have the same time zone version.

In case that this requirement is not meet, following error will occur:

Source time zone version is 4 and target time zone version is 14.

and

ORA-39339: Table "SCOTT"."TABLE_A" was skipped due to transportable import and TSTZ issues resulting from time zone version mismatch.

So, there are several ways to solve this issue - upgrading source database time zone version, and others (can be found in Oracle documentation).

But, we have following situation:

• Oracle database is upgraded from 10gR2 to 11gR2 without time zone files upgrade, so time zone file version on that database was 4.
• When we create new database on same server for purposes of Oracle Transportable Tablespaces process, new database has time zone files version 14, and we can not finish OTT process.
• We were not able to conduct time zone files version upgrade.

So, we need a way to install database on Oracle 11gR2 with time zone file version 4 as a new database.

Here is description of that process, for Windows machine:

• in registry add key ORA_TZFILE, in Oracle Home, to point to desired time zone version file, as shown in picture, in my case, it was path C:\app\Administrator\product\11.2.0\dbhome_1\oracore\zoneinfo with file timezlrg_4.dat

• create database by using dbca, but choose database creation option as shown in picture, and create database in normal procedure:

• after database creation, connect to it with SQLPlus and execute following command:

SELECT * FROM v$timezone_file;

as a result following result should be got, as shown in picture:

After this, we are having database with time zone file version 4.