Upgrading to Oracle Database 10g - Oracle

Oracle Database 10g provides a fairly easy upgrade path for users of older Oracle Database versions. The following versions can directly be upgraded to Oracle Database 10g:

• Oracle Database 8.0.6
• Oracle Database 8.1.7
• Oracle Database 9.0.1
• Oracle Database 9.2

If your database version is not in the preceding list, then you must first upgrade to one of these versions, after which you can upgrade to Oracle Database 10g.

After you are at a supported upgrade level, you can upgrade to Oracle Database 10g by using any one of the following four upgrade options:

• Use the Oracle Database Upgrade Assistant (DBUA). (This was called the Oracle Data Migration Assistant previously.)
  
• Perform a manual upgrade.
  
• Use exp/imp to copy the data in your database to a new Oracle Database 10g database.
  
• Use the SQL*Plus copy command or the create table as select command to copy the data from your current database to your new Oracle Database 10g database.

NOTE: Always back up your database before you start your upgrade!

The DBUA

The DBUA is a GUI that is designed for upgrading your Oracle database to Oracle Database 10g. You will have the option of starting the DBUA from the Oracle Universal Installer (OUI) when installing Oracle Database 10g. DBUA guides you through the upgrade of your Oracle database. You can also start the DBUA at any time in stand-alone fashion (from the command line, just enter dbua) to upgrade your database. From Windows, you can also start the DBUA from the Start menu (either from the Oracle folder or use Start | Run and click dbua). One nice feature of the DBUA is that it will offer to back up your database for you. This feature does have some limited functionality, because backups to removable media are not supported. DBUA supports both normal database upgrades and Real Application Clusters database upgrades.

CAUTION: Oracle Database 10g only supports a direct downgrade back to Oracle release 9.2.0.3 or later. You can use imp/exp (Oracle's import/export utilities), however, to move the migrated database data to other versions of Oracle Database. I strongly advise that you test this method of downgrading on a nonproduction server first, if you plan to use it.

Performing Manual Upgrades

Manual upgrades (my personal favorite) allow you to use a series of scripts and utilities to upgrade your database. The summary steps of manual upgrades include:

• Develop a test plan to run after your upgrade.
  
• Back up your database.
  
• Run the Upgrade Information Tool (UIT), which is a SQL script, utlu10li.sql, located in the directory $ORACLE_HOME/rdbms/admin. This script analyzes your database before you upgrade it and
  alerts you to any problems that might endanger the successful upgrade of your database. If you want your output in XML format, you should
  run utlu101x.sql.
  
• Upgrade the database. Follow the Oracle upgrade instructions for your specific version and operating system. This step includes the creation of the new SYSAUX tablespace, which is new in Oracle Database 10g. (This tablespace is described in detail later in “The SYSAUX Tablespace.”)
  
• Check the component registry (DBA_REGISTRY) to make sure your upgrade was successful.
  
• Back up your new Oracle Database 10g database.
  
• Run your test plan and validate your upgrade.

About the Compatible Parameter

Once you have upgraded to Oracle Database 10g, the compatible parameter can be set no lower than 9.2.0. Thus, if you are upgrading from 8.0.6, you need to set compatible to 9.2.0 before you can open your database under Oracle Database 10g. The Oracle upgrade manual (Oracle 10g Upgrade Guide) provides detailed instructions on setting the compatible parameter. Once you are satisfied that the database can operate under Oracle Database 10g, you can set the compatible parameter to 10.0. Note that, once you set the compatible parameter to 10.0, you cannot set it back. This is different than in previous versions of Oracle. Also note that the command alter database reset compatibility is now obsolete.

There are a number of other possible upgrade issues that you need to deal with depending on the database features that you are using. I strongly suggest that you carefully review the Oracle Upgrade documentation and that you test your Oracle Database 10g upgrades several times before doing one for real in production.

One final upgrade thought—I suggest that you do not use any of the new Oracle Database 10g features in a production environment until you have tested the feature thoroughly. While Oracle does its best to regression-test new features, there are always a few kinks to be worked out in the beginning. If you find a new feature irresistible (and after you read this book, I hope you do!), then by all means try it out. Test it over and over to make sure it works the way it's intended, and that it doesn't have some nasty impacts, like causing performance problems or causing your database to crash. Also, check Oracle MetaLink, and even open an Oracle iTAR, before you use a new feature that will be a prominent part of your design.

Other Upgrade Methods

The use of the Oracle exp/imp utilities is supported for migrating your Oracle database data to Oracle Database 10g. You will use the export utility associated with the version of the database you are currently on (e.g., 8.0.6) to create the dump file. Use the imp utility from the Oracle Database 10g database to import the dump file created for the upgrade. The Oracle upgrade manual provides a complete set of instructions on how to perform this type of upgrade.

Finally, you can use the SQL*Plus copy command or the SQL create table as select command to move your database data to a new Oracle Database 10g instance. Again, the Oracle upgrade manual provides a complete set of instructions on how to perform this type of upgrade.