DST upgrade to 14 after upgrading database from 10.2.0.4 to 11.2.0.3

Current timezone version is lower than 14 (typically 4):

When upgrading from 10.1.0.x , 10.2.0.x or 11.1.0.x to 11.2.0.3:
For 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 there is no need to apply any patchset before upgrading to 11.2.0.3

Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5.

After the upgrade to 11.2.0.3 you can:
(recommended) update the 11.2.0.3 database(s) to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a) onwards, when going to DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home

(optional) update to a higher DST than 14 version if this is needed.
Apply the latest 11.2.0.3 DST update patch after the upgrade to 11.2.0.3 and use DDBMS_DST.

The lastest DST patch and related note on how to apply this is found in Note 412160.1 Updated Time Zones in Oracle Time Zone File patches under “C) Notes covering the current DST available updates”. Simply follow the note for the latest DST update. If decided it is possible to upgrade the database’s timezone after the database upgrade by following the above metalink notes. In this case timezone was upgraded at the same time database was upgraded.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2138521600 bytes
Fixed Size 2161024 bytes
Variable Size 1543505536 bytes
Database Buffers 570425344 bytes
Redo Buffers 22429696 bytes
Database mounted.
Database opened.
SQL>
SQL> set serveroutput on
SQL>

– check if previous prepare window is ended

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

– If there are objects containing TSTZ data in recycle bin,
– please purge the bin now.
– Otherwise dbms_dst.begin_upgrade will report “ORA-38301: Can not perform DDL/DML over objects in Recycle Bin”.

SQL> SQL> purge dba_recyclebin;

DBA Recyclebin purged.

– clean used tables

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

– this alter session might speed up DBMS_DST on some db’s
– see Bug 10209691 / Bug 12658443

SQL> SQL> alter session set “_with_subquery”=materialize;

Session altered.

– to avoid the issue in note 1407273.1

SQL> SQL> alter session set “_simple_view_merging”=TRUE;

Session altered.

– Set client_info so one can use:
– select …. from V$SESSION where CLIENT_INFO = ‘upg_tzv';

SQL> SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(‘upg_tzv’);

PL/SQL procedure successfully completed.

– start the actual upgrade window

SQL> SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

– the message
– “An upgrade window has been successfully started.”
– will be seen

SQL> SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

– check if this select give no rows, if it does something went wrong

SQL> SQL> SELECT * FROM sys.dst$error_table;

no rows selected

– check if this select gives

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 4
DST_UPGRADE_STATE UPGRADE

– Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE

SQL> SQL> select OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS from ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS=’YES';

OWNER TABLE_NAME UPG
—————————— —————————— —
ASO AQ$_ASO_ORDER_FEEDBACK_T_S YES
OKC AQ$_OKC_AQ_EV_TAB_S YES
AR AQ$_AR_REV_REC_QT_S YES
XNP AQ$_XNP_OUT_MSG_QTAB_S YES
CS AQ$_CS_SERVICE_REQUEST_OQT_S YES
CS AQ$_CS_SERVICE_REQUEST_IQT_S YES
APPLSYS AQ$_WF_DEFERRED_TABLE_M_S YES
APPLSYS AQ$_WF_OUT_S YES
APPLSYS AQ$_WF_REPLAY_IN_S YES
APPLSYS AQ$_WF_IN_S YES
APPLSYS AQ$_WF_ERROR_S YES

OWNER TABLE_NAME UPG
—————————— —————————— —
APPLSYS AQ$_WF_DEFERRED_S YES
APPLSYS AQ$_WF_REPLAY_OUT_S YES
APPLSYS AQ$_WF_JMS_IN_S YES
APPLSYS AQ$_WF_JMS_OUT_S YES
APPLSYS AQ$_WF_CONTROL_S YES
APPLSYS AQ$_WF_NOTIFICATION_IN_S YES
APPLSYS AQ$_WF_NOTIFICATION_OUT_S YES
APPLSYS AQ$_WF_WS_JMS_IN_S YES
APPLSYS AQ$_WF_JAVA_DEFERRED_S YES
APPLSYS AQ$_WF_WS_JMS_OUT_S YES
APPLSYS AQ$_WF_JAVA_ERROR_S YES

OWNER TABLE_NAME UPG
—————————— —————————— —
APPLSYS AQ$_WF_JMS_JMS_OUT_S YES
APPLSYS AQ$_WF_WS_SAMPLE_S YES
APPLSYS AQ$_FND_CP_GSM_IPC_AQTBL_S YES
APPLSYS AQ$_FND_CP_GSM_IPC_AQTBL_L YES
APPLSYS AQ$_FND_CP_TM_AQTBL_S YES
APPLSYS AQ$_FND_CP_TM_RET_AQTBL_S YES
AR AQ$_AR_REV_REC_QT_L YES
ASO AQ$_ASO_ORDER_FEEDBACK_T_L YES
XNP AQ$_XNP_OUT_MSG_QTAB_L YES
APPLSYS AQ$_WF_NOTIFICATION_OUT_L YES
OKC AQ$_OKC_AQ_EV_TAB_L YES

OWNER TABLE_NAME UPG
—————————— —————————— —
CS AQ$_CS_SERVICE_REQUEST_OQT_L YES
CS AQ$_CS_SERVICE_REQUEST_IQT_L YES
APPLSYS AQ$_WF_WS_JMS_IN_L YES
APPLSYS AQ$_WF_DEFERRED_TABLE_M_L YES
APPLSYS AQ$_WF_OUT_L YES
APPLSYS AQ$_WF_REPLAY_IN_L YES
APPLSYS AQ$_WF_IN_L YES
APPLSYS AQ$_WF_ERROR_L YES
APPLSYS AQ$_WF_DEFERRED_L YES
APPLSYS AQ$_WF_REPLAY_OUT_L YES
APPLSYS AQ$_WF_JMS_IN_L YES

OWNER TABLE_NAME UPG
—————————— —————————— —
APPLSYS AQ$_WF_JMS_OUT_L YES
APPLSYS AQ$_WF_CONTROL_L YES
APPLSYS AQ$_WF_NOTIFICATION_IN_L YES
APPLSYS AQ$_WF_JAVA_DEFERRED_L YES
APPLSYS AQ$_WF_WS_JMS_OUT_L YES
APPLSYS AQ$_WF_JAVA_ERROR_L YES
APPLSYS AQ$_WF_JMS_JMS_OUT_L YES
APPLSYS AQ$_WF_WS_SAMPLE_L YES
APPLSYS AQ$_FND_CP_TM_AQTBL_L YES
APPLSYS AQ$_FND_CP_TM_RET_AQTBL_L YES
APPLSYS AQ$_FND_CP_GSM_OPP_AQTBL_S YES

OWNER TABLE_NAME UPG
—————————— —————————— —
APPLSYS AQ$_FND_CP_GSM_OPP_AQTBL_L YES

56 rows selected.

SQL> SQL>

– now restart the database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2138521600 bytes
Fixed Size 2161024 bytes
Variable Size 1543505536 bytes
Database Buffers 570425344 bytes
Redo Buffers 22429696 bytes
Database mounted.
Database opened.

SQL> alter session set “_with_subquery”=materialize;

Session altered.

SQL> alter session set “_simple_view_merging”=TRUE;

Session altered.

SQL> SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(‘upg_tzv’);

PL/SQL procedure successfully completed.

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

– ouput of this will be a list of tables like:

– Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
– Number of failures: 0
– ….
– Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
– Number of failures: 0
– Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
– Number of failures: 0
– Failures:0

– this select should , if no errors where given also give “no rows”, if it does something went wrong

output:

Table list: “ASO”.”AQ$_ASO_ORDER_FEEDBACK_T_S”
Number of failures: 0
Table list: “OKC”.”AQ$_OKC_AQ_EV_TAB_S”
Number of failures: 0
Table list: “AR”.”AQ$_AR_REV_REC_QT_S”
Number of failures: 0
Table list: “XNP”.”AQ$_XNP_OUT_MSG_QTAB_S”
Number of failures: 0
Table list: “CS”.”AQ$_CS_SERVICE_REQUEST_OQT_S”
Number of failures: 0
Table list: “CS”.”AQ$_CS_SERVICE_REQUEST_IQT_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_DEFERRED_TABLE_M_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_OUT_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_REPLAY_IN_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_IN_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_ERROR_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_DEFERRED_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_REPLAY_OUT_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JMS_IN_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JMS_OUT_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_CONTROL_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_NOTIFICATION_IN_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_NOTIFICATION_OUT_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_WS_JMS_IN_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JAVA_DEFERRED_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_WS_JMS_OUT_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JAVA_ERROR_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JMS_JMS_OUT_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_WS_SAMPLE_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_FND_CP_GSM_IPC_AQTBL_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_FND_CP_GSM_IPC_AQTBL_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_FND_CP_TM_AQTBL_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_FND_CP_TM_RET_AQTBL_S”
Number of failures: 0
Table list: “AR”.”AQ$_AR_REV_REC_QT_L”
Number of failures: 0
Table list: “ASO”.”AQ$_ASO_ORDER_FEEDBACK_T_L”
Number of failures: 0
Table list: “XNP”.”AQ$_XNP_OUT_MSG_QTAB_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_NOTIFICATION_OUT_L”
Number of failures: 0
Table list: “OKC”.”AQ$_OKC_AQ_EV_TAB_L”
Number of failures: 0
Table list: “CS”.”AQ$_CS_SERVICE_REQUEST_OQT_L”
Number of failures: 0
Table list: “CS”.”AQ$_CS_SERVICE_REQUEST_IQT_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_WS_JMS_IN_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_DEFERRED_TABLE_M_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_OUT_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_REPLAY_IN_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_IN_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_ERROR_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_DEFERRED_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_REPLAY_OUT_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JMS_IN_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JMS_OUT_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_CONTROL_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_NOTIFICATION_IN_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JAVA_DEFERRED_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_WS_JMS_OUT_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JAVA_ERROR_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_JMS_JMS_OUT_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_WF_WS_SAMPLE_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_FND_CP_TM_AQTBL_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_FND_CP_TM_RET_AQTBL_L”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_FND_CP_GSM_OPP_AQTBL_S”
Number of failures: 0
Table list: “APPLSYS”.”AQ$_FND_CP_GSM_OPP_AQTBL_L”
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.

– this select should , if no errors where given also give “no rows”, if it does something went wrong

SQL> SQL> SELECT * FROM sys.dst$error_table;

no rows selected

– if there where no failures then end the upgrade.

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.

–last checks

SQL> 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> SQL> SELECT * FROM v$timezone_file;

FILENAME VERSION
——————– ———-
timezlrg_14.dat 14

Your database DST version is now updated to .

SQL> select VERSION from v$timezone_file;

VERSION
———-
14

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
———-
4

–if they differ after an upgrade then updating registry$database can be done by

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> commit;

Commit complete.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
———-
14

About these ads

About mpoojari
Oracle Database/Applications Administrator

Leave a Reply

Please log in using one of these methods to post your comment:

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: