Friday, July 19, 2013

Overcoming Oracle Apps R12 FRM-92101 Error On Redhat Linux 5.6

Have you ever got the error, FRM-92101 on with Oracle Applications R12 on 64 bit Redhat Linux 5.5? Here is how to overcome it.
Oracle Forms Runtime Error FRM92101
Oracle Forms Runtime Error FRM92101

Here is my environment:
Server: Redhat Linux 5.5
lsb_release -a
LSB Version: :core-3.1-amd64:core-3.1-ia32:core-3.1-noarch:graphics-3.1-amd64:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: EnterpriseEnterpriseServer
Description: Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Release: 5.5
Codename: Carthage
uname -a
Linux black.testrac.com 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
select
APPLICATIONS_SYSTEM_NAME
, ARU_RELEASE_NAME
, release_name
from apps.fnd_product_groups
APPLICATIONS_SYSTEM_NAME ARU_RELEASE_NAME
—————————— ——————-
RELEASE_NAME
————————————————–
VIS R12
12.1.1
———–
Prerequisites:
The java is working in your browser. To test whether Java is working in the web browser, run this in the URL.
http://java.com/en/download/testjava.jsp
You have overcome the error concerning: NPX_PLUGIN_PATH, and oaj2se.exe.  See my previous post.
http://rodgersnotes.wordpress.com/2012/01/20/firefox-oracle-apps-java-plugins-on-64-bit-redhat-5-5/
———–
Symptom:
Start the database, listener, and all the Oracle Apps processes. Open a browser. Test an Oracle Apps Form with the forms test webpage. In my case:
file:///oapps/applmgr/VIS/apps/tech_st/10.1.2/tools/web/html/runform.htm
You get a java window open. But you get the error: FRM-92101 There was a failure in the Forms server during startup. This could happen due to an invalid configuration. Please look into the web server log for the details.
Click on Details:
Java exception:
oracle.forms.net.ConnectionException: forms session <1> failed during startup: no response from runtime process
at oracle.forms.net.ConnectionException.createConnectionException(Unknown source)
at oracle.forms.net.HTTPStream.getResponse(Unknown source)
at oracle.forms.net.HTTPStream.doFlush(Unknown source)
at oracle.forms.net.HTTPStream.Flush(Unknown source)
at java.io.DataOutputStream.flush (DataOutputStream.java:106)
at oracle.forms.net.HTTPConnection.connect(Unknown source)
at oracle.forms.engine.FormsDispatcher.initConnection(Unknown source)
at oracle.forms.engine.FormsDispatcher.init(Unknown source)
at oracle.forms.engine.Runform.initConnection(Unknown source)
at oracle.forms.engine.Runform.startRunform(Unknown source)
at oracle.forms.engine.Main.createRunform(Unknown source)
at oracle.forms.engine.Main.start(Unknown source)
at sun.plugin2.applet.Plugin2Manager$AppletExecutionRunnable.run(Plugin2Manager.java:1698)
at Java.lang.Thread.run(Thread.java:662)
————
Check the Oracle Forms Log:
$LOG_HOME/ora/10.1.3/opmn/forms_default_group_1/formsstd.out
In my case:
/oapps/applmgr/VIS/inst/apps/VIS_black/logs/ora/10.1.3/opmn/forms_default_group_1/formsstd.out
tail -100 /oapps/applmgr/VIS/inst/apps/VIS_black/logs/ora/10.1.3/opmn/forms_default_group_1/formsstd.out

