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: , ,

7 Responses to “Timezone file version warning when upgrading Oracle database from 10.2.0.1 to 11.1.0.7”

  1. Teekay Lu Says:

    This worked pretty fast for me. Thanks

  2. Prashanth Says:

    thanks for the information… but i am not able to download if i click on Note 413671.1 link there is an error… pls go through the below error

    Failure of server APACHE bridge:

    No backend server available for connection: timed out after 10 seconds or idempotent set to OFF.

    pls help me ……

    • lifedba Says:

      Hi Prashanth,

      You will need to have an Oracle Metalink/Support account before you can access its support documents. You may probably need to check with the Oracle Admin in your organization to get the approval before hand.

  3. FoodBeverages Says:

    Having read this I thought it was very enlightening.
    I appreciate you spending some time and effort to put this article together.
    I once again find myself personally spending a lot of time both reading and
    posting comments. But so what, it was still worthwhile!

  4. Remise En Etat Presse Says:

    I am in fact happy to read this blog posts which carries tons of useful data,
    thanks for providing these statistics.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: