UPGRADE 11.1.0.7 DATABASE TO 11.2.0.3 DATABASE WITH R12.1.1

This page addresses the challenges all DBA’s often have to address when upgrading an older version of EBS DB. Please use at your own risk and after thorough testing in your development environment.

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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options  Connecting to SYSTEMConnected. 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]

Upgrade to JDK 7.0 on EBS R12 Application Tier

Upgrade the JDK in 10.1.3 Oracle Home from JDK 1.5/1.6 to JDK 7.0

Step 1: Apply following Prerequisite Patches

  • 14635756 – QRMP: FIX NEEDED FOR 10.1.2 JDK 7 UPGRADE

o    Prerequisite (R12.TXK.B) 8919489 – Already applied

Step 2: Download JDK

Download the 32-bit JDK, not the Java Runtime Environment (JRE).

Do not choose the 64-bit version, which is not supported.

http://www.oracle.com/technetwork/java/javase/downloads/index.html

Step 3: Shutdown Applications

Stop all application tier services before installing the latest update of JDK 7.0 into the [IAS_ORACLE_HOME]/appsutil/jdk location

o    $ cd $INST_TOP/admin/scripts/

o    $ adstpall.sh

Step 3 : Replace JDK Home Used With Oracle E-Business 12

Oracle E-Business Suite application tier uses the JDK from a fixed installation location, [IAS_ORACLE_HOME]/appsutil/jdk.

This can be replaced either by copying the installation from a separate default location or working directory, or by replacing the old JDK installation with the new one on each application tier node.

Perform this action by entering the applicable commands below.

  • $ cd [IAS_ORACLE_HOME]/appsutil
  • $ mv jdk jdk_old
/** Install the latest JDK 7.0 Update here. In the example, the directory name is jdk1.7.0_5 .**/
  • $ mv jdk1.7.0_5 jdk

Step 4 : Rename Existing JRE Directories

The existing JRE directory is no longer needed. Perform this action by entering the applicable commands below.

  • $ mv $IAS_ORACLE_HOME/jre/1.4.2 $IAS_ORACLE_HOME/jre/1.4.2_old

Step 5 : Install Albany (Display) Font

Installation of the font files listed below is required to support Oracle XML Publisher and enable the Oracle UIX Dynamic Image technology to successfully generate images. These font files must be available on each application tier node. They are located under $FND_TOP/resource.

The font files are:

  • ALBANYWT.ttf – Single-byte characters only
  • ALBANWTJ.ttf – Unicode font with Japanese Han Ideographs
  • ALBANWTK.ttf – Unicode font with Korean Han Ideographs
  • ALBANWTS.ttf – Unicode font with Simplified Chinese Han Ideographs
  • ALBANWTT.ttf – Unicode font with Traditional Chinese Han Ideographs

o    $ cp $FND_TOP/resource/font_file_name.ttf $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts

Upgrade the JDK in 10.1.2 Oracle Home from JDK 1.5/1.6 to JDK 7.0

Step 1 : Apply latest Forms & Reports Patches (437878.1)

  • Stop all services

o    Adstpall.sh

  • Apply Prerequisite Technology Stack Patches

o    Patch 8919489 – R12.TXK.B.Delta.3

  • Back Up OracleAS 10.1.2 ORACLE_HOME and Inventory folder
  • Latest certified OracleAS patchset

o    Patch 5983622– Oracle Application Server 10g Patchset

  • Install Forms Bundle Patch

o    Patch 14825718– Forms Bundle Patch

  • Install Additional Patches

Note: Must select “iAS 10.1.2.3” from the release dropdown list before downloading the patch

o    Patch 17303472– Forms Overlay Patch

o    Patch 57121788– Forms Platform Specific Patch

o    Patch 8528194  – Forms Platform Specific Patch

o    Patch 8557019 – Forms C Patch

o    Report C Patches

  • 7016961
  • 5917053
  • 6371228
  • 10152652
  • 11780669
  • 12379038
  • 12887068
  • 12898423
  • 16778382

o    Patch 8339196 Report C Patch

o    Patch 14374587 Report Java Patch

o    Patch 7140392 Report Java Patch

o    Patch 8300196 Report Generic Patch

  • Download and apply 10.1.0.5 RSF fixes

o    Patch 3559326

o    Patch 5394728

o    Patch 5261796

o    Patch 5651245

o    Patch 4047619 

o    Patch 5604120 

o    Patch 16275529 

After applying patch 16275529 perform this task in 10.1.2 ORACLE_HOME.

  • $ cd $ORACLE_HOME/nls/data/old
  • $ perl cr9idata.pl
  • Apply patch 6400501 (Oracle Database 11g only)

Download patch 6400501 for Database 10.1.0.5, and apply it to the OracleAS 10.1.2 Oracle Home. This one-off is originally applied as part of the 11g database upgrade, which is rolled back by the OracleAS 10.1.2.3 patchset.

  • Apply Oracle E-Business Suite Interoperability Patch

o    Patch 14837539 – Oracle E-Business Suite Interoperability Patch

  • Rebuild Forms and Reports Executable

o   cd $ORACLE_HOME/forms/lib32

Note: if this directory does not exist:

o   cd $ORACLE_HOME/forms/lib 
$ make -f ins_forms.mk install

o   cd $ORACLE_HOME/reports/lib32

Note: if this directory does not exist:

o   cd $ORACLE_HOME/reports/lib 
$ make -f ins_reports.mk install

  • Regenerate JAR files

Run the AD Administration Utility to regenerate the JAR files:

  1. Run the AD Administration Utility (adadmin)
  2. Select Generate Applications Files
  3. Generate Product JAR Files
  • Start All Services

o    $ADMIN_SCRIPTS_HOME/adstrtal.sh

  • Run Forms compiler

o   $ORACLE_HOME/bin/frmcmp help=y

  • Run Report Builder

o   $ORACLE_HOME/bin/rwrun ?|grep Release

Step 2 : Download and Apply Patch 12848228 and 5659594

Apply Patch 12848228 and 5659594 to 10.1.2.3 Oracle Home using Opatch

  • Patch 12848228 – APPS6:RWRUN HANGS DUE TO ISSUE IN JOBMANAGER. UPDATEJOBSTATUS (Already applied)
  • Patch 5659594 – R11:REG:RWBUILDER/RWRUN RAISING EXCEPTION WHILE RUNNING REPORT IN VIEW

Step 3 : Download Latest JDK 7.0 Update

Download the 32-bit JDK, not the Java Runtime Environment (JRE).

Do not choose the 64-bit version, which is not supported.

o    http://java.sun.com/javase/downloads/index.jsp

Step 4 : Replace JDK Home Used With Oracle E-Business Suite Release 12

The JDK home within the OracleAS 10.1.2 Forms and Reports ORACLE_HOME can be replaced by entering the applicable commands below.

o    $ cd [10.1.2_ORACLE_HOME]

o    $ mv jdk jdk_old

/* Now install the downloaded latest update of JDK 7.0 here. In this example the version is 7.0_2. Then */

o    $ mv jdk1.7.0_2 jdk

Step 5 : Rename The Following Directories

o    $mv [10.1.2_ORACLE_HOME]/jdk64 [10.1.2_ORACLE_HOME]/jdk64_old

o    $mv [10.1.2_ORACLE_HOME]/jre/1.4.2 [10.1.2_ORACLE_HOME]/jre/1.4.2_old

o    $mv [10.1.3_ORACLE_HOME]/jre/1.4.2 [10.1.3_ORACLE_HOME]/jre/1.4.2_old

Step 6 : Rebuild Forms and Reports Executable (For Unix/Linux Only)

Source the environment (under $INST_TOP/ora/10.1.2) to point to the 10.1.2 Oracle Home before relinking forms and reports executable.

o    cd $ORACLE_HOME/forms/lib32

/* Note: if this directory does not exist, use the following command: */ 

o    cd $ORACLE_HOME/forms/lib

$ make -f ins_forms.mk sharedlib install

o    cd $ORACLE_HOME/reports/lib32

/* Note: if this directory does not exist, use the following command: */ 

o    cd $ORACLE_HOME/reports/lib 
$ make -f ins_reports.mk install

Step 7 : Re-generate Oracle E-Business Suite Forms and Reports

Run ADAdmin and select the Forms and Reports regeneration.

Step 8 : Start All Apps

o    $ cd $INST_TOP/admin/scripts/

o   $ adstrtal.sh

 

Verify Configurations On All Application Tier Nodes

  • Check JDK version and CLASSPATH

Open the $APPL_TOP/admin/adovars.env file and ensure that the environment variables below are defined. Run the following commands to verify that the environment variables ADJVAPRG and AFJVAPRG point to the JDK 7.0 executable. The CLASSPATH and AF_CLASSPATH environment variables must contain the necessary JDK 7.0 libraries.

o    $ $ADJVAPRG -version

o    $ echo $CLASSPATH

o    $ $AFJVAPRG -version

o    $ echo $AF_CLASSPATH

  • Verify Oracle E-Business Suite concurrent programs

o    Log in to Oracle E-Business Suite and select the “System Administrator” responsibility.

Check the Concurrent –> Manager –> Administer form and ensure that all managers have the appropriate numbers for their actual and target. For more details on how to manage concurrent managers and concurrent programs, refer to Managing Concurrent Processing and Concurrent Programs, Chapter 2 of Oracle Applications System Administrator’s Guide – Maintenance.

Upgrade to latest JRE 7.0 on Database Tier Node

Step : 1 Download Latest JRE 7.0 Update

Download the 32-bit JRE only, not the Java SE Development Kit (JDK).

o   http://www.oracle.com/technetwork/java/javase/downloads/index.html

 

Step : 2 Replace JRE Home Used With Oracle E-Business Suite Release 12

The Oracle E-Business Suite database tier uses the JRE 7.0 from a fixed installation location, $ORACLE_HOME/appsutil/jre. This can be replaced by copying the install from a separate default location or working directory, or by installing the new version following the procedure below, which replaces the old JRE installation used by AutoConfig with a new one for each database tier node.

o    $ cd [ORACLE_HOME]/appsutil

o    $ mv jre jre_old

/** Install the latest JRE 7.0 Update here. In the example, the directory name is jre1.7.0_5 .**/

o    $ mv jre1.7.0_5 jre

 

Verify JRE Installation on Database Tier Node

  • cd [ORACLE_HOME]/appsutil/jre/bin
  • $ java –version

FRM-92050. Failed to connect to the Server: /forms/lservlet-1

After recent upgrade JDK upgrade on our EBS R12 environment, most of the users reported that they encounter below error while trying to launch forms thru EBS front end.

FRM-92050. Failed to connect to the Server: /forms/lservlet-1 

To fix the issue, Oracle recommends setting the Oracle EBS application to run through the trusted sites zones, with a medium security setting.

  1. Open IE
  2. Select Tools -> Internet Options -> Security tab -> Trusted Sites icon -> Sites button from the Internet Explorer Menu Bar.
  3. Set the ‘Security Level for this Zone’ to ‘Medium’ if it is not set already.In the ‘Add this Web site to the zone:’ field, enter the ‘hostname and domain’ of the environment you are running the Oracle E-Business Suite from; i.e. https://., then click the ‘Add’ button

or

  1. Alternatively, you can simply add the domain name preceded by ‘*’, so that all sites under that domain go through this zone. i.e. *.

 

Also, I would suggest unchecking IE to upgrade to latest version 11.

Common Issues After Upgrading from 11i to R12 – Part 2