12/01/21 12:32:08 FormsServlet init():
configFileName: /oapps/applmgr/VIS/inst/apps/VIS_black/ora/10.1.2/forms/server/appsweb.cfg
testMode: false
12/01/21 12:32:08 Oracle Containers for J2EE 10g (10.1.3.4.0) initialized
12/01/21 13:04:27 ListenerServlet init()
12/01/21 13:04:28 Forms session <1> aborted: runtime process failed during startup with errors /oapps/applmgr/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory
12/01/21 13:19:53 Forms session <2> aborted: runtime process failed during startup with errors /oapps/applmgr/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory
————–
And Check The File, application.log:
tail -100 /oapps/applmgr/VIS/inst/apps/VIS_black/logs/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log
12/01/21 13:04:27.314 formsweb: ListenerServlet init()
12/01/21 13:04:28.455 formsweb: Forms session <1> aborted: runtime process failed during startup with errors /oapps/applmgr/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory
12/01/21 13:04:28.455 formsweb: Forms session <1> exception stack trace:
oracle.forms.engine.RunformException: Forms session <1> failed during startup: no response from runtime process
at oracle.forms.servlet.RunformProcess.connect(Unknown Source)
at oracle.forms.servlet.RunformProcess.dataToRunform(Unknown Source)
at oracle.forms.servlet.RunformSession.dataToRunform(Unknown Source)
at oracle.forms.servlet.ListenerServlet.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:619)
————–
Cause:
The cause is not unusual with Linux, or Oracle, or Oracle Apps. It’s that Oracle Forms is looking for a library file. But the file doesn’t exist in the expected place. Nor does a link. Not again.
In this case, libXm.so.2 can’t be found.
———-
Analysis:
Find the files. As root, do lots of linear searches all over the hard drive looking for library files.
find / -type f -ls 2> /dev/null | grep -i “libXm.so”

214067513 2692 -rwxr-xr-x 1 root root 2747528 Jun 6 2007 /usr/lib64/libXm.so.3.0.2
214054985 2776 -rwxr-xr-x 1 root root 2836528 Mar 4 2010 /usr/lib64/libXm.so.4.0.1
214067522 2492 -rwxr-xr-x 1 root root 2542828 Jun 6 2007 /usr/lib/libXm.so.3.0.2
1802278 2584 -rwxr-xr-x 1 root root 2637260 Mar 4 2010 /usr/lib/libXm.so.4.0.1

I did have more occurences of this file. But they were under different subdirectories.
/oapps/oracle/VIS/db/tech_st/11.1.0/lib32/stubs
/middleware/Oracle_BI1/lib32/stubs
The directories under /usr are the critical ones.
You can find any existing links with:
find / -type l -ls 2> /dev/null | grep -i “libXm.so”
—————
File Types:
file /usr/lib64/libXm.so.4.0.1
/usr/lib64/libXm.so.4.0.1: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), stripped
file /usr/lib/libXm.so.3.0.2
/usr/lib/libXm.so.3.0.2: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), stripped
—————
Find or Verify the Packages Needed For The Library Files:
Reading other webpages (below), these library files are provided by the package: openmotif22-2.2.3-18.
I had installed everything, every last package, when I installed Redhat. It’s to avoid nonsense like this. So, I already the package installed. If you don’t have the package installed, download it, and install it. rpmfind.net is a good place to start.
Another time, I’d stuggled with a similar installation. In my notes, I was concerned with three library files: libMrm.so, libUil.so, and libXm.so. So, I leveraged my notes, and looked for all three. Turns out they are all owned by the same package: openmotif.
—–
rpm -q -f /usr/lib64/libXm.so.4.0.1
openmotif-2.3.1-2.el5_4.1
—–
rpm -q -f /usr/lib/libXm.so.3.0.2
openmotif22-2.2.3-18
—–
What Are All The Files Associated With That Package?
rpm -q openmotif22 -l | sort | more
/usr/lib64/libMrm.so.3
/usr/lib64/libMrm.so.3.0.2
/usr/lib64/libUil.so.3
/usr/lib64/libUil.so.3.0.2
/usr/lib64/libXm.so.3
/usr/lib64/libXm.so.3.0.2
….
/usr/lib/libMrm.so.3
/usr/lib/libMrm.so.3.0.2
/usr/lib/libUil.so.3
/usr/lib/libUil.so.3.0.2
/usr/lib/libXm.so.3
/usr/lib/libXm.so.3.0.2
/usr/share/doc/openmotif22-2.2.3

This query obviously finds both the 32 and 64 bit files.
—————–
—————–
Analysis For Links:
cd /usr/X11R6/lib
Original files:
/usr/lib/libMrm.so.3.0.2
/usr/lib/libMrm.so.4.0.1
/usr/lib/libUil.so.3.0.2
/usr/lib/libUil.so.4.0.1
/usr/lib/libXm.so.3.0.2
/usr/lib/libXm.so.4.0.1
—–
The application, is looking for files in the directory: /usr/X11R6/lib
But it does not exist. Create it.
As root:
mkdir -p /usr/X11R6/lib
—–
Make Links To The Original Files:
cd /usr/X11R6/lib
ln -s /usr/lib/libMrm.so.3.0.2 .
ln -s /usr/lib/libMrm.so.4.0.1 .
ln -s /usr/lib/libUil.so.3.0.2 .
ln -s /usr/lib/libUil.so.4.0.1 .
ln -s /usr/lib/libXm.so.3.0.2 .
ln -s /usr/lib/libXm.so.4.0.1 .
The above ^ will have a link, the same name as the original file.
—–
Make Links With An Abbreviated Name:
ln -s /usr/lib/libXm.so.3.0.2 libXm.so.3
ln -s /usr/lib/libXm.so.4.0.1 libXm.so.4
ln -s /usr/lib/libXm.so.4.0.1 libXm.so
ln -s /usr/lib/libUil.so.4.0.1 libUil.so.4
ln -s /usr/lib/libUil.so.3.0.2 libUil.so.3
ln -s /usr/lib/libUil.so.4.0.1 libUil.so
ln -s /usr/lib/libMrm.so.4.0.1 libMrm.so.4
ln -s /usr/lib/libMrm.so.3.0.2 libMrm.so.3
ln -s /usr/lib/libMrm.so.4.0.1 libMrm.so
—–
At this point,I still got the error: FRM-92101
Of course, the software is still looking for libXm.so.2, but I had not yet created the link it.
As root:
cd /usr/X11R6/lib
ln -s /usr/lib/libMrm.so.3.0.2 libMrm.so.2
ln -s /usr/lib/libUil.so.3.0.2 libUil.so.2
ln -s /usr/lib/libXm.so.3.0.2 libXm.so.2
———–
Try again. It works. In the sense that I can get a login form.
Oracle Applications Login
Oracle Applications Login
However, when I try logging in as sysadmin, OPERATIONS, or APPS, I now get another error:
APP-FND-01542:  The applications servers is not authorized to access this database.
Are these not the right users to login with? If so, when do the errors ever stop? If not, what are the right users to login with?  Please leave a comment.
—————
Useful Links:
Test whether Java is working in the web browser.
http://java.com/en/download/testjava.jsp
Installing Oracle E-Business 12.1.1 on Oracle Enterprise Linux 5.4 64-bit
http://www.oracle-developer.com/installing_oracle_r12_rhel5_64bit.html
Installing Oracle E-Business 12.1.1 on Oracle Enterprise Linux 5.4 64-bit
http://www.pythian.com/news/1265/installing-oracle-r12-ebs-in-my-living-room/
Oracle® Fusion Middleware Release Notes 11g Release 1 (11.1.1) for Linux x86 E10133-25  44 Oracle Forms
http://docs.oracle.com/cd/E23549_01/relnotes.1111/e10133/forms.htm

Sunday, March 10, 2013

UPGRADE 11.1.0.7 DATABASE TO 11.2.0.3 DATABASE WITH R12.1.3



11.2.0.3 is the latest release (at the time of writing this post) for the Oracle database. The intent of this guide is to upgrade the current database version from 11.1.0.7 to 11.2.0.3.
The installation procedure for the new Oracle Database 11g Release 2 (11.2) installs the Oracle software into a new Oracle home. This is referred to as an out-of-place upgrade and is different from patch set releases for earlier releases of Oracle Database, where the patch set was always installed in place. Oracle strongly recommends that you follow the steps in this procedure to ensure minimal downtime for the upgrade process and integrity of the new binaries and software libraries.

Also, Manual upgrade method has been used instead of DBUA for better control over the complete upgrade process.

Linux x86-64 is used as platform in the below guide

SOFTWARE SOURCES

You can obtain the 11.2.0.3 software from patch 10404530 on My Oracle Support
Files 1, 2 and 3 are sufficient to cover 11.2.0.3 database (including grid infra)

Part 1: INSTALL ORACLE 11.2.0.3 SOFTWARE

1.1) Check oratab file
Ensure that the oratab file contains an entry for the database to be upgraded

1.2) Check for installed languages
SQL> select LANGUAGE_CODE,LANGUAGE_ID,NLS_LANGUAGE,INSTALLED_FLAG from fnd_languages where INSTALLED_FLAG in (‘I’,'B’);

LANG LANGUAGE_ID NLS_LANGUAGE                   I
—- ———– —————————— -
US             0 AMERICAN                       B
CS            30 CZECH                          I
D              4 GERMAN                         I

1.3) Install 11g database software

cd <software location>
./runInstaller
Below are the details of OUI screen and actions.

Screen 1: Configure Security updates
Leave blank and press next

Screen 2: Download Software updates
Check ‘Skip software updates’ and press next

Screen 3:  Select Installation option
Check ‘Install database software only’ and press next

Screen 4: Grid Installation options
Check ‘Single Instance database installation’ and press next

Screen 5: Select Product Languages
Choose English & all other languages required and press next
NOTE : You may select only ‘English language’ even if on EBS side you have many languages installed. The languages that we select during database install/upgrade through OUI are not related to what languages we install in EBS. If you select languages other than the English language, the Oracle installer will bring over extra *.msb files for the languages selected and the user/developer will be able to see ”core” ora- messages in languages other than English too.

Screen 6: Select database edition
Check ‘EnterpriseEdition’

Screen 7: Specify Installation location
Input values of ‘Oracle base’ and press next

Screen 8: Privileged operating system groups
Take dba (or whatever group you created ) as OSDBA Group and press next

Screen 9: Perform prerequisite checks
Correct if anything is required to (by fix & check again) and press next

Screen 10: Summary
Check and press Install and press next

Screen 11a: Install
Installation will proceed.

Screen 11b: run root.sh scripts as instructed

Screen 12: Finish
This completes the database 11g installation.

Part 2: PRE-UPGRADE STEPS

2.1) Take Invalid Count
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID’;

2.2) Apply mandatory patches

Category a) For customers on E-Business Suite Release 12.1, apply:
Check in ad_bugs and apply whichever patch is not available. All the below patches need to be applied by adpatch
1) 9062910 (11g Release 2 interoperability patch for Release 12.1 )
2) 8919489 (12.1 TXK Delta 3 patch)
3) 9868229
4) 10163753

Category b) ) On 11.2.0.3 home, apply additional 11.2.0.3 RDBMS patches :
Apply the following patches (OPATCH) for all UNIX/Linux platforms:
NOTE: Do not run any of the post install instructions as those will be done after the upgrade.
1) 4247037
There is a Special post patch Instructions (mentioned below), execute ONLY after database upgrade part is done
Post install steps:
cd $ORACLE_HOME/md/admin
connect to the database using sqlplus / as sysdba
sqlplus> @catmgdidcode

2) 9858539
There is a post patch install Instructions (step 3, mentioned below), execute ONLY after database upgrade is done
Post install steps:
Run following files as sysdba
cd $ORACLE_HOME/rdbms/admin
@catnomtt.sql
@catnomta.sql
@dbmsmeta.sql
@dbmsmeti.sql
@dbmsmetu.sql
@dbmsmetb.sql
@dbmsmetd.sql
@dbmsmet2.sql
@catmeta.sql
@prvtmeta.plb
@prvtmeti.plb
@prvtmetu.plb
@prvtmetb.plb
@prvtmetd.plb
@prvtmet2.plb
@catmet2.sql
-- recompile dbms_metadata_int to enable the diffing code
alter package dbms_metadata_int compile plsql_ccflags = 'ku$xml_enabled:true';
-- recompile dbms_metadata_util to enable the xmlschema load code
alter package dbms_metadata_util compile plsql_ccflags = 'ku$xml_enabled:true';
3) 12942119
4) 12960302
5) 12985184
6) 13001379
7) 13004894
8) 13258936
9) 13366268

2.3) Run utlu112i.sql on existing 11.1.0.7 oracle database
Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory(/tmp) on your system.
Connect via existing 11.1.0.7 database and run utlu112i.sql
Below issue were found and were corrected
ISSUE  1
———-
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
–> plsql_native_library_dir     11.2       OBSOLETE
–> plsql_native_library_subdir_ 11.2       OBSOLETE

SOLUTION 1
—————-
commented out below values in init file

#plsql_native_library_dir = /u01/oracle/<instance_name>/db/tech_st/11.1.0/plsql/nativelib

#plsql_native_library_subdir_count = 149

ISSUE 2
———-
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 11.1.0.7.0 database timezone version
…. to the latest version which comes with the new release

SOLUTION 2
————–
-ignore
Upgrade to 11.2.0.3. No need to apply DST patches on the 11.1.0.6 or 11.1.0.7 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 11.1.0.6 or 11.1.0.7.


ISSUE 3
———–
WARNING: –> Your recycle bin contains 99 object(s).
…. It is REQUIRED that the recycle bin is empty prior to upgrading
…. your database.  The command:
PURGE DBA_RECYCLEBIN

SOLUTION 3
—————
<run PURGE DBA_RECYCLEBIN>

ISSUE 4
———-
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

SOLUTION 4
—————-
<run  EXECUTE dbms_stats.gather_dictionary_stats;>

ISSUE 5
———-

Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command
while connected AS SYSDBA:

SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE ‘\_%’ ESCAPE ‘\’

Changes will need to be made in the init.ora or spfile.

SOLUTION 5
————–
Comment out all hidden variables in init file

2.4) Shut down Applications server processes and database listener

shut down all the application services running on Apps tier and also the database listener ( keep the database running)

2.5) Run dbupgdiag.sql on existing 11.1.0.7 oracle database
Run dbupgdiag.sql to find any duplicate objects in sys or system schema
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]
It will ask for input directory to create log file
See the instructions/warnings/errors in log file produced and correct if anything

2.6) Purge DBA_RECYCLEBIN on existing 11.1.0.7 oracle database
SQL> PURGE DBA_RECYCLEBIN;
Note: Already done as corrective action in Step 2.3

2.7) Check Hidden Parameters on existing 11.1.0.7 oracle database
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\’;
Oracle recommends removing all hidden parameters prior to upgrading.
Note: Already done as corrective action in Step 2.3

2.8) Set the new environment for 11.2.0.3 oracle home
After the software installation of Oracle 11.2.0.3 you must set the environment variables to the new home.
export ORACLE_BASE=/u01/oracle/<instance_name>/
export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0
export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata

Make sure that The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])

2.9) Create nls/data/9idata directory on new 11.2.0.3 home
Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.
$ perl cr9idata.pl
$ export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata

2.10) Check for TIMESTAMP WITH TIMEZONE Datatype on existing 11.1.0.7 oracle database
The RDBMS DST patching has been greatly improved in 11gR2.
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
———-
10
For Upgrade to 11.2.0.3, there is no need to apply DST patches on the 11.1.0.6 or 11.1.0.7 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 11.1.0.6 or 11.1.0.7.

2.11) Check the National Characterset  on existing 11.1.0.7 oracle database
Check that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16.
select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET’;
If this is UTF8 or AL16UTF16 then no action is needed.
SQL> select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET’;
 VALUE
———————-
AL16UTF16

2.12) Run Optimizer Statistics on existing 11.1.0.7 oracle database
When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.
$ sqlplus “/as sysdba”
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

2.13) Drop SYS.ENABLED$INDEXES (conditional)
If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and running the following command to drop it:
SQL> drop table sys.enabled$indexes;
PL/SQL procedure successfully completed.
2.14) Disable Database Vault (conditional)

If you have Database Vault installed, perform steps 1 to 6 of Part 2 of document 1091083.1 on My Oracle Support to disable Database Vault.

Check>>
 SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;
PARAMETER                                              VALUE
—————————————————————-
Oracle Database Vault                                 FALSE
If the Value is ‘FALSE’ so no action is required.

2.15) Back up Enterprise Manager Database Control Data (conditional)
You may need to backup the EM data if you have installed.

