How to update/patch the time zone version

How to update/patch the time zone version 1 to 4

(Before or after error while upgrading 

[9i-11gr1])

-The best way to update the time zone is to apply the patch to 9i home before starting upgrade(DOC id   568125.1).

-9i has time zone version 1 but to upgrade to 11g we need time zone version 4.

--We have not got any Warning in pre-check upgrade log (Usual the warning message will be shown).

--We checked for the affected object from time zone version in 9i home.

---As per the above output no object was affected, but still we thought to apply patch, and the reason was unknown as it was taking long time to diagnose. We decided to ignore the patch (which was bad idea).

--While upgrade process (catupgrd.sql), it was stooped with the error related to time zone.

***********************END LINES OF ERROR**********************************

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')

2 FROM registry$database

3 WHERE tz_version != (SELECT version from v$timezone_file);

SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')

*

ERROR at line 1:

ORA-01722: invalid number

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

--After many attempts to apply patch and trying to troubleshoot we have ended up with unknown reason for patch fail.

--As we could not apply patch we thought to do this process manually (after recommended by oracle support)

--Below are the manual steps to follow.

--Now our 9i database was of no use as we have opened 11gr1 (11.1.0.7) in upgrade mode

 

oracle 9i

*********

To check the time stamp version in 9i.

SQL> SELECT CASE COUNT(DISTINCT(tzname))

 WHEN 183 then 1

 WHEN 355 then 1

 WHEN 347 then 1

 WHEN 377 then 2

 WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END

 WHEN 185 then 3

 WHEN 386 then 3

 WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end

 WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end

 WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end

 WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end

 WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end

 ELSE 0 end VERSION

 FROM v$timezone_names;

VERSION

----------

1

Steps as osuser (oracle)--Replace the existing file with new files from patch.

Extract the patch zip file p5632264_92080_SOLARIS64.zip.

unzip p5632264_92080_SOLARIS64.zip

Take the backup of $ORACLE_HOME/oracore/zoneinfo/

cd $ORACLE_HOME/oracore

cp -R zoneinfo zoneinfo.bkp

cd 5632264/files/oracore/zoneinfo/

Copy the three files readme.txt, timezlrg.dat and timezone.dat into $ORACLE_HOME/oracore/zoneinfo/

 

Perform the below activity to update the timezone in registry$database.

UPDATE registry$database SET TZ_VERSION=4 ;

commit ;

Verify both v$timezone_file and registry$database having same value for timezone version and re-try the upgrade.

SQL> select * from registry$database;

PLATFORM_ID

-----------

PLATFORM_NAME

--------------------------------------------------------------------------------

EDITION TZ_VERSION

------------------------------ ----------

4

SQL> select * from v$timezone_file;

FILENAME VERSION

------------ ----------

timezlrg.dat 4

There you good to go.................................................