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.

Statistics over external tables

On one Oracle server, I've encountered following part of alert.log

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file C:\APP\diag\rdbms\dev\dev\trace\dev_j002_6004.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file test.txt in EXT_DIR not found

Reason for this error is that DBMS_STATS is tried to be executed over external table - which is all ok - since it is possible to run DBMS_STATS package over external table - but it seems that this external table is bad, since it is missing its file on OS.

And my presumption proves true. I have found that EXT_DIR Oracle directory is pointing to the Windows directory C:\TEMP\utf8load and that there is no test.txt file, and that there is external table (by looking at DBA_EXTERNAL_TABLES view) which is created over that file.

For more information about managing external tables in Oracle 11gR2 look at the following document:

https://docs.oracle.com/cd/E18283_01/server.112/e17120/tables013.htm