Oracle 11gR2 database upgrade in Apps R12.1.1 - Printable Version +- Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities (http://www.oraerp.com) +-- Forum: Oracle Applications (http://www.oraerp.com/forum-39.html) +--- Forum: Oracle Tutorials, Articles, eBooks, Tools & Tips (http://www.oraerp.com/forum-12.html) +--- Thread: Oracle 11gR2 database upgrade in Apps R12.1.1 (/thread-71027.html)

Oracle 11gR2 database upgrade in Apps R12.1.1 - Sara Khan - 12-20-2013 Oracle E-Business suite R12.1.1 will be shipped with database version 11.1.0.7. Here is the step-by-step approach for upgrading database version from 11.1.0.7 to 11.2.0.1 1) Pre-Upgrade Steps - perform all OS per-requistes for Installing 11gR2 SW - Install 11gR2 database software only - Install 11gR2 examples CD - copy required file from 11gR1 (11.1.0.7) $ORACLE_HOME to 11gR2 (11.2.0.1) $HOME - perform a full system cold backup - Install all required application and database patches - Run pre-upgrade utlu112i.sql and update changes as required 2) Upgrade - execute dbua to perform an database upgrade - verify the upgrade logfile for any errors 3) Post upgrade steps - start database listener - Run scripts (adgrants.sql, adctxprv.sql, wfaqupfix.sql) on database - Run autoconfig on database tier - startup all Application Services and verify database upgrade in OAM. Pre-upgrade Steps: - perform all OS per-requistes for Installing 11gR2 SW > Make sure all below rpm's exists on Server binutils-2.17.50.0.6 compat-libstdc++-33-3.2.3 compat-libstdc++-33-3.2.3 (32 bit) elfutils-libelf-0.125 elfutils-libelf-devel-0.125 gcc-4.1.2 gcc-c++-4.1.2 glibc-2.5-24 glibc-2.5-24 (32 bit) glibc-common-2.5 glibc-devel-2.5 glibc-devel-2.5 (32 bit) glibc-headers-2.5 ksh-20060214 libaio-0.3.106 libaio-0.3.106 (32 bit) libaio-devel-0.3.106 libaio-devel-0.3.106 (32 bit) libgcc-4.1.2 libgcc-4.1.2 (32 bit) libstdc++-4.1.2 libstdc++-4.1.2 (32 bit) libstdc++-devel 4.1.2 make-3.81 numactl-devel-0.9.8.x86_64 sysstat-7.0.2 > Make sure below kernel parameters are configure properly: fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.core.rmem_default = 4194304 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 net.ipv4.ip_forward = 0 net.ipv4.conf.default.rp_filter = 1 tcp.ipv4.tcp_wmem = 262144 262144 262144 tcp.ipv4.tcp_rmem = 4194304 4194304 4194304 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65000 > unset environment variables related to 11gR1 Home > create new directories and grant privileges accordingly [root@erpdb1 11g_grid]# cd .. [root@erpdb1 u01]# ls 11g_grid agent12c lost+found oraR12 R12_db [root@erpdb1 u01]# mkdir 11g_db [root@erpdb1 u01]# mkdir 11gr2_db [root@erpdb1 u01]# chmod -R 775 /u01/11gr2_db [root@erpdb1 u01]# chown -R oramgr:dbaerp /u01/11gr2_db [root@erpdb1 u01]# > Database version before upgrade: - Install 11gR2 database software only > Set proper display variable & begin Installation http://2.bp.blogspot.com/-k8cn7VFTr6g/T2B3UK7LS1I/AAAAAAAABvw/dbKBSlC1Vp8/s1600/u6.JPG Installation of Oracle database software completed successfully. - Install 11gR2 examples CD - 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 [oramgr@erpdb1 admin]$ cp -pr PRODERP_erpdb1 /u01/11gr2_db/product/11.2.0/network/admin [oramgr@erpdb1 admin]$ [oramgr@erpdb1 11.1.0]$ ls -l *.env -rwxrwxr-x 1 oramgr dbaerp 4295 Feb 15 13:35 PRODERP_erpdb1.env [oramgr@erpdb1 11.1.0]$ cp PRODERP_erpdb1.env /u01/11gr2_db/product/11.2.0 [oramgr@erpdb1 11.1.0]$ > change the oracle home location in file listener.ora, tnsnames.ora & sqlnet.ora 11gR1 Home - /u01/oraR12/PRODERP/db/tech_st/11.1.0 11gR2 Home - /u01/11gr2_db/product/11.2.0 > copy appsutil directory from 11gR1 home to 11gR2 Home [oramgr@erpdb1 11.1.0]$ cp -pr appsutil /u01/11gr2_db/product/11.2.0 [oramgr@erpdb1 11.1.0]$ > change the oracle home values in 11gR2 home environment file (PRODERP_erpdb1.env) and context file (PRODERP_erpdb1.xml) 11gR1 Home - /u01/oraR12/PRODERP/db/tech_st/11.1.0 11gR2 Home - /u01/11gr2_db/product/11.2.0 > copy dbs folder from 11gR1 to 11gR2 home and done appropriate changes [oramgr@erpdb1 11.1.0]$ echo $ORACLE_HOME /u01/oraR12/PRODERP/db/tech_st/11.1.0 [oramgr@erpdb1 11.1.0]$ cd dbs [oramgr@erpdb1 dbs]$ ls hc_PRODERP.dat init.ora initPRODERP_noaq.ora lkPRODERP peshm_PRODERP_0 PRODERP_ifile.ora initdw.ora initPRDXBLD9.ora_bk initPRODERP.ora peshm_PRDXBLD9_0 PRODERP_erpdb1_ifile.ora [oramgr@erpdb1 dbs]$ cp initPRODERP.ora /u01/11gr2_db/product/11.2.0/dbs [oramgr@erpdb1 dbs]$ - perform a full system cold backup > After successful completion of backup start database and listener from 11gR1 home - 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 [applmgr@erpappl1 bin]$ perl admkappsutil.pl Starting the generation of appsutil.zip Log file located at /u01/applR12/PRODERP/inst/apps/PRODERP_erpappl1/admin/log/MakeAppsUtil_03131154.log output located at /u01/applR12/PRODERP/inst/apps/PRODERP_erpappl1/admin/out/appsutil.zip MakeAppsUtil completed successfully. [applmgr@erpappl1 bin]$ scp /u01/applR12/PRODERP/inst/apps/PRODERP_erpappl1/admin/out/appsutil.zip oramgr@erpdb1:/u01/11gr2_db/product/11.2.0 The authenticity of host 'erpdb1 (192.168.1.71)' can't be established. RSA key fingerprint is aa:24:f6:b4:04:f9:73:01:e0:cc:67:e3:7e:85:0f:9a. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'erpdb1,192.168.1.71' (RSA) to the list of known hosts. oramgr@erpdb1's password: appsutil.zip 100% 2720KB 2.7MB/s 00:00 [applmgr@erpappl1 bin]$ [oramgr@erpdb1 11.2.0]$ chmod 775 appsutil.zip [oramgr@erpdb1 11.2.0]$ ls -l appsutil.zip -rwxrwxr-x 1 oramgr dbaerp 2785461 Mar 13 11:56 appsutil.zip [oramgr@erpdb1 11.2.0]$ [oramgr@erpdb1 11.2.0]$ unzip -o appsutil.zip Archive: appsutil.zip creating: appsutil/ creating: appsutil/bin/ inflating: appsutil/bin/adchkutl.sh inflating: appsutil/bin/txkGenCtxInfRep.pl inflating: appsutil/bin/txkrun.pl inflating: appsutil/bin/adclone.sh inflating: appsutil/bin/addlnctl.pl - -- --- > Install Oracle RDBMS patches to 11gR2 Home: [oramgr@erpdb1 DB]$ ls -lrt total 4016 -rwxrwxr-x 1 oramgr dbaerp 27567 Mar 13 10:07 p9719541_112010_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 85877 Mar 13 10:07 p9535951_112010_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 459293 Mar 13 10:07 p9756939_112010_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 502222 Mar 13 10:07 p9657344_112010_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 39899 Mar 13 10:07 p9644960_112010_Generic.zip -rwxrwxr-x 1 oramgr dbaerp 18466 Mar 13 10:07 p9500046_112010_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 42299 Mar 13 10:07 p9318214_112014_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 2529040 Mar 13 10:07 p9218789_112010_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 24016 Mar 13 10:07 p9081430_112010_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 43049 Mar 13 10:07 p8993052_112010_LINUX.zip -rwxrwxr-x 1 oramgr dbaerp 300009 Mar 13 10:07 p8328200_112014_LINUX.zip [oramgr@erpdb1 DB]$ > Set Opatch variable in PATH and apply these pacthes to 11gR2 Home [oramgr@erpdb1 ~]$ more ora11r2.env ORACLE_BASE=/u01/11gr2_db; export ORACLE_BASE ORACLE_HOME=/u01/11gr2_db/product/11.2.0; export ORACLE_HOME PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH; export PATH [oramgr@erpdb1 ~]$ >> Apply all other patches same as below: [oramgr@erpdb1 9500046]$ pwd /u01/sw_home/patches_11gr2/DB/9500046 [oramgr@erpdb1 9500046]$ opatch apply Invoking OPatch 11.1.0.6.6 Oracle Interim Patch Installer version 11.1.0.6.6 Copyright © 2009, Oracle Corporation. All rights reserved. Oracle Home : /u01/11gr2_db/product/11.2.0 Central Inventory : /u01/11g_grid/oraInventory from : /etc/oraInst.loc OPatch version : 11.1.0.6.6 OUI version : 11.2.0.1.0 OUI location : /u01/11gr2_db/product/11.2.0/oui Log file location : /u01/11gr2_db/product/11.2.0/cfgtoollogs/opatch/opatch2012-03-13_10-23-52AM.log Patch history file: /u01/11gr2_db/product/11.2.0/cfgtoollogs/opatch/opatch_history.txt ApplySession applying interim patch '9500046' to OH '/u01/11gr2_db/product/11.2.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 = '/u01/11gr2_db/product/11.2.0') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files and inventory (not for auto-rollback) for the Oracle Home Backing up files affected by the patch '9500046' for restore. This might take a while... Backing up files affected by the patch '9500046' for rollback. This might take a while... Patching component oracle.rdbms, 11.2.0.1.0... Updating archive file "/u01/11gr2_db/product/11.2.0/lib/libserver11.a" with "lib/libserver11.a/kkqcby.o" Running make for target ioracle ApplySession adding interim patch '9500046' to inventory Verifying the update... Inventory check OK: Patch ID 9500046 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 9500046 are present in Oracle Home. The local system has been patched and can be restarted. OPatch succeeded. [oramgr@erpdb1 9500046]$ > stop database listener from 11gR1 home [oramgr@erpdb1 u01]$ lsnrctl stop PRODERP LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 13-MAR-2012 12:10:37 Copyright © 1991, 2008, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=erpdb1.orasol.com)(PORT=1525))) The command completed successfully [oramgr@erpdb1 u01]$ - Run pre-upgrade utlu112i.sql and update changes as required: [oramgr@erpdb1 db_scripts]$ cp /u01/11gr2_db/product/11.2.0/rdbms/admin/utlu112i.sql [oramgr@erpdb1 db_scripts]$ [oramgr@erpdb1 ~]$ cd db_scripts/ [oramgr@erpdb1 db_scripts]$ ls utlu112i.sql /code> [oramgr@erpdb1 db_scripts]$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 13 14:18:21 2012 Copyright © 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> spool db_upgrade.info SQL> @utlu112i.sql Oracle Database 11.2 Pre-Upgrade Information Tool 03-13-2012 14:20:39 . ********************************************************************** Database: ********************************************************************** --> name: PRODERP --> 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: 11042 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: 1445 MB --> APPS_TS_TX_DATA tablespace is adequate for the upgrade. .... minimum required size: 5213 MB --> APPS_TS_QUEUES tablespace is adequate for the upgrade. .... minimum required size: 107 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: 501 MB .... increase current size by: 29 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 .... MDSYS WARNING:--> recycle bin in use. .... Your recycle bin is turned on and it contains .... 1610 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. . PL/SQL procedure successfully completed. SQL> spool off > added datafile to sysaux talespace SQL> alter tablespace sysaux 2 add datafile '/u01/oraR12/PRODERP/db/apps_st/data/sysaux002.dbf' size 500M; Tablespace altered. > other warnings ignored as these warning are related to initialization parameters and i am using dbua , so dbua will set all required parameters in initialization parameter file. > Need to update all parameters if you are using manual database upgrade process > Purged recycle bin SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged. 2) Upgrade: > do not set any environment variable and excute dbua [oramgr@erpdb1 bin]./dbua -initParam "_disable_fast_validate=TRUE" > Upgrade completed successfully > check upgrade logfile for errors if any > run script cr9idata.pl [oramgr@erpdb1 old]$ pwd /u01/11gr2_db/product/11.2.0/nls/data/old [oramgr@erpdb1 old]$ ls -l cr9idata.pl -rw-r--r-- 1 oramgr dbaerp 10849 Jun 9 2004 cr9idata.pl [oramgr@erpdb1 old]$ perl cr9idata.pl Creating directory /u01/11gr2_db/product/11.2.0/nls/data/9idata ... Copying files to /u01/11gr2_db/product/11.2.0/nls/data/9idata... Copy finished. Please reset environment variable ORA_NLS10 to /u01/11gr2_db/product/11.2.0/nls/data/9idata! > Run catmgdidcode.sql and utlrp.sql scripts for the post install instructions in patch 9218789 3) Post upgrade steps - start database listener > start db listener from 11gR2 HOME - Run scripts (adgrants.sql, adctxprv.sql, wfaqupfix.sql) on database [applmgr@erpappl1 admin]$ pwd /u01/applR12/PRODERP/apps/apps_st/appl/admin [applmgr@erpappl1 admin]$ ls -l adgrants.sql -rwxr-xr-x 1 applmgr dbaerp 52911 Mar 27 2009 adgrants.sql [applmgr@erpappl1 admin]$ scp adgrants.sql oramgr@erpdb1:/home/oramgr/db_scripts oramgr@erpdb1's password: Permission denied, please try again. oramgr@erpdb1's password: adgrants.sql 100% 52KB 51.7KB/s 00:01 [applmgr@erpappl1 admin]$ [applmgr@erpappl1 admin]$ cd $AD_TOP/patch/115/sql [applmgr@erpappl1 sql]$ ls -l adctxprv.sql -rwxr-xr-x 1 applmgr dbaerp 1664 Dec 31 2006 adctxprv.sql [applmgr@erpappl1 sql]$ pwd /u01/applR12/PRODERP/apps/apps_st/appl/ad/12.0.0/patch/115/sql [applmgr@erpappl1 sql]$ scp adctxprv.sql oramgr@erpdb1:/home/oramgr/db_scripts oramgr@erpdb1's password: adctxprv.sql 100% 1664 1.6KB/s 00:00 [applmgr@erpappl1 sql]$ [oramgr@erpdb1 db_scripts]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 08:44:48 2012 Copyright © 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @adgrants.sql Connected. --------------------------------------------------- --- adgrants.sql started at 2012-03-14 08:44:55 --- Creating PL/SQL profiler objects. --------------------------------------------------- --- profload.sql started at 2012-03-14 08:44:55 --- Package created. Grant succeeded. Synonym created. Library created. Package body created. Testing for correct installation SYS.DBMS_PROFILER successfully loaded. PL/SQL procedure successfully completed. ----------------------------------------------------- --- profload.sql completed at 2012-03-14 08:45:02 --- -------------------------------------------------- --- proftab.sql started at 2012-03-14 08:45:02 --- Table dropped. Table dropped. Table dropped. Sequence dropped. Table created. Comment created. Table created. Comment created. Table created. Comment created. Sequence created. ----------------------------------------------------- --- profltab.sql completed at 2012-03-14 08:45:20 --- Creating PL/SQL Package AD_DBMS_METADATA. Package created. Package body created. End of Creating PL/SQL Package AD_DBMS_METADATA. Enter value for 1: APPLSYS PL/SQL procedure successfully completed. Commit complete. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oramgr@erpdb1 db_scripts]$ [oramgr@erpdb1 db_scripts]$ sqlplus apps/apps @adctxprv.sql oracle CTXSYS SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 08:52:05 2012 Copyright © 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Connecting to SYSTEM Connected. PL/SQL procedure successfully completed. Commit complete. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oramgr@erpdb1 db_scripts]$ [oramgr@erpdb1 db_scripts]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 08:54:54 2012 Copyright © 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public'); PL/SQL procedure successfully completed. SQL> -> This script excuted form application server node [applmgr@erpappl1 sql]$ cd $FND_TOP/patch/115/sql [applmgr@erpappl1 sql]$ ls -l wfaqupfix.sql -rwxr-xr-x 1 applmgr dbaerp 4631 Mar 30 2009 wfaqupfix.sql [applmgr@erpappl1 sql]$ sqlplus apps/apps @wfaqupfix.sql APPLSYS APPS SQL*Plus: Release 10.1.0.5.0 - Production on Wed Mar 14 08:56:47 2012 Copyright © 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options PL/SQL procedure successfully completed. Commit complete. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [applmgr@erpappl1 sql]$ - Run autoconfig on database tier > Gather statistics for SYS schema $ 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 grants and synonyms using adadmin utility on Apps Server > startup application services and verify all services started sucessfully I hope this arcticle will be helpful for individuals who wants to upgrade their E-Business suite R12 database. In next article i will upgrade same databse from version 11.2.0.1 to 11.2.0.3 Any comments/suggestion are highly appreciated. Courtesy: http://appsdbaworkshop.blogspot.ae/