Understanding the Upgradation Process

Sunday, 3 July 2016

Understanding the Upgradation Process

To upgrade oracle database manually from 10g to 11g, we need to use Oracle-supplied pre- and post-upgrade scripts.

If I’m upgrading from an Oracle Database 10g release database to Oracle Database 11g and would need to use the following scripts in turn to perform the manual upgrade.

- utlu111i.sql

- catupgrd.sql

- utilu111s.sql

- catuppst.sql and

- utlrp.sql

Following is a summary of the functions performed by each of the upgrade scripts:

utlu111i.sql (Pre-Upgrade Information Tool)

This gathers information from the database and analyses it to make sure that it meets all the upgrade requirements,… such as whether the database already contains the SYSAUX tablespace or not.

The Pre-Upgrade Information Tool will issue warnings about potential upgrade issues such as database version and compatibility, redo log size, initialization parameters, and tablespace size estimates, and generates warnings if your database doesn’t satisfy the

requirements for upgrading to Oracle Database 11g.

catupgrd.sql This performs the actual upgrading of the database to the Oracle Database 11g release and it now supports parallel upgrades of the database.

utlu111s.sql (Upgrade Status Utility script)

This lets you check the status of the upgrade… whether the upgraded database’s components have a valid status.

catuppst.sql (This is new in Oracle Database 11g Release 1.)

This is the script you run to perform post-upgrade actions.

utlrp.sql This script re-compiles and re-validates any remaining application objects.

Tips :

Tip #1: Either take a Cold or Hot backup of source 10g database (advisable to have cold backup).

Since 11g upgrade requires downtime of oracle database, I strongly suggest to take cold backup beforehand. The reason to do that is to minimize downtime as much as possible. If anything wrong happens during upgrade, we get the most straightforward way to restore source database.

Tip #2: Running utlu111i.sql would be helpful to solve possible pre-requisite problem of upgrade.

Script utlu111i.sql does more than gathering information from database and analysing database. It solves problems which could break the process of upgrade.

For instance, the time zone files required in Oracle 11g has been updated to version 4. Failing doing that will cause break out of upgrade while we do upgrade with running catupgrd.sql. By running script utlu111i.sql, the new time zone file (version 4) will be recorded in view registry$database.

Tip #3: With running utlu111i.sql, making necessary change on parameter file if required.

Running utlu111i.sql will present the report about warnings in upgrade issues such as database version and compatibility, and so on…

Before doing real upgrade, I followed the generated report to change parameter file as per as below.

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

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"

Also, it’s critical that the parameter “compatible" need to be changed from 10.*.*.*.* to 11.1.0.0.0. Otherwise, upgrade process will raise problem and it’s very likely that control file of source database would be corrupt and need to be recovered if upgrade failed. That absolutely make DBA in timely trouble to recover source database.

See Also