Oracle8 Migration Release 8.0 A58243-01 |
Library | Product | Contents | Index

# 2
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:

- Step 1: Prepare to Migrate
- Step 2: Test the Migration Process
- Step 3: Test the Migrated Test Database

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
- Choose a Migration Method
- Assess System Requirements vs. Resources Available
- Start with Oracle Version 7, Release 7.X or Higher
- Avoid Common Migration Problems
- Prepare a Backup Strategy
- Develop a Testing Plan

### 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.

Note: 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:

- Migration Utility, for migrating a version 7 database to version 8. See your platform-specific Oracle documentation for information about the earliest release that the Migration Utility can migrate on your platform. For example, on some platforms, the Migration Utility can migrate only release 7.1.4 and later databases.
- Export (full or partial) of a version 7 (or version 6) source database, followed by a full or partial Import into a version 8 target database.
- Copying data from a source database into a version 8 database using the COPY command or the AS clause of the CREATE TABLE command.

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.
Export/Import: 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 performance. 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.

#### Export/Import

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:

- Defragmenting the data; that is, you can compress the imported data to improve performance.
- Restructuring the database; that is, you can create new tablespaces or modify existing tables, tablespaces, or partitions to be populated by imported data.
- Allowing the migration of specified database objects or users; that is, you can import only the objects, users, and other items that you wish.
- Serving as a backup archive; that is, you can use a full database export as an archive of the source database.

##### Export/Import Limitations

Data migration by Export/Import has following limitations:

- Migrating a database by Export/Import requires an expert DBA. The combination of required planning and complicated execution typically requires multiple stagings and a great deal of practice before the final migration can be attempted.
- For a large database, a full database export/import can require a substantial amount of temporary storage space for the export dump file.
- The export may need to be partitioned into multiple jobs if the operating system does not support a file size as large as the database.
- Export/Import creates an entirely new database. To keep the source database in place, and to import its export dump file/data into a version 8 target database, requires the creation of target data files on the system before you import.
- Making multiple changes to the database at the same time, such as migrating to version 8 and defragmenting/restructuring the database at the same time, can hinder troubleshooting.
- To keep data in the source database and the target database synchronized during migration, either prohibit any data change in the source or make full provisions to mirror the changes in the migrated data in the target database.

##### 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:

- Migration time easily exceeds the non-peak or off-production hours available in a typical daily schedule. Therefore, making the database unavailable for production tasks for the duration of the migration process might be impractical.
- If you make the source database read-only or do not allow changes to be made in it after the export, applications will be unavailable until after the import and final migration steps are completed.
- For larger databases, consider operating parallel export streams to reduce the time and optimize the process.

##### 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:

    CREATE TABLE NEW_EMP

     (EMPNO         NUMBER(4),

     ENAME          VARCHAR2(10),

     JOB            VARCHAR2(9),

     MGR            NUMBER(4),

     HIREDATE       DATE,

     SAL            NUMBER(7,2),

     COMM           NUMBER(7,2),

     DEPTNO         NUMBER(2)) AS SELECT EMPNO, ENAME, JOB, MGR,

                     HIREDATE, SAL, COMM, DEPTNO

     FROM EMP@V7DB WHERE DEPTNO IN (10, 20, 30);

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:

- configuration requirements for both the operating system and hardware
- the size of the existing production database
- possible size adjustments to it associated with implementing version 8

  See Also: Appendix E, "General System Requirements for Migration", and your platform-specific Installation Guide, for details about system requirements.

  Note: Version 8 binaries may require as much as three times the disk space required by version 7 binaries. To estimate disk space requirements, run the Migration Utility in CHECK_ONLY mode.

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.

Note: 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:

- If you use ROWIDs stored in columns or in application code, see Chapter 7, "Migration Issues for the Version 8 ROWIDs". Because the format for ROWIDs is different in version 8, the old ROWIDs are invalid and must be converted.
- Make sure you do not use new reserved words as names for database objects (tables, columns, and so forth). See Appendix D, "New SQL Key and Reserved Words" for information.
- Make sure all Oracle product versions, operating system versions, and third-party software versions ar certified against version 8 on your platform. See your platform-specific Installation Guide for information.

### 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:

- How long can the production database remain inoperable before business consequences become intolerable?
- What backup strategy should be used to meet your availability requirements?
- Are backups archived in a safe, offsite location?
- How quickly can backups be restored (including backups in offsite storage)?
- Have recovery procedures been tested successfully?

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.

WARNING: 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:

- Pro*C/C++ applications running against the target database instance should be tested to ensure that there are no problems with the new software.
- GUI interfaces should be tested with other components.
- Subtle changes in the target database, such as datatypes, data in the data dictionary (additional rows in the data dictionary, object type changes, and so forth) can have an effect all the way up to the front-end application, regardless of whether the application is directly connected to the version 8 instance or not.
- If the connection between two components involves Net8 or SQL*Net, those connections should also be tested and stress tested.

#### 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:

- timing tests
- data dictionary growth observations
- database resource usage observations, such as rollback and temporary segment usage

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:

- If you plan to use the Migration Utility, create a test version (typically a subset) of the source database to test migration.
- If you plan to use Export/Import, export and import small test pieces of the actual version 7 production database.

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.

WARNING: 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.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index