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.................................................
