Release 3 (8.1.7)
Part Number A86632-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Migration Release 3 (8.1.7) Part Number A86632-01 |
|
This chapter covers the steps that must be completed before you migrate a production database. This chapter covers in detail Steps 1 through 3 of the migration process, which were outlined in Chapter 2.
This chapter covers the following topics:
The information in this chapter is generic and applies generally to Oracle7 and version 6 production databases.
|
See Also:
|
Complete the following tasks to prepare to migrate:
Before you plan the migration process, become familiar with the new features of the Oracle8i database. Getting to Know Oracle8i is a good starting point for learning the differences between release 8.1, release 8.0, and release 7.3. Also, check specific books in the Oracle documentation library to find information about new features for a certain component; for example, see Oracle8i Parallel Server Documentation Set: Oracle8i Parallel Server Concepts; Oracle8i Parallel Server Setup and Configuration Guide; Oracle8i Parallel Server Administration, Deployment, and Performance for changes in Oracle Parallel Server.
Choose one of the following methods to migrate your database to Oracle8i:
The Migration utility is a command-line utility for migration of a complete database from Oracle7 to Oracle8i. It changes datafile headers but leaves actual data unchanged. It does not copy data.
The Oracle Data Migration Assistant has a graphical user interface (GUI) for migration or upgrade of a complete database. It changes datafile headers but leaves actual data unchanged. It does not copy data.
Export/Import can migrate parts of the database. Export/Import leaves datafile headers and actual data unchanged, and makes a new copy of the data.
Data copying can migrate parts of the database. Data copying leaves datafile headers and actual data unchanged, and makes a new copy of the data.
Table 3-1 summarizes the advantages of each of these methods. Table 3-2 summarizes the disadvantages of each of these methods.
The following sections describe each of the migration methods in detail, covering the relative amounts of time and space the methods require and the situations in which the methods are most appropriate.
The Migration utility is a command-line utility that converts files and structures in the Oracle7 source database to Oracle8i 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 or content.
The primary advantages of using the Migration utility are speed and relative 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 Oracle7 features, Export/Import may not be feasible, and the only practical options may be either the Migration utility or the Oracle Data Migration Assistant.
The Migration utility requires only enough temporary space in the SYSTEM tablespace to hold both the Oracle7 (source) and Oracle8i (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 Oracle7 database, you can open and access data with the Oracle7 instance. However, after the Migration utility has migrated the Oracle7 source database to Oracle8i, you can go back to Oracle7 only by restoring a full backup of the Oracle7 source database.
The Migration utility cannot perform direct migrations on release 7.0 databases, nor on databases below a specific 7.1 release. In general, the Migration utility supports migrations of the last 7.1 release and higher databases on your operating system. The exact maintenance release number of the last 7.1 release varies from operating system to operating system.
If you are using a release below the release supported by the Migration utility on your operating system, then you first must migrate or upgrade your database to a supported Oracle7 release before using the Migration utility to migrate to Oracle8i. See your operating system-specific Oracle documentation for information about the earliest release supported by the Migration utility on your operating system.
|
See Also:
Chapter 4, "Migrating from Oracle7 Using the Migration Utility", for detailed information about using the Migration utility. |
The Oracle Data Migration Assistant provides a user-friendly, graphical user interface (GUI) that guides you through the migration process. The Oracle Data Migration Assistant calls the Migration utility and runs it in the background, which means that you avoid running the Migration utility manually from a command-line.
The primary advantage of the Oracle Data Migration Assistant is that it is easy to use. Because the Oracle Data Migration Assistant calls the Migration utility, most of the advantages and disadvantages of the Migration utility also apply to the Oracle Data Migration Assistant. The section "Choosing Between the Oracle Data Migration Assistant and the Migration Utility" provides information about the differences between the Oracle Data Migration Assistant and the Migration utility.
|
See Also:
Chapter 5, "Migrating from Oracle7 Using the Oracle Data Migration Assistant", for detailed information about using the Oracle Data Migration Assistant. |
When choosing between the Oracle Data Migration Assistant and the Migration utility, consider these differences:
In general, if you prefer a graphical user interface (GUI) over a command-line interface, and you like highly automated processes with few choices, then use the Oracle Data Migration Assistant. If, on the other hand, you prefer a command-line interface over a GUI, and you like to have more control over the migration process, then use the Migration utility.
Unlike the Migration utility, the Export/Import operation physically copies 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 Oracle8i Import utility loads the exported data into the new Oracle8i database. However, the new Oracle8i target database already must exist before the export file can be migrated into it.
The following sections highlight aspects of Export/Import that may help you to decide whether to use Export/Import for migrating your database.
|
See Also:
Chapter 6, "Migrating Using Export/Import", and also Oracle8i Utilities, for more information about using Export/Import for migration. |
The Export/Import method of migration does not change the source database, which enables 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), then 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, keep an existing Oracle7 production database running while the new Oracle8i 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 Oracle7 database cannot be permitted without the same changes to the data in the Oracle8i database.
The Export/Import method also can be used to upgrade or downgrade a database. For example, the transformation of an Oracle8i database back into an Oracle7 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. For example, although the Export/Import creates an identical copy of the migrated database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems.
As a result of data defragmentation, database restructuring by the DBA, and the new Oracle8i software, expect changes in the following areas:
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, then it may be difficult to determine the cause(s) of the differences.
Data migration by Export/Import offers the following benefits:
Data migration by Export/Import has the following limitations:
Migrating an entire database by using Export/Import can take a long time, especially compared to using the Migration utility or the Oracle Data Migration Assistant. 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, Oracle7 database was migrated to Oracle8i using the Migration utility in about an hour. The same Oracle7 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 Source Database Using Export/Import" took two days.
Consider the following factors related to the extended time required to migrate a database by Export/Import:
When importing data from an earlier version, the Oracle8i 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, then the source database must first be upgraded to at least version 6 before the export is performed.
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 statement, 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 statement and the CREATE TABLE ... AS statement.
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, enables 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 to migrate data from Oracle7 to Oracle8i, 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 and 20), you can use the following SQL statement:
CREATE TABLE new_emp AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp@v7db WHERE deptno IN (10, 20);
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*Plus 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 statement in the Oracle8i SQL Reference and to the COPY command in the SQL*Plus User's Guide and Reference.
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, then complete an estimate for each potential method of migrating the existing database to Oracle8i.
Consider the following factors in your estimates:
Oracle8i binaries may require as much as three times the disk space required by Oracle7 binaries. This threefold increase can require special attention on large batch systems (which may generate dozens or hundreds of executables). The space required for executables also depends on the options you choose for the Oracle8i environment, such as the following:
In addition, the Oracle8i data dictionary may require as much as double the space of the Oracle7 data dictionary in the SYSTEM tablespace. If you plan to use the Migration utility, then you can estimate space requirements for the SYSTEM tablespace by running the Migration utility in CHECK_ONLY mode.
Also, Oracle8i may require up to twice as much RAM as Oracle7. The amount of RAM required also depends on the options you choose for the Oracle8i environment.
Figure 3-5 illustrates the differences in system requirements between Oracle7 and Oracle8i.
After you have chosen a migration method and estimated your requirements, secure the necessary resources for a successful migration.
The memory size of a Oracle8i system depends on concurrent access and the way in which concurrent access is accomplished. Oracle8i supports the following connect options:
|
Option 1: |
Use local connections in dedicated server architecture (also called "two-task common"). Set this option as it was set in Oracle7. |
|
Option 2: |
Use remote connections through SQL*Net. Set this option as it was set in Oracle7. |
|
Option 3: |
Use multithreaded shared servers for local and remote connections. After migrating, set initialization parameters for this option as specified in the Oracle8i Administrator's Guide. |
|
Option 4: |
Use transaction processor (TP) monitors. |
Option 1 requires more memory than Option 2 or Option 3. With Option 1, if both client application and its Oracle server (or shadow) process reside on the same computer, memory is required for both. For example, 100 client application processes connected to Oracle8i results in 100 additional Oracle server processes on the system, totaling 200 in all.
With Option 2, only the Oracle processes reside on the system, and the client processes are connected remotely. Thus, you need to consider only to the size of the Oracle server processes and the size of the available shared memory.
Option 3, using multithreaded server architecture, enables the processes of several local or remote client processes to connect to a single dispatcher process, instead of having a dedicated Oracle shadow process. While not designed as a performance enhancement, multithreaded server configuration enables more concurrent connections on an Oracle8i server, thereby improving throughput. Multiple clients can connect to a single dispatcher, so the memory utilization for concurrent user connections decreases.
|
See Also:
Oracle8i Concepts and the Oracle8i Administrator's Guide and Oracle8i Designing and Tuning for Performance for more information on the multithreaded server feature of Oracle8i. |
Option 4, use of TP monitors, is an alternative for systems requiring a high number of users (greater than several hundred) all performing OLQP/OLTP type transactions. Such transactions are usually short-lived and do not require the user to make a direct connection to the database. All transactions are performed with messages routed by the TP (transaction processor) monitor service. The TP layer provides named services and coordinates service requests with various DBMS systems, including Oracle.
In summary, you can estimate system memory requirements, for a single system, by considering the following factors:
You must choose an Oracle home directory for the new Oracle8i release that is separate from the Oracle7 Oracle home directory. You cannot install the Oracle8i software into the same Oracle home directory that you used for Oracle7.
Using separate installation directories enables you to keep your Oracle7 software installed along with the Oracle8i software. This method enables you to test the migration process on an Oracle7 test database before replacing your production environment entirely.
You can save time by eliminating common migration problems before you migrate your database. Common problem areas include those in the following table:
| Issues That Affect Migration | Description |
|---|---|
|
Oracle8i binaries may require as much as three times the disk space required by Oracle7 binaries. This requirement may cause you to run out of disk space during migration. It is very important that you read "Assess System Requirements vs. Resources Available" to understand requirements before you migrate. In addition, Chapter 4 and Chapter 5 discuss requirements in more detail. During migration, the data dictionary requires 50% more space to hold both Oracle7 and Oracle8i data dictionaries. Actual requirements can be verified by running the MIG utility in CHECK_ONLY mode. |
|
|
The time it takes to migrate is not dependent on the size of the database, but on the number of objects in the data dictionary. For example, actual migration for a 3 1/2 GB database with 25,473 objects on a Sun E6000 with 20 CPUs, with datafiles stripped on the file system on 128 KB slices, can take 1 1/2 hours. Remember to allow extra time for backing up and restoring the database in case of problems. |
|
|
Check for usage of ROWIDs in both user columns and application code (including triggers & packaged procedures). These may need to be converted using the DBMS_ROWID package. See Chapter 12, "Migration Issues for Physical Rowids". Because the format for rowids is different in version 8, the old rowids are invalid and must be converted Check the names of any Oracle7 database objects (for example, tables and columns) that use names that are key words or reserved words for Oracle8i. Usage of key words and reserved words can cause unexpected failures during migration. See Oracle8i SQL Reference for lists of key words and reserved words. Certain version 7 initialization parameters are obsolete in Oracle8i. Remove all obsolete parameters from the Oracle7 initialization parameter file that start an Oracle8i instance. Obsolete parameters may cause errors if used with an Oracle8i database. Also, alter any parameter whose syntax has changed in Oracle8i. See Appendix B, "Changes to Initialization Parameters" for lists of new, changed, and obsolete parameters. |
|
|
Make sure that all Oracle product versions, operating system versions, and third-party software versions are certified against Oracle8i. See the Oracle documentation for your operating system for information. |
|
|
Migration leaves all objects (packages, triggers, views, and so on) invalid except for tables. All other objects must be made valid again by recompilation. You can either do this manually, or you can do this automatically as the objects are first accessed. The latter will of course slow down initial access. All estimated or calculated statistics are lost during migration. These need to be recalculated to ensure proper functionality of the optimizer. Some bitmapped indexes will become invalidated. Check all bitmapped indexes in the DBA_INDEXES table and recreate any that are marked as status unusable after migration. |
|
|
If you are using a Windows platform, and you edit the registry for any reason during the migration process, then you need to reboot your computer. |
|
|
Oracle7 read-only tablespaces are readable by Oracle8i and do not require any conversion. But to prevent Oracle8i rowid conversions from taking place every time a table is accessed, the tablespaces in read-only mode should be made read-write. Perform full table scans on all tables in the tablespace. After the full table scans are complete, you can put the tablespaces in read-only mode again. |
|
|
You can return the database to an Oracle7 version up until the ALTER DATABASE CONVERT statement is run. If a failure occurs during ALTER DATABASE CONVERT (when it is converting the physical file headers of the datafiles), then you must restore the database from backup and rerun the migration. Do not open the database between running the migration and executing the ALTER DATABASE CONVERT statement. |
|
|
To avoid restoring the entire database due to any failures during the ALTER DATABASE CONVERT, put all tablespaces, except SYSTEM and ROLLBACK into read-only or offline normal mode. This causes the ALTER DATABASE CONVERT statement to only convert the datafile headers for SYSTEM and ROLLBACK. If any errors occur, then you need only restore the datafiles for SYSTEM and ROLLBACK and rerun the migration. If the migration is successful, then the headers for the rest of the datafiles will be converted when they are changed to read-write or online. |
|
|
Most migration problems can be avoided if a test migration is performed first. Performing a test migration helps raise any problems that can occur and provides a basis for the amount of time it will take to migrate your production database. See "Test the Migration Process" for more information. |
* These issues apply only to the Migration utility and the Oracle Data Migration Assistant. They do not apply to Export/Import and data copying.
The ultimate success of your migration depends heavily 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 Oracle7 Server Administrator's Guide for Oracle7 databases and the Oracle8i Backup and Recovery Guide for Oracle8i databases. |
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.
The testing plan must include the following types of tests:
Migration testing entails planning and testing the migration path from the source database to the migrated database, whether you use the Migration utility, the Oracle Data Migration Assistant, Export/Import, or other data-copying methods to migrate the production database data to the target database. These methods are discussed in Chapter 4, "Migrating from Oracle7 Using the Migration Utility", Chapter 5, "Migrating from Oracle7 Using the Oracle Data Migration Assistant", and Chapter 6, "Migrating Using Export/Import".
Regardless of the migration method you choose, you must establish, test, and validate a migration plan.
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 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 examines the interaction of each component of the system. Consider the following factors when you plan your integration 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.
For example, if you are using Oracle Parallel Server, and you want to measure the performance gains realized from using cache fusion when you migrate to the new release, then make sure you record your system's statistics before migrating. For cache fusion, record the statistics from the views V$SYSSTAT, V$LOCK_ACTIVITY, and V$LOCK_CLASS_PING. Doing so enables you to compare pre- and post-cache fusion performance statistics.
For best results, run the SQL scripts utlbstat.sql and utlestat.sql to collect V$SYSSTAT statistics for a specific period. Use a collection timeframe that most consistently reflects peak production loads with consistent transaction activity levels. To obtain data from V$LOCK_ACTIVITY and V$LOCK_CLASS_PING, use a SELECT * statement at the beginning and end of the statistics collection period. Repeat this process after cache fusion is running on the new release and evaluate your system's performance as described in Oracle8i Parallel Server Documentation Set: Oracle8i Parallel Server Concepts; Oracle8i Parallel Server Setup and Configuration Guide; Oracle8i Parallel Server Administration, Deployment, and Performance.
|
See Also:
Oracle8i Designing and Tuning for Performance for information about tuning. To thoroughly understand the application's performance profile under the source database, enable the SQL trace facility and profile with TKPROF. |
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 also should 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.
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 clause 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, then execute the statement on the migrated database and compare the performance with the performance of the statement executed on the source database.
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.
Make sure you upgrade any OCI and precompiler applications that you plan to use with your Oracle8i database. Then, you can test these applications on a sample Oracle database before migrating your production database. See "Upgrading Precompiler and OCI Applications" for more information.
Perform the planned tests on the source database and on the test database that you migrated to Oracle8i. Compare the results, noting anomalies. Repeat the test migration as many times as necessary.
Test the newly migrated Oracle8i test database with existing applications to verify that they operate properly with a migrated Oracle8i database. You also might test enhanced functionality by adding features that use the available Oracle8i functionality. However, first make sure that the applications operate in the same manner as they did in the source database.
|
See Also:
Chapter 10, "Upgrading Your Applications", for more information on using applications with Oracle8i. |
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|