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