Workflow

  1. Upgraded Workflow notifications generate errors:
    Error Name = WF_ERROR
    Error Message = [WF_ERROR] ERROR_MESSAGE=3835: Error ‘-20002 – ORA-20002: 2018: Unable to generate the notification XML.
    Caused by: 2020: Error when getting notification content.
    Caused by: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275Solution: Refer to MOS Doc ID 743148.1 and apply the provided solution which involves running scripts from patch 6234198.
  2. Many notifications with subject “Local Event ERROR : oracle.apps.ar.applications.CashApp.apply” are generated when applying AR receipts (12.0 and 12.1 only).Solution: If you don’t need the notification subscription then you can disable it by following the instructions in MOS Doc ID 973074.1.
    If you need it then you must apply patch 8903995.
  3. Unable to view Workflow Status Diagram.Solution: Set the profile option “Server Timezone” to the appropriate value at the site level.

My First Post!

Hello,

Being an active member of Oracle user since 20-Sept-1997, I decided to extend my contributions beyond just answering questions.

www.paeconsuting.wordpress.com and thepaeconsultingOracleBlog.com,  I hope to provide useful articles to help others get things done.  I too use same as a knowledge base to keep track and use when faced with a challenge on various projects.  They are essentially my chit sheet.

My sincere regards,

–phil

Philip Ejisimekwu

User Logins Statistics

If you are looking for a simple way to find out how many users logged into the EBS system per day, you may use the following query:

select to_char(start_time,'RRRR-MM-DD Day') login_day, terminal_id,
       count(0) login_count, count(distinct user_id) user_count
from fnd_logins
where start_time > trunc(sysdate-90)
group by to_char(start_time,'RRRR-MM-DD Day'), terminal_id
order by 1,2;

If you want a summary of logins per user then use the following query:

select to_char(start_time,'RRRR-MM-DD Day') login_day, terminal_id,
       u.user_name, u.description, count(0) login_count
from fnd_logins l, fnd_user u
where l.user_id = u.user_id
  and start_time > trunc(sysdate-90)
group by to_char(start_time,'RRRR-MM-DD Day'), terminal_id, 
         u.user_name, u.description
order by 1,2,3;

Note that the terminal_id “Concurrent” refers to requests submitted by users.
You may exclude it if you wish.

Query Profile Option Values at All Levels

If you want to know the value of a profile option at more than one level (user, resp, appl, etc.) then you can use the below query.
Note that it will show you the profile options that have not been set, too!

SELECT user_profile_option_name, name, level_set, context, 
       value, last_update_date, last_updated_by
FROM (
SELECT po.user_profile_option_name,
       po.profile_option_name name,
       pov.level_id,
       decode(pov.level_id,
         10001, 'Site',
         10002, 'Application',
         10003, 'Responsibility',
         10004, 'User',
         10005, 'Server',
         10006, 'Organization',
         10007, 'ServResp',
         'Undefined') LEVEL_SET,
       decode(to_char(pov.level_id),
         '10001', '',
         '10002', app.application_short_name,
         '10003', rsp.responsibility_key,
         '10004', usr.user_name,
         '10005', svr.node_name,
         '10006', org.name,
         '10007', (SELECT n.node_name from fnd_nodes n
                   WHERE n.node_id=level_value2) ||'/'||
                   (decode(pov.level_value, -1,'Default',
                    (SELECT responsibility_key
                     FROM fnd_responsibility
                     WHERE responsibility_id = level_value))),
         pov.level_id) CONTEXT,
       pov.profile_option_value VALUE,
       pov.last_update_date,
       usrlst.user_name last_updated_by
FROM fnd_profile_options_vl po,
     fnd_profile_option_values pov,
     fnd_user usr,
     fnd_application app,
     fnd_responsibility_vl rsp,
     fnd_user usrlst,
     fnd_nodes svr,
     hr_operating_units org
WHERE usrlst.user_id = pov.last_updated_by 
 AND pov.application_id = po.application_id
 AND pov.profile_option_id = po.profile_option_id
 AND usr.user_id(+) = pov.level_value
 AND rsp.application_id(+) = pov.level_value_application_id
 AND rsp.responsibility_id(+) = pov.level_value
 AND app.application_id(+) = pov.level_value
 AND svr.node_id(+) = pov.level_value
 AND org.organization_id(+) = pov.level_value

UNION ALL

SELECT user_profile_option_name, profile_option_name name,
       null level_id,
       'Not Set' LEVEL_SET,
       null CONTEXT,
       null VALUE,
       null last_update_date,
       null last_updated_by
FROM fnd_profile_options_vl p
WHERE NOT EXISTS (SELECT 'x' FROM fnd_profile_option_values ov
                  WHERE p.profile_option_id = ov.profile_option_id)
)
WHERE UPPER(user_profile_option_name) LIKE UPPER('FND%Debug%')
ORDER BY name, level_id, value;

Find Orphaned Records in PO_ACTION_HISTORY Table

The following query helps identifying orphaned records in PO_ACTION_HISTORY table that have no reference to the respective purchasing tables.

This is usually caused by a bug during purging/deleting a purchasing document.
There is a known bug in EBS R12.1 that is fixed by patch 21618132. The details are in the document referenced at the bottom of this post.

SELECT *
FROM po_action_history pah
WHERE (NOT EXISTS (SELECT 1 FROM po_requisition_headers_all
                   WHERE requisition_header_id = pah.object_id)
       AND pah.object_type_code = 'REQUISITION')
 OR (NOT EXISTS (SELECT 1 FROM po_headers_all
                 WHERE po_header_id = pah.object_id)
     AND pah.object_type_code = 'PO')
 OR (NOT EXISTS (SELECT 1 FROM po_releases_all r
                 WHERE r.po_release_id = pah.object_id)
     AND pah.object_type_code = 'RELEASE')
ORDER BY object_type_code;

To correct this anomaly for existing records, you should log an SR with Oracle Support to obtain a data fix.

References:

Deleted Incomplete Requisition History In PO_ACTION_HISTORY Table (Doc ID 1920118.1)

Closing “Local Event Error” Notifications

You may have experienced notifications piling up in the SYSADMIN inbox with subject starting with “Local Event ERROR : oracle.apps.ar.applications.CashApp.apply”.

There is a number of blog posts and MOS documents to stop them from being generated but no one shows a way to respond to them from the backend.
The following PL/SQL block responds to all notifications with “Abort” option:

declare
 v_count pls_integer := 0;
 
 cursor wferror_cur is
 select *
 from wf_notifications
 where message_type = 'WFERROR'
   and message_name = 'DEFAULT_EVENT_ERROR'
   and status = 'OPEN'
   and subject like 'Local Event ERROR : oracle.apps.ar.applications.CashApp.apply%';
begin
 for wf_error_rec in wferror_cur loop
   
   wf_notification.setattrtext(nid => wf_error_rec.notification_id,
                               aname => 'RESULT',
                               avalue => 'ABORT');
   
   wf_notification.respond(nid => wf_error_rec.notification_id,
                           respond_comment => 'Aborted from backend',
                           responder => 'SYSADMIN');
   
   v_count := v_count + 1; 
 end loop;
 
 dbms_output.put_line('Aborted ' || v_count || ' notification(s).');
end;

 

Do not forget to commit the changes!

You can also use the wf_engine.abortprocess but this will work differently.
For example, the notifications will have the status “Canceled” instead of “Closed”.

 

References:

Receipt Workbench Error: Applying Receipt Generates Workflow Notification With Error – Local Event Error: oracle.apps.ar.applications.cashapp.apply (Doc ID 973074.1)

http://oracleappsdna.com/2015/02/how-to-close-approval-or-response-required-based-notifications-from-backend/