Upgrading EBS R12.1 Database from 11.1 to Oracle Database 11g Release 2 (11.2.0)

1. Check the current version of the database and applications:

select * from v$version;
select release_name from fnd_product_groups;

2. Install the pre-req apps patches using adpatch utility:

Patch 7303030 - Oracle E-Business Suite 12.1.1 - 32bit will also work for 64bit machines
Patch 9062910 - 11g Release 2 interoperability patch for Release 12.1
Patch 8919489 - 12.1 TXK Delta 3 patch
Patch 9868229 - CST_LAYER_ACTUAL_COST_DTLS_V Becomes Invalid After 11.2.0.2 Upgrade
Patch 10163753 - BIV_B_AGE_H_SUM_MV Fails During Index Creation
Patch 11071569 - ADBLDXML Fails On Windows Server 2008 R2 64 Bit

3. Update AutoConfig Version:

Patch 9583541 - R12.TXK.B  patch
Patch 9852070 - ADBLDXML AND AUTOCONFIG COMPLETES WITH JAVA.LANG.UNSATISFIEDLINKERROR
Patch 12686610 - R12 And R121 Backport Request For Bug Fix 12336911

 Note: Check for any post install steps for all patches and implement them now.

 4. Install New 11.2.0.3.0 Oracle Home.

     eg. Install at directory /u01/oracle/PROD/db/tech_st/11.2.0

      Also install Oracle Database 11g Products from the 11g Examples CD (mandatory)

 5. Unset the old 11.1 environment variables or open a new terminal/putty session and set the variables for 11.2

$ export ORACLE_BASE=/u01/oracle/VIS/db/tech_st/11.2.0/admin/VIS_ebsr12
$ export ORACLE_HOME=/u01/oracle/VIS/db/tech_st/11.2.0
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$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

6. Check for 11.2.0.3.0 latest PSU patches on metalink and apply it and also apply the below patches if they are not included in the PSU:

Patch 4247037
Patch 9858539
Patch 12942119
Patch 12960302
Patch 12985184
Patch 13001379
Patch 13004894
Patch 13258936
Patch 13366268

7. Database Upgrade:

a) Stop all application tier services from applmgr:

$ cd $ADMIN_SCRIPTS_HOME
$ ./adstpall.sh apps/apps

  b) Pre-upgrade Tasks:  

   i) In a new terminal/putty session set the environment variables for old ORACLE HOME by running the DB environment file:

$ . /u01/oracle/PROD/db/tech_st/11.1.0/PROD_erpr12.env

   ii) From SQL Plus and run below commands (Conditional):

SQL> desc SYS.ENABLED$INDEXES -> If it exists, then drop it.
SQL> drop table sys.enabled$indexes;

   iii) From SQL Plus Disable Database Vault (Conditional):  

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

SQL> select * from v$option where parameter='Oracle Database Vault'; -> If value is FALSE, then no action is required.

   iv) Run utlrp.sql script to validate the invalid objects:

SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status <> 'VALID';

   v) Back up Enterprise Manager Database Control Data (conditional):

$ emca -config dbcontrol db -backup

   vi) Run the Pre-Upgrade information tool by executing the utlu112i.sql script from 11.2 oracle home

SQL > @/u01/oracle/PROD/db/tech_st/11.2.0/rdbms/admin/utlu112i.sql -> Fix all warnings and critical items from the output.

  vii) Check the /etc/oratab file if it has entry for the database or else insert an entry for the database:

eg.: PROD:/u01/oracle/PROD/db/tech_st/11.1.0:N

 c) In a new terminal/putty session set the environment variables for new 11.2 Oracle Home:

$ export ORACLE_SID=PROD
$ export ORACLE_BASE=/u01/oracle/PROD
$ export ORACLE_HOME=/u01/oracle/PROD/db/tech_st/11.2.0
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$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

    i) Create nls/data/9idata directory:  

     Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory

$ pwd
/u01/oracle/PROD/db/tech_st/11.2.0/nls/data/old
$ perl cr9idata.pl

    ii) Start Database Upgrade Assistant (DBUA)    

    - Set DISPLAY

$ dbua

    - In the Welcome Screen Click Next
    - Select the database that you want to upgrade, Click Next
    - Here DBUA will show warnings that were not solved after running Pre-Upgrade Information tool, Click Yes
    - Turn OFF archiving while upgrading, Click Next
    - If archiving is not ON, then this window will not be displayed.
    - Check "Recompile invalid objects at the end of upgrade" and give Degree of Parallelism to proper value, Check "Upgrade Timezone Version and TIMESTAMP WITH TIME ZONE Data", Uncheck "Backup Database".
    - Check whether you want to move the datafiles while upgrade, if not Click Next
    - Uncheck "Configure the Database with Enterprise Manager" and Click Next
    - Check Summary Page and then Click Finish
    - Check the Results

  d) Post-Upgrade Tasks:

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

