DBA World: 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

Patch 7303030 - Oracle E-Business Suite 12.1.1
Patch 9062910 - 11g Release 2 interoperability patch for Release 12.1
Patch 8919489 - 12.1 TXK Delta 3 patch
Patch 9868229 - CST_LAYER_ACTUAL_COST_DTLS_V Becomes Invalid After 11.2.0.2 Upgrade
Patch 10163753 - BIV_B_AGE_H_SUM_MV Fails During Index Creation
Patch 11071569 - ADBLDXML Fails On Windows Server 2008 R2 64 Bit

· 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

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]

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

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]

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

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]

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]

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

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]

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]

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

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]

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]

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

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]

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

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]

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]

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

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]

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]

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

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]

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]

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

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 '\';

_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

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

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

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

Search Blog Post

Sunday, December 15, 2013

UPGRADE 11.1.0.7 DATABASE TO 11.2.0.3 DATABASE WITH R12.1.1

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

Before the Database Installation:

Database Upgrade:

4 comments: