News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Upgrading to 11g Release 2 and DST Updates

  • Posted by Gavin Soorma
  • On June 16, 2011
  • 0 Comments

When performing an upgrade to Oracle 11g Release 2, we would need to take into consideration if there are any DST (Daylight Saving Time) implications which could be the vase in case we have application tables using the datatypes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.

Oracle 11.2.0.2 uses Time Zone version 14 and 11.2.0.1 use Time Zone version 11. In Oracle 10.2.0.4 the Time Zone is 4 and if we are planning to upgrade from 10.2.0.1 to 11g Release 2, then we need to first patch the database to at least version 10.2.0.2 before we can attempt a direct upgrade to 11g Release 2.

One of the good new features in 11.2.0.2 Database Upgrade Assistant (DBUA) is that DBUA would automatically upgrade Time Zone to version 14 if you checked “Upgrade Timezone Version and TIMESTAMP WITH TIME ZONE data” box as shown below.

If we are using the manual method to upgrade, then the utlu112i.sql (Pre-Upgrade Information Tool) will generate the following warning if the source database is using a timezone file older than version 14:

“WARNING: –>Database is using a timezone file older than version 14. After the release migration, it is suggested that DBMS_DST package be used to upgrade the 10.2.0.4.0 database timezone version to the latest version which comes with the new release.”

The following procedure outlines the manual steps taken to update the DST when upgrading from a 10.2.0.2 database to 11.2.0.2. These are done after we have finished running the catupgrd.sql script to upgrade the database.

Metalink note 815679.1 explains the steps in more detail.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size                  2165168 bytes
Variable Size             960501328 bytes
Database Buffers          100663296 bytes
Redo Buffers                5922816 bytes
Database mounted.
Database opened.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

PL/SQL procedure successfully completed.

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         2 >>> STILL SHOWS PRE 11g R2 Upgrade version



SQL> conn / as sysdba

Connected.


SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

SQL>   

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       2
DST_UPGRADE_STATE              UPGRADE  >> READY FOR UPGRADE


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size                  2165168 bytes
Variable Size             960501328 bytes
Database Buffers          100663296 bytes
Redo Buffers                5922816 bytes
Database mounted.
Database opened.

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> set serveroutput on

VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
SQL> SQL>   2    3    4    5    6    7    8    9   10   11  Failures:0

PL/SQL procedure successfully completed.

SQL>
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
SQL>   2    3    4    5
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

SQL> SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14
 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *