Upgrading to Oracle Database 10g

Want to upgrade to Oracle Database 10g, but not sure how? Fortunately, there are a number of ways to do it. This article, the first of two parts, describes these methods and guides you as to which one to use, depending on your circumstances. It is excerpted from chapter two of the Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459).

If you have previously installed an earlier version of the Oracle database server, you can upgrade your database to Oracle Database 10g. Multiple upgrade paths are supported; the right choice for you will depend on factors such as your current Oracle software version and your database size. In this chapter, you will see descriptions of these methods along with guidelines for their use.

If you have not used a version of Oracle prior to Oracle Database 10g, you can skip this chapter for now. However, you will likely need to refer to it when you upgrade from Oracle Database 10g to a later version or when you migrate data from a different database into your database.

Prior to beginning the upgrade, you should read the Oracle Database 10g Installation Guide for your operating system. A successful installation is dependent on a properly configured environment—including operating system patch levels and system parameter settings. Plan to get the installation and upgrade right the first time rather than attempting to restart a partially successful installation. Configure the system to support both the installation of the Oracle software and the creation of a usable starter database.

This chapter assumes that your installation of the Oracle Database 10g software (see Chapter 1) completed successfully and that you have an Oracle database that uses an earlier version of the Oracle software on the same server. To upgrade that database, you have four options:

  1. Use the Database Upgrade Assistant to guide and perform the upgrade in place. The old database will become an Oracle 10g database during this process.
  2. Perform a manual upgrade of the database. The old database will become an Oracle 10g database during this process.
  3. Use the Export and Import utilities to move data from an earlier version of Oracle to the Oracle 10g database. Two separate databases will be used—the old database as the source for the export and the new database as the target for the import.
  4. Copy data from an earlier version of Oracle to an Oracle 10g database. Two separate databases will be used—the old database as the source for the copy and the new database as the target for the copy.

Upgrading a database in place—via either the Database Upgrade Assistant or the manual upgrade path—is called a direct upgrade. Because a direct upgrade does not involve creating a second database for the one being upgraded, it may complete faster and require less disk space than an indirect upgrade.


Direct upgrade of the database to version 10 is only supported if your present database is using one of these releases of Oracle: 8.0.6, 8.1.7, 9.0.1, or 9.2. If you are using any other release, you will first have to upgrade the database to one of those releases or you will need to use a different upgrade option. Oracle 8.0.6 is only supported for some versions (generally 64-bit), so be sure to check the online certification matrixes at Oracle’s Metalink site.


Plan your upgrades carefully; you may need to allow time for multiple incremental upgrades (such as from 8.1.6 to 8.1.7) prior to upgrading to Oracle Database 10g.

{mospagebreak title=Choosing an Upgrade Method}

As described in the previous section, two direct upgrade and two indirect upgrade paths are available. In this section, you will see a more detailed description of the options, followed by usage descriptions.

In general, the direct upgrade paths will perform the upgrade the fastest because they upgrade the database in place. The other methods involve copying data, either to an Export dump file on the file system or across a database link. For very large databases, the time required to completely re-create the database via the indirect methods may exclude them as viable options.

The first direct method relies on the DatabaseUpgradeAssistant (DBUA). DBUA is an interactive tool that guides you through the upgrade process. DBUA evaluates your present database configuration and recommends modifications that can be implemented during the upgrade process. These recommendations may include the sizing of files and the specifications for the new SYSAUX tablespace. After you accept the recommendations, DBUA performs the upgrade in the background while a progress panel is displayed. DBUA is very similar in approach to the Database Configuration Assistant (DBCA). As discussed in Chapter 1, DBCA is a graphical interface to the steps and parameters required to make the upgrade a success.

The second direct method is called a manual upgrade. Whereas DBUA runs scripts in the background, the manual upgrade path involves database administrators running the scripts themselves. The manual upgrade approach gives you a great deal of control, but it also adds to the level of risk in the upgrade because you must perform the steps in the proper order.

You can use Export and Import as an indirect method for upgrading a database. In this method, you export the data from the old version of the database and then import it into a database that uses the new version of the Oracle software. This process may require disk space for multiple copies of the data—in the source database, in the Export dump file, and in the target database. In exchange for these costs, this method gives you great flexibility in choosing which data will be migrated. You can select specific tablespaces, schemas, tables, and rows to be exported.

In the Export/Import method, the original database is not upgraded; its data is extracted and moved, and the database can then either be deleted or be run in parallel with the new database until testing of the new database has been completed. In the process of performing the export/ import, you are selecting and reinserting each row of the database. If the database is very large, the import process may take a long time, impacting your ability to provide the upgraded database to your users in a timely fashion. See Chapter 12 for details on the Export and Import utilities.


Depending on the version of the source database, you will need to use a specific version of the Export and Import utilities. See “Export and Import Versions to Use” later in this chapter.

In the data-copying method, you issue a series of create table as select or insert as select commands that cross database links (see Chapter 16) to retrieve the source data. The tables are created in the Oracle 10g database based on queries of data from a separate source database. This method allows you to bring over data incrementally and to limit the rows and columns migrated. However, you will need to be careful that the copied data maintains all the necessary relationships among tables. As with the Export/Import method, this method may require a significant amount of time for large databases.


If you are changing the operating platform at the same time, you can use transportable tablespaces to move the data from the old database to the new database. For very large databases, this method may be faster than the other data-copying methods. See Chapter 17 for the details on transportable tablespaces.

Selecting the proper upgrade method requires you to evaluate the technical expertise of your team, the data that is to be migrated, and the allowable downtime for the database during the migration. In general, using DBUA will be the method of choice for very large databases, whereas smaller databases may use an indirect method.

Before Upgrading

Prior to beginning the migration, you should back up the existing database and database software. If the migration fails for some reason and you are unable to revert the database or software to its earlier version, you will be able to restore your backup and re-create your database.

You should develop and test scripts that will allow you to evaluate the performance and functionality of the database following the upgrade. This evaluation may include the performance of specific database operations or the overall performance of the database under a significant user load.

Prior to executing the upgrade process on a production database, you should attempt the upgrade on a test database so any missing components (such as operating system patches) can be identified and the time required for the upgrade can be measured.

Prior to performing a direct upgrade, you should analyze the data dictionary tables. During the upgrade process to Oracle 10g, the data dictionary will be analyzed if it has not been analyzed already, so performing this step in advance will aid the performance of the upgrade.

{mospagebreak title=Using the Database Upgrade Assistant}

You can start the Database Upgrade Assistant (DBUA) via the


command (in UNIX environments) or by selecting Database Upgrade Assistant from the Oracle Configuration and Migration Tools menu option (in Windows environments). If you are using a UNIX environment, you will need to enable an Xwindows display prior to starting DBUA.

When started, DBUA will display a Welcome screen. At the next screen, select the database you want to upgrade from the list of available databases. You can upgrade only one database at a time.

After you make your selection, the upgrade process begins. DBUA will perform pre-upgrade checks (such as for obsolete initialization parameters or files that are too small). DBUA will then create the SYSAUX tablespace, a standard tablespace in all Oracle 10g databases. You can override Oracle’s defaults for the location and size parameters for the datafiles used by the SYSAUX tablespace.

DBUA will then prompt you to recompile invalid PL/SQL objects following the upgrade. If you do not recompile these objects after the upgrade, the first user of these objects will be forced to wait while Oracle performs a run-time recompilation.

DBUA will then prompt you to back up the database as part of the upgrade process. If you have already backed up the database prior to starting DBUA, you may elect to skip this step. If you choose to have DBUA back up the database, it will shut down the database and perform an offline backup of the datafiles to the directory location you specify. DBUA will also create a batch file in that directory to automate the restoration of those files to their earlier locations.

The next step is to choose whether to enable Oracle Enterprise Manager (OEM) to manage the database. If you enable the Oracle Management Agent, the upgraded database will automatically be available via OEM.

You will then be asked to finalize the security configuration for the upgraded database. As with the database-creation process, you can specify passwords for each privileged account or you can set a single password to apply to all the OEM user accounts.

Finally, you will be prompted for details on the flash recovery area location (see Chapter 15), the archive log setting, and the network configuration. A final summary screen displays your choices for the upgrade, and the upgrade starts when you accept them. After the upgrade has completed, DBUA will display the Checking Upgrade Results screen, showing the steps performed, the related log files, and the status. The section of the screen titled Password Management allows you to manage the passwords and the locked/unlocked status of accounts in the upgraded database.

If you are not satisfied with the upgrade results, you can choose the Restore option. If you used DBUA to perform the backup, the restoration will be performed automatically; otherwise, you will need to perform the restoration manually.

When you exit DBUA after successfully upgrading the database, DBUA removes the old database’s entry in the network listener configuration file, inserts an entry for the upgraded database, and reloads the file.

{mospagebreak title=Performing a Manual Direct Upgrade}

In a manual upgrade, you must perform the steps that DBUA performs. The result will be a direct upgrade of the database in which you are responsible for (and control) each step in the upgrade process.

You should use the Pre-Upgrade Information Tool to analyze the database prior to its upgrade. This tool is provided in a SQL script that is installed with the Oracle Database 10g software; you will need to run it against the database to be upgraded. The file, named utlu 101i.sql, is located in the /rdbms/admin subdirectory under the Oracle 10g software home directory. You should run that file in the database to be upgraded as a SYSDBA-privileged user, spooling the results to a log file. The results will show potential problems that should be addressed prior to the upgrade.

If there are no issues to resolve prior to the upgrade, you should shut down the database and perform an offline backup before continuing with the upgrade process.

Once you have a backup you can restore if needed, you are ready to proceed with the upgrade process. The process is detailed and script-based, so you should consult with the Oracle installation and upgrade documentation for your environment and version. The steps are as follows:

  1. Copy configuration files (init.ora, spfile.ora, password file) from their old location to the new Oracle software home directory. By default, the configuration files are found in the /dbs subdirectory on UNIX platforms and the /database directory on Windows platforms.
  2. Remove obsolete and deprecated initialization parameter from the configuration files. Update the COMPATIBLE parameter for Oracle 10. Make sure your SHARED_POOL_ SIZE parameter is set to at least 96MB for 32-bit platforms and at least 144MB for 64-bit platforms. Set PGA_AGGREGATE_TARGET to at least 24MB, LARGE_POOL_SIZE to at least 8MB, and JAVA_POOL_SIZE to at least 48MB. For Windows parameters, set BACKGROUND_DUMP_DEST to oradatadatabase_namebdump under the Oracle base software directory, and set USER_DUMP_DEST to oradatadatabase_nameudump under the Oracle base software directory. Use full pathnames in the parameter files.
  3. If you are upgrading a cluster database, set the CLUSTER_DATABASE initialization parameter to FALSE. After the upgrade, you must set this initialization parameter back to TRUE.
  4. Shut down the instance.
  5. If you are using Windows, stop the service associated with the instance and delete the Oracle service at the command prompt. For Oracle 8.0, use the command ORADIM80              –DELETE –SID instance_name. For Oracle8.1 and higher, use ORADIM –DELETE –SID instance_name. Then create the new Oracle Database 10g service using ORADIM, as shown here:


    The following variables are available for this command:





    The name of the SID (instance identifier) of the database you are upgrading.


    The password for the new release 10.1 database instance. This is the password for the user connected with SYSDBA privileges. If you do not specify INTPWD, operating system authentication is used and no password is required.


    The maximum number of users who can be granted SYSDBA and SYSOPER privileges.


    The release 10.1 Oracle home directory. Ensure that you specify the full pathname with the -PFILE option, including the drive letter of the Oracle home directory.

  6. If your operating system is UNIX, make sure the following environment variables point to the new release 10.1 directories: ORACLE_HOME, PATH, ORA_NLS33, and LD_ LIBRARY_PATH.
  7. Log into the system as the owner of the Oracle Database 10g software.
  8. Change your directory to the /rdbms/admin subdirectory under the Oracle software home directory.
  9. Connect to SQL*Plus as a user with SYSDBA privileges.
  10. Issue the startup upgrade command.
  11. Use the spool command to log the results of the following steps.
  12. Create a SYSAUX tablespace via the create tablespace command. You should allocate SYSAUX between 500MB and 5GB of disk space, depending on the number of user objects. SYSAUX must be created with the following clauses: online, permanent, read write, extent management local, and segment space management auto. All those clauses except segment space management auto are the defaults. Here’s an example:

    create tablespace SYSAUX
      datafile ‘/u01/oradata/db1/sysaux01.dbf’
      size 500m reuse
      extent management local
      segment space management auto

  13. Run the script for the old release. For example, if you are upgrading from Release 8.0.6, run only the u0800060.sql script and then move on to the next step in the upgrade process. Consult the following table for which script to run.


    Upgrading From:

    Run Script:









  14. Stop spooling (via spool off) and review the spool file for errors. Resolve any problems identified there.

  15. Run the utlu101s.sql file, with TEXT as the input parameter:

    @utlu101s TEXT

    Oracle will then display the status of the upgrade. The upgrade elements should all be listed with a status of “Normal successful completion.”

  16. Shut down and restart the instance. 

  17. Run the utlrp.sql script to recompile invalid packages. You can then verify that all packages and classes are valid:

    select distinct Object_Name from DBA_OBJECTS
      where Status = ‘INVALID’;

  18. Exit SQL*Plus.

  19. Shut down the database and perform an offline backup of the database; then restart the database. The upgrade is complete.


After the upgrade, you should never start your Oracle 10g database with the software from an earlier release.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye