Manual upgrade 11gR2 to later Version Time Zone Upgrade

conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on

-- check if previous prepare window is ended

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


purge dba_recyclebin;

-- clean used tables

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

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

alter session set "_with_subquery"=materialize;

-- to avoid the issue in note 1407273.1

alter session set "_simple_view_merging"=TRUE;

-- start upgrade window

EXEC DBMS_DST.BEGIN_UPGRADE();

-- the message
-- "An upgrade window has been successfully started."
-- will be seen




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

SELECT * FROM sys.dst$error_table;

-- check if this select gives

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

-- gives this output:

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

-- now restart the database

shutdown immediate
startup

-- at this point the database can actually be used note however that the

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

-- now upgrade the tables who need action

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;
/

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

SELECT * FROM sys.dst$error_table;

-- if there where no failures then end the upgrade.



VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

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

SELECT * FROM v$timezone_file;
exit;

conn / as sysdba
SELECT VERSION FROM v$timezone_file;
select TZ_VERSION from registry$database;

If select TZ_VERSION from registry$database still shows 4 , then please perform

conn / as sysdba
update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

commit;

Find component Version in Apps 11i/R12/12i



A.  Connect to database as user apps
SQL> select release_name from apps.fnd_product_groups;
Output like 12.0.4 or 11.5.10.2
Q. Web Server/Apache or Application Server in Apps 11i/R12
A. Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
Output for 11i should be like
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built:   Jan 26 2005 11:06:44 (iAS 1.0.2.2.2 rollup 5)
Output for R12 should be like
Server version: Oracle-Application-Server-10g/10.1.3.0.0Oracle-HTTP-Server
Server built:   Dec  4 2006 14:44:38
Q. Forms & Report version (aka developer 6i) in 11i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/f60run | grep Version | grep Forms 
$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version R12
output like
Forms 6.0 (Forms Runtime) Version 6.0.8.25.2 (Production)

Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)

Check fourth character in version 25 which means Forms 6i patchset 16 (25-9)
.
Q. Forms & Report version in R12/12i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release
Output should be like
Report Builder: Release 10.1.2.2.0
You can safely ignore warnings
Q. Database Version in 11i/R12/12i
A. Go to database section below.
Q. Oracle Jinitiator in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )
Q. Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE
Q. File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility
Q. Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check
adident Header $AU_TOP/resource/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(‘$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);
Q. OA Framework Version
A.http:// hostname.domainName:port/OA_HTML/OAInfo.jsp (Only for 11i)
A.  Log in as Application user, set environment variable and run below query
adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp
output for both should look like
$Header OA.jsp 115.60 2006/03/31 00:47:28 atgops1 noship $
120.21 means OA Framework Version (coming soon..)
115.60 means OA Framework Version (coming soon..)
115.56 means OA Framework Version (coming soon..)
115.36 means OA Framework Version 5.7
115.27 means OA Framework Version 5.6E
115.26 means OA Framework Version 5.5.2E
Q. Discoverer Version for 11i (3i or 4i)
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/disc4ws | grep -i Version
Q. Discoverer Version for 11i or R12 (10g AS)
Check under Application Server Section as 10g AS Discoverer is on standalone
Q. Workflow Version with Apps
A. Connect to Database as apps user
SQL> select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0
Version for Fusion Middleware Component

Identity Management component Version/Release Number
A. Oracle Single Sign On
Connect to database which holds SSO repository
SQL>select version from orasso.wwc_version$;
B. Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
>>> To find software/binary version
$ORACLE_HOME/bin/oidldapd -version
output should look like
oidldapd: Release 10.1.4.0.1 – Production on mon jul 14 14:14:21 2008
Copyright (c) 1982, 2006 Oracle.  All rights reserved.
>>> To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \
-s base “objectclass=*” orcldirectoryversion
and output should be like
version: 1
dn:
orcldirectoryversion: OID 10.1.4.0.1
or run following query in database
SQL> select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;
Output should be like OID 10.1.4.0.1
C. Application Server
1. Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0
2. For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
SQL> select * from ias_versions;
or
SQL>select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
.
D. AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version
.
E. Oracle Portal
SQL> select version from portal.wwc_version$;
.
Database Component
I) Oracle Database
To find database version
SQL> select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail
.
Oracle Enterprise Manager
Metalink Note 605398.1  How to to find the version of the main EM components
.
Unix Operating System
Solaris -> cat /etc/release

Red Hat Linux -> cat /etc/redhat-release

Workflows Related Tables

SELECT * FROM WF_USER_ROLE_ASSIGNMENTS
SELECT * FROM WF_USER_ROLES
SELECT * FROM WF_ROLES
SELECT * FROM WF_ITEMS
SELECT * FROM WF_ITEM_ATTRIBUTES
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES
SELECT * FROM WF_ITEM_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITIES
SELECT * FROM WF_ACTIVITIES_TL
SELECT * FROM WF_ACTIVITY_ATTRIBUTES
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITY_TRANSITIONS
SELECT * FROM WF_DEFERRED--WF_CONTROL

SELECT * FROM WF_ACTIVITY_ATTR_VALUES
WHERE NAME LIKE '%MASTER%'
AND PROCESS_ACTIVITY_ID
IN(
SELECT *-- PROCESS_ACTIVITY
 FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_TYPE = 'ERP'
AND ITEM_KEY ='63865'
)

