Oracle8 Migration
Release 8.0






Prev Next

Preparing to Migrate

This chapter covers the steps that must be completed before you migrate a production database. Steps 1 through 3 of the migration process, outlined in Chapter 1, "Migration Overview", are covered in detail in this chapter:

The information in this chapter is generic and applies generally to version 7 and version 6 production databases.

See Also:

Oracle8 Replication, Appendix B, "Migration and Compatibility", if you are migrating a pre-version 8 database system that has Advanced Replication installed.


Step 1: Prepare to Migrate

This step includes the following actions, which are covered in detail in the following sections:

Become Familiar with the Features of the New Version 8 Database

Before you plan the migration process, become familiar with the new features of the version 8 database. Getting to Know Oracle8 and the Oracle8 Enterprise Edition is a good starting point for learning the differences between a version 8 RDBMS and a release 7.3 RDBMS.

See Also:

Oracle8 Parallel Server Concepts and Administration, if you are using the Parallel Server option, for changes in Parallel Server.



Version 8 training classes are an excellent way to learn how to take full advantage of the functionality available with version 8.


Choose a Migration Method

Use one of these three methods to migrate a database to version 8:

summarizes these methods and lists their advantages and disadvantages.

Table 2-1 Advantages and Disadvantages of Migration Methods

Migration Method   Advantages   Disadvantages  

Migration Utility:

For migration of a complete database, version 7 to version 8.

Changes datafile headers but leaves actual data unchanged.

Does not copy data.  

Automatic and requires minimal interaction by the DBA.

Relatively fast, whatever the size of the database, because the data dictionary objects are the only objects that are changed.

Imposes essentially no limit on the size of the database it can migrate.

Usually requires relatively little additional disk space, when compared with other migration methods.  

Performs only version 7 to version 8 migrations, and cannot downgrade back to version 7.

Cannot perform release-to-release upgrades, for example release 8.0.3 to release 8.0.4. However, upgrades can be accomplished easily with the Oracle Installer.

Cannot migrate selected parts of a database-migrates only the entire database.  


For migration of parts of the database.

Leaves datafile headers and actual data unchanged.

Makes new copy of data.


Can migrate version 6 and version 7 databases to version 8.

Can migrate specific parts of a database.

Can be used to downgrade between versions of Oracle, for example, downgrading from version 8 to version 7.

Can be used for release-to-release upgrade or downgrade operations, for example, upgrading from 8.0.3 to 8.0.4.

Datafiles can be defragmented, and migrated data compressed, to improve performance.

A database can be restructured with modified or new tablespaces, or by the partitioning of tables.  

Extremely slow except for very small databases. Time required increases with the amount of data and use of LONG datatypes. Very large databases of several gigabytes may take many hours, and terabyte databases may take days.

Requires large amounts of disk space for copying data into export file(s).  

Copying Data:

For migration of parts of the database.

Leaves datafile headers and actual data unchanged.

Makes new copy of data.  

Datafiles can be defragmented, and migrated data compacted, to improve

A database can be restructured with modified or new tablespaces.

Can migrate version 6 or version 7 databases to version 8.

Can migrate specific parts of a database.

Can be used for release-to-release upgrade or downgrade operations, for example, upgrading from 8.0.3 to 8.0.4.  

Extremely slow except for very small databases. Time required increases with the amount of data and use of LONG datatypes. Very large databases of several gigabytes may take many hours, and terabyte databases may take days.

Requires that both source and target databases be available at once during copying operations.  

The following sections describe each of the migration methods in detail, covering the relative amounts of time and space they require and the situations in which they are appropriate.

Migration Utility

The Migration Utility converts files and structures in the version 7 source database to version 8 format, changing only the file headers and, if necessary, the definitions of the data in the files. The Migration Utility does not change the data portions of the datafiles, nor their format and content.

The primary advantages of using the Migration Utility are speed and ease of use. The Migration Utility takes significantly less time than Export/Import, and its use entails a standardized series of specific, easy steps. In addition, the time required to migrate a database with the Migration Utility depends less on the size of the database than on the number of objects in the data dictionary.

The Migration Utility is especially useful for quickly migrating an entire source database. Unlike Export/Import, the Migration Utility cannot selectively migrate specific datafiles. However, for databases with large amounts of data, large datatypes, and some other version 7 features, the Migration Utility may be the only practical tool for migration to version 8.

The Migration Utility requires only enough temporary space in the SYSTEM tablespace to hold both the version 7 (source) and version 8 (target) data dictionaries simultaneously.

The Migration Utility converts the entire database, including database files, rollback segments, and the control file(s). At any point before actually migrating the version 7 database, you can open and access data with the version 7 instance. However, once the Migration Utility has migrated the version 7 source database to version 8, you can go back to version 7 only by restoring a full backup of the version 7 source database.

See Also:

Chapter 3, "Migrating Using the Migration Utility", for detailed information about using the Migration Utility.



Unlike the Migration Utility, the Export/Import utilities physically copy data in the source database to a new database. The source database's Export Utility copies specified parts of the source database into an export file. Then, the version 8 Import Utility loads the exported data into the new version 8 database. However, the new version 8 target database already must exist before the export file can be migrated into it.

The following sections discuss aspects of Export/Import operations that may help you to decide whether to use Export/Import for migrating your database.

See Also:

Chapter 4, "Migrating Using Export/Import", and also Oracle8 Utilities, for more information about using Export/Import for migration.


Export/Import Effects on Migrated Databases

The Export/Import method of migration does not change the source database, enabling the source database to remain available throughout the migration process; however, if a consistent snapshot of the database is required (for data integrity or other purposes), the source database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the source database can remain available, you can, for example, allow an existing version 7 production database to continue running while the new version 8 database is being built at the same time by Export/Import. During this migration, to maintain complete database consistency, changes to the data in the version 7 database cannot be permitted without the same changes to the data in the version 8 database.

The Export/Import method also can be used to upgrade or downgrade. For example, the transformation of a version 8 database back into a version 7, release 7.3 database can be accomplished using Export/Import.

Most importantly, the Export/Import operation results in a completely new database. Although the source database ultimately contains a copy of the specified data, the migrated database may perform differently from the original source database. As a result of data defragmentation, database restructuring by the DBA, or the upgrade to version 8, expect changes in performance, data growth patterns, shared resource usage, data dictionary size, and object organization.

Careful planning, expert implementation, and rigorous testing are required to take advantage of the possible positive effects of Export/Import on the database; otherwise, the database changes may create problems. If the database was restructured during migration, and the migrated database behaves differently, it may be difficult to determine the cause of the differences.

Export/Import Benefits

Data migration by Export/Import offers the following benefits:

Export/Import Limitations

Data migration by Export/Import has following limitations:

Time Requirements for Export/Import

Migrating an entire database by Export/Import can take a long time, especially compared with using the Migration Utility. Therefore, you may need to schedule the migration during non-peak hours or make provisions for propagating to the new target database any changes that are made to the source database during the migration.

The time and system resources (particularly disk space) required for Export/Import migration depend on DBA skill, database size, and the type of data to be migrated, particularly the number, size, and type of indexes that must be rebuilt.

For example, a relatively simple 6-gigabyte, version 7 database was migrated to version 8 using the Migration Utility in about an hour. The same version 7 database was exported, producing a single 2-gigabyte export dump file. To import that one export dump file took 20 hours. The complete migration using the steps described in "Migrate the Pre-Version 8 Source Database Using Export/Import" on page 4-3 took two days.

Consider the following factors related to the extended time required to migrate a database by Export/Import:

Data Definition Conversion by Version 8 Import

When importing data from an earlier version, the version 8 Import Utility makes appropriate changes to data definitions as it reads earlier versions' export dump files. That is, it handles dump files produced by the Export utilities of Oracle version 6, version 7, and version 8. If the export source database is earlier than version 6, the source database must first be upgraded to at least version 6 before the export is performed.

Copying Data

You can copy data from one Oracle database to another Oracle database using database links. For example, you can copy data from a source database table to a target database table with the SQL*Plus COPY command, or you can create new tables in a target database and fill the tables with data from the source database by using the INSERT INTO command, the CREATE TABLE ... FROM command, and the CREATE TABLE ... AS command.

Copying data and Export/Import offer the same advantages for migration. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can migrate only specified database objects or users.

Copying data, however, unlike Export/Import, allows the selection of specific rows of tables to be placed into the target database. Copying data is thus a good method for migrating only part of a database table. In contrast, using the Export/Import utilities to migrate data from version 7 to version 8, you can migrate only entire tables.

For example, to create a new table (NEW_EMP) that contains a subset of the data in an existing table (EMP@V7DB, only the employees in departments 10, 20, and 30), you can use the following SQL statement:


Copying data requires less disk space and memory buffer space for migration than Export/Import because copying data requires only that the source database and the target database both are online. There is no need to allocate large amounts of extra space for temporary files or for Export dump files.

The SQL COPY command is useful for working with large clustered tables. Further, the SQL*Plus COPY command can move portions of the cluster in parallel using Net8 (or SQL*Net). For more information about copying data from one database to another, refer to the CREATE TABLE command in the Oracle8 SQL Reference and to the COPY command in the SQL*Plus User's Guide and Reference.

Assess System Requirements vs. Resources Available

Estimate the system resources required for successful migration. Different migration methods may result in different resource requirements; therefore, if you are not certain of the method you want to use, complete an estimate for each potential method of migrating the existing database to version 8.

Consider the following factors in your estimates:

After you have chosen a migration method and estimated your requirements, secure the necessary resources for a successful migration.

Start with Oracle Version 7, Release 7.X or Higher

If you plan to use the Migration Utility, the earliest release supported by the Migration Utility is platform-specific. For example, on some platforms, the Migration Utility cannot migrate a release lower than version 7, release 7.1.4 (such as version 6, release 7.0, or release 7.1.3). See your platform-specific Oracle documentation for the supported releases on your platform.

If your database release number is lower than the release supported by the Migration Utility on your platform, upgrade or migrate the database to the required release. Use Oracle7 Server Migration, Release 7.3 to migrate or upgrade the system to the required release. Then, use this Oracle8 Migration manual to migrate to version 8.


If you do not use the Migration Utility but instead use Export/Import or data copying, this restriction does not apply. You can use Export/Import or data copying to migrate data directly from a pre-version 8 database (for example, version 6, release 7.0, or release 7.1.3) to version 8.


Avoid Common Migration Problems

You can save time by eliminating common migration problems before you migrate your database. Common problem areas include the following:

Prepare a Backup Strategy

The ultimate success of your migration depends strongly on the design and execution of an appropriate backup strategy. To develop a backup strategy, consider the following questions:

Your backup strategy should answer all of these questions and include procedures for successfully backing up and recovering your database.

See Also:

The Oracle8 Backup and Recovery Guide for more information.


Develop a Testing Plan

You need a series of carefully designed tests to validate all stages of the migration process. Executed rigorously and completed successfully, these tests ensure that the process of migrating the production database is well understood, predictable, and successful. Perform as much testing as possible before migrating the production database. Do not underestimate the importance of a test program.


Failing to test rigorously before migration is risky and may lead to unpredictable results.


The testing plan must include the following types of tests:

Migration Testing

Migration testing entails planning and testing the migration path from the source database to the migrated database, whether you use the Migration Utility, Export/Import, or other data-copying methods to migrate the production database data to the target database. These methods are discussed in Chapter 3, "Migrating Using the Migration Utility" and Chapter 4, "Migrating Using Export/Import".

