Oracle Apps DBA

My photo
More than 5+ Years of IT Experience in administering Enterprise wide Multi Vendor UNIX Servers, Oracle Databases (8i to 11gR2), Middle-tiers, Applications and Clusters. I am a Sun Certified System Administrator (SCSA) for Solaris 10, oracle 10g database certified associate(OCA), Oracle 10g Database certified professional (OCP) and Oracle E-Business suite R12 certified professionl(OCP)

Oracle Database and Applications

Wednesday, 16 March 2011

Oracle Database Upgrade from 10gR2 to 11gR2 in Oracle Applications 11i (EBS)

The Below article illustrate all step by step methods Involved in performing out of place upgrade (11.2.0.2) in Oracle E-Business suite 11i Environment.

For more Information on "Inplace and Out-of-place Upgrade" please refer http://appsdbaworkshop.blogspot.com/2011/03/oracle-11gr2-inplace-and-out-of-place.html

Steps:

1) Perform all OS specific requirement for Installing 11gR2 Database.
2) Install 11.2.0.2 $ORACLE_HOME in separate home.
3) Install examples CD in 11.2.0.2 HOME
4) Copy Context_name.env, Initilization parameter files and oracle network configuration files to NEW home and update 11.2.0.2 settings
5) Source new env file and verify all variables
6) Create nls/data/9idata directory
7) Add ORA_NLS10 env variable to env file and verify the env variable
8) Apply additional interoperability database patches (Opatches)
9) Run Pre-upgrade tool
10) Update changes as recommended by pre-upgrade tool
11) Verify INVALID components
12) Check time zone file
13) check deprecated connect role
14) Purge recyclebin
15) Shutdown database and perform full backup
16) confirm backup
17) start listener from NEW 11g home
18) Run dbua from 11g_home without setting any .env file
19) Perform All post upgrade tasks - Run adgrants.sql, Grant create procedure privilege on CTXSYS , - Set CTXSYS parameter.
20) Implement and run autoconfig
21) Gather statistics for SYS schema
22) Apply Application patches adpatch
23) Re-create grants and synonyms using adadmin
24) Upgrade TimZone file version
25) startup applications and verify access
26) Check all database and application alert logs
27) After confirmation release system for users.


1) Perform all OS specific requirement for Installing 11gR2 Database.


Hardware Requirements
* Oracle software requires a minimum of 1024 MB of RAM for successful installation.

* The following table describes the relationship between installed RAM and the configured swap space requirement:
RAM Swap Space
Between 1 GB and 2 GB 1.5 times the size of RAM
Between 2 GB and 16 GB Equal to the size of RAM
More than 16 GB 16 GB


* 1 GB Mb of free space in /tmp
* The disk space requirements for software files for each installation type:
Enterprise Edition - 4.68 GB
Standard Edition - 4.62 GB
Custom Edition - 4.71 GB (maximum)

* Between 1.5 GB and 2 GB of disk space is required for preconfigured database that uses file system storage (optional).

Available hardware is sufficient for 11gR2 Installation.

Software Requirements
Minimum version Solaris 10 U6 (5.10-2008.10)
Installed Version Solaris 10 U8 (5.10-2009-9)

Check OS packages:


pkginfo -i SUNWarc SUNWbtool SUNWcsl SUNWhea SUNWi15cs SUNWi1cs SUNWi1of SUNWlibC SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWxwfnt





To verify patches:


bash-3.00$ patchadd -p | grep 141736
bash-3.00$ patchadd -p | grep 141414
bash-3.00$ patchadd -p | grep 139555
bash-3.00$ patchadd -p | grep 141016
bash-3.00$ patchadd -p | grep 140899
bash-3.00$ patchadd -p | grep 140796
bash-3.00$ patchadd -p | grep 125555
bash-3.00$ patchadd -p | grep 119963
bash-3.00$ patchadd -p | grep 139574
bash-3.00$ patchadd -p | grep 120753


Compiler Requirement:

Sun One Studio 12 (C and C++ 5.9) is supported with Pro*C/C++, Oracle Call Interface, Oracle C++ Call Interface, and Oracle XML Developer's Kit (XDK) for Oracle Database 11g Release 2.

To check Installation of Sun Studio:




Additional requirements:

To verify version of JAVA:


bash-3.00$ java -version
java version "1.5.0_22"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_22-b03)
Java HotSpot(TM) Server VM (build 1.5.0_22-b03, mixed mode)
bash-3.00$


OS Environment
*Kernel parameters
Solaris 10 uses the resource control facility to implement the System V IPC. However, Oracle recommends that you set both resource control and /etc/system/ parameters. Operating system parameters not replaced by resource controls continue to affect performance and security on Solaris 10 systems.

Parameter Replaced by Resource Control Minimum Value
noexec_user_stack NA (can be set in /etc/system only) 1
semsys:seminfo_semmni project.max-sem-ids 100
semsys:seminfo_semmsl process.max-sem-nsems 256
shmsys:shminfo_shmmax project.max-shm-memory 4294967295
shmsys:shminfo_shmmni project.max-shm-ids 100
Please note that "project.max-shm-memory" represent the maximum shared memory available for a project, so the value for this parameter should be greater than sum of all SGA size.

