Wednesday, March 31, 2010

11gR2 upgrade with 12.1.2

Steps for 11gR2 Upgrade
Pre-Upgrade Task
Remove DB from archive log mode
Complete Backup of the Database & Application Binaries
Apps Patches:
9062910 -- 11g Release 2 interoperability patch for Release 12.1
7651166 -- 12.1 TXK Delta 2 patch
Adconfig Patches

Set the path of oraInventory in /var/opt/oracle/oraInst.loc file.
Make entry for the oracle home that needs to be upgrade in /var/opt/oracle/oratab file.
Take a backup of the existing RDBMS oraInventory folder.

Software Installation Task
Set the path for ORACLE_SID and ORACLE_BASE env variable
Create a 11.2.0 directory under the $ORACLE_BASE.
Execute the runInstaller to start the 11.2.0 Software only installation.
Click the Ignore all checkbox on Perform Prerequisite Checks when the checks fail for OS Patch - 124861-15 as this is an optional patch.
Run through root user when prompted.
Install the 11g Examples CD

"After the installation, make sure that: "
The ORACLE_SID environment variable must be set accordingly.
The ORACLE_BASE environment variable must be set accordingly.
The ORACLE_HOME environment variable points to the new 11.2.0 Oracle home.
The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
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])

ORACLE_SID=test11g; export ORACLE_SID
ORACLE_BASE=/test11g/oracle; export ORACLE_BASE
ORACLE_HOME=/test11g/oracle/11.2.0; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:/usr/dt/lib:/usr/openwin/lib; export LD_LIBRARY_PATH
PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0; export PERL5LIB
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

Create nls/data/9idata directory
From 11.2.0 Oracle Home run the following:
perl $ORACLE_HOME/nls/data/old/
Check the directory created --- $ORACLE_HOME/nls/data/9idata
set the env variable ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Additional RDBMS Patch
Apply RDBMS Patch - 9218789
Note: Do not run any of the post install instructions as those will be done after the upgrade
Stop the Application Processes and database listener.
From old ORACLE_HOME --Connect DB as sysdba and Drop SYS.ENABLED$INDEXES
SQL> drop table sys.enabled$indexes;

Prepare for DB Upgrade
Copy utlu112i.sql from 11.2.0 ORACLE_HOME/rdbms/admin to a temporary folder.
"From Old ORACLE_HOME, connect DB as SYSDBA"
SQL> SPOOL upgrade_info.log
SQL> @utlu112i.sql
Check for the output of the script.
Add space to SYSAUX Tablespace.
SQL> alter database datafile '//dbdata/data1/sysaux01.dbf' resize 600M;

add the following paraneter to init.ora file:
comment out the following parameters from init.ora file:
#plsql_native_library_dir = //oracle/11.1.0/plsql/nativelib
#plsql_native_library_subdir_count = 149

Restart the Database
Connect database as SYSDBA and execute the following queries:
SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
SQL> SELECT * FROM dba_2pc_pending;
Re-execute utlu112i.sql

DB Upgrade Task
Run netca from old 11.1.0 Oracle Home and delete the listener.
Shutdown the database and comment out the following parameter in 11.1.0 Oracle Home's init.ora # local_listener='_LOCAL'

Make sure the following envs' are set properly:
$ export ORACLE_SID=
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
$ export TNS_ADMIN=$ORACLE_HOME/network/admin
Run netca from new 11.2.0 Oracle Home and create the new listener.
Run database upgrade assistant as follows:
"$ dbua -initParam ""_disable_fast_validate=TRUE"""

Modify initialization parameters
Use the init.ora parameter setting for the 11.2.0 as per NoteID:396009.1
"Ensure the parameter ""_disable_fast_validate=TRUE"" is set.."

Restart the Database.
Start the Database listener.
Connect with SYS and run only catmgdidcode.sql and utlrp.sql as part of the post install instructions in patch 9218789. The other scripts were already run as part of the dbua upgrade.
SQL> conn / as sysdba
SQL> @?/md/admin/catmgdidcode.sql
SQL> @?/rdbms/admin/utlrp.sql

Post DB Upgrade Tasks
Copy $APPL_TOP/admin/adgrants.sql from admin node to temporary folder.
Run as follows:
"$ sqlplus ""/ as sysdba"" @adgrants.sql apps"

Copy $AD_TOP/patch/115/sql/adctxprv.sql from admin node to temporary folder.
Run as follows:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
"$ sqlplus ""/ as sysdba"""
"SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');"

Login to Admin Node and execute the following script connected with APPS schema.
$ sqlplus apps/[APPS password] @$FND_TOP/patch/115/sql/wfaqupfix.sql APPLSYS APPS

Implement and run AutoConfig
Make sure all the above pre-req Patches are applied mentioned in lines 8-13
Login to Application Tier and Source the env
perl $AD_TOP/bin/
the scripts will create in $INST_TOP/admin/out
Copy AutoConfig to the RDBMS ORACLE_HOME
Copy or FTP the file to the
unzip -o

Install JRE on the Database tier
Download Latest Update of JRE 6.0
Ftp the file to $ORACLE_HOME/appsutil
$ cd $ORACLE_HOME/appsutil
$ sh
$ mv jre1.6.0._latest_update# jre

Generate the Database Context File
$ cd $ORACLE_HOME/appsutil/bin
$ perl
"Note: After creating the XML context file, do the following changes:"
1. Backup the existing context_file.
2. rename the existing context_file (_.xml) to _.xml [E.g SID_hostname.xml -> sid_vhost.xml]
3. Open the _.xml and change all the occurance of to vi sid_vhost.xml
4. ensure variable s_jretop points to a proper JRE directory i.e. $ORACLE_HOME/jdk/jre.
5. ensure variable s_db_tnsadmin points to $ORACLE_HOME/network/admin/$CONTEXT_NAME

Run AutoConfig on the Database tier
$ cd $ORACLE_HOME/appsutil/bin
$ ./ contextfile=

Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from admin node to temporary folder.
Note: adstats.sql has to be run in restricted mode.
Make sure that you have at least 1.5 GB of free default temporary tablespace.
"$ sqlplus ""/ as sysdba"""
SQL> alter system enable restricted session;
SQL> @adstats.sql
"$ sqlplus ""/ as sysdba"""
SQL> alter system disable restricted session;
SQL> exit;

Re-create custom database links (conditional)
Re-create grants and synonyms
"Run ""Recreate grants and synonyms for APPS schema"" through ADADMIN."

Restart Applications server processes
Synchronize Workflow views
"Log on to Oracle E-Business Suite with the ""System Administrator"" responsibility"
Run the request with the following parameteres:
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

Refernce Metalink Notes:
ID 1058763.1 Interoperability Notes EBS R12 with Database 11gR2.
ID 387859.1 Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12.
ID 396009.1 Database Initialization Parameters for Oracle Applications Release 12.
ID 418664.1 Overview of Using Java with Oracle E-Business Suite Release 12
ID 455492.1 Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12

Happy Troubleshooting !!!


Anonymous said...


do I really need to set the ORACLE_BASE variable? I am in the process of upgrading the db but cannot set oRACLE_BASE to the samevalue as ORACLE_HOME, in R12 we did not have ORACLE_BASE set when we performed the fresh installation.


Sundeep Dubey said...

It is required to set ORACLE_BASE env variable otherwise it will pickup some default values. So do set this.