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

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

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

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]