Friday, March 2, 2012

Upgrade Oracle database to with EBS R12

Upgrade Oracle database to with EBS R12

The below provides high level steps for the upgrade of the EBS database from to
The main document to be followed for this upgrade is "Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1] "

1)Install software in a different oracle home
If the existing database oracle home is /u01/TEST/db/tech_st/11.1.0, You can create directory /u01/TEST/db/tech_st/11.2.0 and install it on this location.

2)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;

3) Run the Pre-Upgrade Information Tool by executing the utlu112i.sql script:
SQL> SPOOL upgrade.log
SQL> @utlu112i.sql

4)Shutdown Apps & DB services

4)Set the environment variables to the new home i.e /u01/TEST/db/tech_st/11.2.0
 a.The ORACLE_BASE environment variable must be set accordingly.
 b.The ORACLE_HOME environment variable points to the new 11.2.0 Oracle home.
 c.The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
 d.The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
 e.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])

5)Create nls/data/9idata directory
On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the $ORACLE_HOME/nls/data/old/ 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.

6)Apply additional RDBMS patches 
Apply the following patches on the NEW ORACLE_HOME:
For all UNIX/Linux platforms:

7) Copy initSID.ora from old oracle_home to new oracle_home
 set at the initSID.ora
 compatible = '11.2.0'

8)Upgrade the database instance
sqlplus "/ as sysdba"
Run the catupgrd.sql script, this script is doing the UPGRADE
SQL> spool upgrade.log
SQL>spool off
Run utlu112s.sql to display the results of the upgrade as follows:
SQL> @utlu112s.sql

9)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

10) 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');

11) Copy the listner & tnsnames.ora from old oracle home to new oracle home and modify the oracle home & all the refrences of old entries to new oracl home

12)Copy the old appsutil from ORACLE_HOME to the new ORACLE_HOME. and change in all the files inside appsutil all the references from 11.1.0 to 11.2.0

13)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.

14)Copy $APPL_TOP/admin/adstats.sql from the apps tier to the database home and execute it in database restrict mode.

15)Run adadmin - recreate grants and synonyms and compile apps schema

16)Upgrade completed - Start the Applications: