Upgrade Oracle database to with EBS R12
                                                 Last update (2012-01-03 20:24:41)
                                                                                                                   Date added (2011-12-31 11:57:36)

Here is a small draft to upgrade the Oracle Database from, which comes with the standard fresh installation of Oracle E-Business Suite R12.1.1, to Oracle database The system to upgrade is Red Hat Linux 5.5 x86-64 bit.

The main document to follow is
Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]

The document indicates 30 steps, but the most of them can be ignored.

Steps 1 to 3 can be ignored.

Steps 4-5 are about creating directory structure and installing the software for Oracle For example if the old Oracle 11gR1 home was: /u01/sd_ar/db/tech_st/11.1.0 the new Oracle 11g R2 home can be /u01/sd_ar/db/tech_st/11.2.0

The installation of Oracle Database 11g Products from the 11g Examples CD can be ignored

Step 6, after the software installation of Oracle you must set the environment variables to the new home.
export ORACLE_BASE=/u01/sd_ar/db/tech_st
export ORACLE_HOME=/u01/sd_ar/db/tech_st/11.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl/5.8.3
Step 7, create nls/data/9idata directory
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 11gR2 Oracle home.

Step 8 for the additional on-off patches of Oracle 11g R2 binaries can be ignored.

Step 9, shutdown apps tier, and oracle 11.1.0 listener.

Steps 10, 11 can be ignored.

Step 13, upgrade the database instance. I usually use the Manual database upgrade approach, not the DBUA. The Oracle guide for upgrading can be found here:

The main steps are:
set Oracle 11.1.0 home
copy /u01/sd_ar/db/tech_st/11.1.0/dbs/ /u01/d_ar/db/tech_st/11.2.0/dbs/
cd /u01/sd_ar/db/tech_st/11.2.0/rdbms/admin
set Oracle 11.2.0 home
sqlplus "/ as sysdba"
SQL> SPOOL upgrade.log
Run the Pre-Upgrade Information Tool by executing the utlu112i.sql script:
SQL> @utlu112i.sql
Run the catupgrd.sql script, this script is doing the UPGRADE
SQL> @catupgrd.sql

Run utlu112s.sql to display the results of the upgrade as follows:
SQL> @utlu112s.sql
if you get the ORA-01408 error(which is a known problem with Oracle E-Business Suite databases) just ignore it!

After the upgrade you must accommodate all the issued with the obsolete parameter in Oracle

Step 14, set at the initSID.ora
compatible = '11.2.0'
remove plsql_native_libary_dir and plsql_native_libary_subdir_count

Steps 15, 16 can be ignored.

Step 17, is 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 /u01/sd_ar/db/tech_st/11.1.0 to the new 11.2.0 directory.

Second copy the old appsutil from /u01/sd_ar/db/tech_st/11.1.0/appsutl to the new /u01/sd_ar/db/tech_st/11.2.0/appsutil. This steps is very important not to forget. You must also change in all the files inside appsutil all the references from 11.1.0 to 11.2.0

At last, 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. Check if you can connect to the database. In case you have the error:
$>sqlplus "/ as sysdba"
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
then check the solution for SP2-1503: Unable to initialize Oracle call interface
After solving any problems related to connecting to the database, start the listener and proceed to next steps.

Step 18, run adgrants.sql. Copy $APPL_TOP/admin/adgrants.sql file from the apps tier to the database tier. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
$sqlplus "/ as sysdba" @adgrants.sql APPS

Steps 19,20,21 can be found at the Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]

Step 22 can be ignored, since you don't have to change port or SID or hostname.

Step 23, gather statistics for SYS schema. Copy $APPL_TOP/admin/adstats.sql from the apps tier to the database home. 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;
Steps 25, 26 can be ignored

Step 27, re-create grants and synonyms. Oracle Database 11g Release 2 (11.2) contains new functionality for grants and synonyms compared to previous database releases. As a result, you must re-create the grants and synonyms in the APPS schema. On the apps tier, run adadmin and select the Recreate grants and synonyms for APPS schema task from the Maintain Applications Database Objects menu

Step 28, compile APPS schema. It is very usual to end-up with invalid objects at the apps schema. Use adadmin to 3. Compile/Reload Applications Database Entities menu and then choose: Compile APPS schema

Steps 28, 30 can be ignored

Step 29, implement and run AutoConfig, on database tier and apps tier
Implement and run AutoConfig in the new Oracle database home. You must also run AutoConfig on application tier server node to update the system with the new listener.

Finally start the application tier