Upgrades

EBIZ UPGRADE FROM 12.0.4 to 12.1.1

High Level Steps:

Step 1: Process of Database Upgrade from (10.2.0.2 to 10.2.0.4)

Step 2: (Upgrade 10gAS 10.1.3 to 10.1.3.4)

Step 3: Upgrading the Java JDK

Step 4: Forms server Upgrade from 10.1.2.0.1 to 10.1.2.3 is to be done.

Step 5:  upgrading to 12.1.1

Upgrade Oracle Application E Business Suite 12.0.X to 12.1.1

(Includes Upgrade of Database, Complete Middle Tier and Java Upgrade with Application TOP)

This includes Challenges and issues and fixes during the process of upgrade.

This document refers to Redhat Enterprise Linux 4 update 5., Database 10.2.0.2, Forms server version 10.1.2.0.1 and 10g AS

Web server 10.1.3

Take a backup of the production before doing (Strong Recommendation)

Metalink Document to refer: 752619.1 (But many challenges ahead if you follow the document)

The as-is server is being 12.0.5 and database is of 10.2.0.2, we upgraded the Database to 10.2.0.4 using Patchset 6810189

Step 1: Process of Database Upgrade:

1. Unzip the p6810189_10204_Linux-x86.zip to a temporary directory which is obtained from metalink.

2. Shut down all the application services, DB, listener, isqlplus and emctl dbconsole.

Please do remember, Take a backup of the database and inventory.

3. Go to unzipped directory and run ./runInstaller

 $./runInstaller

Once completed, startup the database using

$sqlplus / as sysdba

SQL> startup upgrade;

SQL> spool 'upgrade10204.log'

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

At the end the script will return the results of upgrade validity. Verify the validity of the database after upgrade.

SQL> shut immediate;

SQL> Startup;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

This will compile all the invalid objects that are added and not compiled during upgrade.

After co-mpilation:

Shutdown the instance and apply the additional RDBMS Patches using Opatch for E Business Suite upgrade.

Please be sure to have OPatch in the PATH

To Include OPatch in the path use

$export PATH=$PATH:$ORACLE_HOME/OPatch

Using Opatch Apply the following Patches

4247037

6600051

6870937

6991626

Step 2: (Upgrade 10gAS 10.1.3 to 10.1.3.4)

Important:

1.Take a backup of inventory before proceeding.

2. Source the environment file from $INST_TOP/ora/10.1.3/

    $. PROD_appsmachine.env

3. Verify echo $ORACLE_HOME as $ORACLE_APPS_BASE/apps/tech_st/10.1.3

    $ export ORACLE_HOME=/u01/oracle/PROD/apps/tech_st/10.1.3

4. Ensure that all the OC4J services are up and running.

1. Unzip and Apply Patch 7272722 obtained from metalink using ./runInstaller

If you are using local inventory please use

./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc

During the process, the OPMN services will be down and started up by the OUI.

When prompted please give the OC4J admin password as either 'secret' or 'opmn' (As I upgraded two instance, one (12.0.6) was successful with 'secret' and another (12.0.5) by 'opmn' or ' oafm' )

This will complete the 10gAS (10.1.3.0.1) to be upgraded with 10.1.3.4

Time taken for this procedure ~ 1 hour.

Shutdown the services, Run Autoconfig on the apps Tier.

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

As per the metalink document, dont upgrade Opatch version to 1.0.0.0.62, it failed two times for me during the other patches. I advise you to keep the current Opatch as opatch tool.

Be sure, the path of the Opatch is present in the PATH variable.

Apply the following patches:

(if you are using local inventory, then use

Patch_Directory$opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc)

1. 6702510 (The issue is that.. being a general platform Patch, Opatch will fail at the verification of platform)

use $export OPATCH_PLATFORM_ID=46,

then rerun the opatch apply.

2. Apply 6771776 (If you are getting error during opatch as Platform as 196 then use OPATCH_PLATFORM_ID = 46)

3. 7411481

4. 7139320

5. 7139339

Post Upgrade steps

Source Application environment file

Regenerate appsborg.zip and appsborg2.zip using adadmin

Navigation: adadmin -> Choose Option 1-> Regenerate Product JAR files -> Force Option: No

Run autoconfig in application tier system.

Verify the Upgrade using:

either:

use ./runInstaller and then select installed products, it will show you the components from WEB_OH that are all upgraded to 10.1.3.4

or:

use opatch lsinventory -detail

Look for Oracle Application Server Patchset as 10.1.3.4 as follows:

Installed Patch List:

===================== 1) Patch 7139339 applied on Fri Jul 17 20:03:46 IST 2009 [ Base Bug(s): 7139339 ]

2) Patch 7139320 applied on Fri Jul 17 20:02:23 IST 2009 [ Base Bug(s): 7139320 ]

3) Patch 7411481 applied on Fri Jul 17 20:00:42 IST 2009 [ Base Bug(s): 7411481 ]

4) Patch 6771776 applied on Fri Jul 17 19:58:55 IST 2009 [ Base Bug(s): 6771776 ]

5) Patch 6702510 applied on Fri Jul 17 19:51:41 IST 2009 [ Base Bug(s): 6702510 ]

6) Patch 5586892 applied on Sat Dec 30 08:18:37 IST 2006

[ Base Bug(s): 5586892 5126270 4689959 5238255 ]

Verification from Opatch lsinventory –detail

oracle@xyz.com 7139339]$ opatch lsinventory -detail

Oracle Interim Patch Installer version 1.0.0.0.56

Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation underOPatch/docs for usage reference.We also recommend usingthe latest OPatch version. For the latest OPatch versionand other support related issues, please refer to document293369.1 which is viewable from metalink.oracle.com

Oracle Home:/oracle/ACE/apps/tech_st/10.1.3Oracle Home Inventory:/oracle/ACE/apps/tech_st/10.1.3/inventoryCentral Inventory:/oracle/ACE/inst/apps/ACE_samarth/admin/oraInventory from:/oracle/ACE/apps/tech_st/10.1.3/oraInst.locOUI location:/oracle/ACE/apps/tech_st/10.1.3/ouiOUI shared library:/oracle/ACE/apps/tech_st/10.1.3/oui/lib/linux/liboraInstaller.soJava location:/oracle/ACE/apps/tech_st/10.1.3/jre/1.4.2/bin/javaLog file location:/oracle/ACE/apps/tech_st/10.1.3/.patch_storage//*.log

Creating log file "/oracle/ACE/apps/tech_st/10.1.3/.patch_storage/LsInventory__07-17-2009_20-17-29.log"

Result:

PRODUCT NAME VERSION

============ =======

Agent Required Support Files Patch 10.1.0.5.0

Agent Required Support Files 10.1.0.2.0

Apache Module for Oracle Distributed Authoring and Versioning 10.1.2.1.0

Assistant Common Files Patch 10.1.0.5.0

Assistant Common Files 10.1.0.2.0

Bali Share 1.1.18.0.0

DataDirect Connect JDBC Drivers 10.1.2.0.1

DBJAVA Required Support Files Patch 10.1.0.5.0

DBJAVA Required Support Files 10.1.0.2.0

Documentation Required Support Files 10.1.0.3.0

Enterprise Manager Minimal Integration Patch 10.1.0.5.0

Enterprise Manager Minimal Integration 10.1.0.2.0

Enterprise Manager plugin Common Files 10.1.0.2.0

Enterprise Manager plugin Common Files 10.1.0.5.0

Extended Windowing Toolkit 3.3.18.0.0

HTTP Server Files 1.3.31.0.0

Oracle Application Server PatchSet 10.1.3.4.0

Oracle ASkernel Common 10.1.3.0.0

Oracle Business Rules 10.1.3.0.0

.

.

.

OPatch succeeded.

Now upgrade the Opatch using 6880880 (Unzip the patch directly into the 10.1.3 Oracle Home)

Do not Start the application services and try to login to the applications using the port. As it throws login page cannot be displayed as the variable in context are pointing to portal/AppsLogin.jsp.

This will be resolved once you complete applying the patch 7303030.

Step 3: Upgrading the Java JDK

Download the latest JDK from http://java.sun.com/javase/downloads/index.jsp

download jdk-6u14-linux-i586.bin

1. Be sure to shutdown all the middle tier services.

a) Perform JDK Upgrade in IAS_ORACLE_HOME:

Navigate to $IAS_ORACLE_HOME/appsutil/

$mv jdk/ jdk_old/

$mkdir jdk/

copy the jdk-6u14-linux-i586.bin to $IAS_ORACLE_HOME/appsutil/jdk

Install the bin file using

$./jdk-6u14-linux-i586.bin

This will install latest jdk in appsutil/jdk

Copy the *.ttf files from $FND_TOP/resource to $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts.

Important: You need to do the steps as a post upgrade step if you complete with 7303030

b) Upgrade jre in $RDBMS_ORACLE_HOME

copy the jdk-6u14-linux-i586.bin to $RDBMS_ORACLE_HOME/appsutil

Source the database environment file

