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

Autoconfig On Db Tier Fails With Error – SP2-1503: Unable to initialize Oracle call interface

Oracle Applications 11.5.10.2 on Solaris 5.10 64bit SPARC

Oracle Application Database 10gR2 (10.2.0.4) upgraded to Oracle Database 11.2.0.3 using Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1].

Issue encountered while executing adconfig in the end:

$>./adconfig.sh
Enter the full path to the Context file: /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/myinstance_myserver.xml
Enter the APPS user password:
The log file for this session is located at: /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/log/myinstance_myserver/07300952/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
Using ORACLE_HOME location : /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1
Classpath                   : /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jdk/jre/lib/rt.jar:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jdk/lib/dt.jar:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jdk/lib/tools.jar:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5.jar:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/java/xmlparserv2.zip:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/java:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jlib/netcfg.jar

Using Context file          : /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/myinstance_myserver.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db112
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed with errors.

adconfig.log
============

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
<filename>  <phase>  <return code where appropriate>

[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/install/myinstance_myserver
afdbprf.sh              INSTE8_PRF         1

[APPLY PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/install/myinstance_myserver
adcrobj.sh              INSTE8_APPLY       1
AutoConfig is exiting with status 2

Cause:
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Solution:

The issue is related to ORA_TZFILE env variable in 11GR2 this env variable is no longer needed.

In Oracle Database server to fix this issue Unset the env varaible ORA_TZFILE and try to connect.

In Oracle Applications Database to fix this issue Perform the below.

The ORA_TZFILE is set while at 11gR2 this should not be set.

The  $ORACLE_HOME/appsutil/template/ad8idbux.env is from the wrong version  (ad8idbux.env 120.8)

The wrong file includes the  following:

ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"

export  ORA_TZFILE

While for 11gr2 it should be:

if test "%s_database%" =  "db112" ;  then

ORA_TZFILE=""

else

ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"

fi

export  ORA_TZFILE

Source the environment variable and executed autoconfig in Database Node, completed without issue

Reference: Autoconfig On Db Tier Fails With Error – SP2-1503: Unable to initialize Oracle call interface (Doc ID 1187616.1)

The issue is also resolved when you upgrade the applications 11i to Release 12 (12.1.1) or higher version on 11gR2 Database.