HomeOracle Page 2 - Upgrading to Oracle Database 10g
Choosing an Upgrade Method - Oracle
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).
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.
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.