Database Upgradation from 10.2.0.3 to 11.1.0.7 in Oracle E-Business Suite 11.5.10.2

Introduction

The database upgradation process for 11G is same as other version of oracle ,
Either using DBUA or manually. I followed the manual process to accomplish the task.

.
Prerequisites

i). Apply ATG rollup patch 6   5903765
 In my case instances was not updated to ATG roll up patch 6 , so I have applied the patch before proceeding .

SQL> select * from ad_applied_patches where PATCH_NAME=’5903765′;

no rows selected
 Follow metalink note 444524.1 for applying the Rup 6 patch
ii).Developer 6i /forms patch set  level should be 18 .Use the below script to find the patch set level
#!/bin/sh
########################################
#fpslevel.sh – Used to find out the forms patchset level
########################################
clear
PatchLevel=0
f60gen > /dev/null 2>&1
if [ $? -eq 127 ] then
echo “Environment is not Set”
else
PatchLevel=`f60gen | grep ‘(Form Compiler)’ | grep -v Release | awk -F. ‘{print $5}’ `
echo ‘Forms Patchset Level :’ ` expr $PatchLevel – 9 `
fi
In my case the result was 18
[oa11gtst cidcsebu12 EBUDEV] $ ./patchset.sh
Forms Patchset Level : 18
If the patch set level is less than 18 then you to upgrade the patch set level to minimum 18.
Refer metalink Note 125767.1  for patch set upgradation .
 iii). Latest Autoconfig patch 7011280
SQL>  select * from ad_bugs where BUG_NUMBER=’7011280′;
no rows selected
In my case I have applied the above mentioned patch using adpatch utility. Please go through readme.txt before applying the patch .

.
Prepare to create the 11.1.0 Oracle home
i)
Install 11.1.0 software in different location
Set up database environment for installation
Login as Unix os user
[oa11gtst cidcsebu12 ] $ ORACLE_BASE=/fh03/TST11G/oraapps
[oa11gtst cidcsebu12 ] $ export ORACLE_BASE
[oa11gtst cidcsebu12 ] $  ORACLE_HOME=$ORACLE_BASE/11.1.0
[oa11gtst cidcsebu12 ] $ export ORACLE_HOME
[oa11gtst cidcsebu12 ] $ ORACLE_SID=TST11G
[oa11gtst cidcsebu12 ] $ export ORACLE_SID
[oa11gtst cidcsebu12 ] $LD_LIBRARY_PATH=$ORACLE_HOME/lib 
[oa11gtst cidcsebu12 ] $ export  LD_LIBRARY_PATH
[oa11gtst cidcsebu12 ] $ PATH=$PATH:/usr/local/bin:/usr/ccs/bin:
/usr/sfw/bin:$ORACLE_HOME/bin

[oa11gtst cidcsebu12 ] $ export  PATH

ii) Start the Oracle Installation    

  set display=<local IP of your machine >   

iii) Select Installation method Click next  

Note follow the instruction on the installation screen and click next

iv) Execute root.sh

 v) End of Installation

 

Prepare/Perform  to Install 11.1.0.7 patch set
i) Install 11.1.0.7 patch on top of the existing 11.1.0.6 Oracle Home .
# ls -ltr
total 3503232
-rwxrwxrwx   1 root     root      125772 Oct  3  2008 README.html
drwxrwxrwx   6 root     root        8192 Oct  3  2008 Disk1
-rwxrwxrwx   1 root     root     1793502538 Apr  9 01:28 p6890831_111070_SOLARIS64.zip
root on cidcsebu12:</fs02/11.0.7.patch>
# pwd
/fs02/11.0.7.patch
root on cidcsebu12:</fs02/11.0.7.patch>
The installation process remain same using RunInstaller.sh .

.

Post Installation Task
i)
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/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 11g Oracle home.
ii)Apply Additional RDBMS patch in 11.1.0 Home
For all UNIX/Linux platforms, apply the following RDBMS patches:
• 6530141
• 6972189
• 7111245
• 7253531
• 7295298
• 7486407
Note :- The patch 7486407 has got post patch task , which can be done once your database in up and running after upgradation .

Login as Oracle owner set Oracle ENV and  set PATH
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH: export PATH

Apply all the patches using opatch utility of Oracle .Please go thorough readme.txt
/fs02/11gpostpatch/6530141
oa11gtst on cidcsebu12:</fs02/11gpostpatch/6530141>
$ ls -ltr
total 16
drwxrwxrwx   4 root     root          96 Nov 14 07:24 etc
drwxrwxrwx   3 root     root          96 Nov 14 07:24 files
-rwxrwxrwx   1 root     root        4980 Nov 14 07:24 README.txt
oa11gtst on cidcsebu12:</fs02/11gpostpatch/6530141>
$ opatch apply
Invoking OPatch 11.1.0.6.2
Oracle Interim Patch Installer version 11.1.0.6.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.
Oracle Home       : /fh03/TST11G/oraapps/11.1.0
Central Inventory : /fh03/TST11G/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0
OUI location      : /fh03/TST11G/oraapps/11.1.0/oui
Log file location : /fh03/TST11G/oraapps/11.1.0/cfgtoollogs/opatch/opatch2009-04-22_00-32-36AM.log
ApplySession applying interim patch ‘6530141’ to OH ‘/fh03/TST11G/oraapps/11.1.0’
Running prerequisite checks…
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/fh03/TST11G/oraapps/11.1.0’)
Is the local system ready for patching? [y|n] Verifying the update…
Inventory check OK: Patch ID 6530141 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 6530141 are present in Oracle Home.
The local system has been patched and can be restarted.
OPatch succeeded.
—————-
The same process needs to follow to apply all the above patches.

.
Run the Pre-Upgrade Information Tool
i)
Copy utlu111i.sql from New ORACLE_11GHome to temporary directory
$ ls -l utlu111i.sql
-rw-r–r–   1 oa11gtst dba       159224 Jul 31  2008 utlu111i.sql
oa11gtst on cidcsebu12:</fh03/TST11G/oraapps/11.1.0/rdbms/admin>
Login  to your source 10G database as sysdba or system .
Database Environment set to : TST11G
Type ‘exit’ or ‘CTRL-D’ to return to previous shell and environment

[oa11gtst cidcsebu12 TST11G] $ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Apr 22 02:12:57 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

Startup the database if it is shutdown
SQL> spool upgradeinfo.log
SQL> @utlu111i.sql
Miscellaneous Warnings
*********************************************************
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
….   SYS
….   OLAPSYS
….   SYSMAN
….   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 SYS has 1 INVALID objects.
…. USER XXINV has 5 INVALID objects.
…. USER APPS has 1 INVALID objects.
…. USER XXONT has 4 INVALID objects.
…. USER XXPO has 1 INVALID objects.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER XXMRP has dependent objects.
…. USER XXINV has dependent objects.
…. USER APPS has dependent objects.
…. USER XXONT has dependent objects.
…. USER XXBOM has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.

SQL> spool off

Tablespaces: [make adjustments in the current environment] ******************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 7868 MB
–> APPS_UNDOTS1 tablespace is adequate for the upgrade.
…. minimum required size: 629 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 61 MB
–> APPS_TS_QUEUES tablespace is adequate for the upgrade.
…. minimum required size: 307 MB
–> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
…. minimum required size: 3249 MB
–> CTXD tablespace is adequate for the upgrade.
…. minimum required size: 29 MB
–> ODM tablespace is adequate for the upgrade.
…. minimum required size: 15 MB
–> OLAP tablespace is adequate for the upgrade.
…. minimum required size: 32 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 1074 MB
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile] **********************************************
–> “background_dump_dest” replaced by  “diagnostic_dest”
–> “user_dump_dest” replaced by  “diagnostic_dest”
–> “core_dump_dest” replaced by  “diagnostic_dest”

Check the above report meticulously , because we need to change/adjust  the parameter based on the report in 11g  init.ora parameter or in 10G database level .
ii) Create pfile from Spfile  and copy the pfile to new 11G_home/dbs
and comment user_dump_dest, core_dump_dest and background_dump_dest
Add the new parameter *.diagnostic_dest=<Oracle_Base>
Change the parameter  *.compatible=’11.1.0′
iii) Copy Tnsname.ora  and Listener.ora from 10G  $TNS_ADMIN to New 11G TNS_ADMIN
and change $ORACLE_HOME location , SID ,Port and Host
   Set the env   TNS_ADMIN in New Oracle 11G and start the listener.
iv) Check the Timezone version in 10G
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT version FROM v$timezone_file;
   VERSION
———-
         4
The minimum version should be 4.
v) Check the Custom DB Links and create spool file .
SQL> SQL> SET LONG 9000
SQL> SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) FROM dba_db_links
vi) Check Invalid objects in 10G .
SQL> spool invalid_pre.lst
SQL> select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <>’VALID’
  2    3    4    5  ;