1. Navigate to $ORACLE_HOME/appsutil/

2. mv jre/ jre_old/

3. mkdir jre/

Install the jdk_version_linus-i586.bin to appsutil/jre

Verification of JDK installation

$ADJVAPRG -version

[oracle@prod appl]$ $ADJVAPRG -version

"1.6.0_14"

Java(TM) SE Runtime Environment (build 1.6.0_14-b08)

Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)

echo $CLASSPATH

[oracle@prod appl]$ echo $CLASSPATH

/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/lib/dt.jar:

/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/lib/tools.jar:

/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar:

/oracle/PRODN/apps/apps_st/comn/java/lib/appsborg.zip:

/oracle/PRODN/apps/tech_st/10.1.2/forms/java:

/oracle/PRODN/apps/tech_st/10.1.2/forms/java/frmall.jar:/oracle/PRODN/apps/tech_st/10.1.2/jlib/ewt3.jar:/

oracle/PRODN/apps/tech_st/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar:/oracle/PRODN/apps/apps_st/comn/java/classes

$AFJVAPRG -version

[oracle@prod appl]$ $AFJVAPRG -version

java version "1.6.0_14"

Java(TM) SE Runtime Environment (build 1.6.0_14-b08)

Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)

echo $AF_CLASSPATH

Each should reflect the paths belonging to latest JDK like this

[IAS_ORACLE_HOME]/appsutil/jdk/lib/dt.jar, [IAS_ORACLE_HOME]/appsutil/jdk/lib/tools.jar, [IAS_ORACLE_HOME]/appsutil/jdk/jre/lib/rt.jar, and [IAS_ORACLE_HOME]/appsutil/jdk/jre/lib/charsets.jar

Method:1

Create an jsp file under $OA_HTML like jdktest.jsp

The JDK version is: <%= System.getProperty("java.version") %>

compile the jdktest.jsp using ojspcompile, syntax is:

perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile -s jdktest.jsp'

Dont check it now, as your middle tier services are down.   

Check with Database Tier:

$cd $ORACLE_HOME/appsutil/jre/bin

./java -version

[oracle@prod jre]$ cd bin

[oracle@prod bin]$

./java -version

java version "1.6.0_14"

Java(TM) SE Runtime Environment (build 1.6.0_14-b08)

Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)

Run Autoconfig on both tiers

Step 4: Forms server Upgrade

In this Step upgradation of Forms server from 10.1.2.0.1 to 10.1.2.3 is to be done.

Backup the inventory file.

Source the application environment file

1. Unzip 5983622 and run ./runInstall

2. Supply oc4jadmin as 'secret' when prompted.

(Note here configuration assistant will fail at oc4j configuration Assistant, Simply skip and click next. This is normal behavior for E Business Upgrade)

Once completed:

Apply the Following Patches using OPatch tool from TOOLS_ORACLE_HOME

Error was: OPatch failed with error code 73

OPatch version    : 10.2.0.5.1

OUI version       : 10.1.0.6.0

=====================

p3559326_10105_LINUX.zip

p6880880_102000_LINUX.zip      ---

p4407272_10123_LINUX.zip

p7016961_101210_LINUX.zip

p4526825_10123_LINUX.zip

p7016961_10123_LINUX.zip

p5394728_10105_LINUX.zip

p5917053_10123_LINUX.zip

p7121788_10123_LINUX.zip

p5983622_10123_LINUX.zip

p7140392_10123_GENERIC.zip

p6371228_10123_LINUX.zip

p7329300_10123_LINUX.zip

p6857221_10123_LINUX.zip

p8300196_10123_GENERIC.zip

p8374931_10123_GENERIC.zip

Note: if you face the error below during any of the Opatch or ./runInstaller: rwconverter error: like this

/ACE/apps/tech_st/10.1.2/lib//librw.a -lnsl/oracle/ACE/apps/tech_st/10.1.2//lib/

/librw.so: undefined reference to `rolgxinoci_cid'

collect2: ld returned 1 exit status

make: *** [rwbuilder] Error 1

DO the following:

Restore the file

librw.so, and librw.a

from old backup to $TOOLS_ORACLE_HOME/lib and run

$TOOLS_ORACLE_HOME/reports/lib/make -f ins_reports.mk install

The result will be

/oracle/ACE/apps/tech_st/10.1.2/lib,-rpath,/oracle/ACE/apps/tech_st/10.1.2/jdk/jre/lib/i386:/lib:/usr/lib -lm `cat /oracle/ACE/apps/tech_st/10.1.2/lib/sysliblist` -ldl -lpthread -lm -L/oracle/ACE/apps/tech_st/10.1.2/lib -L/oracle/ACE/apps/tech_st/10.1.2/lib/stubs/ -lsnls10 -lpthread -ljvm -lhpi -Wl,-rpath,/usr/X11R6/lib -L/usr/X11R6/lib -lXm -lXt -lX11 -lm -lXp -lXext /oracle/ACE/apps/tech_st/10.1.2/lib//librw.a -lnslmv rwbuilder /oracle/ACE/apps/tech_st/10.1.2/bin/rwbuilderchmod 700 /oracle/ACE/apps/tech_st/10.1.2/bin/rwbuilder

then proceed with the patch, it should complete normally.

Create formsapp.ear

$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp

Enter Application name for re-deployment ? forms

Enter Oc4j Instance password for re-deployment ? welcome (or current password) error message 1

? No ">Run Autoconfig ? No

If you are prompted with oc4jadmin password is incorrect Do the following:

$INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

$cp system-jazn-data.xml system-jazn-data-ori.xml

$vi system-jazn-data.xml

locate oc4j-admin section

[user]

[name]oc4jadmin[/name]

[display-name]OC4J Administrator[/display-name]

[guid]88836370D11611DC9F30F9C1CD6F1A73[/guid]

[description]OC4J Administrator[/description]

[credentials]{903}F+iG1A46edXG9RdfY0pD2O4Ge/qyEjsg[/credentials]

[/user]

replace

{903}F+iG1A46edXG9RdfY0pD2O4Ge/qyEjsg

with !Welcome [Please note '!' is important]

then issue oc4jadmin password as 'Welcome'. it will pass the step.

Run Autoconfig on Apps Tier

Apply Interoperability Patch 7120543

Apply OPatch 6880880 as in the previous section to upgrade Opatch tool to 1.0.0.0.62

Rebuild Forms and Reports executables

cd $ORACLE_HOME/forms/lib32

Note: if this directory does not exist: cd $ORACLE_HOME/forms/lib

$ make -f ins_forms.mk install

cd $ORACLE_HOME/reports/lib32

Note: if this directory does not exist: cd $ORACLE_HOME/reports/lib

$ make -f ins_reports.mk install

Regenerate appsborg.zip and appsborg2.zip using adadmin

Step 5:  upgrading to 12.1.1

Apply AD.B.1 Patch (7461070)

Copy adgrants.sql from patch/admin directory to $RDBMS_ORACLE_HOME/appsutil/admin directory

If it is not present create a directory /admin inside $RDBMS_ORACLE_HOME/appsutil

run adgrants.sql as sys user with syntax

sqlplus "/as sysdba" @adgrants.sql APPS

once finished,

Apply the patch AD.B.1

run d below patch as applmgr:-

cd /software/qadar_patches/7461070

adpatch

Recreate grants and synonyms using adadmin

Run adadmin -> Maintain Applications Database Entities menu ->

     Re-create grants and synonyms for APPS schema.

Apply the Main patch 7303030 (Contains 8 parts)

During the patch run, please expect

XDOLoader.class will fail on user xdo

Follow the procedure from http://balajiabhi.blogspot.com/2009/07/rebuilding-xdo-objects-failed-worker-on.html

(Completed within 15 hours)

Apply online Help patch 7303031 using adpatch. (It takes 28 minutes)

Apply the product specific patches as per the metalink document stated above.

Start the application services using adstrtal.sh from $ADMIN_SCRIPT_HOME

Verify the forms version using 'About Applications' from Forms session.

It should show the version as follows

To get other versions, enable about this page link in LOGINPAGE of the server

To do this please do the following:

set the profile option FND: Diagnostics = Yes

Then bounce the middle tier services, you will see a link 'About This Page' at the bottom of the web page

The output of the About this page will give all the particulars about the version upgrade.

IMORTANT NOTES

Choose an Upgrade Method

The following sections describe the upgrade methods you can use to upgrade your database to the new Oracle Database 10g release:

·         Database Upgrade Assistant

·         Manual Upgrade

·         Export/Import

·         Data Copying

·         1) Database Upgrade Assistant

Database Upgrade Assistant

The Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database 10grelease. The DBUA automates the upgrade process by performing all of the tasks normally performed manually. The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations.

The DBUA provides support for Real Application Clusters (RAC) and Automatic Storage Management (ASM).

Support for Real Application Clusters

In a Real Application Clusters (RAC) environment, the DBUA upgrades all the database and configuration files on all nodes in the cluster.

Note:

On Windows operating systems, DBUA does not support a direct upgrade of Oracle Parallel Server version 8.1.7 databases to Oracle Database 10g with RAC. First, manually upgrade the Oracle Parallel Server database to Oracle Real Application Clusters Oracle9i release 2 (9.2), and then upgrade it to Oracle Database 10g with Real Application Clusters (RAC) using DBUA.

 

Support for Automatic Storage Management

The DBUA supports upgrades of databases that use Automatic Storage Management (ASM). If an ASM instance is detected, you have the choice of updating both the database and ASM or only the ASM instance.

The following sections guide you through the process of upgrading a database using the Database Upgrade Assistant (DBUA). (Note also that you must run the Oracle Net Configuration Assistant before running the Database Upgrade Assistant.)

The DBUA provides a graphical user interface (GUI) to guide you through the upgrade of a database, or you can invoke it in silent mode, which does not present a user interface:

·         Using the Database Upgrade Assistant Graphical User Interface

·         Using the Database Upgrade Assistant in Silent Mode

Note:

If the database instance is not running, the DBUA will try to start the instance with the default initialization parameter file. If that fails, you will be prompted to provide the name of the correct initialization parameter file or to start the instance. If the instance is already up and running, the DBUA connects to it.

Note:

If you abort the upgrade, but do not restore the database, then you should not restart the DBUA until you start up the existing database in UPGRADE mode using the 10.2 server. You cannot go back to the original server unless you restore your database. If you restore your database manually (not using the DBUA), then remove the following file before starting the DBUA: $10.2OracleHome/cfgtoollogs/dbua/logs/Welcome_<SID>.txt. The presence of this file indicates to the DBUA that this is a re-run operation.

In the environment of the new Oracle Database 10g release, start the Database Upgrade Assistant as follows:

• On UNIX platforms, enter the following command at a system prompt:

·         dbua

Note:

The dbua executable is usually located in ORACLE_HOME/bin.

• On Windows operating systems, choose:

·         Start > Programs > Oracle - HOME_NAME> Configuration and Migration Tools >

Database Upgrade Assistant

When the Database Upgrade Assistant starts, it shows the Welcome screen of the Database Upgrade Assistant. Before the upgrade, the DBUA performs the following steps:

·         Check for any invalid user accounts or roles

·         Check for any invalid datatypes or invalid objects

·         Check for any desupported character sets

·         Check for adequate resources, including rollback segments, tablespaces, and free disk space

·         Check for any missing SQL scripts needed for the upgrade

·         Optionally, DBUA backs up all necessary files

The DBUA does not begin the upgrade until all of these pre-upgrade steps are completed.

During the upgrade, the DBUA automatically modifies or creates new required tablespaces and invokes the appropriate upgrade scripts.

While the upgrade is running, the DBUA shows the upgrade progress for each component. The DBUA writes detailed trace and log files and produces a complete HTML report for later reference. To enhance security, the DBUA automatically locks new user accounts in the upgraded database. The DBUA then proceeds to create new configuration files (parameter and listener files) in the new Oracle home.

Note:

Database Upgrade Assistant does not back up ASM databases. You must manually back them up on your own.

Using the Database Upgrade Assistant in Silent Mode

When invoked with the -silent command line option, the Database Upgrade Assistant operates in silent mode. In silent mode, the Database Upgrade Assistant does not present a user interface. It also writes any messages (including information, errors, and warnings) to a log file.

For example, the following command upgrades a database named ORCL in silent mode:

dbua -silent -dbName ORCL &

·         2) Manual Upgrade

Manual Upgrade

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database 10g release.

While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.

Before the Upgrade

When manually upgrading a database, perform the following pre-upgrade steps:

·         Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is a SQL script that ships with the new Oracle Database 10g release, and must be run in the environment of the database being upgraded.

The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for the new Oracle Database 10g release.

·         Prepare the new Oracle Home.

·         Perform a backup of the database.

Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).

Run the Pre-Upgrade Information Tool

The Pre-Upgrade Information Tool is a SQL script that ships with the new Oracle Database 10g release, and must be copied to and run from the environment of the database being upgraded. Complete the following steps to run the Pre-Upgrade Information Tool:

1.      Log in to the system as the owner of the Oracle home directory of the new Oracle Database 10g release.

2.      Copy the following file from the ORACLE_HOME/rdbms/admin directory of the new Oracle Database 10g release to a directory outside of the Oracle home, such as the temporary directory on your system:

o    utlu102i.sql

·         Back Up the Database

·         Prepare the New Oracle Home

·         Upgrade the Database

·         Troubleshoot the Upgrade

·         Abandon the Upgrade

Tip:

It may be necessary to create a text initialization parameter file (pfile) from the server parameter file (spfile) so that you can edit the initialization parameters.

Note:

If you are upgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.

Note:

The UPGRADE keyword allows you to open a pre-10.2 database. It also restricts logons to AS SYSDBA sessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.

1.      Run catupgrd.sql:

2.      SQL> @catupgrd.sql

The catupgrd.sql script determines which upgrade scripts need to be run and then runs each necessary script. You must run the script in the new release 10.2 environment.

The upgrade script creates and alters certain data dictionary tables. It also upgrades or installs the following database components in the new release 10.2 database:

o    Oracle Database Catalog Views

o    Oracle Database Packages and Types

o    JServer JAVA Virtual Machine

o    Oracle Database Java Packages

o    Oracle XDK

o    Oracle Real Application Clusters

o    Oracle Workspace Manager

o    Oracle interMedia

o    Oracle XML Database

o    OLAP Analytic Workspace

o    Oracle OLAP API

o    OLAP Catalog

o    Oracle Text

o    Spatial

o    Oracle Data Mining

o    Oracle Label Security

o    Messaging Gateway

o    Expression Filter

o    Oracle Enterprise Manager Repository

Troubleshoot the Upgrade

There are three resources that generally require increases for a new Oracle Database release:

• SYSTEM tablespace
• Shared memory
• Rollback segments/Undo Tablespace

If you run out of one of these resources during the upgrade, then increase the resource allocation. After increasing the resource allocation, you should perform a SHUTDOWN ABORT and restart the instance (in UPGRADE mode) before rerunning the catupgrd.sql script or restarting the Database Upgrade Assistant.

·         3)Export/Import

Export/Import

Unlike the DBUA or a manual upgrade, the Export/Import utilities physically copy data from your current database to a new database. You can use either the Oracle Data Pump Export and Import utilities (available as of Oracle Database 10g) or the original Export and Import utilities to perform a full or partial export from your database, followed by a full or partial import into a new Oracle Database 10g database. Export/Import can copy a subset of the data in a database, leaving the database unchanged.

The current database's Export utility copies specified parts of the database into an export dump file. Then, the Import utility of the new Oracle Database 10grelease loads the exported data into a new database. However, the new Oracle Database 10g database must already exist before the export dump file can be copied into it.

When importing data from an earlier release, the Oracle Database 10g Import utility makes appropriate changes to data definitions as it reads earlier releases' export dump files.

The following sections highlight aspects of Export/Import that may help you to decide whether to use Export/Import to upgrade your database.

Export/Import Effects on Upgraded Databases

The Export/Import upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the current database can remain available, you can, for example, keep an existing production database running while the new Oracle Database 10g database is being built at the same time by Export/Import. During the upgrade, to maintain complete database consistency, changes to the data in the database cannot be permitted without the same changes to the data in the new Oracle Database 10g database.

Most importantly, the Export/Import operation results in a completely new database. Although the current database ultimately contains a copy of the specified data, the upgraded database may perform differently from the original database. For example, although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems.

Export/Import Benefits

Upgrading using Export/Import offers the following benefits:

·         Defragments the data - you can compress the imported data to improve performance.

·         Restructures the database - you can create new tablespaces or modify existing tables, tablespaces, or partitions to be populated by imported data.

·         Enables the copying of specified database objects or users - you can import only the objects, users, and other items that you wish.

·         Serves as a backup archive - you can use a full database export as an archive of the current database.

Time Requirements for Export/Import

Upgrading an entire database by using Export/Import can take a long time, especially compared to using the DBUA or performing a manual upgrade. Therefore, you may need to schedule the upgrade during non-peak hours or make provisions for propagating to the new database any changes that are made to the current database during the upgrade.

Note:

If the new Oracle Database will be created on the same computer as the source database, and you do not want to overwrite the source database data files, then you must pre-create the tablespaces and specify DESTROY=N when you import.

4)Data Copying

Data Copying

You can copy data from one Oracle Database to another using database links. For example, you can create new tables and fill the tables with data by using the INSERT INTO statement and the CREATE TABLE ... AS statement.

Copying data and Export/Import offer the same advantages for upgrading. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can copy only specified database objects or users.

Copying data, however, unlike Export/Import, enables the selection of specific rows of tables to be placed into the new database. Copying data is a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables.

Upgrade from 9i (9.2.0.6) to Oracle 10g (10.2.0.1)

Manual Upgradation :  A  manual   upgrade consists  of  running  SQL  scripts  and  utilities  from  a command line  to  upgrade  a  database  to  the  new  Oracle Database 10g  release. While  a   manual  upgrade  gives us finer control over the upgrade process, it  is  more susceptible to error  if  any  of  the  upgrade or pre-upgrade steps  are either not followed or are performed out of order. Below are the steps

1.) Install Oracle 10g software : For Upgradation , Invoke the .exe or runInstallers ad select  “Install software only” to Install the Oracle S/w .

2.) Take Full Backup Database :  Take full database backup of database which is to be upgraded .

3.) Check the invalid Objects : Check the invalid objects by running ultrp.sql scripts as
SQL> @ORACLE_HOME/rdbms/admin/utlrp.sql

4.) Login into 9i  home  and run the utlu102i.sql : This  script is in oracle 10g home .
SQL> spool  pre_upgrd.sql
SQL> @<ORACLE_10G_HOME>/rdbms/admin/utlu102i.sql
SQL> spool off

The above scripts checks a number of areas to make sure the instance is suitable for upgrade including

·         Database version

·         Redolog file sizes

·         Tablespace sizes

·         Server options

·         Initialization parameters (updated, depercated and obsolete)

·         Database components

·         Miscellaneous Warnings

·         SYSAUX tablespace present

·         Cluster information

The  issues  indicated  by  this script  should  be  resolved  before  a  manual  upgrade  is  attempted. Once we  have  resolved the  above  warning , then re-run  the  above  script  once  more  to  cross-check . We can execute any number of times.

5.)  Check  for  the  timestamp  with  timezone  Datatype : The  time zone  files  that are  supplied  with  Oracle  Database 10g  have  been  updated  from  version 1  to version 2  to  reflect changes  in  transition  rules  for  some  time  zone  regions. The changes may affect existing  data  of  TIMESTAMP WITH TIME ZONE  datatype. To  preserve this  TIMESTAMP data for updating  according  to  the  new  time zone  transition  rules, we  must  run  the utltzuv2.sql script on  the database  before  upgrading. This  script  analyzes our database for  TIMESTAMP WITH TIME ZONE columns  that are  affected  by  the  updated  time  zone  transition  rules.

SQL> @ORACLE_10G_HOME/rdbms/admin/utltzuv2.sql
SQL> select * from sys.sys_tzuv2_temptab;

If   the  utltzuv2.sql   script  identifies  columns   with   time zone   data   affected  by  a  database  upgrade, then  back  up  the  data  in character  format  before we upgrade the database. After the   upgrade,   we   must  update  the  tables  to ensure  that  the data  is  stored   based on  the new  rules. If  we export  the tables  before  upgrading  and  import  them  after  the  upgrade, the  conversion  will  happen  automatically  during the import.

6.) Shutdown the database :
shut down the database and copy the spfile(or pfile) and password file from 9i home to 10g home .

7.) Upgrade Database : Set following environment for 10g and login using  “SYS”  user . It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.

ORACLE_SID=<sid>
ORACLE_HOME=<10g home>
PATH=<10g path>
sqlplus / as sysdba

SQL> startup  upgrade
SQL>spool  upgrd_log.sql
SQL>@catupgrd.sql
SQL> spool off

8.)  Recompile any invalid objects : Compare the number of invalid objects with the number noted in step 4 . It should hopefully be the same or less.

SQL>@ORACLE_HOME/rdbms/admin/utlrp.sql

9.) Check the status of the upgrade :
SQL> @ORACLE_HOME/rdbms/admin/utlu102s.sql

The above script queries the DBA_SERVER_REGISTRY to determine upgrade status and provides information about invalid or incorrect component upgrades. It also provides names of scripts to rerun to fix the errors.

10.) Edit the spfile : Create a  pfile  from spfile as
SQL>create pfile from spfile ;

Open the pfile and set the compatible parameter to 10.2.0.0.0 . Shutdown the database and create the new modified spfile .

SQL>shut immediate
SQL> create spfile from pfile ;

11.) Start the database normally 
SQL> startup

Step by Step Upgrading Oracle 10g to Oracle 11g

Recently we did database upgrade from 10g to Oracle 11g.I would like share that activity with you.

Pre-Requisite:

You should have the Oracle database 10g, which you want to migrate.
Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle Home.

Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0

then my 11g Oracle Home is : /u01/app/oracle/oracle/product/11.1.0

Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.

Start the installation using the below command

./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst

Screen 1 – Select Product Install
select “Oracle Database 11g”

Screen 2 – Select Installation Method
Choose “Advanced Installation”

Screen 3 – Specify Inventory directory and credentials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.

Screen 4 – Select Installation Type
Choose “Enterprise Edition”

Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME

Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further

Screen 7 – Upgrade an Existing Database
Choose “No”

Screen 8 – Select Configuration Option
Choose “Install Software Only”

Screen 9 – Privileged system groups

Based on the group of oracle user, this value has to be set.

Screen 10 – Summary
Click on “Install”

At the end of installation, installer will ask to run root.sh script. Do not press OK button.
Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

[oracle]$ cd $ORACLE_HOME
[oracle]$ cd rdbms/admin/
[oracle]$ pwd
/u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
[oracle]$ cp utlu111i.sql /tmp

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Most of the time issue use to come up with time zone….

Then login to the 10g oracle database and run the above sql you copied.

Oracle Database 11.1 Pre-Upgrade Information Tool 23-02-2011 01:34:07
.
**********************************************************************
Database:
**********************************************************************
–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:

SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 2

SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
ELSE 0 end VERSION
FROM v$timezone_names; 

VERSION
———-
2

If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.

Check the database version

SQL> select banner from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.

Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.

If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.

For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script &timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.

Please follow the metalink note ID 396387.1

Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
[oracle]$ unzip p5632264_10202_LINUX.zip
[oracle]$ ls
etc files README.txt
[oracle]$ cd files/oracore/zoneinfo
[oracle]$ ls
readme.txt timezlrg.dat timezone.dat

Backup $ORACLE_HOME/oracore/zoneinfo directory

[oracle]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup

Copy the .dat files

[oracle]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo

Bounce the database and check the TIMEZONE version again

SQL> select * from v$timezone_file;

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

SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 386 then 3
end
ELSE 0 end VERSION
FROM v$timezone_names;

VERSION
———-
4

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
…. SYSMAN

Gather Dictionary stats:

Connect as sys user and gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

PL/SQL procedure successfully completed.

Step 4) Run Pre-Upgrade Utility again

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.

Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.

If everything looks fine, Shut down the database from 10g Oracle Home

This time make sure you dont have the critical warnings like the one with TIMEZONE version.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

sqlplus “/ as sysdba” –> will be connected to idle instance

SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql

Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

Run utlu111s.sql to display the results of the upgrade:

SQL> @?/rdbms/admin/utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 23-02-2011 05:22:40
.Component Status Version HH:MM:SS
.Oracle Server

Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57

PL/SQL procedure successfully completed.

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> @?/rdbms/admin/catuppst.sql

Run utlrp.sql to recompile

SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;

COUNT(*)
———-
1576

SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_objects where status = ‘INVALID’;

UPGRADE FROM 11g to 12c

I recently tested the upgrade of an Oracle 11.2.0.3 database to 12c Release 1 and here are the screen shots as well as some new things in found in the 12c Database Upgrade Assistant.

• The DBUA GUI interface has now changed as compared to previous releases. It is like the OUI with split screen layout with tasks listed on the left of the screen and steps, details and progress on the right.

• The  utlu121s.sql script has been replaced with the preupgrd.sql script. The new Pre-Upgrade Information Tool provides fix-up scripts with the ability to fix and address any issues identified by the pre-upgrade check script

• The previous upgrade script catupgrd.sql has been replaced with the new catctl.pl Parallel Upgrade Utility script which provides the ability to run upgrade scripts in parallel taking advantage of spare CPU capacity which can potentially reduce upgrade times

• If Flashback database is enabled, DBUA creates a guaranteed restore point which can be used in case we have a  failure in the upgrade process. We can also restart DBUA to recover from any failures during the upgrade.

• We can now use an existing RMAN backup which DBUA is aware of in case we need to restore the database to the pre-12c upgrade state

• Oracle XML DB is now a mandatory component of the Oracle 12c database and is automatically installed as part of the upgrade process if not found

• If Database Control from earlier release exists, we need to drop the same before upgrading the database. We can do this via DBUA or reduce the upgrade downtime by performing this task in advance by copying a new script emremove.sql from the 12c Oracle Hone and running it against the 11g database.

I chose DBUA to upgrade my test database as it was the simplest and quickest of all. Below are the database environment details prior to upgrade:

Oracle Database Version:             Oracle Database 11gR2 (11.2.0.3) (64-bit)

Operating System:                         Oracle Enterprise Linux 6.1 (64-bit)

To begin the upgrade process, I copied Oracle 12c software to the database server and did the following

1.                  Backup Oracle database

2.                  Stop the database

3.                  Stop listener

4.                  Change ORACLE_HOME environment variable in the bash profile of “oracle” user

5.                  Launch Oracle 12c Installer (./runInstaller)

You will see the following screen: