Migrating Data to Oracle10G Part 1 - a Review of Database Upgrade Best Practices and an Introduction to the Database Upgrade Assistant SGT (Sissy GUI Tool)

My Oracle career now spans two decades (give or take a year or two).   During this time, I have upgraded my fair share of databases.  Actually, I have had the good fortune of upgrading databases to Oracle Versions 6, 7, 8, 9i and now 10G.   When I was working as the technical lead for a remote database consulting company, I made a semi-career out of coordinating and performing database upgrade activities with our many customers.   We offered database upgrades for free if potential customers chose our remote database administration services.   Believe me, our customers took full advantage of that free upgrade policy.  I think you could safely estimate that I have been involved in dozens and dozens (and dozens) of database upgrades.   

There are two activities that can make any DBA lose sleep - the database recovery and the database upgrade.   It is a simple process to become confident in both of them.   To build your recovery skills, you create a recovery database and restore it again and again and again.   Then verify your backups are actually working on a regular basis, ensure your tape retention policies are what you think they are and you are well on your way to achieving "Recovery Zen" (at least database-wise).

Like database recoveries, database upgrades become easier as you gain experience with them.   Once you get one or two (or a ½ dozen) under your belt, the rest become easier.   The first upgrade to a new release will always be a little nerve wracking.   Remember, the more prepared you are - the more confident you will be.     

Helpful Hints and Tips
The following recommendations will help increase your chances of success:

• If you have the luxury of choosing the first database to upgrade, choose one that is small, easy to recover, not mission critical and has an adequate outage window.   Choosing an Electronic Funds Transfer or a Financial Application for your first upgrade instead of your unit's internal vacation database could be a mistake. 
• Sell the benefits that 10G has to offer to management.   10G has dozens of new features so it should be easy.    Let's face it; the database upgrade process is costly and to be quite frank, a little dangerous.   You'll need their buy in to push the upgrade process through the organization.
• Sell the reasons why this upgrade is important to the application development teams.  These are the folks that will be spending the numerous hours required to test their application.   I know of few organizations that have developers with enough free time to test a new release without impacting other projects.   Understand that they have production deadlines that also must be met.    If you have many different databases to convert (like we do), create a conversion strategy that takes application workload into consideration.   Juggle the application conversion sequence until you come up with a set of conversion dates that all can agree upon.  Lastly, don't take it personal (as I used to) when an application team is forced to change a conversion date.   Rest assured, they don't usually dream up ways of making you miserable.  They are being driven by the business.
• Read the migration manual and follow it like a cookbook.  Scour the web for information on the upgrade process and potential pitfalls.  Start with metallink.oracle.com and technet.oracle.com and then do some Googling.  Currently, Technet has the the 2004 Oracle Open World Presentations available for downloading.  A few of the presentations focus specifically on the 10G upgrade process.  In addition, don't forget to visit the various database discussion forums.  One thing about the DBA community - we are a helpful bunch and always willing to give advice.  Choose it wisely.
• Hope for the best but prepare for the worst-case scenario.  When reading the migration manual, pay special attention to the common migration problems and back-off procedure sections.
• You'll need to determine the upgrade path required.  Oracle supports a direct upgrade to Oracle10G from versions 8.0.6, 8.1.7, 9.0.1, and 9.2.0.   Upgrades from other releases require upgrading to an intermediate release before migrating to 10G.   For example, a database on release 7.3.4 would first be upgraded to 9.2.0, and then to 10.1.0.
• Create an upgrade database and convert it before you upgrade a test database that is used by application developers.   You can fight through any conversion errors at your leisure. Use this conversion database to test the latest, high-tech features to ensure they work as advertised (and they don't crash the database).
• Create a migration document that includes checklists, application test plans, back-off procedures and business user contacts.  Include a step-by-step description of the activities that will be executed during the migration process.   Contact information for everyone involved in the migration should also be included in the migration document.
• Perform the upgrade in test and ask the developers to run a complete test of the applications that access the database being upgraded.  A formalized test plan should be created that tests application functionality, compares application performance to the old release and evaluates any new features that the application will be utilizing in the near future.
• Create application developer and business user sign off documents.  Once the developers and business users have completed their testing, the documents will be signed and the initial migration process will be complete.  I have been burned once or twice by applications that were inadequately tested.
• Schedule a meeting with operating system administrators, application developers, operations personnel and business users to discuss the migration process.  Discuss and verify the contents of the turnover document.   This meeting will also ensure that everyone understands what is expected of him or her during the migration process.
• A cold database backup of all datafiles, control files, redo logs, parameter file and password file will provide the foundation for successful database restoration.

Up Next - The Database Upgrade Assistant (DBUA)
I have been burned by Oracle's "automatic" upgrade utilities several times in the past.   Although I am an OEM afficianado, I have never been a big fan of other Sissy GUI Tools that Oracle has provided in the past.   As a result, I took a very cynical approach during my DBUA evaluation.   I spent a great deal of time reading and evaluating DBUA problems on Metalink and scoured the internet for all of the information I could find on DBUA.  I have talked to Oracle personnel and users that have used DBUA numerous times.  I have personally reviewed close to two dozen upgrades by folks that have used DBUA to perform the upgrade.

So, I am now forced to admit that I am quite fond of the DBUA (even though it is still a Sissy GUI Tool).  The Database Upgrade Assistant is so much more robust than previous upgrade tools that it has made a believer out of me.  It is smarter, more user-friendly and, best of all,  it works.   In the next blog, we'll take an in-depth look at all of the different upgrade mechanisms available for us to choose from.