Timezone file version warning when upgrading Oracle database from 10.2.0.1 to 11.1.0.7

Just recollect something happened a few months ago when upgrading Oracle database from version 10.2.0.1 to version 11.1.0.7, hope the following workaround helps.

Sometimes, thing is simple but not easy.

1.   Purpose

Provide a workaround for upgrading Oracle Time Zone File version from v2 to v4 before upgrading Oracle database from version 10.2.0.1 to version 11.1.0.7.

2.   Symptoms

In this Oracle database 11gR1 upgrade operation, several warnings and errors raised by Oracle database upgrade scripts, the following typical problem is the main concern. Unless this problem is solved, otherwise the Oracle database 11gR1 upgrade operation cannot be completed successfully.

2.1   Time Zone File Version

After running Oracle 11g upgrade prerequisite checking script utlu111i.sql on Oracle 10g (10.2.0.1) database, the following warning is raised, and the upgrade operation cannot be continued with a low time zone file version:

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: –> Database is using an old timezone file version.

…. Patch the 10.2.0.1.0 database to timezone file version 4

…. BEFORE upgrading the database.  Re-run utlu111i.sql after

…. patching the database to record the new timezone file version.

3.   Root Cause

3.1   Time Zone File Version Warning

The time zone files that are supplied with Oracle Database 11g Release 1 (11.1) have been updated from version 2 to version 4 to reflect changes in transition rules for some time zone regions. The changes might affect existing data of TIMESTAMP WITH TIME ZONE data type. If you have not already updated your existing databases to time zone file version 4, you must do so prior to upgrading to Oracle Database 11g Release 1 (11.1).

4.   Solution

4.1   Upgrade Time Zone File Version

4.1.1       Overview

In Oracle database 10.2.0, Oracle use Time Zone File version 2, we need to upgrade it to version 4 before we can upgrade our database system to Oracle database 11gR1. However, this is no any official Oracle patches available for upgrading time zone file to version 4 for Oracle database 10.2.0.1, which is the Oracle database version we are going to upgrade.

Although we have tried out all the possible solutions on Oracle Metalink to work this problem out, the Oracle database 11gR1 was unable to be upgraded due to the cause of time zone file version lower than version 4.

4.1.2       Oracle Support Solution

The following two references are the most suitable ways to solve our problem. However, these two official solutions could not solve our problem.

Please refer to the following two Metalink reference for more detail:

1)      Note 413671.1 Applying “version 4″ Time Zone Files on an Oracle Database

2)      Note 396387.1 Workarounds when Database time zone patches are not available for your patchset

4.1.3       Action Plan

We tried an ultimate action to solve this problem. Fortunately, it works perfectly and the upgrade operation has been done successfully within the expected time and result.

1)      Backup Time Zone File Version 2

Backup Time Zone Files of Oracle Database 10.2.0.1 which is version 2, in case we can rollback our Time Zone File upgrade operation:

[oracle@db ~]$ cd  /u01/app/oracle/product/10.2.0/db_10201/oracore/zoneinfo

[oracle@db ~]$ cp timezlrg.dat  timezlrg.dat.v2

[oracle@db ~]$ cp timezone.dat  timezone.dat.v2

2)      Copy Time Zone File Version 4

Copy Time Zone File from Oracle 10.2.0.3 Home directory which is version 4, to Oracle 10.2.0.1 Home directory:

[oracle@db ~]$ cp /u01/app/oracle/product/10.2.0/db_10203/oracore/zoneinfo/*.* /u01/app/oracle/product/10.2.0/db_10201/oracore/zoneinfo

3)      Confirm the New Time Zone File Version

Make sure the new Time Zone Files version is reflected instantly by issuing the following command:

SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 4

4)      Continue to upgrade

After the above operations have been done successfully, you can now retry the Oracle 11g upgrade prerequisite checking by script utlu111i.sql. At this time, you should see the following result with the ‘V4’ of Time Zone file version, which means we can continue the rest parts of the Oracle 11gR1 upgrade operation.

**********************************************************************

Database:

**********************************************************************

…

–> timezone file: V4

About these ads

Tags: 11g, timezone file, upgrade