Migrating to Oracle 8i in the Real World: A Senior DBA’s E-business Experience

Roger Schrag, Database Specialists, Inc.


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.


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:

  1. Thoroughly document the migration plan, including detailed database migration steps
  2. Copy one production database to a test environment and migrate, following the documented steps
  3. Regression test all applications against the migrated database
  4. Migrate the production database
  5. Resync development and test copies of the production database
  6. Monitor the system for a few weeks for problems caused by the migration
  7. Repeat the above steps for each production database

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:

  1. Install the Oracle 8i software on the database server. You should develop a procedure for Oracle software installation and follow it rigorously in order to standardize the Oracle setup on all of your database servers. You don’t need to have Oracle create a starter database for you at this point. If you choose to, however, take precaution to ensure that Oracle does not start up an Oracle 8i Net8 listener on the same port as your existing SQL*Net listener.

  2. Use the Database Configuration Assistant to create scripts for creating a new database. You could just go with the starter database if you wish, but I found the starter database to be poorly configured. I found it more practical to edit the scripts created by the Database Configuration Assistant once and then use these scripts repeatedly for each database I needed to create. This leads to less tedium and more consistency across databases. Here are some of the configurations you’ll need to edit:

  3. Run the scripts to create your new Oracle 8i database. If you will be creating the Oracle 8i database on the same database server where the current Oracle 7.3 database resides, then give it a different name from the existing Oracle 7.3 database. Add an entry to the oratab file for this new database.

  4. Copy the parameter files from your Oracle 7.3 database to the new Oracle 8i database. Edit as required to account for different path names, different instance and database names, and the fact that certain parameters have changed between Oracle 7.3 and 8i. Here are some parameters that are obsolete in Oracle 8i that you should watch for:

  5. If you use tnsnames.ora files, then copy an up-to-date tnsnames.ora file to the network/admin directory in the new Oracle 8i home. Replace the tnsnames.ora files in other Oracle homes with a link to the file in the Oracle 8i home. This keeps all tnsnames.ora files in sync with each other. Also, add a temporary entry for the new Oracle 8i database.

  6. Configure the Oracle 8i Net8 listener to listen for all databases on the server. Shut down the SQL*Net listener and start up the Oracle 8i Net8 listener. Confirm connectivity to all databases on the server.

  7. If you have customized the environment scripts oraenv and coraenv, then copy these customizations to the bin directory in the Oracle 8i home. If you have executables linked with Oracle 7.3 libraries that will need to access an Oracle 8i database from an Oracle 8i home, then ensure that your ORA_NLS32 environment variable is set to point to the NLS data files area of your Oracle 7.3 home. (We’ll talk more about this in a later section of the paper.)

  8. Your database server now has multiple versions of Oracle software installed on it. It is important to use oraenv and coraenv--and not simply hard code environment variables--in order to ensure that all necessary environment variables are set correctly when switching between Oracle homes.

  9. Test connectivity to your new Oracle 8i database. You should be able to connect to it without using networking (by setting ORACLE_SID) and also through Net8 from the Oracle 8i home and through SQL*Net from the Oracle 7.3 home.

  10. Prepare a script to take a direct path export of the Oracle 7.3 database. You want this to run as quickly as possible, so you may wish to compare performance writing to different physical disk devices, and you may wish to experiment with writing directly into a compress or gzip process via a named pipe. Note the elapsed time and disk space required for the export.

  11. If you are unhappy with the tablespace assignments or extent sizes of existing objects in your Oracle 7.3 database, then you can reorganize your database while migrating it. If you wish to do this, then prepare a SQL*Plus script to run on the Oracle 8i database that will precreate tables and the users that own them. You should not precreate indexes or constraints, as these will dramatically slow down the import process and lead to suboptimal indexes. Instead, you should let the indexes and constraints import into the wrong place and use the ALTER INDEX ... REBUILD statement later to improve them.

  12. Prepare a script to import the Oracle 7.3 export file into the Oracle 8i database. Unless your database is very small, you should use the commit=y import option. If you will be precreating tables to improve placement or extent sizes, be sure to specify the ignore=y option as well.

  13. Run the Oracle 7.3 export script, Oracle 8i object creation script (if you prepared one), and the Oracle 8i import script. Note the execution time of each. Troubleshoot any errors that occur. If error messages do occur and are expected, then document them so that you will know to expect them when performing the live migration.

  14. If you have access to a tool that can compare two database schemas, then run a comparison between your Oracle 7.3 and Oracle 8i databases to make sure no objects were lost in the export/import process.

  15. Examine the system, role, and object-level privileges of your database users to make sure they were preserved correctly when importing into the Oracle 8i database. This is especially important if you manually created users in your Oracle 8i database so that you could precreate tables before importing.

  16. If you are using a character set other than US7ASCII, look at your data in the Oracle 8i database to make sure no accidental character set translation has occurred. If you have multi-byte or 8-bit characters in your data, then query them from the Oracle 8i database to make sure they were not garbled in the export/import process.

  17. You are now ready to regression test your applications against the Oracle 8i database. Ideally, you should perform an exhaustive test of every module in your application. At the very least, it is imperative that you test a sample of application modules that covers every application technology that must access the database. Test your backup procedures and monitoring infrastructure against the Oracle 8i database as well. Do not move beyond this step until all testing is complete and any discovered issues have been fully resolved.

  18. If the database to be migrated to Oracle 8i is involved in replication in any way, you will need to evaluate how the migration will impact the replication process and your other databases. For example, if the database you are migrating contains masters for fast-refresh snapshots based on ROWIDs, then the migration will invalidate the snapshot logs and you will need to perform a complete refresh. (If the database containing the snapshot is an Oracle 8.0 or 8i database, you may wish to switch to primary key snapshots at this time.)

  19. Review the benchmark times and finalize the plan for the live cutover.

  20. Scrub the Oracle 8i database clean by dropping all application users and their schema contents.

  21. If you chose to precreate tables in order to relocate them or change their extent sizes, then run the script you prepared to do so.

  22. Prepare a script that will rename the Oracle 8i database and all of its data files and online redo logs so that their path names match the eventual name of the database. You will use this script during the live cutover when you rename the Oracle 8i database to have the same name that the Oracle 7.3 database used to have. The easiest way to prepare this script is to perform an ALTER DATABASE BACKUP CONTROLFILE TO TRACE on the Oracle 8i database and edit the script Oracle generates for you.

  23. You may wish to temporarily set the sort_area_size on the Oracle 8i instance to a very large number in order to speed up index creation during the live cutover.

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:

  1. Shut down all applications that access the Oracle 7.3 database.

  2. Use your previously created and tested scripts to export the Oracle 7.3 database and import the data into the Oracle 8i database. Review the output from both the export and import processes--there should be no error messages other than those documented during the test process.

  3. Right after launching the import, shut down the Oracle 7.3 database with normal priority and rename all of the directories that hold its control files, online redo logs, archived redo logs, and data files. Also rename the Oracle 7.3 database’s admin area (where the background dump destination, user dump destination, etc. are).

  4. While the import is running, edit your tnsnames.ora file to remove the temporary entry you added to reference the Oracle 8i database.

  5. After the import is well underway, connect to the Oracle 8i database from SQL*Plus as an application user (not as SYSTEM). If you get a product profile error, then rerun the pupbld.sql script as the SYSTEM user. This script is located in the sqlplus/admin directory under the Oracle 8i home.

  6. After the import is complete and you have checked for errors, rename the Oracle 8i database and instance to take on the name previously used by the Oracle 7.3 database. This is done by shutting down the Oracle 8i database with normal priority, renaming all of the directories used by the Oracle 8i database to take on the new name, changing the ORACLE_SID in your environment, and applying the database rename script you created during the preparation phase. Make sure to fix the link to the Oracle 8i parameter file in the dbs directory of the Oracle 8i home.

  7. Switch the Oracle 8i database to archivelog mode, if appropriate. Also, return the sort_area_size to its normal setting if you had temporarily increased it.

  8. Edit both the oratab file on the database server and the listener.ora file in the Oracle 8i network/admin directory to show that your database is now using the Oracle 8i home instead of the Oracle 7.3 home. Also, delete the temporary entry you had created in each file during the preparation phase.

  9. Restart the Oracle 8i Net8 listener and confirm that you can access the database via Net8 both locally on the database server and on clients or application servers elsewhere on the network.

  10. At this point the migrated database is available for application use. However, there are still a few housekeeping details to take care of.

  11. If the migrated database is involved in replication, then carry out the plan you devised during the preparation phase in order to resynchronize data between the migrated database and other databases in the enterprise.

  12. If during the preparation phase you determined that the migration would break any of your backup procedures or monitoring infrastructure, then carry out the plan you devised to fix these processes.

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:

  1. Prepare a test database server that has Oracle 7.3 software and a working, current copy of the Oracle 7.3 database to be migrated. The test database server should be set up as similarly as possible to the production database server. Create the copy of the Oracle 7.3 database by restoring a hot or cold backup onto the test database server.

  2. Perform some quick application tests to validate the copy of the Oracle 7.3 database on the test server.

  3. Install the Oracle 8i software on the test server. You should develop a procedure for Oracle software installation and follow it rigorously in order to standardize the Oracle setup on all of your database servers. You don’t need to have Oracle create a starter database, but there should be no harm done if you do.

  4. Ensure that the SYSTEM tablespace on the Oracle 7.3 database has enough free space. Oracle 8i requires a lot more space for the data dictionary than Oracle 7.3 did. The Oracle 8i migration guide states that the SYSTEM tablespace should be at least two-thirds empty so that it can accommodate a temporary copy of the data dictionary that is twice the size of the current data dictionary. In reality you may need even more space than that. If you plan to use Oracle 8i bells and whistles like JServer, your SYSTEM tablespace should be at least 200 Mb in size to be safe.

  5. Make sure that none of the data files are in backup mode or need recovery. You can check this by querying v$backup and v$recover_file. (The first should show all data files have status NOT ACTIVE, and the second should show no rows.) Note that the SYSTEM and rollback segment tablespaces must be online, but that the migration guide says other tablespaces may be offline for the migration--as long as they were taken offline with normal priority.

  6. Ensure that the SYSTEM rollback segment has no OPTIMAL setting and large enough NEXT and MAXEXTENTS settings to allow it to grow to a few Mb in size. You can check these settings with the following query:

          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;

  7. Verify that the SYSTEM user's default tablespace is what you want it to be (typically TOOLS or USERS) and that the default storage for that tablespace is what you want it to be. At the end of migration, many new objects will be created in the SYSTEM schema using the default tablespace for the SYSTEM user and the default storage for that tablespace. You may want to temporarily set the default storage for the SYSTEM user's default tablespace to be the same as the SYSTEM tablespace, but this is up to you.

  8. Ensure that the SYSTEM user's default tablespace has sufficient space available to hold many new schema objects. Expect a few dozen tables, indexes, LOBs, and LOB indexes to be created in the SYSTEM schema using the SYSTEM user’s default tablespace and that tablespace’s default storage.

  9. Throughout the migration process you will need to bounce your environment between the Oracle 7.3 and Oracle 8i homes. Prepare a script that sets your environment to each Oracle home. The script must take care of setting ORACLE_HOME, PATH, ORA_NLS33 (if you use a character set other than US7ASCII), and LD_LIBRARY_PATH. It is very important that when you switch from one environment to the other, the bin directory from the old Oracle home does not remain on your path.

  10. Log in as the Oracle software owner and set your environment to point to the Oracle 8i home. Use the migprep utility to copy Oracle 8i migration executables to your Oracle 7.3 home. This operation runs quickly, and requires approximately 15 Mb of space in your Oracle 7.3 home. The command line is as follows:

          migprep <8i home> <7.3 home>

  11. Set your environment to point to the Oracle 7.3 home and set ORA_NLS33 to point to migrate/nls/admin/data under the Oracle 7.3 home. Then shut down the database with normal priority.

  12. You may wish to run the migration utility in "check only" mode to verify that the SYSTEM tablespace has enough free space in it. To do this, make sure your environment is pointing to the Oracle 7.3 home and run:

          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.

  13. With the environment still pointing to the Oracle 7.3 home, run the migration utility for real with the command:

          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.

  14. Do not open the database again using the Oracle 7.3 home. If you do, you’ll need to run the mig utility again to generate a new convSID.dbf file.

  15. Review the spool file generated by the mig utility. There should be no errors. You can perform case-insensitive searches for "ora" and "err" to help you scan the file.

  16. You should take a cold backup of the database at this time. This will allow you to restore the database and try again if the migration fails further down the line. (Data files for offline tablespaces should not need to be restored from the backup, because the migration process should not affect them.)

  17. Set your environment to point to the Oracle 8i home. Edit the oratab file to show that the database now has Oracle 8i as its home.

  18. Rename the existing database control files. The migration process will create new control files with the same names as the old control files. By renaming the old control files first, you will not lose them when the new control files are created.

  19. Copy the convSID.dbf file created by the mig utility from the dbs directory in the Oracle 7.3 home to the dbs directory in the Oracle 8i home.

  20. Create a symbolic link from the dbs directory of the Oracle 8i home to the parameter file for the database.

  21. Edit the parameter file and set compatibility to 8.1.5 or 8.1.6 (depending on which release of Oracle 8i you are migrating to). Temporarily set job_queue_processes to zero.

  22. If there are any question marks in the parameter files, replace them with the full path of the Oracle 8i home. (Remember to check the included parameter file if the ifile parameter is set in the main parameter file.)

  23. Comment out all parameters in the parameter files that are obsolete in Oracle 8i. Here are some to watch for:

  24. Confirm that all environment variables point to the Oracle 8i home. Go to the rdbms/admin directory in the Oracle 8i home and run the following commands in Server Manager (svrmgrl).

          SPOOL <path>

    If you get errors when trying to start the instance, then examine the parameter files again for parameters that are obsolete in Oracle 8i.

  25. Continuing on in the same Server Manager session, migrate the database to Oracle 8i with the following commands:

          SET ECHO ON
          SPOOL OFF

    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.

  26. Review the spool output from Server Manager for errors. This log file could be over 10 Mb in size, so it is not practical to read it line by line. You might want to search for lines that begin with "ORA" in order to look for error messages. You should make sure there is a reason for every error message. This can be difficult just because of the sheer number of messages. You might be tempted to take it on faith that the migration was successful, but at least a quick review of the spool file is strongly recommended. Here are some observations:

  27. If you changed the job_queue_processes parameter in the parameter file earlier, then put it back to its regular setting now.

  28. If you changed the SYSTEM user's default tablespace or the default storage for this tablespace earlier, then you may now change it back.

  29. If you changed the NEXT, MAXEXTENTS, or OPTIMAL settings on the SYSTEM rollback segment earlier, you may wish to change them back. You may find that during migration MAXEXTENTS for the SYSTEM rollback segment was set to unlimited, and this could cause you problems when you try to alter the rollback segment. You can correct this by changing MAXEXTENTS to a smaller number such as 121.

  30. Check both the oratab file on the server and the listener.ora file in the network/admin directory of the Oracle 8i home to make sure that your database is shown as now using the Oracle 8i home instead of the Oracle 7.3 home. Restart the Oracle 8i Net8 listener if you make any changes to the listener.ora file.

  31. If you want to use a new Oracle 8i option such as JServer on your migrated database, then you must run the Database Configuration Assistant to create the necessary database objects to make the option usable. The basic steps to do so are as follows:

  32. Run the utlconst.sql script in the rdbms/admin directory of the Oracle 8i home to see if any check constraints in the migrated database perform date manipulations that are not tolerated by Oracle 8i's more stringent rules.

  33. If the database contains bitmap indexes, then you should check for ones that have taken on an UNUSABLE status during the migration and rebuild them. You can check for these indexes with this query:

          SELECT index_name, index_type, table_owner, status  
          FROM   dba_indexes 
          WHERE  index_type = 'BITMAP' 
          AND    status = 'UNUSABLE';

  34. Check your database for new users created during the migration or installation of new options, such as OUTLN and CTXSYS. Change the passwords for these users appropriately.

  35. You are now ready to regression test your applications against the Oracle 8i database. Ideally, you should perform an exhaustive test of every module in your application. At the very least, it is imperative that you test a sample of application modules that covers every application technology that must access the database. Test your backup procedures and monitoring infrastructure against the Oracle 8i database as well. Do not move beyond this step until all testing is complete and any discovered issues have been fully resolved.

  36. Repeat steps 3 through 10 above on the database server where the production database to be migrated resides. This will prepare your database server for the live cutover.

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.


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.