Search Blog Post

Sunday, December 15, 2013

UPGRADE 11.1.0.7 DATABASE TO 11.2.0.3 DATABASE WITH R12.1.1



The below steps provide you an overview of all the steps for upgrading an EBS database from 11.1.0.7 to 11.2.0.3

Section 1: Upgrading an R12 Database to Oracle Database 11g Release 2 (11.2.0)

Follow the instructions in this section if you plan to upgrade the database server and instance to the latest version of Oracle Database 11g Release 2 (11.2.0).

Before the Database Installation:

1.       Verify software versions
Oracle Database Enterprise Edition Version
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Oracle E-Business Suite
SQL> select release_name from fnd_product_groups;
RELEASE_NAME
--------------------------------------------------
12.1.1


If you are on E-Business Suite Release 12.1, apply

·         Applied all the missing patches to the apps tier.

AutoConfig Version

Implement AutoConfig or upgrade to the latest version. See Using AutoConfig to Manage System Configurations with Oracle E-Business Suite Release 12 on My Oracle Support for instructions. If you are on Oracle E-Business Suite Release 12.1, apply patches Patch 9738085 and Patch 9852070.
Patch 12686610 R12 And R121 Backport Request For Bug Fix 12336911
·         Applied all the patches to Apps Tier.

·         After installation of  Oracle 11.2.0.3 you must set the environment variables to the new home.
 [oratst@oracleupk oraInventory]$ export ORACLE_BASE=/data/db_top
[oratst@oracleupk oraInventory]$ export ORACLE_HOME=/data/db_top/db/tech_st/11.2.0
[oratst@oracleupk oraInventory]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
[oratst@oracleupk oraInventory]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[oratst@oracleupk oraInventory]$ export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0

Apply additional 11.2.0.3 RDBMS patches
Apply the following patches:
For all UNIX/Linux platforms:
[oratst@oracleupk 4247037]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oratst@oracleupk RDBMS_Patches]$ cd 4247037
[oratst@oracleupk 4247037]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-20-30AM.log

Applying interim patch '4247037' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...

Patching component oracle.sdo.locator, 11.2.0.3.0...
Patch 4247037 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-20-30AM.log

OPatch succeeded.

NOTE: Do not run any of the post install instructions as those will be done after the upgrade.
There is a Special post patch Instructions (mentioned below), execute ONLY after database upgrade part is done

[oratst@oracleupk 9858539]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-36-55AM.log

Applying interim patch '9858539' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 9858539 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-36-55AM.log

OPatch succeeded.

[oratst@oracleupk 12942119]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-41-41AM.log

Applying interim patch '12942119' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/data/db_top/db/tech_st/11.2.0')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 12942119 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-41-41AM.log

OPatch succeeded.

[oratst@oracleupk 12960302]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-42-53AM.log

Applying interim patch '12960302' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/data/db_top/db/tech_st/11.2.0')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.ctx, 11.2.0.3.0...

Patching component oracle.ctx.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 12960302 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-42-53AM.log

OPatch succeeded.

[oratst@oracleupk 12985184]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-44-00AM.log

Applying interim patch '12985184' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/data/db_top/db/tech_st/11.2.0')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.ctx.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 12985184 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-44-00AM.log

OPatch succeeded.

[oratst@oracleupk 13001379]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-53-28AM.log

Applying interim patch '13001379' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 13001379 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-53-28AM.log

OPatch succeeded.

[oratst@oracleupk 13004894]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-56-44AM.log

Applying interim patch '13004894' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/data/db_top/db/tech_st/11.2.0')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 13004894 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-56-44AM.log

OPatch succeeded.

[oratst@oracleupk 13258936]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-57-34AM.log

Applying interim patch '13258936' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/data/db_top/db/tech_st/11.2.0')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.ctx, 11.2.0.3.0...
Patch 13258936 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_08-57-34AM.log

OPatch succeeded.

[oratst@oracleupk 13366268]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /data/db_top/db/tech_st/11.2.0
Central Inventory : /data/db_top/db/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_09-00-31AM.log

Applying interim patch '13366268' to OH '/data/db_top/db/tech_st/11.2.0'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/data/db_top/db/tech_st/11.2.0')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 13366268 successfully applied
Log file location: /data/db_top/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2013-10-18_09-00-31AM.log

OPatch succeeded.

Database Upgrade:


·         Drop SYS.ENABLED$INDEXES (conditional)
If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and running the following command to drop it:
SQL> drop table sys.enabled$indexes;
In our env, table doesnot exists
SQL> desc SYS.ENABLED$INDEXES
ERROR:
ORA-04043: object SYS.ENABLED$INDEXES does not exist

Disable Database Vault (conditional)
If you have Database Vault installed, perform steps 1 to 6 of Part 2 of document 1091083.1 on My Oracle Support to disable Database Vault.
SQL> column PARAMETER format a40
SQL> column VALUE format a20
SQL> select * from v$option where parameter='Oracle Database Vault';
PARAMETER                                VALUE
---------------------------------------- --------------------
Oracle Database Vault                    FALSE

If the Value is ‘FALSE’ so no action is required.

Back up Enterprise Manager Database Control Data (conditional)
You may need to backup the EM data if you have installed.

·         Run the Pre-Upgrade Information Tool by executing the utlu112i.sql script:

·         Set the environment variables for the 11.1.0.7 ORACLE_HOME
[oratst@oracleupk admin]$ pwd
/data/db_top/db/tech_st/11.2.0/rdbms/admin
[oratst@oracleupk admin]$ ls utlu112i.sql
utlu112i.sql
[oratst@oracleupk admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Oct 18 09:44:07 2013

Copyright (c) 1982, 2008, Oracle.  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> spool upgrade.log
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 10-18-2013 09:44:40
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          TEST
--> version:       11.1.0.7.0
--> compatible:    11.1.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V10
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 11067 MB
--> CTXD tablespace is adequate for the upgrade.
.... minimum required size: 16 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> APPS_UNDOTS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
.... minimum required size: 5293 MB
--> APPS_TS_QUEUES tablespace is adequate for the upgrade.
.... minimum required size: 80 MB
WARNING: --> SYSAUX tablespace is not large enough for the upgrade.
.... currently allocated size: 446 MB
.... minimum required size: 520 MB
.... increase current size by: 74 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
.
**********************************************************************
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]
**********************************************************************
--> 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 14.
.... 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 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 44 INVALID objects.
WARNING: --> Your recycle bin contains 18 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.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APPS has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command
while connected AS SYSDBA:

    SELECT name,description from SYS.V$PARAMETER WHERE name
        LIKE '\_%' ESCAPE '\'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
The DMSYS schema exists in the database.  Prior to performing an
upgrade Oracle recommends that the DMSYS schema, and its associated
objects be removed from the database.

Refer to the Oracle Data Mining Administration Guide for the
instructions on how to perform this task.

**********************************************************************

**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APPS has dependent objects.

Issue 1:
WARNING: --> Database is using a timezone file older than version 14.
.... 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.

Solution to Issue 1: No need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.

Issue 2:
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 44 INVALID objects.

Solution to Issue 2:

Issue 3:
WARNING: --> Your recycle bin contains 18 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.

Solution to Issue 3: <run PURGE DBA_RECYCLEBIN>

Issue 4
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

Solution to Issue 4:
    EXECUTE dbms_stats.gather_dictionary_stats;

Issue 5:
Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command
while connected AS SYSDBA:

SQL>    SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

SQL> SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
_system_trig_enabled
are system triggers enabled

_sort_elimination_cost_ratio
cost ratio for sort eimination under first_rows mode

_b_tree_bitmap_plans
enable the use of bitmap plans for tables w. only B-tree indexes

_fast_full_scan_enabled
enable/disable index fast full scan

_index_join_enabled
enable the use of index joins

_sqlexec_progression_cost
sql execution progression monitoring cost threshold

_like_with_bind_as_equality
treat LIKE predicate with bind as an equality predicate

_optimizer_autostats_job
enable/disable auto stats collection job

_trace_files_public
Create publicly accessible trace files

