Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Blogs » Chris Foot Blog » Chris Foot's Oracle10g Blog » Migrating Data to Oracle10G Part 2 - A Comparison of Upgrade Tools and Procedures.
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3606
 

Migrating Data to Oracle10G Part 2 - A Comparison of Upgrade Tools and Procedures. Migrating Data to Oracle10G Part 2 - A Comparison of Upgrade Tools and Procedures.

In Part 2 of our series on upgrading databases to 10G, we review the different upgrade mechanisms available to us: Export/Import, manual upgrade, data copy and the Database Upgrade Assistant (DBUA). I'll finish by providing you with some hints and tips that will help you choose the most appropriate upgrade method for your environment.

Let me start this discussion by stating that I won't be telling you how to perform the upgrade.  If you have been reading my past blogs, you know that I am a firm believer in following the manual like a cookbook when installing and upgrading Oracle databases.    I will be providing you with hints and tips to help you along the way but the primary source of information should be from the Oracle documentation.

Don't assume from the above rant that I am against all third-party documentation.  Hey, I wrote a book myself!  Third-party books are an excellent source of information. The big advantage third-party books have over the technical reference manuals is that they are able to quickly deliver the information that most of us feel is important. Technical reference manuals must provide all of the information on the entire Oracle environment while third-party books are able to focus on just what the author felt was important.

Instead of listing books, I'll list my favorite authors. You'll have the best chance of buying a great book if you select one from the following authors: any of the experts from TUSC, Don Burleson, Michael Ault, Michael Abbey, George Koch, and Kevin Loney. But, I'm digressing from the topic at hand so let's get back to our discussion on upgrade mechanisms.

The migration manual provides the DBA with several different methods to perform the upgrade.  Let's take a quick look at some of the alternatives that Oracle recommends:

Data Copy
Ok, I must admit, I have NEVER used this method of migrating data to a newer release.  The first step required would be to duplicate the entire database being converted to Oracle10G.   Everything but the data would have to be cloned to the new environment.  I would create the 10G database using the Database Configuration Assistant SGT (Sissy GUI Tool) and use Export/Import to clone all of the data objects, programs, users and security to the new environment.  The next step would be to create database links between the two databases to enable the data transfer.    The two primary tools I would recommend to copy the data to the 10G database would be the SQL*Plus COPY command and the INSERT INTO… SELECT FROM SQL statements. 

Export/Import
Using Export/Import to perform database upgrades can be described as follows:  the utility has been around forever (I have been using it since Version 6), it is very reliable and it is very, very slow.  Export/Import is often the only method that allows the DBA to migrate databases from very old releases to more current versions.  The DBA uses the Export utility to migrate the database data to a flat file from the old release and uses the Import utility to load the data into the database created using the new release's binaries.   The DBUA only allows direct upgrades from a handful of previous releases.   If you are not on versions 8.0.6, 8.1.7, 9.0.1, and 9.2.0 (and you don't want to perform a double upgrade), Export/Import may be your only alternative.

Export/Import is not the preferred method of upgrading large databases due to its slow performance and additional disk space requirements for the Export's output files and duplicate database data files. Since the database is duplicated, switching back to the old release is simple and straight-forward.  Unlike its migration utility counterpart, Export/Import can be used to move the database to a new server or operating system during the migration process. 

Like the data copy method, I would create the new 10G database using the Database Configuration Assistant and use the Export/Import utilities to transfer the data, data structures, programs, users and security from the older database to Oracle10G.

One last thing to consider, the Import may set the status column of stored PL/SQL programs and views in DBA_OBJECTS to 'INVALID'.   Have a script ready that recompiles all invalid stored PL/SQL programs to validate them.  Because of object dependencies, you may have to run the script several times before all invalid objects are successfully recompiled.   Oracle provides a script in $ORACLE_HOME/rdbms/admin called utlrp.sql which recompiles invalid objects.

Manual Upgrade
The database administrator manually executes all of the steps that the Database Upgrade Assistant performs automatically.   Is my preference for DBUA starting to show through a little?  Oracle recommends that the DBA begin the manual upgrade process by running a script in $ORACLE_HOME/RDBMS/ADMIN called UTLU101I.SQL.  This script, which is automatically run by DBUA, evaluates your current environment and details the changes required to ensure a successful upgrade to Oracle10G.

Instead of me regurgitating the information provided by these scripts, I have provided two output files created by UTLU101I.SQL.  The first file 92to10g.txt is the output from a 9.2 database evaluation and 817to10g.txt is the output from an 8.1.7 evaluation.  You'll see that the script is very thorough, providing you with all the information you need to ensure a successful upgrade.  

After running the assessment script, there are about 25 more steps listed in the Oracle10G Database Upgrade Guide Documentation that you'll need to perform to complete the upgrade.  Did I tell you that the Database Upgrade Assistant performs these steps for you automatically?

Administrators performing the upgrade must then run a few SQL scripts to perform the actual database conversion.  The script that is run depends upon the release you are migrating from. For example, you would run script u0902000.sql to upgrade a database from Oracle Version 9.2 to Oracle10G.

 At the end of the steps, you can run UTLU101S.SQL to verify that the upgrade completed successfully. 

 Here is an example of the output from UTLU101S.SQL after I upgraded my desktop database:

