Roger Schrag, Database Specialists, Inc.
http://www.dbspecialists.com
Abstract
Have you seen the Oracle demo where a member of the audience is called up on stage to click the mouse button that launches an Oracle 8 migration? While salespeople tell you how easy it is to migrate your entire enterprise to the latest Oracle release, a live migration takes place before your very eyes. If you have been an Oracle DBA in the real world, you might be just a little bit skeptical. Rigorous planning and testing are critical in order to minimize risk and down time, and the actual migration process itself is complicated and error-prone. In this presentation we will look at the real life story of how a high-profile, high-traffic internet e-business migrated all of its databases from Oracle 7.3 to Oracle 8i release 8.1.5 and 8.1.6 on Solaris. This company was getting 60 million hits per day, and had database tables over 25 Gb in size. This very technical session will cover the strategy used, the steps followed, and the pitfalls encountered. PS: There was more to it than one mouse click.
Introduction
In 1996 a certain dot-com company developed a family of popular web-based services, using Oracle 7.3 databases running on Solaris on the backend to manage membership, e-commerce transactions, and customer data. The dot-com became successful by all accounts, and by mid 1999 their web site was topping 60 million hits per day. I’ll refer to this company as "Acme" from here on out in order to respect their privacy.
Acme brought me on board in October of 1999 to migrate all databases across their organization from Oracle 7.3.4 to Oracle 8i release 8.1.5 Enterprise Edition--five production databases and a dozen development databases in all. The databases were to be migrated one at a time in order to make the change as gradual as possible.
I developed a detailed plan for the project, along with precise specifications as to how each database was to be migrated in a test environment, validated, and then migrated for real. The three smallest production databases and their corresponding development environments were to be migrated first, using the export/import method. The two larger production databases and their matched development databases were to be migrated last, using the command-line migration utility.
Unfortunately, a bug in Oracle 8i release 8.1.5 went undetected in the testing environment and Acme’s live web site experienced instability after one of the production databases was migrated to Oracle 8i. Acme immediately slammed the brakes on the enterprise-wide migration effort. Ultimately we went to Oracle 8i release 8.1.6 to get stability. As of the writing of this paper, only the three smaller production databases at Acme have been migrated to Oracle 8i. A lack of confidence in Oracle 8i has caused Acme to lower the priority on completing the migration effort.
In the next two sections of this paper I will outline the high level migration plan I developed for Acme, and I will walk through a detailed checklist you might use to migrate your databases. In the last section, I’ll share a laundry list of pitfalls and problems we encountered at Acme. From this paper I hope you will get ideas for how to plan your migration, and perhaps you will benefit from Acme’s experiences when dodging problems along the way.
I need to point out that technology is a fast-moving target. When Acme began migrating to Oracle 8i, the current release was 8.1.5. Partway through the project, 8.1.6 became available. Perhaps an even later release will be available by the time you read this paper. Please keep in mind that all of the information presented here is applicable to Oracle 8i release 8.1.5 Enterprise Edition on Sun SPARC Solaris. Some things might be different with future releases or other platforms.
Acme’s Migration Strategy
Acme needed to minimize the risk of disruption caused by the migration, even if this meant dragging out the project over a period of months. Acme wanted to migrate production databases one at a time, allowing a few weeks between each. Development databases would be migrated immediately after the production database that they mirrored. Before a production database would be migrated, it would be copied to a test server where the migration procedure could be validated and all affected applications could be regression tested against Oracle 8i.
Having settled on a gradual approach, the next step was to choose the order in which to migrate the production databases and the migration method to use for each. I chose to migrate the smallest databases first because in a catastrophic failure situation, smaller databases are faster to recover from a backup. The smallest databases were small enough that an up-to-date backup could be kept available uncompressed on local disk, should a recovery be necessary. The larger databases did not allow this luxury, so I wanted to start small to build up Acme’s confidence in the stability of Oracle 8i and the migration process.
As is the case at many dot-com companies, most of the Oracle databases at Acme had originally been set up by developers pinch-hitting for DBAs. Some of the databases had problems, such as a suboptimal character set or a small block size, that could only be fixed by rebuilding the database. Other problems, such as severe fragmentation and poor segment tablespace assignments, could only be fixed by taking tablespaces offline and reorganizing them.
I saw the migration project as an opportunity to correct many of the mistakes that had been made in the past. By using the export/import method to migrate the smaller databases to Oracle 8i, I could switch these databases to a more appropriate character set and block size, as well as institute a uniform extent sizing approach to eliminate free space fragmentation. The export/import method also has the added feature that the migration could be aborted without having to restore the original database from a backup.
The two larger production databases were too large for the export/import migration method to be feasible. (The down time would have been measured in days.) I planned to benchmark the migration process using the export/import method for the three smaller databases. If the required down time for each database was acceptable to Acme, then I would proceed with the export/import method for these databases. Otherwise, I’d use one of the other migration methods.
Aside from the export/import method, I considered using the command-line migration utility "mig" and the Data Migration Assistant GUI tool. Having used the Database Configuration Assistant GUI tool that comes with Oracle 8i, I immediately ruled out the Data Migration Assistant as a viable option. I did not feel comfortable betting Acme’s business on a GUI tool that might crash, suppress error messages I should be aware of, or otherwise do the wrong thing.
By being clever and preprocessing as much work as possible, I found that I could migrate each of the smaller databases using the export/import method with just under one hour of down time. Acme was able to live with this timeframe.
So, Acme’s migration strategy from a 30,000 foot view was to migrate production databases one at a time smallest to largest, the smallest ones by the export/import method and the largest ones by the command-line migration utility.
The Migration Steps
In this section we will look at the steps required to migrate an entire enterprise to Oracle 8i. First we’ll look at the phases of the migration--the high level steps. In the latter part of this section we’ll walk through detailed steps for how you migrate individual databases to Oracle 8i. We’ll look at the steps using both the export/import method and the command-line migration utility. The information presented here is based on my experience migrating Acme’s databases to Oracle 8i.
Phases of the Migration
The Acme migration consisted of the following phases:
Migrating a database to Oracle 8i is complicated and involves many steps that must be done exactly right. I developed thorough documentation for Acme detailing how to carry out the migration of each database. I validated the documentation by migrating a copy of each database in a test environment, following the documentation to the letter. If something was stated incorrectly or if I determined that a step was missing from the plan, I updated the documentation immediately.
Migrating a copy of the database in a test environment allowed me to practice the migration, determine how much down time would be required, and avoid nasty surprises when migrating the live database. This also allowed the quality assurance group to test the applications that interact with the database in order to make sure everything would work the same after the database had been migrated to Oracle 8i.
As soon as a production database was migrated to Oracle 8i, I resynchronized all development and test copies of that database with production and thereby brought them up to Oracle 8i. This ensured that applications were being coded and tested against the same version of Oracle used in production.
I planned to take a three week breather between production database migrations. This gave Acme time to watch for stability and compatibility problems, and it gave me time to prepare for the next migration. Since I was also the lead production support DBA for Acme, the hiatus between migrations allowed me to catch up on my other duties.
Migration Using the Export/Import Method
To migrate an Oracle 7.3 database to Oracle 8i using the export/import method, you first follow these preparation steps:
At this point you are prepared to migrate the Oracle 7.3 database to Oracle 8i "for real." The steps for the live cutover are as follows:
Migration Using the Command-line Utility "mig"
To migrate an Oracle 7.3 database to Oracle 8i using mig, you first follow these preparation steps:
SELECT A.next_extent, A.max_extents, B.optsize
FROM SYS.dba_rollback_segs A, v$rollstat B
WHERE A.segment_name = 'SYSTEM'
AND B.usn = A.segment_id;
migprep <8i home> <7.3 home>
mig CHECK_ONLY=TRUE
This will spew a bunch of SQL on the screen, with an estimate of SYSTEM space required at the end. mig might exit with a non-zero exit code. On Unix systems this usually indicates an error condition, but should be ignored here. The mig utility might also leave the database open. If so, shut it down again with normal priority.
mig DBNAME=<name> NEW_DBNAME=<name> PFILE=\"<path>\" SPOOL=\"<path>\"
Note that the backslashes before the quotes are required. The path provided for the PFILE parameter should be the full path and filename of the parameter file used by the Oracle 7.3 instance to open the database. This operation takes only a few minutes, and generates a file called convSID.dbf in the dbs directory under the Oracle 7.3 home . A huge amount of output is written to the screen, but all of it appears to be captured in the spool file as well.
SPOOL <path>
CONNECT INTERNAL
STARTUP NOMOUNT
If you get errors when trying to start the instance, then examine the parameter files again for parameters that are obsolete in Oracle 8i.
ALTER DATABASE CONVERT;
ALTER DATABASE OPEN RESETLOGS;
SET ECHO ON
@u0703040.sql
@utlrp.sql
SHUTDOWN NORMAL
SPOOL OFF
EXIT
The two ALTER DATABASE commands should only take a few seconds. The u703040.sql script rebuilds the data dictionary catalog views and could take roughly half an hour to run. The utlrp.sql script simply recompiles all invalid stored PL/SQL objects. This is handy because the migration process invalidates everything.
SELECT index_name, index_type, table_owner, status
FROM dba_indexes
WHERE index_type = 'BITMAP'
AND status = 'UNUSABLE';
At this point you are prepared to migrate the Oracle 7.3 database to Oracle 8i "for real." To perform the live cutover, continue on from step 11 above on the database server where the production database to be migrated resides.
Migration Pitfalls Encountered Along the Way
The enterprise-wide Oracle 8i migration at Acme went reasonably well, but it was anything but smooth. We discovered plenty of "gotchas" and unpleasantries along the way. Thankfully, most of the problems were discovered during the planning and testing phase of the migration and we were able to resolve them before they could impact production.
Unfortunately, one serious problem did sneak through testing and was only discovered after mission-critical production databases had been migrated to Oracle 8i (release 8.1.5 Enterprise Edition on Sun SPARC Solaris, to be precise). A trivial INSERT statement in a stored procedure appeared to tip off a memory leak of sorts within the SGA of the database instance.
Each time the INSERT statement ran--and it ran a few times per second--the sharable memory in the shared SQL area attributed to the INSERT statement would grow by a few bytes. This would continue on until the INSERT statement had consumed over 50 Mb of memory in the shared SQL area. Gradually Oracle would start spending more and more CPU time managing objects in the shared SQL area because less and less space was available for other statements. At a certain point the database would become unusable because the most basic queries would hang for several minutes waiting on a "library cache pin" wait event while Oracle tried to make space in the shared SQL area.
Calls to Oracle Support were useless. After analyzing Acme’s bstat/estat reports and trace files for six days, an Oracle support analyst sent me email suggesting that we (1) make sure sql_trace is not turned on for the entire instance, and (2) consider adding an index to the table to speed up INSERT statements.
I kid you not.
So Acme experienced everybody’s worst upgrade nightmare. They migrated to Oracle 8i and a production system that was stable on Oracle 7.3 suddenly became unstable. Oracle Support did not stand behind the product, and Acme was left with little choice but to restart Oracle instances every few days. Seeing no other option, I recommended that Acme upgrade to Oracle 8i release 8.1.6 as soon as it became available. This indeed fixed the problem.
In the remainder of this section I’ll point out, in no particular order, some of the migration difficulties I discovered during the planning and testing phases at Acme and how I resolved them.
NLS Issues
Oracle changed the format for its NLS data files between version 7.3 and 8.0 of the Oracle client. This means that if you have an application that was linked with Oracle 7.3 libraries, you may have trouble running the application in an Oracle 8.0 or Oracle 8i home. If you use the US7ASCII character set in your database and on your client, this issue will probably not apply to you.
However, if you use any other character set in the database or on the client, you will likely encounter an ORA-12705 error when you run your application linked with Oracle 7.3 libraries from an Oracle 8.0 or Oracle 8i home. There are several ways to deal with this problem.
You could choose to retain an Oracle 7.3 home and have your applications run from this environment, connecting to your Oracle 8i database via SQL*Net and Net8. This is probably the easiest solution, but it requires that you keep older versions of Oracle software around and it prevents your applications from leveraging new Oracle features. If your applications are third-party tools, this approach might be your only option.
If you have the source code to your applications, you can relink them with the Oracle 8i libraries. This will break the dependency on Oracle 7.3 NLS files right away. Ideally you would enhance your applications to leverage new Oracle 8i features and OCI calls, but this could be done gradually over time.
If your plan is to ultimately relink all of your applications with Oracle 8i libraries but you are not able to do this immediately, you can run your applications from the Oracle 8i home, but retain the Oracle 7.3 NLS data files and set the ORA_NLS32 environment variable to point to the Oracle 7.3 NLS files. This tactic allows you to work from an Oracle 8i home without breaking your older applications. This allows you to relink or enhance your applications gradually over time.
Interoperability Issues
One of the nice features of the Optimal Flexible Architecture (OFA) is that if you install Oracle software on your database server in an OFA compliant manner, it is easy to run multiple databases on different versions of Oracle. If you have two Oracle 7.3 databases on one server, for example, you have the option of migrating them to Oracle 8i one at a time.
Unfortunately, at Acme I discovered a few hiccups when trying to have Oracle 8i interoperate with other versions of Oracle on the same database server.
The dbstart script that comes with both Oracle 8i release 8.1.5 and 8.1.6 has a bug in it that will cause it to skip Oracle 7.3 databases instead of starting them. If you had a database server with an Oracle 7.2 database, an Oracle 7.3 database, an Oracle 8.0 database, and an Oracle 8i database, you would find that Oracle 8i’s dbstart script would start all of the databases except for the Oracle 7.3 database.
Basically, there is a case statement in the dbstart shell script that decides whether to use sqldba, svrmgrl, or sqlplus to start the database. If there is no sqldba executable present in the bin directory of the Oracle home, dbstart invokes svrmgrl and looks at the version of PL/SQL installed. If the version is 7.3 or 8.0, then svrmgrl is used to start the database. If the version is 8.1, then sqlplus is used to start the database. If the version is anything else, then the database is not started. On Oracle 7.3 databases, the PL/SQL version shows as 2.3. (Oops!)
At Acme we also discovered a Net8 connectivity problem between Oracle 8i release 8.1.5 and Oracle 8i release 8.1.6. Typically when you have multiple versions of Oracle installed on one database server, you run the Net8 listener from the Oracle home of the newest Oracle version. If you run an Oracle 8i release 8.1.6 Net8 listener, however, you may find that you cannot connect to Oracle 8i release 8.1.5 databases via Net8.
Oracle has documented this behavior in technical support bulletin 95398.1 dated January 20, 2000. The work-around is simple: Don’t set the LD_LIBRARY_PATH environment variable when starting the Net8 listener--even though the documentation tells you to set LD_LIBRARY_PATH to the lib directory under the Oracle home.
Execution Plan Stability
Oracle Corporation is constantly refining the algorithms used by the query optimizer, and so it should not be surprising to find that execution plans on SQL statements might change after migrating a database to Oracle 8i. While some execution plans might change for the better, it is possible that some will change for the worse. This is one of the reasons that exhaustive application testing before migrating a production database to Oracle 8i is important.
At Acme four of the five production databases run with cost based optimization. We found that one application suffered significant performance degradation at the hands of the Oracle 8i cost based optimizer. On Oracle 7.3 the application took 84 minutes to complete, but on Oracle 8i the execution time shot up to 44 hours. We nudged the Oracle 8i optimizer down the right path by embedding optimizer hints in the main query.
Silly Bugs and Annoyances
Oracle 8i has a few bugs that are more embarrassing for proponents of Oracle technology than real production problems. For example, when you perform a "typical" Oracle 8i installation, the global name of your starter database will be "java8.us.oracle.com". This is in spite of the fact that the installer specifically asked you what you would like the global name for the database to be. This can be fixed quickly with an ALTER DATABASE RENAME GLOBAL_NAME command.
A potentially more irritating bug occurs when you operate an Oracle 8i database in archivelog mode. Apparently a developer at Oracle added a nice feature to allow DBAs to trace and tune archiver operation. This would be really great, except that you can’t turn it off. Oracle has recognized this as bug number 993914, and it is fixed in Oracle 8i release 8.1.6. If your database generates lots of archived redo logs and you will be migrating to Oracle 8i release 8.1.5, then you’d better make sure you have lots of disk space free for trace files and a fat alert log.
In the documentation bug department, the v$option dynamic performance view can be confusing or misleading. v$option lists Oracle options (such as partitioning and spatial) and whether or not they are available. What the documentation doesn’t tell you is that v$option is only showing you whether or not the necessary Oracle software to support a given option has been installed in the Oracle home that was used to start the instance--v$option does not address whether the necessary schemas and objects have been created in the database to enable the option. After migrating an Oracle 7.3 database to Oracle 8i, for example, you may see that you have options such as spatial and Java available when in fact these options will not work against the database. You need to run the Database Configuration Assistant to create the necessary database objects before these options can be used.
Conclusion
It is definitely possible to migrate all of the Oracle databases across your enterprise to Oracle 8i. (Of course it is! Lots of companies have done it.) However, there are many issues to consider and many land mines that you want to avoid. As when making any significant change to a complex system, the more planning and testing you perform beforehand, the better your chance of success.
I cannot emphasize enough the importance of planning your Oracle 8i migration, testing the plan thoroughly, and sticking rigidly to the plan when performing the live cutover. This approach helps ensure success in three ways: First, you’ll make sure you know how to perform the migration before you actually touch a production database. Second, you’ll discover Oracle 8i bugs or incompatibilities before they affect your live systems. And third, all of the practice will help you carry out the live cutover with as little down time as possible.
I hope that the experiences and tips I’ve shared here will help you plan and carry out your Oracle 8i migration smoothly and cleanly. Good luck!
About the Author
Roger Schrag has been an Oracle DBA and application architect for over twelve years. He started out at Oracle Corporation on the Oracle Financials development team and moved into the roles of production DBA and database architect at various companies in the San Francisco Bay Area. Roger is a frequent speaker at Oracle OpenWorld and the IOUG Live! conferences. He is also vice-president of the Northern California Oracle Users Group. In 1995, Roger founded Database Specialists, Inc., (http://www.dbspecialists.com) a consulting firm specializing in business solutions based on Oracle technology. In addition to consulting, the company offers flexible solutions including part-time DBA support and Database Rx (http://www.dbspecialists.com/database_rx.html), a web-based monitoring and alert notification service for Oracle databases. In 2001, the San Francisco Business Times named Database Specialists one of the Top 150 Fastest-Growing Private Companies in the Bay Area.