Wednesday, November 11, 2009

Updating Timezone

Updating timezone to timezone 11. This is specially handy if the database is later upgraded to 11g Release 2.

Couple of metalink documents to go through 840118.1 and 412160.1 and few KEY points

1. Oracle 9, 10 and 11.1 can be upgraded to RDBMS DSTv11 using patch 8524113, DSTv11 is included in 11gR2 standard version.

2. download and run the utltzuv11.sql (url included in the metalink note 412160.1)

further steps are needed depending on the result of utltzuv11.sql script.

After running utltzuv11.sql, you need do a
select * from sys.sys_tzuv2_temptab;

If a "select * from sys.sys_tzuv2_temptab;" gives no rows then there is no action to take you can simply apply the RDBMS DSTv11 patch without action on stored TSTZ data.

If a "select * from sys.sys_tzuv2_temptab;" give(s) affected TSTZ data then you need to back up the TSTZ data before applying the RDBMS DSTv11 patch.

3. Patch 7695070 needs to be applied before the RDBMS DSTv11 patch can be installed.
Unlike previous DST patches the DSTv11 patch 8524113 needs to have 2 codefixes in place (Bug 7695070 and bug 7395472) to work. and (and higher) include the needed fixes, no need to apply patch 7695070 before installing the RDBMS DSTv11 patch on these versions.

once updated

WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the database timezone version
.... to the latest version which comes with the new release.

will not be prompted when upgrading to 11gR2.