SQL> @?/rdbms/admin/utlu112s.sql

      Note: If  you get the ORA-01408 error(which is a known problem with Oracle E-Business Suite databases) just ignore it!

   ii) Check the version of the upgraded database:

SQL> select * from v$version;

  iii) Check the invalid object count and the count should be less than or equal to the count from Pre-upgrade Step 7.b.iv

SQL> select count(*) from dba_objects where status <> 'VALID';

  iv) Run all the patch post installation steps:  

       Post patch steps for 13258936:

$ cd $ORACLE_HOME/ctx/admin/ctxpatch/
$ sqlplus / as sysdba
SQL> ctxpatch11203.sql

       Note: If there are any other post patch steps for DB Patches perfome them now. 

8. Move network files from old home to new home and modify the oracle home & all the references of old entries to new oracle home and start the listener:

$ cp -R /u01/oracle/VIS/db/tech_st/11.1.0/network/ $ORACLE_HOME/network/admin/
$ cd $ORACLE_HOME/network/admin/VIS_ebsr12
$ vi listener.ora
:%s/11.1.0/11.2.0/g
:wq

    Repeat above last 2 steps for tnsnames.ora and sqlnet.ora files and if there are any ifiles.

$ lsnrctl start PROD

9. Copy the environment file from old home to new home and modify the oracle home & all the references of old entries to new oracle home:

$ cp /u01/oracle/VIS/db/tech_st/11.1.0/PROD_erpr12.env $ORACLE_HOME
$ cd $ORACLE_HOME
$ vi PROD_erpr12.env
:%s/11.1.0/11.2.0/g

 - comment out below two lines from .env file because ORA_TZFILE is no longer needed for Oracle 11gR2 database

######################
# Timezone Specification file
#ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
#export ORA_TZFILE
######################
:wq

 Note: Modify the path for .env file in .bash_profile if you have an entry for it. 10. 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

11. 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 @adctxprv.sql \manager CTXSYS

12. 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');

13. Validate Workflow ruleset:

      Copy $FND_TOP/patch/115/sql/wfaqupfix.sql from the administration server node to the database server node. Run the below command:

$ sqlplus apps/apps @wfaqupfix.sql
 Enter value for 1: APPLSYS
 Enter value for 2: APPS

14. Implement and run AutoConfig on the new Oracle Home:

   i) Create appsutil.zip file (From Application Tier, login as applmgr):

$ cd $AD_TOP/bin
$ ls admkappsutil.pl
   admkappsutil.pl
$ perl admkappsutil.pl

   - This will create appsutil.zip in $INST_TOP/admin/out directory in application tier.

  ii) Copy or FTP appsutil.zip to $ORACLE_HOME of DB tier and uncompress it:

$ unzip -o appsutil.zip

 iii) Generate the Database Context File:

$ perl $ORACLE_HOME/appsutil/bin/adbldxml.pl

  - This will create context file in $ORACLE_HOME/appsutil directory

     Note: If you get java error, execute the command:

cp -R $ORACLE_HOME/jdk/jre $ORACLE_HOME/appsutil/

 iv) Run AutoConfig on the Database tier by executing below command:

$ cd $ORACLE_HOME/appsutil/bin
$ ./adconfig.sh

15. Restart DB Tier (Database & Listener) services to load the new environment settings.

16. 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
SQL> alter system disable restricted session;

17. Re-create grants and synonyms from Application Tier (Login as applmgr):

      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 administration server node, as the owner of the Applications file system, run AD Administration and select the "Recreate grants and synonyms for APPS schema" task from the Maintain Applications Database Objects menu.

$ adadmin
 - Select [4] in AD Administration Main Menu
 - Select [2]

 Also Disable the maintenance mode from adadmin

18. Recompile invalid objects on DB Tier:

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlrp.sql
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects  where STATUS <> 'VALID';

19. Restart database services and start the applicaiton services.

20. Synchronize Workflow views:

      Log on to Oracle E-Business Suite with the "System Administrator" responsibility.
      Under Requests > Run > Single Request and then click OK button.
 
      Enter the following parameters:
      Request Name = Workflow Directory Services User/Role Validation
      Batch Size = 10000
      Fix dangling users = Yes
      Add missing user/role assignments = Yes
      Update WHO columns in WF tables = No
      Click "OK" and "Submit".

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