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
See Also