2.16) Check for any logical corruption
To check for corruption in the dictionary, use the following commands in SQL*Plus (connected as sys):
—————————————————————-
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT ‘Analyze cluster “‘||cluster_name||’” validate structure cascade;’
FROM dba_clusters
WHERE owner=’SYS’
UNION
SELECT ‘Analyze table “‘||table_name||’” validate structure cascade;’
FROM dba_tables
WHERE owner=’SYS’
AND partitioned=’NO’
AND (iot_type=’IOT’ OR iot_type is NULL)
UNION
SELECT ‘Analyze table “‘||table_name||’” validate structure cascade into invalid_rows;’
FROM dba_tables
WHERE owner=’SYS’
AND partitioned=’YES’;
spool off
——————————————————————

This creates a script called analyze.sql.
Now execute the following steps:
$ sqlplus “/ as sysdba”
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
This script (analyze.sql) should not return any errors.

Note:
1. ORA-30657 might occur if there is any external table validated, which can be safely ignored as per Note 209355.1 ORA-30657: Using ANALYZE TABLE for an External Table
2. Errors shown below when executing analyze.sql can be ignored:
SP2-0734: unknown command beginning “SQL> SELEC…” – rest of line ignored.
SP2-0042: unknown command “SQL>” – rest of line ignored.
SP2-0734: unknown command beginning “SQL> spool…” – rest of line ignored.
SQL> @analyze.sql
Analyze table “SYS_TZUV2_AFFECTED_REGIONS” validate structure cascade
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table


2.17) Ensure that all snapshot refreshes are successfully completed
Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.
SELECT DISTINCT(TRUNC(last_refresh))FROM dba_snapshot_refresh_times;


2.18) Ensure that no files need media recovery and that no files are in backup mode
Ensure that no files need media recovery and that no files are in backup mode.
SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;
This should return no rows.


2.19) Resolve outstanding distributed transactions
Resolve outstanding distributed transactions prior to the upgrade.
SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry(”);
SQL> COMMIT;
Note: no records were returned

2.20) To check if a standby database exists, issue the following query
To check if a standby database exists, issue the following query:
SELECT SUBSTR(value,INSTR(value,’=',INSTR(UPPER(value),’SERVICE’))+1)
FROM v$parameter WHERE name LIKE ‘log_archive_dest%’ AND UPPER(value) LIKE ‘SERVICE%’;
If this query returns a row, then sync the standby database with the primary database.
1. Make sure all the logs are transported to the standby server after a final log switch in the primary.
2. Start the recovery of the standby database with the NODELAY option.
2.21) Disable all batch and cron jobs
Disable all batch and cron jobs.
About jobs initiated with Oracle the packages DBMS_JOB, DBMS_SCHEDULER can be used , regarding cron jobs (external jobs controlled at the OS level), this is a task for your Unix administrator
See also :
Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER
Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB
Note 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification

2.22) Check SYS and SYSTEM  tablespace

Ensure the users SYS and SYSTEM have ‘SYSTEM’ as their default tablespace.
You must have sufficient space in the tablespace or be set to extents unlimited.

SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in (‘SYS’,'SYSTEM’);

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace to SYSTEM by using the below command.

SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;

2.23) Check AUD$ table
Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.
 SQL> SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name=’AUD$’;
If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put it back to the SYSTEM tablespace and it should be owned by SYS .
Note: If the AUD$ table exists and is in use, upgrade performance can be effected depending on the number of records in the table.
For 10.2 and later source versions there is now a pre-process script available.
Please review and refer the following note  for complete information :
NOTE:1329590.1 How to Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or later to 11gR1 or later

2.24) Check for externally authenticated SSL users
Check whether database has any externally authenticated SSL users.
 SQL> SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = ‘GLOBAL’;
If any SSL users are found then few Step has to be followed after the upgrade ( refer to the documents mentioned at the end of document under ‘References’ section.

2.25) Take Backup of Configuration files
Note down the location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;

2.26) Check if the  database listener is stopped
Stop the listener for the database.
$ lsnrctl stop
Previous versions of the listener are not supported for use with an Oracle Database 11g Release 2 (11.2) database. However, it is possible to use the new version of the listener with previous versions of Oracle Databases.
You must remove the old listener before creating a new one. If you attempt to create a new listener from the new Oracle Home first, and use the same name and port as the old listener, then Oracle Net Configuration Assistant returns an error.
2.27) Stop other executable such as dbconsole, isqlplus, etc.
Stop other executable such as dbconsole, isqlplus, etc.
 $ emctl stop dbconsole
$ isqlplusctl stop

2.28) Shutdown the 11.1.0.7 database and take cold backup
Shutdown the database and take cold backup (more reliable)
 $ sqlplus “/as sysdba”
SQL> shutdown immediate;
Back up the Database

2.29) Make a backup of the init<SID>.ora file
Make a backup of the init<SID>.ora file.
Comment out obsoleted parameters if present.
DRS_START
GC_FILES_TO_LOCKS
MAX_COMMIT_PROPAGATION_DELAY
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
SQL_VERSION

2.30) Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Refer > Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Since most data dictionary objects should be owned by SYS (see exceptions below) you will want to drop the objects that are owned by SYSTEM in order to clear up this situation.
List all objects that have been created in both the SYS and SYSTEM schema:
column object_name format a30
select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = ‘SYS’)
and owner = ‘SYSTEM’;
This will give a list of duplicate objects and you will simply issue the appropriate DROP command to get rid of the object that is owned by the SYSTEM user.
EXCEPTION TO THE RULE (Important!)
Please check the above mentioned note id and do not drop few objects mentioned in it which are related to replication or else it will cause replication to fail.


Part 3: UPGRADE DATABASE TO 11.2.0.3

3.1) Copy over the modified init file
Once the parameter file is modified as per your requirement (already done above), copy the file from old 11.1.0.7 $ORACLE_HOME/dbs to the new 11.2.0.3 $ORACLE_HOME/dbs.

3.2) Check Environment Variables
Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:
 export ORACLE_BASE=/u01/oracle/<instance_name>/
export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0
export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata
3.3) Update the oratab entry
Update the oratab entry to set the new ORACLE_HOME pointing to <db_name> and disable automatic startup
/etc/oratab entries
#<instance_name>:/u01/oracle/<instance_name>/db/tech_st/11.1.0:N
<instance_name>:/u01/oracle/<instance_name>/db/tech_st/11.2.0:N
Note: After /etc/oratab is updated to have SID and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the SID which is entered in /etc/oratab against the 11gR2 home.
3.4) Upgrading Database to 11gR2
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.

$ export ORACLE_SID=<instance_name>
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus “/ as sysdba”
SQL> startup UPGRADE
Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.
 SQL> set echo on
 SQL> SPOOL upgrade.log
 SQL> @catupgrd.sql
 SQL> spool off
These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting.

3.5) Check the upgraded database

SQL> select banner from v$version;
 BANNER
———————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production


3.6) Run utlu112s.sql script
Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed.
$ sqlplus “/as sysdba”
SQL> STARTUP
SQL> @utlu112s.sql

3.7) Run catuppst.sql
Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.
SQL> @catuppst.sql
3.8) Run dbupgdiag.sql
Check for the integrity of the upgraded database by running dbupgdiag.sql script
Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (next step)
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
3.9) Run utlrp.sql
Run $ORACLE_HOME/rdbms/admin/utlrp.sql multiple times to validate the invalid objects in the database, until there is no change in the number of invalid objects.
Note: This step will take some considerable time on first run
Keep monitoring the invalid count by
 select count(*) from dba_objects where status =’INVALID’;

3.10)  Check components in dba_registry for status ‘VALID’
set lines 150 pages 500
 column COMP_NAME format a45
 column version format a15
 column status format a12
 SQL> select comp_name,version,status from dba_registry;

COMP_NAME                                     VERSION         STATUS
——————————————— ————— ————
OLAP Catalog                                  11.2.0.3.0      VALID
Oracle Data Mining                            11.2.0.3.0      VALID
Oracle XML Database                           11.2.0.3.0      VALID
Oracle Text                                   11.2.0.3.0      VALID
Spatial                                       11.2.0.3.0      VALID
Oracle Multimedia                             11.2.0.3.0      VALID
Oracle Database Catalog Views                 11.2.0.3.0      VALID
Oracle Database Packages and Types            11.2.0.3.0      VALID
JServer JAVA Virtual Machine                  11.2.0.3.0      VALID
Oracle Database Java Packages                 11.2.0.3.0      VALID
Oracle XDK                                    11.2.0.3.0      VALID
Oracle Real Application Clusters              11.2.0.3.0      INVALID
OLAP Analytic Workspace                       11.2.0.3.0      VALID
Oracle OLAP API                               11.2.0.3.0      VALID

14 rows selected.



PART 4: POST-UPGRADE STEPS

4.1) Perform patch post-install instructions
Run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.

4.2) Start the new database listener (conditional)
To start the new Oracle 11.2.0 Listener. First copy the old Oracle 11.1.0 $TNS_ADMIN directory to the new Oracle 11.2.0 $TNS_ADMIN. Be careful to change inside the files all the old references to the 11.1.0 directories to the new 11.2.0 directories.

lsnrctl start

 4.3) Set the  environment variables
copy the $ORACLE_11.1.0_HOME/SID_host.env to $ORACLE_11.2_HOME/SID_host.env, make the necessary changes to point the new 11.2.0 home and source the environment ( correct the value in .bash_profile).

4.4) Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
 $ sqlplus “/ as sysdba” @adgrants.sql (or adgrants_nt.sql)
    [APPS schema name]
4.5) Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:

$ sqlplus apps/[APPS password] @adctxprv.sql \ [SYSTEM password] CTXSYS
4.6) Set CTXSYS parameter
Use SQL*Plus to connect to the database as SYSDBA and run the following command:
 $ sqlplus “/ as sysdba”
 SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);

4.7) Validate Workflow ruleset
On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:

$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql\ [APPLSYS user] [APPS user]

4.8)  Implement and run AutoConfig
Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.

See “Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]”
on My Oracle Support for instructions on how to implement and run AutoConfig.
Section 3.2 explains how to generate a context file on the database tier.
Step 1: Create appsutil.zip
On the application tier (as the APPLMGR user):
Log in to the APPL_TOP environment (source the environment file)
Create appsutil.zip file
perl <AD_TOP>/bin/admkappsutil.pl
This will create appsutil.zip in <INST_TOP>/admin/out

Step 2: Copy appsutil.zip to DB node and uncompress
On the database tier (as the ORACLE user):
Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>
cd <RDBMS ORACLE_HOME>
unzip -o appsutil.zip

Step 3: Generate the Database Context File
Execute the following command to create your Database Context File:
perl <RDBMS_ORACLE_HOME>/appsutil/bin/adbldxml.pl

Step 4:  Run AutoConfig on the Database tier
Run AutoConfig on the Database tier by executing the below command:
On Unix:
<RDBMS_ORACLE_HOME>/appsutil/bin/adconfig.sh contextfile=<context_file>
Shut down all processes, including the database and the listener, and restart them to load the new environment settings.

4.9) Apply post-upgrade ECX patch
If you are on E-Business Suite Release 12.0, apply ECX patch 9922442.
If you are on E-Business Suite Release 12.1, apply patch 9151516.

4.10) Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to run adstats.sql in restricted mode:
$ sqlplus “/ as sysdba”
 SQL> alter system enable restricted session;
 SQL> @adstats.sql
 $ sqlplus “/ as sysdba”
 SQL> alter system disable restricted session;
 SQL> exit;

4.11) Re-create custom database links (conditional)
Create as required

4.12) Re-create grants and synonyms
You must re-create the grants and synonyms in the APPS schema. On the administration server node, as the owner of the Applications file system, run AD Administration and select the  “4.    Maintain Applications Database Entities menu” and then > “Recreate grants and synonyms for APPS schema” task.


4.13) Compile Invalid Objects  
SQL>@utlrp.sql
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects  where STATUS=’INVALID’;

4.14) Restart Applications server processes 
Start the application process and do a basic check of all application services from EBS side.


4.15) Synchronize Workflow views

Log on to Oracle E-Business Suite with the “System Administrator” responsibility. Click Requests > Run > Single Request and the OK button. Enter the following parameters:

Request Name = Workflow Directory Services User/Role Validation
p_BatchSize = 10000
p_Check_Dangling = Yes
Add missing user/role assignments = Yes
Update WHO columns in WF tables = No

REFERENCES
  1. Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]
  2. Oracle Database Upgrade Guide 11g Release 2 (11.2)
  3. Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
  4. Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]
  5. Database Initialization Parameters for Oracle E-Business Suite Release 12 [ID 396009.1]
  6. Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]