Upgrading a Database
Perhaps your task is not to create a new database, but to upgrade an existing database.
A database upgrade is necessary when Oracle DBA have an older database such as Oracle9i
and need to upgrade it to the current release. Oracle provides a tool for upgrading an
existing database, called the Database Upgrade Assistant (DBUA).
Overview of the Database Upgrade Assistant
The Database Upgrade Assistant (DBUA) interactively steps Oracle DBA through the upgrade
process and configures the database for the new Oracle Database 10g release. The
Database Upgrade Assistant automates the upgrade process by performing all of the
tasks normally performed manually. The Database Upgrade Assistant makes
appropriate recommendations for configuration options such as tablespaces and redo
logs. Oracle DBA can then act on these recommendations.
For example, the Database Upgrade Assistant recommends sizing information for the
new SYSAUX tablespace, which is required in Oracle Database 10g.
Before the Upgrade
The Database Upgrade Assistant performs the following pre-upgrade steps:
It checks for any invalid user accounts or roles
It checks for any invalid datatypes
It checks for any desupported character sets
It checks for adequate resources, including rollback segments, tablespaces, and
free disk space
It optionally backs up all necessary files
The Database Upgrade Assistant does not begin the upgrade until it completes all of
the pre-upgrade steps.
During the Upgrade
The Database Upgrade Assistant automatically modifies or creates new required
tablespaces, invokes the appropriate upgrade scripts, archives the redo logs, and
disables archiving during the upgrade phase.
While the upgrade is running, the Database Upgrade Assistant shows the upgrade
progress for each component. The Database Upgrade Assistant writes detailed trace
and log files and produces a complete HTML report for later reference. To enhance
security, the Database Upgrade Assistant automatically locks new user accounts in the
upgraded database. The Database Upgrade Assistant then proceeds to create new
configuration files (parameter and listener files) in the new Oracle home.
Real Application Clusters Support
The Database Upgrade Assistant is fully compliant with the Real Application Clusters
(RAC) environment. In a RAC environment, the Database Upgrade Assistant upgrades
all the database and configuration files on all nodes in the cluster.
Support for Silent Mode
The Database Upgrade Assistant supports a silent mode of operation where no user
interface is presented to the user. Silent mode lets Oracle DBA use a single statement for the
Oracle Database Version Restrictions
The Database Upgrade Assistant supports the following versions of the Oracle
Database for upgrading to Oracle Database 10g:
Oracle database version 8.0.6 (single instance databases only)
Oracle8i Release 8.1.7 (single instance and Real Application Clusters databases)
Oracle9i Release 1 (9.0.1) (single instance and Real Application Clusters databases)
Oracle9i Release 9.2.0 (single instance and Real Application Cluster databases)
If your database version is not in this list, Oracle DBA need to upgrade first to the closest
release as listed. Oracle DBA can then upgrade the database to the current release.
Starting the Database Upgrade Assistant
During installation, if Oracle DBA install software only and specify that you are upgrading
your database to the new Oracle release, then the Database Upgrade Assistant is
launched automatically at the end of the software install and
If you perform a software-only install and do not upgrade your database at that time,
then you can do so later by launching the Database Upgrade Assistant.
To launch the Database Upgrade Assistant on the Windows operating system choose:
Start > Programs > Oracle - home_name > Configuration and Migration Tools >
Database Migration Assistant
To launch the Database Upgrade Assistant on a UNIX or Linux operating system enter
the following at a command prompt:
typically found in your $ORACLE_HOME/bin directory.
When the Database Upgrade Assistant starts, the Welcome page appears.
Upgrading the Database Using the Database Upgrade Assistant
Complete the following steps to upgrade a database using the Database Upgrade
1. At the Welcome page of the Database Upgrade Assistant, make sure the database
being upgraded meets the specified conditions. Then, click Next.
If you need help at any page or want to consult more documentation about the
Database Upgrade Assistant, then click Help to open the online help.
2. At the Select a Database page, select the database Oracle DBA want to upgrade. Then, click
Oracle DBA may need to provide a user name and password with SYSDBA privileges if
you do not have operating system authentication.
3. At the Sysaux page, give the related attributes for creation of a SYSAUX tablespace.
Then, click Next.
4. At the Recompiling Invalid Objects page, decide whether you want the Database
Upgrade Assistant to recompile all invalid PL/SQL modules after the upgrade is
complete. When you upgrade a database to the new Oracle Database 10g release,
many of the PL/SQL modules in your database will become invalid. As a result,
all existing PL/SQL modules in an INVALID state must be recompiled, such as
packages, procedures, types, and so on.
By default, the Oracle Database recompiles invalid PL/SQL modules as they are
used. For example, if an invalid PL/SQL module is called, it will first be
recompiled before it is actually executed. The time it takes to recompile the
module can result in poor performance as you begin to use your newly upgraded
To eliminate these performance issues, select Recompile invalid objects at the end
of upgrade. When Oracle DBA select this option, the Database Upgrade Assistant
recompiles all the invalid PL/SQL modules immediately after the upgrade is
performed. This will ensure that Oracle DBA will not experience any performance issues
later, as you begin using your newly upgraded database.
The task of recompiling all the invalid PL/SQL modules in your database can take
a significant amount of time and increase the time it takes to complete your
database upgrade. If you have multiple CPUs, then you can reduce the time it
takes to perform this task by taking advantage of parallel processing on your
available CPUs. If you have multiple CPUs available, then the Database Upgrade
Assistant automatically adds an additional section to the Recompile Invalid
Objects page and automatically determines the number of CPUs Oracle DBA have
See Also: Oracle Database Upgrade Guide for information about
Database Upgrade Assistant command line options
Note: Selecting Recompile invalid objects at the end of upgrade is
equivalent to running the ORACLE_
HOME/rdbms/admin/utlrp.sql script, which is used to
recompile stored PL/SQL and Java code and is described in the
Oracle Database Upgrade Guide.
The Database Upgrade Assistant also provides a recommended degree of
parallelism, which determines how many parallel processes are used to recompile
your invalid PL/SQL modules. Specifically, the Database Upgrade Assistant sets
the degree of parallelism to one less than the number of CPUs you have available.
For example, if you have three CPUs available for processing, then the Database
Upgrade Assistant selects 2 from the Degree of Parallelism menu. You can adjust
this default value by selecting a new value from the Degree of Parallelism menu.
5. At the Management Options page, you have the option of setting up your
database so it can be managed with Oracle Enterprise Manager. Oracle Enterprise
Manager provides Web-based management tools for managing individual
database instances, as well as central management tools for managing your entire
Oracle environment, including multiple databases, hosts, application servers, and
other components of your network.
a. When Oracle DBA run the Database Upgrade Assistant, the assistant checks to see if
the Oracle Management Agent has been installed on the host computer. If the
assistant locates an Oracle Management Agent, select the Grid Control option
and select an Oracle Management Service from the drop-down list. When Oracle DBA
finish installing the Oracle Database, the database will automatically be
available as a managed target within the Oracle Enterprise Manager Grid
b. If Oracle DBA are not centrally managing your Oracle environment, Oracle DBA can still use
Oracle Enterprise Manager to manage your database. When you install an
Oracle Database, you automatically install the Oracle Enterprise Manager
Database Control, which provides Web-based features for monitoring and
administering the single-instance or clustered database you are installing.
To configure the database so it can be managed with the Oracle Enterprise
Manager Database Control, select the Database Control option.
c. When Oracle DBA select the Database Control management option, you can configure
Enterprise Manager so that email notifications will be enabled immediately
Select Enable Email Notifications if you want the SYSMAN user (the default
Super Administrator and owner of the Management Repository schema) to
receive email notification when a metric for a specified condition reaches a
critical or warning threshold. For example, Enterprise Manager can send an
email when a target goes down or when there are database space usage
d. If you select the Database Control management option, you can also enable
automatic daily backups of your entire database.
Select Enable Daily Backups to use the Oracle-suggested backup strategy to
back up your entire database with a minimum amount of configuration. Later,
you can use Oracle Enterprise Manager to customize your backup strategy
When Oracle DBA select this option, Enterprise Manager will be configured to back up
your database, based on the scheduled start time Oracle DBA enter on this page,
immediately after Oracle DBA finish installing the Oracle Database. Enterprise
Manager will back up the database to the Flash Recovery Area that you
specify later on the Recovery Configuration page of the Database Upgrade
e. After Oracle DBA have made your choices, click Next.
6. At the Database Credentials page, secure your database with passwords for the
Enterprise Manager accounts. You can set a single password, which will be
applied to each of the listed Enterprise Manager user accounts, or enhance the
security of the accounts by providing unique passwords for each user.
7. At the Recovery Configuration page, specify a flash recovery area and enable
archiving. When Oracle DBA are managing your database, it is important to configure the
database so you can recover your data in the event of a system failure.
The Flash Recovery Area can be used to recover data that would otherwise be lost
during a system failure; this location is also used by Enterprise Manager if Oracle DBA
have enabled local management and daily backups on the Management Options
page shown previously in the Database Upgrade Assistant.
8. At the Backup page, you have two options:
Choose I have already backed up my database if Oracle DBA completed a backup
before running the Database Upgrade Assistant.
Choose I would like this tool to back up the database if you did not complete
a backup. If you choose this option, then you can select the backup directory
by clicking Browse.
After Oracle DBA have made your choice, click Next.
9. At the Network Configuration for the database page, there are two property page
The Listeners property page is displayed if you have more than one listener in
the new release’s Oracle home. Select the listeners for which you would like to
register the upgraded database.
The Directory Service property page shows up if you have directory service is
configured in the new release’s Oracle home. You can select to either register
or not register the upgraded database with the directory service.
10. At the Summary page, make sure all of the specifications are correct. If anything is
incorrect, then click Back until Oracle DBA can correct the specification. If everything is
correct, then click Finish.
11. The Database Upgrade Assistant lists the initialization parameters that will be set
for the database during the upgrade. Click Upgrade.
A Progress dialog appears and the Database Upgrade Assistant begins to perform
12. During the upgrade, you may encounter error messages with Ignore and Abort
choices. If other errors appear, than you must address them accordingly.
If Ignore is presented as a choice in the message, then clicking the button will
ignore the current upgrade step.
This causes the Database Upgrade Assistant to move on to the next step in the
upgrade, ignoring this and any dependent steps. After the upgrade is
complete, you can fix the problem, restart the Database Upgrade Assistant,
and complete the ignored steps.
If Ignore is not presented as a choice in the message, then you need to
terminate the process by clicking Abort.
This will terminate the upgrade process. The Database Upgrade Assistant
prompts you to restore the database if the database backup was taken by the
Database Upgrade Assistant.
After the database has been restored, Oracle DBA need to correct the cause of the error
and restart the Database Upgrade Assistant to perform the upgrade again.
If you do not want to restore the database, then the Database Upgrade
Assistant leaves the database in its present state so that you can proceed with
a manual upgrade.
13. After the upgrade has completed, the following message is displayed in the
Upgrade has been completed. Click the "OK" button to see the
results of the upgrade.
14. At the Results dialog, you can view the details of the upgrade. You can also
unlock and set passwords to the user accounts.
If you are not satisfied with the upgrade, then you can restore the database back to
the previous release.
If Oracle DBA are satisfied with the upgrade, then click Done.
15. At the Changes in Default Behavior page, the Database Upgrade Assistant
displays some changes in behavior of Oracle Database 10g from that of previous
releases. In some cases the default values of some initialization parameters have
changed. In other cases some new behavior/requirement has been introduced that
may affect current scripts or applications.
16. Perform the following actions, as necessary:
a. Back up the database
b. Change passwords for any new Oracle supplied administrative accounts.
c. Migrate your traditional text initialization parameter file to a server parameter
file, if Oracle DBA had not already done so in the previous release. This is not a
required step, but using a server parameter file enables initialization
parameter settings to be persistent across startup and shutdown and
eliminates manual maintenance of initialization parameters. For more
information, seeOracle Database Upgrade Guide.
d. Adjust your parameter file for the new release.
PREVIOUS CHAPTER | NEXT CHAPTER
More Tutorials on Oracle dba ...
Source : Oracle Documentation
Liked it ? Want to share it ? Social Bookmarking
Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests