Oracle on Linux tips, tutorials and expert advice

Installing and upgrading Oracle on Linux <<previous|next>>

Oracle database export, import and migration

Upgrading Oracle database and Solaris platform

Upgrading Oracle database and Solaris platform

By  Maria Anderson, Production DBA, Autodesk

SearchOracle.com

The main goal is to upgrade an Oracle DB (9.2.0.6 Enterprise Edition) on a Solaris 8 host to an Oracle 10gR2 EE database on a Solaris 10 host. From this column, I believe a good first step is to install Solaris 10 on the new host and then install DB 10gR2 on the Solaris 10 host. What next? Seems there are a few options and I am having trouble sorting through them. The environment is 24x7, so minimal downtime is a very high priority. After I install the 10gR2 DB, can I migrate the 9.2.0.6 DB to 10gR2 using Streams with zero downtime? Does the DBUA work across hosts, and what is the downtime? Would it be better to upgrade to 10gR2 on the Solaris 8 host and then migrate to the Solaris 10 host? Can Export/Import be used in conjunction with Synchronous Streams to replicate the DB on the Solaris 8 host to 10gR2 with zero downtime? Thanks for any help you may provide.

If your environment must be available 24x7, this will narrow down your options for migrating your Oracle database. I have not used Streams to perform an upgrade but, since it is not mentioned in the Oracle Database Upgrade Guide 10g Release 2 (10.2), I would be tempted to open a TAR with Oracle Support and ask whether this is a supported migration method.

Basically, your options are as follows:

  • Create a new, empty database on the new Solaris 10 host with all the relevant application data and index tablespaces. Export the relevant schemas (or the entire Oracle 9.2) database and then import it into the newly created 10g database on the new host.
  • Create a new, empty database on the new Solaris 10 host. Make sure you create all the relevant tablespaces, as well as any userids and roles. Create a database link from the 10g database to the 9.2 database and use CTAS (create table as select) commands to bring over the data. After this is completed, you must also create any relevant indexes, as well as other objects such as function, procedures, sequences, etc.

Both of these methods will require some downtime, possibly a significant amount of downtime if the database is large.

A final option is to install the Oracle 10g database software on the Solaris 8 host where the database currently resides and upgrade the database in place to 10g. Whether this option is viable depends on several factors, one being whether this database server has the required system resources to run a 10g database. This option may offer less downtime than the two options above. It will be difficult to find a method for migrating your database that offers true zero downtime. There may be some third-party products that offer this capability, so it might be worth asking other DBAs in your community whether they have researched any third-party products that offer this.

25 Jan 2007