Migrate/Upgrade EBS / Apps R12 DB from 11gR1(11.1.0.x) to 11gR2 (11.2.0.1)

During one of the recent upgrade project for EBS R12, we need to migrate our DB, which was running on 11gR1 to 11gR2. 
I tested the migration in our ST Env and it went successful albeit with some road blocks. Once can't accept this kind of Upgrade projects to go smooth, Can one ?


So here I am, with latest update of the patching project, to share with you. 
The whole upgrade can be divided in three steps for easier understanding. 


1. Install 11gR2 Binaries
2. Perform DB Backup and Upgrade DB
3. Post Upgrade steps (configuring the DB for Apps perspective)


Hence, I downloaded the latest 11gR2 binaries from MOS and started the installer as follows.

I did necessary entries for installer to get going. I wont bug you much with that info as there are lots of blogs available on how to install 11gR2 non-cluster binaries. These are some high level screens for the same.

[root@appsdbnode database]# /u01/app/oracle/product/11.2.0/db_1/root.sh
Running Oracle 11g root.sh script...


The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/db_1


Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
[root@appsdbnode database]#

-- copy required file from 11gR1 (11.1.0.7) $ORACLE_HOME to 11gR2 (11.2.0.1) $HOME

-- copy $ORACLE_HOME(11gR1)/network/admin folder to $ORACLE_HOME(11gR2)/network/admin



[oracle@appsdbnode ~]$ cd /u01/app/oracle/visr12/db/tech_st/11.1.0/
[oracle@appsdbnode 11.1.0]$ . ./visr12_appsdbnode.env
[oracle@appsdbnode 11.1.0]$ cd network/admin/
[oracle@appsdbnode admin]$ ll
total 20
drwxr-xr-x 2 oracle oinstall 4096 Jan 17  2009 ADSVIS11_ap6002bld
drwxr-xr-x 2 oracle oinstall 4096 Nov  9  2008 R1211DB1_ap6002bld
drwxr-xr-x 2 oracle oinstall 4096 Nov  6  2008 samples
-rw-r--r-- 1 oracle oinstall  187 May 10  2007 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Jul  3 04:43 visr12_appsdbnode
[oracle@appsdbnode admin]$ scp -r * /u01/app/oracle/product/11.2.0/db_1/network/admin/

[oracle@appsdbnode admin]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@appsdbnode admin]$ ll
total 20
drwxr-xr-x 2 oracle oinstall 4096 Jul  4 06:51 ADSVIS11_ap6002bld
drwxr-xr-x 2 oracle oinstall 4096 Jul  4 06:51 R1211DB1_ap6002bld
drwxr-xr-x 2 oracle oinstall 4096 Jul  4 05:53 samples
-rw-r--r-- 1 oracle oinstall  187 Jul  4 06:51 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Jul  4 06:50 visr12_appsdbnode

-- Copy env file from old ORACLE_HOME to new ORACLE_HOME

[oracle@appsdbnode 11.2.0]$ cd $ORACLE_HOME

[oracle@appsdbnode 11.1.0]$ pwd

/u01/app/oracle/visr12/db/tech_st/11.1.0

[oracle@appsdbnode 11.1.0]$ ll *.env

-rw-r--r-- 1 oracle oinstall 4730 Jul  3 04:43 visr12_appsdbnode.env

[oracle@appsdbnode 11.1.0]$ scp visr12_appsdbnode.env /u01/app/oracle/product/11.2.0/db_1

-- change the oracle home location in file listener.ora, tnsnames.ora & sqlnet.ora
11gR1 Home - /u01/app/oracle/visr12/db/tech_st/11.1.0
11gR2 Home - /u01/app/oracle/product/11.2.0/db_1

-- Make sure you have decent backup to fall back to in case of any issues

[oracle@appsdbnode apps_st]$ scp -r data/ /home/oracle/Backup_visr12_11107\@oradbalab/
[oracle@appsdbnode apps_st]$ cd /home/oracle/Backup_visr12_11107\@oradbalab/
[oracle@appsdbnode Backup_visr12_11107@oradbalab]$ ll
total 0
drwxr-xr-x 3 oracle oinstall 3896 Jul  4  2012 data
[oracle@appsdbnode Backup_visr12_11107@oradbalab]$ du -sh data/
43G     data/
[oracle@appsdbnode Backup_visr12_11107@oradbalab]$ cd -
/u01/app/oracle/visr12/db/apps_st
[oracle@appsdbnode apps_st]$ du -sh data/
43G     data/

[oracle@appsdbnode 11.1.0]$ pwd
/u01/app/oracle/visr12/db/tech_st/11.1.0
[oracle@appsdbnode 11.1.0]$ cd admin/visr12_appsdbnode/
[oracle@appsdbnode visr12_appsdbnode]$ ll
total 16
drwxr-xr-x 2 oracle oinstall 4096 May 14 09:19 bdump
drwxr-xr-x 2 oracle oinstall 4096 May 14 09:19 cdump
drwxrwxr-x 4 oracle oinstall 4096 Jun 28 04:30 diag
drwxr-xr-x 2 oracle oinstall 4096 May 14 09:19 udump
[oracle@appsdbnode visr12_appsdbnode]$ cd  ../
[oracle@appsdbnode admin]$ cd ..
[oracle@appsdbnode 11.1.0]$ scp -r admin/ /u01/app/oracle/product/11.2.0/db_1/

[oracle@appsdbnode visr12_appsdbnode]$ cd /u01/app/oracle/visr12/db/tech_st/11.1.0/

[oracle@appsdbnode 11.1.0]$ scp -r appsutil/ /u01/app/oracle/product/11.2.0/db_1/

-- Change the oracle home values in 11gR2 home environment file  and context file
-- visr12_appsdbnode.env
-- visr12_appsdbnode.xml


-- Copy the files under dbs to new 11gR2 home

[oracle@appsdbnode dbs]$ pwd
/u01/app/oracle/visr12/db/tech_st/11.1.0/dbs
[oracle@appsdbnode dbs]$ scp -r * /u01/app/oracle/product/11.2.0/db_1/dbs/

Next, one has to Install all required application and database patches

> First Install Application ERP patches (9062910, 9852070, 8919489)
>> enable maintenance mode
>> Install patches using adpatch utility
>> perform post patch steps


[oracle@appsnode 9062910]$ adpatch defaultsfile=/u01/app/oracle/visr12/apps/apps                                                                                        _st/appl/admin/visr12/adalldefaults.txt  workers=4 driver=u9062910.drv  logfile=                                                                                        patch_9062910.log

The default directory is [/home/oracle/1213Patches/9062910] :

AutoPatch is complete.



[oracle@appsnode 9852070]$ adpatch defaultsfile=/u01/app/oracle/visr12/apps/apps_st/appl/admin/visr12/adalldefaults.txt  workers=4 driver=u9852070.drv  logfile=patch_9852070.log

The default directory is [/home/oracle/1213Patches/9852070] :

AutoPatch is complete.

[oracle@appsnode 8919489]$ adpatch defaultsfile=/u01/app/oracle/visr12/apps/apps_st/appl/admin/visr12/adalldefaults.txt  workers=4 driver=u8919489.drv  logfile=patch_8919489.log
The default directory is [/home/oracle/1213Patches/pre-req-patches/8919489] :
AutoPatch is complete.


-- Migrate AutoConfig on DB Tier as part of Post Patch process

[oracle@appsnode ~]$ cd /u01/app/oracle/visr12/apps/apps_st/appl/ad/12.0.0/bin
[oracle@appsnode bin]$ perl admkappsutil.pl
Starting the generation of appsutil.zip
Log file located at /u01/app/oracle/visr12/inst/apps/visr12_appsnode/admin/log/MakeAppsUtil_07040846.log
output located at /u01/app/oracle/visr12/inst/apps/visr12_appsnode/admin/out/appsutil.zip
MakeAppsUtil completed successfully.
[oracle@appsnode bin]$ scp /u01/app/oracle/visr12/inst/apps/visr12_appsnode/admin/out/appsutil.zip appsdbnode:/u01/app/oracle/visr12/db/tech_st/11.1.0/
appsutil.zip                                                                  100% 2734KB   2.7MB/s   00:00