SELECT * FROM WF_ITEM_TYPES
SELECT * FROM WF_LOOKUPS_TL

SELECT * FROM WF_NOTIFICATIONS
WHERE MESSAGE_TYPE ='ERP'
ORDER BY BEGIN_DATE DESC

SELECT * FROM WF_NOTIFICATION_ATTRIBUTES
SELECT * FROM WF_MESSAGES
SELECT * FROM WF_MESSAGES_TL
SELECT * FROM WF_MESSAGE_ATTRIBUTES
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL
SELECT * FROM WF_ETS
SELECT * FROM WF_PROCESS_ACTIVITIES

LIST OF ACTIVITIES FOR AN ITEMTYPE
Accepts Workflow itemtype / shortname as input parameter and will all the activities involved along with the status and user name to whom the current activity is assigned.

SELECT A.ITEM_KEY,
       B.ACTIVITY_NAME,
       A.ACTIVITY_STATUS,
       A.ACTIVITY_RESULT_CODE,
       A.ASSIGNED_USER,
       A.BEGIN_DATE,
       A.END_DATE      
FROM WF_ITEM_ACTIVITY_STATUSES A,
     WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
AND A.ITEM_KEY = 64077
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')



TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING
Accepts workflow itemtype and activity as input variables and the results will provide the time frame explaining from how long the activity is pending along with the username whose action is req

SELECT B.ACTIVITY_NAME,
       TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,
       COUNT(B.ACTIVITY_NAME) TOTAL_PENDING
FROM WF_ITEM_ACTIVITY_STATUSES A,
     WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
--AND A.ITEM_KEY = 1131
AND END_DATE IS NULL
AND ACTIVITY_STATUS != 'ERROR'
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
GROUP BY ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)
ORDER BY ACTIVITY_NAME,
         PENDING_FROM_NO_OF_DAYS

LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS


SELECT SUM(TOTAL_PENDING) PENDING_LESS_THAN_5DAYS
FROM
(SELECT B.ACTIVITY_NAME,
       TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,
       COUNT(B.ACTIVITY_NAME) TOTAL_PENDING
FROM WF_ITEM_ACTIVITY_STATUSES A,
     WF_PROCESS_ACTIVITIES B
WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID
AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
AND A.ITEM_TYPE = 'ERP'
--AND A.ITEM_KEY = 1131
AND END_DATE IS NULL
AND ACTIVITY_STATUS != 'ERROR'
AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
GROUP BY ACTIVITY_NAME,
TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)
ORDER BY ACTIVITY_NAME,
         PENDING_FROM_NO_OF_DAYS ) FIVE_DAYS
WHERE FIVE_DAYS.PENDING_FROM_NO_OF_DAYS <

Autoconfig Failed with Error: FsCtxFileException: Start of Root Element Expected

Autoconfig Failed with Error: FsCtxFileException: Start of Root Element Expected


[appldev@erpdev scripts]$ adautocfg.sh
Enter the APPS user password:

The log file for this session is located at: /u01/oracle1/DEV/inst/apps/DEV_erpdev/admin/log/05081154/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location     : /u01/oracle1/DEV/inst/apps/DEV_erpdev
Classpath                   : /qdap/oracle1/DEV/apps/apps_st/comn/java/lib/appsborg2.zip:/qdap/oracle1/DEV/apps/apps_st/comn/java/classes

Using Context file          : /u01/oracle1/DEV/inst/apps/DEV_erpdev/appl/admin/DEV_erpdev.xml

Context Value Management will now update the Context file
ERROR: FsCtxFile.XMLParseException
oracle.xml.parser.v2.XMLParseException: Start of root element expected.
at oracle.xml.parser.v2.XMLError.flushErrors1(XMLError.java:320)
at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:341)
at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:303)
at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:292)
at oracle.apps.ad.autoconfig.oam.FsCtxFile.init(FsCtxFile.java:58)
at oracle.apps.ad.autoconfig.oam.FsCtxFile.overwriteCtx(FsCtxFile.java:208)
at oracle.apps.ad.autoconfig.oam.CtxSynchronizer.downloadToFs(CtxSynchronizer.java:346)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateFileSysContext(FileSysDBCtxMerge.java:681)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateFileSysFiles(FileSysDBCtxMerge.java:210)
at oracle.apps.ad.context.CtxValueMgt.mergeCustomInFiles(CtxValueMgt.java:1790)
at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1608)
at oracle.apps.ad.context.CtxValueMgt.main(CtxValueMgt.java:763)
Error occured before Context Value Management could be completed
ERROR: Context Value Management Failed.
Terminate.

The logfile for this session is located at:
    /u01/oracle1/DEV/inst/apps/DEV_erpdev/admin/log/05081154/adconfig.log


Solutions:


Solution
To implement the solution, please execute the following steps:

1. Connect to SQL*PLUS as "APPLSYS" user. 
(The "APPLSYS" password is always the same as the "APPS" user.)

2. Backup the FND_OAM_CONTEXT_FILES table, for example: 

SQL> CREATE TABLE fnd_oam_context_files_bak 
AS SELECT * FROM fnd_oam_context_files; 

3. Truncate the FND_OAM_CONTEXT_FILES table, for example:

TRUNCATE TABLE fnd_oam_context_files; 


4. Re-run Autoconfig on all nodes to repopulate the data.