[oa11gtst cidcsebu12 TST11G] $ clear
[oa11gtst cidcsebu12 TST11G] $ pwd
/fh03/TST11G/oraapps/10.2.0/rdbms/admin
[oa11gtst cidcsebu12 TST11G] $ ls -l utlrp.sql
-rwxrwxr-x   1 oa11gtst dba         3221 Aug 15  2003 utlrp.sql
[oa11gtst cidcsebu12 TST11G] $ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Apr 22 02:38:08 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> @utlrp.sql

For errors check
select * from UTL_RECOMP_ERRORS
vii) Shutdown Source Application services and Database and Take full backup

.
Database Upgradation
i) $ ls -l catupgrd.sql
-rw-r–r–   1 oa11gtst dba         4026 Apr  2  2007 catupgrd.sql
oa11gtst on cidcsebu12:</fh03/TST11G/oraapps/11.1.0/rdbms/admin>
$ sqlplus “/as sysdba”

SQL*Plus: Release 11.1.0.7.0 – Production on Thu Apr 23 00:54:49 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.
$ sqlplus “/as sysdba”

SQL*Plus: Release 11.1.0.7.0 – Production on Thu Apr 23 00:54:49 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size                  2109352 bytes
Variable Size             427823192 bytes
Database Buffers          624951296 bytes
Redo Buffers               14368768 bytes
Database mounted.
Database opened.
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
Check patch.log after completion .

.

Post Upgradation Task

i) Shutdown the database and then startup the database once the upgradation complete.
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @?/rdbms/admin/utlrp.sql
ii) Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql   from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
SQL> startup restrict
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size                  2109352 bytes
Variable Size             427823192 bytes
Database Buffers          624951296 bytes
Redo Buffers               14368768 bytes
Database mounted.
SQL> @adstats.sql
Connected.
————————————————–
— adstats.sql started at 2009-04-24 01:06:55 —
Checking for the DB version and collecting statistics …

iii) Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. 
bash-3.00$ sqlplus apps/ppsdev10a

SQL*Plus: Release 8.0.6.0.0 – Production on Mon Apr 27 00:19:32 2009

(c) Copyright 1999 Oracle Corporation.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @adctxprv.sql

Connecting to SYSTEM
Enter value for 1: temdev10smay
SQL> @adctxprv.sql

Connecting to SYSTEM
Enter value for 1: temdev10smay
Connected.
Enter value for 2: CTXSYS

.

iv) Implement and Run  Autoconfig.
Please follow my post Autoconfig on Database Tier here
Follow metalink note 165195.1 for Autoconfig .

v) SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.

.

Refer Metalink Note :- 452783.1 for upgradation 
 

Share This Post with Your Friends over Social Media!

About the Author Rajat

I am Rajat Dey and I have more than decade of Oracle experience from all major regions throughout the globe. I am well versed in most facets of Oracle, supporting database, fusion middleware and the eBusiness suite across many operating system platforms.Currently I am located in Sydney  Oracle Financials 11i and Release 12,12.2  Oracle RDBMS 7x,8i , 9i,10g and 11g  Amazon cloud .  Oracle RAC 10g and 11g  Oracle Applications Server  Oracle Web logic Server  OBIEE  Hyperion  SSO  Oracle Portal  PL/SQL  UNIX  SQL  MS-SQL 2008.  OEM 12c .  Data Guard .  Essbase .  Database backup and recovery.  Performance Tunning.  TimeSten

Leave a Comment:

3 comments
Nikhil Mistry says July 14, 2009

Hi Rajat,
do you have documents for 10.2.0.2 database upgrade to 10.2.0.4? I am in process to upgrade my 11.5.10.2 Apps database upgrade !

Reply
Nathan says August 31, 2009

After the upgrade, did you configure the Network ACLs? If so, do you mind posting the details?

Reply
Haresh says September 11, 2009

Hi,

I have confusion in appling the post patch task as below:

As per the 7486407 Patch Installation Instructions:

Set the ORACLE_HOME environment variable to the oracle home in which your Oracle Server is installed.

I have to set the new 11g ORACLE_HOME environment variable as below
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

Startup the Oracle Services.
I have to start Database and listener of old oracle which I want to upgrade to 11g?
In new oracle 11g I have installed software only

Then how to execute the below script?
> sqlplus ‘/ as sysdba’
> @?/rdbms/admin/prvtaw.plb
> @?/rdbms/admin/prvtawex.plb

Regards,
Haresh

Reply
Add Your Reply