Preparing to Migrate
This chapter describes all tasks that must be performed before you attempt to migrate your production database. This chapter also provides a detailed discussion of Steps 1 through 4 of the migration process (previously outlined in Chapter 1 "Migration Overview".
The specific topics presented in this chapter are
The information presented in this chapter is generic and, as such, applies to all Oracle versions and releases. For information on migration, upgrading, and downgrading procedures for specific Oracle versions and releases, see Chapter 7 "Migrating from Version 6 to Version 7", Chapter 8 "Migrating Version 6 Applications", and
Chapter 9 "Upgrading and Downgrading between Oracle7 Releases".
Step 1: Prepare to Migrate
This section contains the following topics:
Become Familiar with the Features of the Target Database
Before you begin to plan the migration process, you should be familiar with the new features of the target database to which you wish to migrate. A good starting point for learning how a specific, Oracle7, Release 7.x database differs from Version 6 or another Oracle7 release are Appendices A, B, C, and D of this manual. These appendices list the changes in each of the Oracle7 releases and give specific references for more information about each new feature. If you are using the Parallel Server option, you should also see Oracle7 Parallel Server Concepts & Administration for changes in the Parallel Server.
You should also take an Oracle training class to learn how to take full advantage of the functionality available with the Oracle7 releases.
Estimate the System Requirements
Estimate the system resources that will be required for the successful migration of your database to the new version of Oracle. The configuration requirements for both the operating system and the hardware must be considered. See Chapter 6 "System Requirements for Migration".
Choose a Migration Method
There are several methods for migrating or upgrading to a target database:
- You can use the Migration Utility.
- You can perform a full or partial database import into a target database from a full or partial database export of a source database.
Additional Information: For more information on using the Oracle Installer method, see the operating system-specific Oracle documentation for your specific platform.
- You can copy data from a source database to a target database using the COPY command or the AS clause of the CREATE TABLE command
Each method is appropriate for different circumstances. The following sections describe each method, the amount of time and space required by each method, and the situations where each method is appropriate.
Warning: Whichever method you choose, you need to
ensure that the character encoding scheme used for data
in the target database is correctly specified. All character data in the target database is assumed to be in the specific character encoding scheme specified when the database was created with the CREATE DATABASE command. For more information on NLS, see Oracle7 Server Reference.
The Migration Utility
The Migration Utility is a program that converts some of the files and structures in your source database to target database format. The primary advantages of the Migration Utility are its speed and ease of use. The Migration Utility takes significantly less time than Export/Import and consists of a few easy steps. However, you cannot selectively migrate datafiles. Thus, the Migration Utility is useful if you want to migrate an entire source database to a target database quickly.
The Migration Utility does not require a significant amount of temporary space. The utility requires only that you have enough extra room in the SYSTEM tablespace to hold the data dictionaries of both the source and target databases simultaneously.
When you use the Migration Utility, the entire database is converted, including database files, rollback segments, and the control file. At any point before actually migrating your source database (Step 5), you can still open and access data with the source database. However, once you have performed Step 5 and migrated the source database to the target database with the Migration Utility, you can only go back to the source database by restoring a full backup of the source database.
If you decide to use the Migration Utility as the method for migrating your database, see Chapter 4 "The Migration Utility" for detailed information about the Migration Utility and its use.
You must create the target database before using the Export/Import method of migration.
The Export utility copies the data in your source database to an export file, from which the Import utility can load the data into a target database. An important distinction between Export/Import and the Migration Utility is that the physical data in your database is copied to a new location with Export/Import, while the Migration Utility changes only the file headers and the definitions of the data in the files where they currently reside.
The Export/Import method of migration provides some
- You can defragment the data. A full database import can compact the data and improve performance.
- You can restructure your database; that is, you can create new tablespaces, or modify existing tables or tablespaces.
Since the Export/Import method of migration does not change the source database, it is available at any point during the migration process. This allows you to keep a source database running in parallel with the target database without requiring the restoration of a backup. (However, you should not change the source database unless you make exactly the same changes to the target database.) Also, a full export can serve as an archive of your source database.
- You can migrate only certain database objects or users. You can selectively import only objects and users that need to be imported at a given time.
The Export/Import method has the following limitations:
Additional Information: The time required for Export/Import migration depends on the characteristics of your operating system. For more information, see your operating system-specific Oracle documentation.
- For a large database, a full database Export/Import can take a significant amount of time.
- For a large database, a full database Export/Import can require a substantial amount of temporary storage space for the data.
If you decide to use Export/Import as the method for migrating your database, see Chapter 5 "Migrating Using Export/Import" for detailed information about using Export/Import.
- Because you should not make changes to the source database after performing the export, your applications are unavailable until the migration is completed.
You can copy data from one Oracle database to another Oracle database using database links. For example, you can either 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 or the CREATE TABLE...AS command.
The copy data method of migration has the same advantages as Export/Import, which are:
- You want to defragment your data files.
- You want to restructure your database; that is, you want to create new tablespaces, or modify existing tables or tablespaces.
One reason to use the Copying Data method of migration, and not Export/Import, is to allow the selection of only certain rows of tables that are to be placed into your target database. The Export/Import utilities can only load entire tables and not selected rows. For example, if you wanted to create a new EMP table that contains a subset of the data in your existing EMP table (for example, employees in departments 10, 20 and 30), you would use the following SQL statement in Oracle7, Release 7.x:
- You want to migrate only certain database objects or users, or even a subset of rows.
CREATE TABLE NEW_EMP
DEPTNO NUMBER(2)) AS SELECT EMPNO, ENAME, JOB, MGR,
HIREDATE, SAL, COMM, DEPTNO
FROM EMP@V6DB WHERE DEPTNO IN (10, 20, 30);
The Copying Data method also requires less space than the other migration methods. Therefore, use the Copying Data migration method if you are migrating only a portion of your database. You do not need to allocate large amounts of extra space for temporary files or for Export/Import files. You simply need your source database and the target database online.
The COPY command is also useful if you are working with large cluster tables. The SQL*Plus COPY command allows you to move different portions of the cluster in parallel using SQL*Net.
For more information about copying data from one database
to another, refer to the CREATE TABLE command in
Oracle7 Server SQL Reference and the COPY command in the
SQL*Plus User's Guide and Reference.
Comparing Migration Methods
The following table summarizes the advantages and disadvantages of the four migration methods:
Table 2 - 1. Comparing Migration Methods
||Automatic and requires minimal interaction by the DBA.
Relatively fast no matter what the size of the database because the data dictionary objects are the only objects that are changed.
||Can only be used for forward, version-to-version migrations.
Cannot be used for reverse migration between versions of the Oracle Server. For example, cannot be used for reverse migration from Oracle7 to Oracle6.
Cannot be used for release-to-release migration.
Cannot be used for migration to Trusted Oracle7.
||Ability to migrate specific segments of a database.
Can be used for reverse migration between versions of the Oracle Server, for example, for reverse migration from Oracle7 to Oracle6.
Can be used for release-to-release migration in upgrade/downgrade operations.
Migrated data can be compacted for improved performance.
Database can be restructured with modified or new tablespaces.
||May be slow, depending on size of the database. For large databases, several Gb in size, may take several hours.
Requires large amounts of disk space for export files and offline backup.
||Datafiles can be defragmented.
Database can be restructured with modified or new tablespaces.
Ability to migrate specific segments of a database.
Can be used for release-to-release migration.
||Same as for Export/Import.
Develop a Testing Plan
A carefully designed series of tests, that will validate all stages of the migration process, will ensure the success of the migration operation. The importance of such a test program should not be underestimated.
Testing Before Migration
A rigorous testing program, performed before moving your production system to a target database, will ensure that the final migration process will be well understood and predictable. Failure to perform a rigorous testing program is risky and may lead to unpredictable results. Therefore, as much testing as possible should be completed before migrating to a target database. Such preparation testing must include the following types of tests: migration, minimal, functionality, integration, performance, and stress.
Migration Testing involves planning and testing the migration path from the source database to the new, target database. You may choose to perform a full database export under the source database and then perform a full database import to move your data to the target database. You may also choose to use the Migration Utility. These methods are discussed in Chapter 4 "The Migration Utility" and Chapter 5 "Migrating Using Export/Import".
Regardless of what migration method you choose, you must establish, test, and validate a migration plan.
Minimal testing involves moving all, or portions, of your 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 does not reveal potential issues you may encounter under a production environment. However, any application startup or invocation problems will be revealed immediately.
Functional testing is a set of tests in which new and existing functionality of the system are tested after migrating. Functional
testing includes all components of the RDBMS system, networking, and application components. The objective of functional testing is to determine if each component of the system functions as it did
Integration testing tests the interaction of each component of
- 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 Oracle7 instance or not.
- If the connection between two components involves SQL*Net, that connectivity should also be tested as well as stress tested.
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 to the target database, you should understand the performance profile of your application under the source database. Specifically, you should understand the calls the application makes to the database kernel.
Suggestion: To thoroughly understand your application's performance profile under the source database, enable SQL_TRACE and profile with TKPROF. For more information, see Oracle7 Server Tuning.
Volume/Load Stress Testing
Volume and load stress testing tests the entire, newly 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 has found that customers often do not conduct any kind of volume or load stress testing. Benchmarks that do not characterize business applications are, instead, relied upon heavily. Benchmarks of the application should be conducted to uncover unknown problems relating to functionality, performance and integration.
Once the source database has been successfully migrated to the target database, you should test the data to ensure that all data is accessible and that your applications function properly. You should also determine if any database tuning is necessary. If possible, you should automate these testing procedures.
Your testing plan should be representative of the work performed at your site. You should test the functionality and performance of all of your applications on your source production databases. Gather performance statistics for both normal and peak usage.
Specific Pre- and Post-Migration Tests
The following tests can assist you in tuning your SQL statements after you migrate to the target database:
- Gather performance statistics for each of your SQL statements. You should be able to write scripts to extract these statements from your applications and run them against your production database.
After migrating, you can compare performance of the target database with your source database test results by completing the following steps:
- Use EXPLAIN PLAN to determine the execution plan Oracle follows to execute each SQL statement. Use the INTO parameter to save this information in a table.
1. Use EXPLAIN PLAN to determine the execution plan for each statement. Save this information in a new table.
2. Compare the target database execution plans to the source database execution plans. If you notice a difference, you should execute the command in the target database and compare this performance to the performance of the source database.
Step 2: Rehearse the Migration Process
You should perform a test migration (or test upgrade) before
beginning the actual migration or upgrade procedure so that you
can resolve any potential problems before actually migrating or upgrading your production database. For example, if you are migrating a Version 6 database and you plan on using Export/Import, you can use your actual Version 6 database.
If you plan on using the Migration Utility, you need to create a test version of the source database on which to perform the test migration.
Additional Information: Refer to your operating system-specific Oracle documentation for information on how to configure another database so that no operating system variables defined for your production database are affected by the test database.
To rehearse the migration of the database, perform Steps 2 through 4 of the migration process described in Chapter 1 "Migration Overview" and later, in this chapter, using either a test database or a subset of the source database, depending upon which migration method you choose.
Step 3: Test Your Applications
After migrating a test database, you should use the test database to ensure that your existing applications operate properly with the target database. You can also begin enhancing source database applications by adding target database functionality to the applications. However, it is suggested that you first make sure that the applications operate in the same manner as they did in the source database. For more information on using your applications with Oracle7, Release 7.x, see Chapter 8 "Migrating Version 6 Applications".
Step 4: Preserve the Source Database
There are several tasks to perform before starting the migration procedure. The following list summarizes the steps to take before implementing any of the migration procedures:
- Shut down the source (original production) database.
- Make a complete backup of the source database.
- Update your initialization (INIT.ORA) file.
- Change any SQL scripts that you currently use to
- Update database administration scripts.
- Update database link names.
- Move constraint identifiers.
- Change unique indexes to UNIQUE or PRIMARY KEY
Shut Down the Source (original production) Database
The source (original production) database must be shut down
normally so that there is no outstanding redo information, and no uncommitted transactions.
If you migrate using the Migration Utility, all source
datafiles that are online when the target database is opened are automatically converted to the target database file format. Files that are offline when the target database is opened remain in the source database file format.
You do not need to convert all of the database files to target database format immediately. The remaining files are converted when they are brought online in the target database.
Rollback segments are converted as they are accessed by the target database. Thus, all rollback segments that are in tablespaces that are online when the database is first opened in the target database are converted. If a source database rollback segment is in a tablespace that is offline when the target database is opened, it is converted the first time it is brought online in the target database.
Back up the Source (original production) Database
After shutting down the database, you should make a full backup of the source database before proceeding with migration. Be certain to back up all datafiles, control files, parameter files, online redo log files, and any script files used to create objects in the source database.
Suggestion: You should make a backup of the online redo log files even though there should be no outstanding redo information. This allows you to easily restore your source database if necessary.
Change Parameter Files (INIT.ORA Files)
Certain initialization parameters are obsolete in new Oracle versions and releases. Obsolete parameters may cause errors if used with a new Oracle version or release database. You must remove all obsolete parameters from any parameter file that starts a new Oracle version or release instance. You must also alter any parameter whose syntax has changed in a new Oracle version or release. Refer to Appendices A, B, C, and D for lists of obsolete and changed parameters for Release 7.0, Release 7.1, Release 7.2, and Release 7.3, respectively.
Change SQL Scripts
All SQL scripts that you built that created objects in the source database should be changed to conform to the syntax of the Oracle version to which you will migrate.
Datatypes change between Oracle versions and releases. Therefore, you must make appropriate changes to your applications before attempting to use them with a new Oracle version or release. Refer to Appendices A, B, C, and D for lists of changed datatypes in Release 7.0, Release 7.1, Release 7.2, and Release 7.3, respectively. Also, see Chapter 7 "Migrating from Version 6 to Version 7" for specific information on datatype changes between Version 6 and Version 7.
Update Database Administration Scripts
Use the CREATE USER and GRANT CREATE SESSION commands to update database administration scripts. Refer to Appendices A, B, C, and D for lists of changed database administration scripts in Release 7.0, Release 7.1, Release 7.2, and Release 7.3, respectively. Also, see Chapter 7 "Migrating from Version 6 to Version 7" for specific information on changes to database administration scripts between Version 6 and Version 7.
Update Database Link Names
The naming convention for database links may change when you migrate, upgrade, or downgrade between Oracle versions and releases. See Chapter 7 "Migrating from Version 6 to Version 7" for specific information on updating database link names.
Move Constraint Identifiers
The constraint clause for the CREATE TABLE command has new syntax in the Oracle7 releases. See Chapter 7 "Migrating from Version 6 to Version 7" for specific information on constraint identifiers.
Change Unique Indexes to UNIQUE or PRIMARY KEY Integrity Constraints
Integrity constraints can be used in Oracle7 releases to enforce uniqueness among column values. Because unique indexes might
not be supported in future versions of Oracle, you should begin using UNIQUE or PRIMARY KEY integrity constraints instead of
Indexes can now be validated using the ANALYZE command. You should begin using this command, because the VALIDATE INDEX command might not be supported by future versions of Oracle. See Chapter 7 "Migrating from Version 6 to Version 7" for more information on unique indexes and Primary Key integrity constraints.