-- On DB Tier

[oracle@appsdbnode appsutil]$ cd /u01/app/oracle/visr12/db/tech_st/11.1.0/
[oracle@appsdbnode 11.1.0]$ unzip -o appsutil.zip


-- Apply DB Patches
-- Download following DB patches and apply as follows. 
Note - Some of the patches may fail due to pre-req patches missing. This is going to be env specific so one has to act according the demand.


[oracle@appsdbnode R12_DB_Upg_11gR1_to_11gR2_Patches]$ cd 8328200
[oracle@appsdbnode 8328200]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.1.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/8328200_Jul_04_2012_09_41_30/apply2012-07-04_09-41-29AM_1.log
Applying interim patch '8328200' to OH '/u01/app/oracle/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
Prerequisite check "CheckPatchApplyDependents" failed.
The details are:
Interim patch 8328200 requires prerequisite patch(es) [ 10248516 ] which are not present in the Oracle Home.
Apply prerequisite patch(es) [ 10248516 ] before applying interim patch 8328200 .
Recommended actions: This patch requires some other patches to be installed in the Oracle Home. OPatch cannot find those required patches in the Oracle Home.
OPatch failed with error code 42



-- One can also check the pre-req patches requirements/dependencies as follows 
[oracle@appsdbnode R12_DB_Upg_11gR1_to_11gR2_Patches]$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./10248516
Oracle Interim Patch Installer version 11.2.0.3.0

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches : 

9756939, 10248516, 9657344

Following patches are not required, as they are subset of the patches in Oracle Home or subset of the patches in the given list : 

9036013, 8897784, 8796511, 8790561, 8761974, 8570322, 8405205

Following patches will be rolled back from Oracle Home on application of the patches in the given list : 

9756939, 9657344, 9036013, 8897784, 8796511, 8790561, 8761974, 8570322, 8405205

-- Patch 8993052

[oracle@appsdbnode 8993052]$ opatch apply    
User Responded with: Y                                                                                                                                                  Backing up files...                                                                                                                                                                                                                                                                                                                             Patching component oracle.rdbms, 11.2.0.1.0...                                                                                                                                                                        
OPatch succeeded.


-- Patch 9081430
[oracle@appsdbnode 9081430]$ opatch apply

Patching component oracle.precomp.common, 11.2.0.1.0...

Patching component oracle.rdbms, 11.2.0.1.0...

Patching component oracle.rdbms.plsql, 11.2.0.1.0...

OPatch succeeded.

-- Patch 9218789
[oracle@appsdbnode 9218789]$ opatch napply -skip_subset -skip_duplicate
Oracle Interim Patch Installer version 11.2.0.3.0

OPatch continues with these patches:   4247037  8405205  8570322  8707506  8761974  8771297  8772028  8790561  8796511  8888178  8897784  8971021  9036013 

 9075534  9140891  9147659  9179659  9193873  9201456  9218789  

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

OPatch succeeded.

-- Patch 9318214

[oracle@appsdbnode 9318214]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.1.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/9318214_Jul_04_2012_10_20_42/apply2012-07-04_10-20-42AM_1.log
Applying interim patch '9318214' to OH '/u01/app/oracle/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
Prerequisite check "CheckPatchApplyDependents" failed.
The details are:
Interim patch 9318214 requires prerequisite patch(es) [ 11724930 ] which are not present in the Oracle Home.
Apply prerequisite patch(es) [ 11724930 ] before applying interim patch 9318214 .

-- Patch 9500046
[oracle@appsdbnode 9500046]$ opatch apply

Oracle Interim Patch Installer version 11.2.0.3.0

Patching component oracle.rdbms, 11.2.0.1.0...

Verifying the update...

Patch 9500046 successfully applied

-- Patch 9535951

[oracle@appsdbnode 9535951]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.0

Verifying the update...

Patch 9535951 successfully applied

-- Patch 9644960

[oracle@appsdbnode 9644960]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.0

Verifying the update...

Patch 9644960 successfully applied

-- Patch 9657344

[oracle@appsdbnode 9657344]$ opatch apply

Oracle Interim Patch Installer version 11.2.0.3.0

Rolling back interim patch '9179659' from OH '/u01/app/oracle/product/11.2.0/db_1'

Patching component oracle.rdbms, 11.2.0.1.0...

RollbackSession removing interim patch '9179659' from inventory

Verifying the update...

Patch 9657344 successfully applied

-- Patch 9719541

[oracle@appsdbnode 9719541]$ opatch apply

Oracle Interim Patch Installer version 11.2.0.3.0

Verifying the update...

Patch 9719541 successfully applied

-- Patch 9756939

[oracle@appsdbnode 9756939]$ opatch apply

Oracle Interim Patch Installer version 11.2.0.3.0

Interim patch 9756939 is a superset of the patch(es) [  9201456 ] in the Oracle Home

OPatch will roll back the subset patches and apply the given patch.

Verifying the update...

Patch 9756939 successfully applied

Since all the patches are now finished we are ready to upgrade our DB. Before we start we need to check the compatibility of our existing DB with new 11g R2 Binaries. Hence we will run following tool from /rdbms/admin directory.

-- Run Preupgrade tool


SQL> @/home/oracle/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool    07-05-2012 04:35:40
**********************************************************************
Database:
**********************************************************************
--> name:          VISR12
--> version:       11.1.0.7.0
--> compatible:    11.1.0
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V10
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 11227 MB
--> CTXD tablespace is adequate for the upgrade.
.... minimum required size: 19 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> APPS_UNDOTS1 tablespace is adequate for the upgrade.
.... minimum required size: 861 MB
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
.... minimum required size: 5639 MB
--> APPS_TS_QUEUES tablespace is adequate for the upgrade.
.... minimum required size: 110 MB
--> OLAP tablespace is adequate for the upgrade.
.... minimum required size: 16 MB
WARNING: --> SYSAUX tablespace is not large enough for the upgrade.
.... currently allocated size: 472 MB
.... minimum required size: 564 MB
.... increase current size by: 92 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
*********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
**********************************************************************
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
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Real Application Clusters    [upgrade]  INVALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... 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.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
....   OLAPSYS
....   CTXSYS
....   XDB
....   MDSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER APPS has 28 INVALID objects.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 1603 object(s).  It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command:  PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.

In the above report there are 2 action items for my DB, they are as follows.


SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/visr12/db/apps_st/data/sysaux02.dbf' resize 600M;

QL> alter system set java_pool_size=64M;
SQL> PURGE DBA_RECYCLEBIN;

Once done, set your env for 11gR2 binaries and star the DBUA to upgrade your DB. 
-- Check the following screens, they are pretty much self explanatory.

However, my upgrade with DBUA failed with following error.
database vault option is enabled in oracle home. This option must be disabled for upgrade


Following is the way to disable the DB Vault option from your DB
< http://handsonoracle.blogspot.in/2012/07/disable-oracle-database-vault-recently.html > 


Even after, disabling DB Vault, my upgrade was failing with DBUA. So I decided to manually upgrade the database old school way aka Manually!!!
I copied the necessary files from 11gR1 home /dbs to 11gR2 and started manual upgrade 


[oracle@appsdbnode bin]$ export ORACLE_SID=visr12[oracle@appsdbnode bin]$ ./sqlplus / as sysdba
SQL> startup upgrade
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 5 07:24:23 2012


SQL> catupgrd.sql
-- output truncated 


SQL> exit


-- Next step was to run cr9idata.pl script on DB Node
[oracle@appsdbnode old]$ cd /u01/app/oracle/product/11.2.0/db_1/nls/data/old/

[oracle@appsdbnode old]$ ll cr9idata.pl
-rw-r--r-- 1 oracle oinstall 10849 Jun  9  2004 cr9idata.pl

[oracle@appsdbnode old]$ perl cr9idata.pl
Creating directory /u01/app/oracle/product/11.2.0/db_1/nls/data/9idata ...
Copying files to /u01/app/oracle/product/11.2.0/db_1/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /u01/app/oracle/product/11.2.0/db_1/nls/data/9idata!

-- Start the listener from 11gR2 home 

[oracle@appsdbnode bin]$ ./lsnrctl status VISR12
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-JUL-2012 03:55:20
Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=appsdbnode.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     VISR12
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                09-JUL-2012 07:23:43
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/visr12_appsdbnode/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/db_1/admin/visr12_appsdbnode/diag/tnslsnr/appsdbnode/visr12/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=appsdbnode.localdomain)(PORT=1521)))
Services Summary...
Service "visr12" has 1 instance(s).
  Instance "visr12", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

-- on Apps tier run following scripts

[oracle@appsnode admin]$ cd $APPL_TOP/admin/
[oracle@appsnode admin]$ ll adgrants.sql
-rwxr-xr-x 1 oracle oinstall 53060 May 15 04:05 adgrants.sql

[oracle@appsnode admin]$ cd $APPL_TOP/admin/

[oracle@appsnode admin]$ ll adgrants.sql

-rwxr-xr-x 1 oracle oinstall 53060 May 15 04:05 adgrants.sql

[oracle@appsnode admin]$ scp adgrants.sql  appsdbnode:/home/oracle/

adgrants.sql                                  100%   52KB  51.8KB/s   00:00

[oracle@appsnode sql]$ pwd

/u01/app/oracle/visr12/apps/apps_st/appl/ad/12.0.0/patch/115/sql

[oracle@appsnode sql]$ ll adctxprv.sql

-rwxr-xr-x 1 oracle oinstall 1664 Dec 31  2006 adctxprv.sql

[oracle@appsnode sql]$ scp adctxprv.sql appsdbnode:/home/oracle/

adctxprv.sql                                  100% 1664     1.6KB/s   00:00

[oracle@appsdbnode ~]$ cd -

/u01/app/oracle/product/11.2.0/db_1/bin

[oracle@appsdbnode bin]$ ./sqlplus / as sysdba

SQL> @/home/oracle/adgrants.sql

Connected.

--------------------------------------------------

--- adgrants.sql started at 2012-07-09 04:00:51 ---

Enter value for 1: apps

Creating PL/SQL profiler objects.

---------------------------------------------------

--- profload.sql started at 2012-07-09 04:01:14 ---

Package created.

............................

Commit complete.

[oracle@appsdbnode bin]$ ./sqlplus apps/apps

-- Remember the "manager" is the password for System user

SQL> @/home/oracle/adctxprv.sql manager CTXSYS

Connecting to SYSTEM

Connected.

PL/SQL procedure successfully completed.

Commit complete.

[oracle@appsdbnode ~]$ export ORACLE_SID=visr12

[oracle@appsdbnode ~]$ sqlplus / as sysdba

SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

PL/SQL procedure successfully completed.

-- This script excuted form application server node

[oracle@appsnode sql]$  cd $FND_TOP/patch/115/sql

[oracle@appsnode sql]$ ls -l wfaqupfix.sql

-rwxr-xr-x 1 oracle oinstall 4631 Mar 30  2009 wfaqupfix.sql

[oracle@appsnode sql]$ sqlplus apps/apps @wfaqupfix.sql APPLSYS APPS

PL/SQL procedure successfully completed.

Commit complete.

-- On Db Tier

[oracle@appsdbnode appsutil]$ pwd

/u01/app/oracle/product/11.2.0/db_1/appsutil

[oracle@appsdbnode appsutil]$ vi visr12_appsdbnode.xml

[oracle@appsdbnode appsutil]$ ./bin/adconfig.sh

Enter the full path to the Context file: visr12_appsdbnode.xml

Enter the APPS user password:

The log file for this session is located at: /u01/app/oracle/product/11.2.0/db_1/appsutil/log/visr12_appsdbnode/07090425/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.

        Using ORACLE_HOME location : /u01/app/oracle/product/11.2.0/db_1

        Classpath                   : :/u01/app/oracle/product/11.2.0/db_1/jdbc/lib/ojdbc6.jar:/u01/app/oracle/product/11.2.0/db_1/appsutil/java/xmlparserv2.jar:/u01/app/oracle/product/11.2.0/db_1/appsutil/java:/u01/app/oracle/product/11.2.0/db_1/jlib/netcfg.jar:/u01/app/oracle/product/11.2.0/db_1/jlib/ldapjclnt11.jar

        Using Context file          : /u01/app/oracle/product/11.2.0/db_1/appsutil/visr12_appsdbnode.xml

Context Value Management will now update the Context file

Error - Here I hit the error with AutoConfig execution on DB Tier. 

[AutoConfig Error Report]

The following report lists errors AutoConfig encountered during each phase of its execution.  Errors are grouped by directory and phase.

The report format is:

      <filename>  <phase>  <return code where appropriate>

  [PROFILE PHASE]

  AutoConfig could not successfully execute the following scripts:

    Directory: /u01/app/oracle/product/11.2.0/db_1/appsutil/install/visr12_appsdbnode

      afdbprf.sh              INSTE8_PRF         1

  [APPLY PHASE]

  AutoConfig could not successfully execute the following scripts:

    Directory: /u01/app/oracle/product/11.2.0/db_1/appsutil/install/visr12_appsdbnode

      adcrobj.sh              INSTE8_APPLY       1

      addbperms.sh            INSTE8_APPLY       1

      txkcreateACL.sh         INSTE8_APPLY       1

Problem : EBS 12.1.1: Autoconfig Fails While Running Scripts "afdbprf.sh" and "adcrobj.sh" with 11GR2 Database

Ref - EBS 12.1.1: Autoconfig Fails While Running Scripts "afdbprf.sh" and "adcrobj.sh" with 11GR2 Database [ID 1336807.1]

-- Download the patch 7651166 (R12.TXK.B.DELTA.2) from MOS and review the readme file

[oracle@appsnode 7651166]$ adpatch defaultsfile=/u01/app/oracle/visr12/apps/apps_st/appl/admin/visr12/adalldefaults.txt  workers=4 driver=u7651166.drv  logfile=patch_7651166.log

After the patch application I migrated the Autoconfig to DB tier and tried running AutoConfig on DB tier. But it failed miserably. After a bit of research I figured I have to rebuild the context file on DB Tier and due to some mis-configuration issue the Autoconfig was failing. 

So I rebuild the context file on DB Tier as follows

<link to another note for rebuilding the context file>

Worraaaaah... AutoConfig ran successfully this time.

-- Next was to Re-create grants and synonyms using adadmin utility on Apps Server

[oracle@appsnode appl]$ adadmin defaultsfile=/u01/app/oracle/visr12/apps/apps_st/appl/admin/visr12/adalldefaults.txt

Choose option 4.    Maintain Applications Database Entities menu

     Choose option  2.    Re-create grants and synonyms for APPS schema

-----------------------

-- Output Truncated

------------------------

Done purging timing information for prior sessions.

AD Administration is complete.

-- Start the apps tier

[oracle@appsnode appl]$ adstrtal.sh apps/apps

[oracle@appsnode appl]$ adopmnctl.sh  status

Processes in Instance: visr12_appsnode.appsnode.localdomain

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status  

---------------------------------+--------------------+---------+---------

OC4JGroup:default_group          | OC4J:oafm           |     890 | Alive   

OC4JGroup:default_group          | OC4J:forms          |     824 | Alive   

OC4JGroup:default_group          | OC4J:oacore        |     691 | Alive   

HTTP_Server                               | HTTP_Server        |     639 | Alive   

-- Now open the Apps console and navigate to DB Host info from OAM.

Instance Status

Name Host Version Startup Time State Shutdown Pending Status Role Sessions visr12 appsdbnode.localdomain 11.2.0.1.0 09-Jul-2012 03:53:45 OPEN NO PRIMARY_INSTANCE 13

Which indicates that now you have successfully migrated to 11gR2 version.