Upgrade 10.2.0.3 to 11.1.0.6 (EBS 12.1.2)

Today I need to blog out my experiences during the Database Upgrade to 11.1.0.6 from 10.2.0.3 (12.1.2 EBSuite)

Server Name: prod.chainsys.com

(Manually Upgraded from 12.0.6 to 12.1.1 then to 12.1.2)

For Upgrading the oracle Applications from 12.0.x to 12.1.1, please refer my previous blog

http://balajiabhi.blogspot.com/2009/07/upgrade-oracle-e-business-suite-1205-to.html

WEB_OH=10.1.3.4.0

TOOLS_ORACLE_HOME=10.1.2.3.0

Previous Locations:

RDBMS_ORACLE_HOME = /HDD/GENIUS/db/tech_st/10.2.0

Upgrade Location:

RDBMS_ORACLE_HOME=/HDD/product/11.1.0/db_2

Steps for Upgrade:

1. Check for Invalid Objects
2. Compile them using adadmin before proceed to Upgrade 11.1.0.6

Applications Part:

1. Apply 64400501 using Opatch tool. For applying opatch, please refer Readme.txt of the patch.

(Ensure that you have included OPatch directory on the path)

1. Move to DB Oracle Home (10.2.0.3 Home)

Populate DB environment file.

De Register current tns configurations from DB Oracle Home

From appsutil/bin

Run:

[oracle@prod 10.2.0]$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl appspass=apps contextfile=$CONTEXT_FILE -removeserver

################################################################

Generate Tns Names

################################################################

Classpath : :/HDD/GENIUS/db/tech_st/10.2.0/jdbc/lib/ojdbc14.jar:/HDD/GENIUS/db/tech_st/10.2.0/appsutil/java/xmlparserv2.jar:/HDD/GENIUS/db/tech_st/10.2.0/appsutil/java:/HDD/GENIUS/db/tech_st/10.2.0/jlib/netcfg.jar:/HDD/GENIUS/db/tech_st/10.2.0/jlib/ldapjclnt10.jar

Loading ORACLE_HOME environment from /HDD/GENIUS/db/tech_st/10.2.0

Logfile: /HDD/GENIUS/db/tech_st/10.2.0/appsutil/log/GENIUS_prod/05311427/NetServiceHandler.log

adgentns.pl exiting with status 0

ERRORCODE = 0 ERRORCODE_END

Please Note: Don’t run Autoconfig on Application Tier until it is instructed. If you want to change the SID, Port and hostname, do that now and update the application tier Context file with relevant values:

The new 11.1.0 Oracle home uses its own database listener for the database instance, replacing the current database listener. Use the Context Editor to update the following variables in the Applications context file on each application tier server node to reflect the 11.1.0 configuration:

Variable Name	Value
s_dbhost	New database hostname
s_dbdomain	New database domain name
s_db_serv_sid	New database SID
s_dbport	New database listener port
s_apps_jdbc_connect_descriptor	NULL

If you don’t change anything, update the s_apps_jdbc_connect_descriptor alone to NULL.

Step:2

Keep ready 11.1.0.6 Database installer and unzip to a temporary location.

Run the ./runInstaller from the unzipped location

Screen 1:

Select Advanced Option

Screen 2:

Select Enterprise Edition

Screen 3:

Specify, Oracle BASE, Oracle Home Name, and location

Screen 4:

Don’t select any database to be upgraded if shown.

Screen 5:

Select Configuration: Install Software only

Continue as pressing Next.

Once completed,

Step 3:

Create a profile file consisting of like this for 11g Database Home

export ORACLE_BASE=/HDD/product/11.1.0

export ORACLE_HOME=/HDD/product/11.1.0/db_2

export ORACLE_SID=GENIUS

export PATH=$PATH:$ORACLE_HOME/bin

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORACLE_TERM=xterm

export ORACLE_OWNER=oracle

export TNS_ADMIN=$ORACLE_HOME/network/admin

export CLASSPATH=$ORACLE_HOME/jdbc/lib/classes12.zip

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32

export LDPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32

Populate the environment file to setup the environment for the 11g Database Home

Step:4

Run $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.

Add this line to profile as you created in the previous step.

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Step 5: (Run the Pre-Upgrade Information Tool)

Copy the utlu111i.sql from 11g ORACLE_HOME/rdbms/admin to 10g Oracle HOME

Move to 10g Oracle Home

Populate Application Database Environment file and run

Please take the backup of the pfile (initSID.ora from dbs/ directory)

$sqlplus “/as sysdba” @utlu111i.sql

Oracle Database 11.1 Pre-Upgrade Information Tool 05-31-2010 15:36:58

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

Database:

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

--> name: GENIUS

--> version: 10.2.0.3.0

--> compatible: 10.2.0

--> blocksize: 8192

--> platform: Linux IA (32-bit)

--> timezone file: V4

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

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 10731 MB

--> CTXD tablespace is adequate for the upgrade.

.... minimum required size: 15 MB

--> ODM tablespace is adequate for the upgrade.

.... minimum required size: 10 MB

--> APPS_UNDOTS1 tablespace is adequate for the upgrade.

.... minimum required size: 57 MB

--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.

.... minimum required size: 5779 MB

--> APPS_TS_QUEUES tablespace is adequate for the upgrade.

.... minimum required size: 123 MB

--> OLAP tablespace is adequate for the upgrade.

.... minimum required size: 16 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 434 MB

--> USERS tablespace is adequate for the upgrade.

.... minimum required size: 353 MB

.... AUTOEXTEND additional space required: 2 MB

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

Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]

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

-- No update parameter changes are required.

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

Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]

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

-- No renamed parameters found. No changes are required.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or

spfile]

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

--> "background_dump_dest" replaced by "diagnostic_dest"

--> "user_dump_dest" replaced by "diagnostic_dest"

--> "core_dump_dest" replaced by "diagnostic_dest"

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

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 contains stale optimizer statistics.

.... Refer to the 11g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

.... OLAPSYS

.... CTXSYS

.... XDB

.... ORDSYS

.... MDSYS

WARNING: --> Database contains INVALID objects prior to upgrade.

.... USER SYSTEM has 1 INVALID objects.

.... USER APPS has 1 INVALID objects.

WARNING: --> Database contains schemas with objects dependent on network

packages.

.... Refer to the 11g Upgrade Guide for instructions to configure Network

ACLs.

.... USER APPS has dependent objects.

PL/SQL procedure successfully completed.

Step 5: (Timezone Update)

Applying "version 4" Time Zone Files on an Oracle Database

Definitely the Version of the v$timezone_file will return version lower than version 4 if you use 10g

of any version.

For 10.2.0.3 there is a available patch, 5632264 from metalink and apply the same as opatch on

10.2.0.3 oracle HOME.

Please Note: If you are using a Version like 10.2.0.4, then you may not get the correct patch from the metalink. So, you can use this workaround.

I Used patch No. 5632264 and copied the 2 .dat files from the unzipped Patch then pasted in the 10g Oracle HOME/oracore/zoninfo. (Take backup of the file prior to copy) Most important (Dont neglect).

(By doing this, your orainventory does not know this. so please make a note of it yourself)

Eventhough this copy does not require any downtime, you require to restart the DB to change the version: After restart, you check with Timezone with this sql:

SQL> select * from v$timezone_file;

FILENAME VERSION
------------ ----------
timezlrg.dat 4




Step 6:


Gather Schema Statistics




From 10g Oracle Home:

Use Note ID: 560336.1 to get a query to identify stale statistics.

If any row identified by this query (please use the query provided by the script itself) else,




$ sqlplus "/as sysdba"




SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Step: 7

Disable Oracle database vault:

Please use Note ID: 453903.1 for doing this.

Step: 8:

Configuring Network ACLs (Network Access Control Lists)

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XMLDB. If you have applications that use one of these packages, you must install OracleXML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases. Actions are discussed in Post Upgrade tasks as the DBMS_NETWORK_ACL_ADMIN package is introduced after upgrading the database and not available in prior releases.

Script is this. It will give you a script called analyze.sql.

Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off

Run this analyze.sql as sys user on 10g Oracle HOME

This may take several hours based on your Applications environment.

Once finished with this script:

Step: 9:

Stop the listener and other executables like emctl and also isqlplusctl

1. $lsnrctl stop SID

Step: 10:

Ensure no media for media recovery (using select * from v$recover_file)

Step: 11:

Resolve any unresolved pending transactions using (select * from dba_2pc_pending)

if this query returns any rows, you should do,

SQL> SELECT local_tran_id

FROM dba_2pc_pending;

SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');

SQL> COMMIT;

Step: 12:

Ensure the users SYS and SYSTEM have system tablespace as their default. (select username,

default_tablespace from dba_users where username in ('SYS', 'SYSTEM'))

if anything is missed out, try to fix as (alter user username default_tablespace system.)

Step: 13:

Ensure that auditing is enabled in system tablespace by (select tablespace_name from dba_tabels where table_name='AUD$';)

Shutdown the instance;

Backup the database (COLD BACKUP) I used RMAN.

Make a backup of init.ora file (pfile) Comment out obsolete parameters and change the deprecated parameters

(List available with Note ID: 454442.1)

Move the pfile to 11g oracle Home dbs/ directory

Step: 14

Apply the additional Patches on 11g Oracle Home:

For all platforms apply OPatch 11.1.0.0.0. This is the same as OPatch 11.1.0.6.2 required by patch 6778860.

Apply the following patches:

For all UNIX/Linux platforms, apply RDBMS patches:

• 6266400
• 6598432
• 6778860
• 6815733
• 6991626
• 7253531
• 7272286
• 7377378

When Applying the patch 7377378, it includes lot of other bug fixed. Please use

$opatch napply

to apply this patch.

Step: 15:

Move to $ADMIN_SCRIPTS_HOME from APPL_TOP and stop the application services.

Step: 16:

Startup the sqlplus from 11g ORACLE_HOME with new initfile from dbs/ directory as per the step described

above. (After removing the deprecated values from the 10g Oracle Home (dbs/initSID.ora)

$sqlplus “/as sysdba”

SQL> Startup upgrade;

Once the database is opened, from rdbms/admin run

Sql> @catupgrd.sql

Once completed,

Run

Sql> @utlrp.sql

Once completed,

Please check as

Sql> select comp_name,comp_id from dba_registry

COMP_NAME

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

VERSION STATUS

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

Oracle Data Mining

11.1.0.6.0 VALID

Oracle XML Database

11.1.0.6.0 VALID

COMP_NAME

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

VERSION STATUS

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

OLAP Catalog

11.1.0.6.0 VALID

Oracle Text

11.1.0.6.0 VALID

COMP_NAME

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

VERSION STATUS

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

Oracle Multimedia

11.1.0.6.0 INVALID

Oracle Database Catalog Views

11.1.0.6.0 VALID

Oracle Database Packages and Types

11.1.0.6.0 VALID

COMP_NAME

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

VERSION STATUS

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

JServer JAVA Virtual Machine

11.1.0.6.0 VALID

Oracle Database Java Packages

11.1.0.6.0 VALID

Oracle XDK

11.1.0.6.0 VALID

COMP_NAME

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

VERSION STATUS

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

OLAP Analytic Workspace

11.1.0.6.0 VALID

Oracle OLAP API

11.1.0.6.0 VALID

12 rows selected.

SQL>

Step: 17:

From $ORACLE_HOME/bin

Run

./netca

Create a listener for 11gOracle Home as it does not have any listener configured.

It will automatically start the listener.

And also, create a tnsnames.ora using netca for the instance.

Applications Part:

Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node.

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

$ sqlplus "/ as sysdba" @adgrants.sql APPS

Once finished:

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:

$ sqlplus apps/[APPS password] @adctxprv.sql [SYSTEM password] CTXSYS

$ sqlplus apps/apps @adctxprv.sql MANAGER CTXSYS

Once finished:

Copy the $FND_TOP/patch/115/sql/wfaqupfix.sql script to the database node and run as using the following

command:

$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql\

  [APPLSYS user] [APPS user]

$ sqlplus apps/apps @wfaqupfix.sql APPLSYS APPS

Once finished:

Go to $AD_TOP/bin

Run 

$perl admkappsutil.pl

[oracle@prod appl]$ . APPSGENIUS_prod.env

[oracle@prod appl]$ cd $AD_TOP/bin

[oracle@prod bin]$ perl admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /HDD/GENIUS/inst/apps/GENIUS_prod/admin/log/MakeAppsUtil_06031008.log

output located at /HDD/GENIUS/inst/apps/GENIUS_prod/admin/out/appsutil.zip

MakeAppsUtil completed successfully.

Once completed:

Copy the appsutil.zip to 11g ORACLE_HOME

Unzip the appsutil.zip using

$ unzip –o appsutil.zip

It will inflate the contents into appsutil directory

Run adbldxml.pl 

oracle@prod bin]$ perl adbldxml.pl jtop=/HDD/product/11.1.0/db_2/jdk/jre

Starting context file generation for db tier..

Using JVM from /HDD/product/11.1.0/db_2/jdk/jre/bin/java to execute java

programs..

APPS Password: apps

The log file for this adbldxml session is located at:

/HDD/product/11.1.0/db_2/appsutil/log/adbldxml_06031014.log

AC-00013: Error: File: /HDD/product/11.1.0/db_2/network/admin/listener.ora is

not a valid file as database SID could not be found for connect string: GENIUS

Could not Connect to the Database with the above parameters, Please answer the Questions

below

Enter Hostname of Database server: prod.chainsys.com

Enter Port of Database server: 1521

Enter SID of Database server: GENIUS

The context file has been created at:

/HDD/product/11.1.0/db_2/appsutil/GENIUS_prod.xml

[oracle@prod bin]$

This will create SID_hostname.env in the 11g ORACLE_HOME and will create the context file in the appsutil directory.

PREPARE TO RUN AUTOCONFIG on the New Oracle HOME:

From appsutil/bin

Run:

oracle@prod bin$./adconfig.sh contextfile=/HDD/product/11.1.0/db_2/appsutil/GENIUS_prod.xml

It will finish the autoconfig on DB Tier.

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 restart the database

in restricted mode, run adstats.sql, and restart the database in normal mode:

$ sqlplus "/ as sysdba"

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql 

SQL> shutdown normal;

Populate the newly created DB_HOME environment file from 11g ORACLEHOME

Startup the listener as

$lsnrctl start [SID]



Once done,



Populate Application Environment file and then

Using adadmin, recreate grants and synonyms.

Run Autoconfig on Application Server

Navigate to $ADMIN_SCRIPTS_HOME

Run 

$adautocfg.sh

Once finished, your database and application node are using the new 11g Database Home.

Startup the application services

To verify the upgrade:

Login to Applications from front end, as a user SYSADMIN

Navigate to OAM, then find out the database version.

Cheers!!!!