Timezone Upgrade Steps

Posted: July 27, 2014 in Database General, TimeZone
Tags: , ,

Below are steps to upgrade the timezone from version 4 to 14 in 11.2.0.4 database. When you upgrade a 10g (10.2.0.4) database to 11g (11.2.0.4) database, while running the utlu112i.sql script, you will get a warning for old timezone of database which needs to be upgraded 14. The warning is like below:

WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended 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.

One database is upgraded successfully to 11.2.0.4, follow the below steps to upgrade the timezone as well. Also refer to oracle support doc 1358166.1 for detailed information on timezone upgrade. It’s recommended to go through this document to understand the affects of below commands and any deviation from below steps if slight change in versions involved in this upgrade.

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         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         4

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_DST.BEGIN_PREPARE(14);

PL/SQL procedure successfully completed.

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         4
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              PREPARE

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> set serveroutput on
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

no rows selected

SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

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         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL>
SQL> select name from v$database;

NAME
---------
TEST11G

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1904128000 bytes
Fixed Size                  2222600 bytes
Variable Size             721421816 bytes
Database Buffers         1174405120 bytes
Redo Buffers                6078464 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL>
SQL> set timing on
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         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.40
SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Elapsed: 00:00:00.17
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

Elapsed: 00:00:00.18
SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

Elapsed: 00:00:00.03
SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

Elapsed: 00:00:00.01
SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.61

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

Elapsed: 00:00:00.03
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       4
DST_UPGRADE_STATE              UPGRADE

Elapsed: 00:00:00.01
SQL> select OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS from ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

Elapsed: 00:00:02.44
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1904128000 bytes
Fixed Size                  2222600 bytes
Variable Size             721421816 bytes
Database Buffers         1174405120 bytes
Redo Buffers                6078464 bytes
Database mounted.
Database opened.
SQL>

SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.33
SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL> VAR numfail number
SQL> 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;
   /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.48
SQL> SELECT * FROM sys.dst$error_table;

no rows selected

Elapsed: 00:00:00.02
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
SQL>   2    3    4    5

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.32
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

Elapsed: 00:00:00.00
SQL> SELECT * FROM v$timezone_file;

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

Elapsed: 00:00:00.00
SQL>

Remember to follow all the steps without fail as missing any step would lead to serious issue. Hope, this will help.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s