Regardless of the migration method you choose, you must establish, test, and validate a migration plan.

Minimal Testing

Minimal testing entails moving all or part of an application on the source database to the target database and running the application without enabling any new, target database features. Minimal testing is a very limited type of testing that may not reveal potential issues that may appear in a "real-world" production environment. However, minimal testing will reveal any application startup or invocation problems immediately.

Functional Testing

Functional testing is a set of tests in which new and existing functionality of the system are tested after migration. Functional testing includes all components of the RDBMS system, networking, and application components. The objective of functional testing is to verify that each component of the system functions as it did before migrating and to verify that new functions are working properly.

Integration Testing

Integration testing examines the interaction of each component of the system. Consider the following factors when you plan your integration testing:

Performance Testing

Performance testing of a target database compares the performance of various SQL statements in the target database with the statements' performance in the source database. Before migrating, you should understand the performance profile of the application under the source database. Specifically, you should understand the calls the application makes to the database kernel.

See Also:

Oracle8 Tuning for information about tuning. To thoroughly understand the application's performance profile under the source database, enable SQL_TRACE and profile with TKPROF.


Volume/Load Stress Testing

Volume and load stress testing tests the entire migrated database under high volume and loads. (Volume describes the amount of data being manipulated. Load describes the level of concurrent demand on the system.) The objective of volume and load testing is to emulate how a production system might behave under various volumes and loads.

Volume and load stress testing is crucial, but is commonly overlooked. Oracle Corporation has found that customers often do not conduct any kind of volume or load stress testing. Instead, customers often rely on benchmarks that do not characterize business applications. Benchmarks of the application should be conducted to uncover problems relating to functionality, performance, and integration, but they cannot replace volume and load stress testing.

After you migrate the source database, you should test the data to ensure that all data is accessible and that the applications function properly. You should also determine whether any database tuning is necessary. If possible, you should automate these testing procedures.

The testing plan should reflect the work performed at the site. You should test the functionality and performance of all applications on the source production databases. Gather performance statistics for both normal and peak usage.

Specific Pre-Migration and Post-Migration Tests

Include the following tests in your testing plan:

Collecting this information will help you compare the source database with the migrated target database.

Use EXPLAIN PLAN on both the source and target databases to determine the execution plan Oracle follows to execute each SQL statement. Use the INTO parameter to save this information in tables.

After migrating, you can compare the execution plans of the migrated database with the execution plans of the source database. If there is a difference, execute the command on the migrated database and compare the performance with the performance of the command executed on the source database.

Step 2: Test the Migration Process

Create a test environment that will not interfere with the current production database. Your test environment will depend on the migration method you have chosen:

Practice migrating the database using the test environment. The best migration test, if possible, is performed on an exact copy of the database to be migrated, rather than on a downsized copy or test data.


Do not migrate the actual production database until after you successfully migrate a test subset of this database and test it with applications, as described in the next step.


Make sure you upgrade any OCI and precompiler applications that you plan to use with your version 8 database. Then, you can test these applications on a sample Oracle database before migrating your production database. See "Upgrading Precompiler and OCI Applications" on page 6-2 for more information.

See Also:

Your platform-specific Oracle documentation for information about configuring a test database so that no operating system variables defined for the production database are affected by the test database.


Step 3: Test the Migrated Test Database

Perform the planned tests on the version 7 source database and on the test database that you migrated to version 8. Compare the results, noting anomalies. Repeat Step 1, Step 2, and Step 3 described in this chapter as necessary.

Test the newly migrated version 8 test database with existing applications to verify that they operate properly with a migrated version 8 database. You also might test enhanced functionality by adding features that use the available version 8 functionality. However, first make sure that the applications operate in the same manner as they did in the source database.

See Also:

Chapter 6, "Upgrading Version 7 Applications", for more information on using applications with version 8.



Copyright © 1997 Oracle Corporation.

All Rights Reserved.