Changes will need to be made in the init.ora or spfile

Solution to Issue 5:
 Comment out all hidden variables in init file


Shut down Applications server processes and database listener
·         Copy initSID.ora from old oracle_home to new oracle_home 11.2.0.3
 set at the initSID.ora
 compatible = '11.2.0'

[root@oracleupk dbs]# cp initTEST.ora /data/db_top/db/tech_st/11.2.0/dbs
[root@oracleupk dbs]# cd /data/db_top/db/tech_st/11.2.0/dbs
[root@oracleupk dbs]# ls -lrt
total 24
-rw-r--r-- 1 oratst dba   2851 May 15  2009 init.ora
-rw-r--r-- 1 root   root 19057 Oct 18 09:04 initTEST.ora
[root@oracleupk dbs]# chown oratst:dba initTEST.ora
·         set at the initSID.ora
 compatible = '11.2.0'
[oratst@oracleupk dbs]$ grep compatible initTEST.ora
compatible                      = 11.2.0

·         Set the environment variable to new home 11.2.0.3
·         [oratst@oracleupk oraInventory]$ export ORACLE_BASE=/data/db_top
·         [oratst@oracleupk oraInventory]$ export ORACLE_HOME=/data/db_top/db/tech_st/11.2.0
·         [oratst@oracleupk oraInventory]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
·         [oratst@oracleupk oraInventory]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
·         [oratst@oracleupk oraInventory]$ export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0


Create nls/data/9idata directory
·         run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory
[oratst@oracleupk old]$ pwd
/data/db_top/db/tech_st/11.2.0/nls/data/old

[oratst@oracleupk old]$ perl cr9idata.pl
Creating directory /data/db_top/db/tech_st/11.2.0/nls/data/9idata ...
Copying files to /data/db_top/db/tech_st/11.2.0/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /data/db_top/db/tech_st/11.2.0/nls/data/9idata!
[oratst@oracleupk old]$ export ORA_NLS10=$ORACLE_HOME/nls/data


Update the oratab entry
cat /etc/oratab
TEST:/data/db_top/db/tech_st/11.2.0:N

Note: After /etc/oratab is updated to have SID and Oracle Home (11.2)

Upgrade Steps:-
[oracle@oracleupk admin]$ sqlplus '/as sysdba'
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
[oracle@oracleupk admin]$ sqlplus /nolog
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
[oracle@oracleupk admin]$ unset ORA_TZFILE
[oracle@oracleupk admin]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 24  15:17:26 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size                                  2235208 bytes
Variable Size                           427820216 bytes
Database Buffers    624951296 bytes
Redo Buffers                            13930496 bytes
Database mounted.
Database opened.


Run the catupgrd.sql script, this script is doing the UPGRADE
·         Remove the obsolete initialization parameters from the parameter file before restarting.sp

SQL> spool upgrade.log
SQL>@$ORACLE_HOME/rdbms/upgrade/catupgrd.sql
SQL>spool off
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                                  2235208 bytes
Variable Size                           427820216 bytes
Database Buffers    624951296 bytes
Redo Buffers                            13930496 bytes
Database mounted.
Database opened.


SQL> @utlu112s.sql
Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed.


If  you get the ORA-01408 error(which is a known problem with Oracle E-Business Suite databases) just ignore it!

·         Check the version of your upgrades database
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production



·         Run catuppst.sql
Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.
SQL> @catuppst.sql
·         Check for the integrity of the upgraded database by running dbupgdiag.sql script
Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

·         There were 378 invalid object after running " dbupgdiag.sql" , so I ran $ORACLE_HOME/rdbms/admin/utlrp.sql to compile the invalid objects

·         After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.

Post Upgrade Steps:-
·         Run all the post patch installation steps
Post patch steps for 13258936
cd $ORACLE_HOME/ctx/admin/ctxpatch/
sqlplus / as sysdba
@ctxpatch11203.sql