The ulimit settings determine process memory related resource limits. Verify that the shell limits displayed in the following table are set to the values shown:

Shell Limit Recommended Value
TIME -1 (Unlimited)
FILE -1 (Unlimited)
DATA Minium value: 1048576
STACK Minium value: 32768
NOFILES Minium value: 4096
VMEMORY Minium value: 4194304

To display the current value specified for these shell limits enter the following commands:
ulimit -t
ulimit -f
ulimit -d
ulimit -s
ulimit -n
ulimit -v


Also set new kernel parameters:


bash-3.00# ndd -set /dev/tcp tcp_largest_anon_port 65550
bash-3.00# ndd -set /dev/udp udp_smallest_anon_port 9000
bash-3.00# ndd -set /dev/udp udp_largest_anon_port 65500
bash-3.00# ndd -set /dev/tcp tcp_smallest_anon_port 9000


-- These OS requirement settings are as per the Sun Solaris 10 Sparc 64-bit architecture. Other platform should verify their OS requirements as per tech note "Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) [ID 169706.1]"


2) Install 11.2.0.2 $ORACLE_HOME in separate home:

- Download and unzip 11.2.0.2 patchset (p10098816_112020_SOLARIS64)unzip it in stage area.
- create 11g realted .env file


bash-3.00$ more ora11g.env
ORACLE_BASE=/db/data
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/11.2.0.2
export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
PATH=/usr/bin:/usr/ccs/bin:/usr/sbin/:usr/ucb:/etc:$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export PATH
bash-3.00$


- Make sure you have proper Display variable configured and xlcock is working fine.
- runInstaller from stage Area
- Follow screens to complete your Installation



























- Run root.sh as root user and then click "OK"



- Installation of 11.2.0.2 completed successfully

3) Install examples CD in 11.2.0.2 HOME:













After the installation, make sure that:
• 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])


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.


bash-3.00$ perl cr9idata.pl
Creating directory /db/data/11.2.0.2/nls/data/9idata ...
Copying files to /db/data/11.2.0.2/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /db/data/11.2.0.2/nls/data/9idata!
bash-3.00$ pwd
/db/data/11.2.0.2/nls/data/old
bash-3.00$


Add ORA_NLS10 env variable to env file and verify the env variable :


bash-3.00$ . PREPROD_testdb.env
bash-3.00$ echo $ORA_NLS10
/db/data/11.2.0.2/nls/data/9idata
bash-3.00$


Apply additional interoperability database patches (Opatches):

- Set the PATH variable for Opatch and verify the same


bash-3.00$ pwd
/db/data/11.2.0.2/OPatch
bash-3.00$ PATH=/db/data/11.2.0.2/OPatch:$PATH; export PATH
bash-3.00$ which opatch
/db/data/11.2.0.2/OPatch/opatch
bash-3.00$


- verify opatch utility


bash-3.00$ opatch lsinventory
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /db/data/11.2.0.2
Central Inventory : /export/home/appltest/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /db/data/11.2.0.2/oui
Log file location : /db/data/11.2.0.2/cfgtoollogs/opatch/opatch2011-03-12_13-14-17PM.log

Patch history file: /db/data/11.2.0.2/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /db/data/11.2.0.2/cfgtoollogs/opatch/lsinv/lsinventory2011-03-12_13-14-17PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 11g                                                  11.2.0.2.0
Oracle Database 11g Examples                                         11.2.0.2.0
There are 2 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.
bash-3.00$



- Apply patches
1) 10149223
2) 10229719


Run Pre-upgrade tool:



SQL> spool 11202_upgrade.txt
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 03-12-2011 14:12:10
Script Version: 11.2.0.2.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          PREPROD
--> version:       10.2.0.4.0
--> compatible:    10.2.0
--> blocksize:     8192
--> platform:      Solaris[tm] OE (64-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 8580 MB
--> APPS_UNDOTS1 tablespace is adequate for the upgrade.
.... minimum required size: 3219 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: 602 MB
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
.... minimum required size: 72424 MB
--> CTXD tablespace is adequate for the upgrade.
.... minimum required size: 590 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 15 MB
--> OLAP tablespace is adequate for the upgrade.
.... minimum required size: 31 MB
--> ORASOL_TS_DATA tablespace is adequate for the upgrade.
.... minimum required size: 8812 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 2073 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
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]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> 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

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

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


Update changes accordingly

- comment parameters cdump, bdump and udump
- Add diag_dest parameter.

#11gR2 database parmaeter for diagnots
diagnostic_dest = /db/data/diag

Compile all Invalid database objects:

- compile Invalid objects usinh utlrp.sql script from $10GORACLE_HOME/rdbms/admin


SQL> @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2011-03-12 14:22:29

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);



- Verify Invalid components with script:



select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from
dba_registry order by comp_name;

COMP_NAME                                STATUS      VERSION
---------------------------------------- ----------- ----------
JServer JAVA Virtual Machine             VALID       10.2.0.4.0
OLAP Analytic Workspace                  VALID       10.2.0.4.0
OLAP Catalog                             VALID       10.2.0.4.0
Oracle Data Mining                       VALID       10.2.0.4.0
Oracle Database Catalog Views            VALID       10.2.0.4.0
Oracle Database Java Packages            VALID       10.2.0.4.0
Oracle Database Packages and Types       VALID       10.2.0.4.0
Oracle OLAP API                          VALID       10.2.0.4.0
Oracle Real Application Clusters         INVALID     10.2.0.4.0
Oracle Text                              VALID       10.2.0.4.0
Oracle XDK                               VALID       10.2.0.4.0
Oracle XML Database                      VALID       10.2.0.4.0
Oracle interMedia                        VALID       10.2.0.4.0
Spatial                                  VALID       10.2.0.4.0

14 rows selected.

SQL>



- verify Invalid objects:


select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from
dba_objects where status='INVALID' order by owner,object_type;


- Verify Timezone version:


SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         4

SQL>



- Check deprecated connect role:



SQL> SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');


Purge Recyclebin:


SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>


- start listener from 11g Home

Run DBUA from 11.2.0.2 Home with out setting any env variables:

- Make sure display variable is set properly
- run dbua


























Post upgrade steps:

Install Oracle Data Mining and OLAP


SQL> select comp_id from dba_registry where comp_id='ODM' or comp_id='AMD';

COMP_ID
------------------------------
ODM
AMD



If the query does not return ODM, then you do not have Oracle Data Mining installed. (No action)



- Start the new database listener (conditional)

Already started listener from New Oracle_Home

Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) 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> @/db_scripts_11202/adgrants.sql APPS
Connected.
--------------------------------------------------
--- adgrants.sql started at 2011-03-13 08:14:49 ---
Creating PL/SQL profiler objects.
---------------------------------------------------
--- profload.sql started at 2011-03-13 08:14:49 ---

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 2011-03-13 08:14:50 ---
------------------------------------------------
--- proftab.sql started at 2011-03-13 08:14:50 ---

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 2011-03-13 08:14:51 ---

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.00$


- Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. If you are upgrading to R12, use the R12 version of the file. Use SQL*Plus to connect to the database as APPS and run the script using the following command:


bash-3.00$ sqlplus apps/***** @adctxprv.sql ***** CTXSYS

SQL*Plus: Release 11.2.0.2.0 Production on Sun Mar 13 08:21:32 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit 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.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.00$


- Set CTXSYS parameter


Use SQL*Plus to connect to the database as SYSDBA and run the following command:

bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Mar 13 08:23:38 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit 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.


- Implement and Run autoconfig


-> Here copy appsutil dir from 10g home to 11g home
-> copy appsutil.zip from admin node to 11.2.0 Home
-> unzip it new 11.2.0 home with unzip -o option

Creating new context file:


bash-3.00$ pwd
/db/data/11.2.0.2/appsutil/bin
bash-3.00$ perl adbldxml.pl tier=db appsuser=APPS appspasswd=********

Starting context file generation for db tier..
Using JVM from /db/data/11.2.0.2/jdk/bin/java to execute java programs..


Provide all information requested by the script hostname, port and SID.

This will create context file.

Run adconfig.sh:


bash-3.00$ adconfig.sh
Enter the full path to the Context file: /db/data/11.2.0.2/appsutil/PREPROD_nodedb.xml
Enter the APPS user password:
The log file for this session is located at: /db/data/11.2.0.2/appsutil/log/PREPROD_testdb/03130846/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
..............................
..........................................
        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db112
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.
bash-3.00$



- Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. If you are upgrading to R12, use the R12 version of the file. 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 restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:


bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Mar 13 08:52:19 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup restrict;
ORACLE instance started.
Total System Global Area 4277059584 bytes
Fixed Size                  2165168 bytes
Variable Size            1224742480 bytes
Database Buffers         3019898880 bytes
Redo Buffers               30253056 bytes
Database mounted.
Database opened.
SQL> @adstats.sql
Connected.
--------------------------------------------------
--- adstats.sql started at 2011-03-13 08:56:35 ---
Checking for the DB version and collecting statistics ...
PL/SQL procedure successfully completed.
------------------------------------------------
--- adstats.sql ended at 2011-03-13 09:22:56 ---
Commit complete.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- Now shutdown and startup normal

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4277059584 bytes
Fixed Size                  2165168 bytes
Variable Size            1241519696 bytes
Database Buffers         3003121664 bytes
Redo Buffers               30253056 bytes
Database mounted.
Database opened.
SQL>


Apply Application patch for HRMS -- Patch 7721754 - ORA-29532 OCCURRED WHEN UPLOADING THE IMAGE FILE

1 comment:

Blogger said...

Searching for the Ultimate Dating Site? Create an account and find your perfect date.