Oracle Database 10.1 Upgrade Status Tool 13-JAN-2005 11:21:34
--> Oracle Database Catalog Views       Normal successful completion
--> Oracle Database Packages and Types  Normal successful completion
No problems detected during upgrade
PL/SQL procedure successfully completed.

Database Upgrade Assistant
The migration utility has matured over time to where it has become one of the preferred methods of performing database upgrades   The utility walks the DBA through the migration process which reduces the possibility of user error.  Because the utility does not require additional disk space to perform the migration, databases of any size can be easily migrated.   One of the drawbacks is that the DBA is unable to move the database to a new server or operating system while performing the migration.

The tool automatically executes the majority of steps you are required to run to perform the upgrade. It automatically modifies or creates tablespaces, disables archiving during the upgrade phase, executes the appropriate upgrade scripts, runs ORADIM for Windows platforms, updates the listener and locks all new accounts to ensure the data remains secured.  In addition, DBUA asks you if you want it to back up your database before it performs the upgrade and it will recompile your invalid objects in parallel at the end of its upgrade processing.

DBUA records informational, warning and error messages in log files to help you debug any problems that may occur during the upgrade process.  In addition, the tool will also produce a HTML report that provides detailed information on the upgrade that can be viewed at your leisure. 

 The upgrade utility allows you to upgrade databases automatically using silent mode.  I have never done an upgrade in batch but I would like to hear comments from the folks who have.   So, if you performed automatic upgrades, please respond to this blog with a comment. 

Evaluating the Upgrade Tools
We'll finish our discussion on data migration with a listing of benefits and issues that are inherent to each of the upgrade choices available:

Data Copy

  • Simplifies and speeds the failed upgrade recovery process.  The source database is still available for use if the upgrade process fails.
  • Database, tablespace and data object structures must be precreated.
  • Administrators are able to to migrate only the data they want.
  • Precreating data objects allows you to reduce the number of extents per object and defragment files during the upgrade process.
  • Provides the ability to restructure the database during the upgrade.
  • All data being migrated will be duplicated.


Export/Import

  • Simplifies and speeds the failed upgrade recovery process.  The source database is still available for use if the upgrade process fails.
  • Database, tablespace and data object structures must be precreated.
  • Setting the COMPRESS=Y flag in the Export utility allows you to reduce the number of extents and defragment the data.
  • Creating a duplicate database will require double the amount of space.
  • Performance can be an issue.
  • Allows administrator to import selected user objects.
  • All data being migrated will be duplicated.
  • Provides administrators with the ability to upgrade from releases not supported by the DBUA.  So, if you aren't running 8.0.6, 8.1.7, 9.0.1, or  9.2.0 and you don't want to do a double upgrade,  this is the only alternative you have.
  • Database can be upgraded AND migrated to a different platform/operating system combination in one, single step.   

If you go to the 2004 Oracle Open World presentations now on technet.oracle.com, make sure you take a look at presentation numbers 1127 and 1163.  In presentation 1163, the author describes how they used Oracle's transportable tablespace feature and not Export/Import to upgrade from 8I to 10G and migrate data from HP/UX to Linux at the same time.  As one of my favorite developers has a habit of saying "this sounds like crazy-talk."  But the authors felt it was faster to upgrade the source database to 10G then transfer the database data to Linux by using the transportable tablespace feature.


Manual

  • Upgrade backoff process is more complex than Data Copy and Export/Import upgrade methods.  You don't have a duplicate database to fall back to if a catastrophic upgrade failure occurs.
  • Abundant number of manual steps required makes upgrade process more susceptible to human error.
  • Provides a finer control over the upgrade process.
  • Database can't be upgraded AND migrated to a different platform/operating system combination in one, single step.   
  • Since the data is not duplicated, less space is required to perform the upgrade.


DBUA

  • Backoff process is more complex than Data Copy and Export/Import upgrade methods.  You don't have a duplicate database to fall back to if a catastrophic upgrade failure occurs.
  • Automates the upgrade process, which reduces both the amount of human intervention involved, and the resulting number of human errors.
  • This DBUA performs most of the steps in rapid succession.  Much faster than a database administrator would be able to.
  • Database can't be upgraded AND migrated to a different platform/operating system combination in one, single step.   
  • Because you don't duplicate data, this is the preferred method for migrating large data stores to Oracle10G.
  • Since the data is not duplicated, less space is required to perform the upgrade.


Next Up
In my next blog, I'll provide you with a structural description of the upgraded data warehouse that we will be using to perform our final 10G testing.   In addition, I'll highlight some of the tests we will be performing to ensure there won't be any surprises when we go live with our first 10G multi-terabyte data warehouse.
 


Saturday, January 15, 2005  |  Permalink |  Comments (0)
trackback URL:   http://www.dbazine.com/blogs/blog-cf/chrisfoot/10gconversionparttwo/sbtrackback
 

Powered by Plone