Similarly, perform post patch installation for rest of the patches
·         Copy the old Oracle 11.1.0 $TNS_ADMIN directory to the new Oracle 11.2.0 $TNS_ADMIN and modify the oracle home & all the refrences of old entries to new oracle home.
[oratst@oracleupk admin]$ pwd
/data/db_top/db/tech_st/11.1.0/network/admin
[oratst@oracleupk admin]$ cd ../../../11.2.0/network/admin/
[oratst@oracleupk admin]$ cp -R /data/db_top/db/tech_st/11.1.0/network/admin/TEST_oracleupk .
[oratst@oracleupk admin]$ ls -lrt
total 12
-rw-r--r-- 1 oratst dba  205 May 11  2011 shrept.lst
drwxr-xr-x 2 oratst dba 4096 Oct 17 04:25 samples
drwxr-xr-x 2 oratst dba 4096 Dec 15 05:39 TEST_oracleupk
·         Copy the $ORACLE_11.1.0_HOME/SID_host.env to $ORACLE_11.2_HOME/SID_host.env, make the necessary changes to point the new 11.2.0 home and source the environment
In case you have the error:
$>sqlplus "/ as sysdba"
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
This problem is related to the $ORA_TZFILE environment variable in Oracle 11GR2 which is no longer needed.
Uncomment these lines from .env file which is places in $ORACLE_HOME
######################
# Timezone Specification file
#ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
#export ORA_TZFILE
######################
 
 
·         run adgrants.sql. Copy $APPL_TOP/admin/adgrants.sql file from the apps tier to the database tier. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command
 
$ sqlplus '/as sysdba' @adgrants.sql apps
 
·         Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:
 
[oratst@oracleupk ~]$ sqlplus apps/[apps passwd] @adctxprv.sql \ [system passwd] CTXSYS
 
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 15 06:16:09 2013
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.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.

·         Set CTXSYS parameter
 Use SQL*Plus to connect to the database as SYSDBA and run the following command:
 $ sqlplus "/ as sysdba"
 SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
·         Validate Workflow ruleset
On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:
[appltst@oracleupk sql]$ sqlplus apps/[apps passwd] @wfaqupfix.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Dec 15 07:31:59 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enter value for 1: APPLSYS
Enter value for 2: APPS

PL/SQL procedure successfully completed.


Commit complete.

·         Deregister the current database server (conditional)
If you plan to change the database port, host, SID, or database name parameter on the database server, you must also update AutoConfig on the database tier and deregister the current database server node.
Use SQL*Plus to connect to the database as APPS and run the following command:
$ sqlplus apps/[APPS password]
SQL> exec fnd_conc_clone.setup_clean;

·         Implement and run AutoConfig

Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.
Step 1: Create appsutil.zip
On the application tier (as the appltst user):
Log in to the APPL_TOP environment (source the environment file)
Create appsutil.zip file
perl <AD_TOP>/bin/admkappsutil.pl
[appltst@oracleupk appl]$ . ./APPSTEST_oracleupk.env
[appltst@oracleupk appl]$ cd $AD_TOP/bin
[appltst@oracleupk bin]$ ls admkappsutil.pl
admkappsutil.pl
[appltst@oracleupk bin]$ perl admkappsutil.pl
Starting the generation of appsutil.zip
Log file located at /data/appl_top/inst/apps/TEST_oracleupk/admin/log/MakeAppsUtil_12150737.log
output located at /data/appl_top/inst/apps/TEST_oracleupk/admin/out/appsutil.zip
MakeAppsUtil completed successfully.
This will create appsutil.zip in <INST_TOP>/admin/out
Step 2: Copy appsutil.zip to DB node and uncompress
On the database tier (as the ORACLE user):
Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>
cd <RDBMS ORACLE_HOME>
unzip -o appsutil.zip
Step 3: Generate the Database Context File
Execute the following command to create your Database Context File:
perl <RDBMS_ORACLE_HOME>/appsutil/bin/adbldxml.pl
[oratst@oracleupk bin]$ perl adbldxml.pl
Starting context file generation for db tier..
Using JVM from /data/db_top/db/tech_st/11.2.0/jdk/jre/bin/java to execute java programs..
APPS Password: apps
The log file for this adbldxml session is located at:
/data/db_top/db/tech_st/11.2.0/appsutil/log/adbldxml_12150755.log
Enter the value for Display Variable:oracleupk:0.0
The context file has been created at:
/data/db_top/db/tech_st/11.2.0/appsutil/TEST_oracleupk.xml


Step 4:  Run AutoConfig on the Database tier
Run AutoConfig on the Database tier by executing the below command:
On Unix:
<RDBMS_ORACLE_HOME>/appsutil/bin/adconfig.sh contextfile=<context_file>
[oratst@oracleupk bin]$ ./adconfig.sh
Enter the full path to the Context file: /data/db_top/db/tech_st/11.2.0/appsutil/TEST_oracleupk.xml
Enter the APPS user password:
The log file for this session is located at: /data/db_top/db/tech_st/11.2.0/appsutil/log/TEST_oracleupk/12150759/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
        Using ORACLE_HOME location : /data/db_top/db/tech_st/11.2.0
        Classpath                   : :/data/db_top/db/tech_st/11.2.0/jdbc/lib/ojdbc5.jar:/data/db_top/db/tech_st/11.2.0/appsutil/java/xmlparserv2.jar:/data/db_top/db/tech_st/11.2.0/appsutil/java:/data/db_top/db/tech_st/11.2.0/jlib/netcfg.jar:/data/db_top/db/tech_st/11.2.0/jlib/ldapjclnt11.jar

        Using Context file          : /data/db_top/db/tech_st/11.2.0/appsutil/TEST_oracleupk.xml

Context Value Management will now update the Context file

        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.


·         Shut down all processes, including the database and the listener, and restart them to load the new environment settings.

·         Apply post-upgrade ECX patch
If you are on E-Business Suite Release 12.0, apply ECX Patch 9922442.
If you are on E-Business Suite Release 12.1, apply Patch 9151516.
After applying Patch 9151516, you might get warnings in your adpatch.log file
WARNING: The following path(s), defined in /data/appl_top/apps/apps_st/appl/fnd/12.0.0/java/make/fndjar.dep as elements of the output file fndaol.jar, could not be found in any area:
  Note: - These warnings can be safely ignored.

·         Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. 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 run adstats.sql in restricted mode:

SQL> alter system enable restricted session;
System altered.
SQL> @adstats.sql
Connected.
--------------------------------------------------
--- adstats.sql started at 2013-12-15 08:31:08 ---
Checking for the DB version and collecting statistics ...
PL/SQL procedure successfully completed.
------------------------------------------------
--- adstats.sql ended at 2013-12-15 08:37:51 ---
Commit complete.
SQL> alter system disable restricted session;
System altered.
Note: Make sure that you have at least 1.5 GB of free default temporary tablespace.



·         Re-create grants and synonyms
Oracle Database 11g Release 2 (11.2) contains new functionality for grants and synonyms compared to previous database releases. As a result, you must re-create the grants and synonyms in the APPS schema. On the administration server node, as the owner of the Applications file system, run AD Administration and select the "Recreate grants and synonyms for APPS schema" task from the Maintain Applications Database Objects menu.
·         Compile Invalid Objects  
SQL>@utlrp.sql
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects  where STATUS=’INVALID’;
·         Restart Applications server processes
Restart all the Application tier server processes that you shut down previously. Remember that the Oracle Net listener for the database instance, as well as the database instance itself, need to be started in the 11.2 Oracle home. Users may return to the system.
·         Synchronize Workflow views
Log on to Oracle E-Business Suite with the "System Administrator" responsibility. Click Requests > Run > Single Request and the OK button. Enter the following parameters:
  • Request Name = Workflow Directory Services User/Role Validation
  • Batch Size = 10000
  • Fix dangling users = Yes
  • Add missing user/role assignments = Yes
  • Update WHO columns in WF tables = No
Click "OK" and "Submit".

REFERENCES
  1. Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]
  2. Oracle Database Upgrade Guide 11g Release 2 (11.2)
  3. Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
  4. Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]
  5. Database Initialization Parameters for Oracle E-Business Suite Release 12 [ID 396009.1]
  6. Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

No comments:

Post a Comment