'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += '
'; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 3 - Migrating an Oracle7 Database to Oracle8

Using Oracle8

Chapter 3

Migrating an Oracle7 Database to Oracle8

Why Migrate?

You may want to migrate an Oracle7 database to Oracle8 for a number of reasons. You may want to take advantage of one or more of Oracle8's new features, outlined in Appendix B, "What's New to Oracle8." You may simply want to benefit from the faster processing that the revised code tree should allow. Whatever the reason, you have a number of options regarding the method you can use to complete the migration process. One of them is a migration tool provided by Oracle. Although this chapter concentrates on the migration tool, it also discusses the alternatives. In the following section you learn about all the options. After reading it, you should be able to determine which method is best to use to migrate your database.

The structural changes in Oracle8
A migration is necessary because the new functionality in Oracle8 requires changes to the basic items in the data dictionary. Until the new dictionary is built, the Oracle8 kernel can't operate successfully. In addition, the structure of the data file header blocks has changed to support some of the new features. These changes must be in place for the code to work correctly. Unlike simple upgrades such as those you might have performed to move from version 7.2 to version 7.3, these structural changes require more than simply installing the new code and relinking the applications.

For more details and a further discussion of the migration options you should read the Oracle8 Server Migration manual, part number A54650-01.

Selecting a Migration Method

The end result of a migration from Oracle7 is a database that contains essentially the same user objects as the original database, but in data files with updated headers and supported by a data dictionary that allows the new Oracle8 features. In some cases, this may not be your ultimate goal. For example, you might want to migrate only a portion of your database for testing purposes, where you want to test only a subset of your applications before migrating the entire database, or because you don't need objects created for now-obsolete portions of the application. On the other hand, you might want to use the downtime required for the migration to make some structural changes to the database. This might include moving segments between different tablespaces or may simply involve coalescing free space in one or more fragmented tablespaces.

We will examine three basic approaches to migration in this chapter: Oracle's Migration utility, export/import, and table copying. You can read the details concerning each strategy in the following sections and decide which best suits you. To get started, look at Table 3.1 to see the basic features of each approach.

Choose your best migration method
Your choice of migration method will depend on how much you want to accomplish as part of the migration and on how much space you have to complete the task. It might also depend on the length of time you can afford to make the database inaccessible, because some methods take much longer than others.

TABLE 3.1  Overview of migration options

Need for
Additional Space:
Migration utility
System tablespaceLeast
Export dump fileGreat
Table copying
Two databasesGreatest

As you can see, the fastest approach, the one needing the least overhead, is the Migration utility. However, you can't include other database-restructuring or related changes if you use this method. The Migration utility will migrate your entire database as it is. With the two other options, you can make changes to the structure, layout, and tablespace assignments, but you'll need more time and disk resources to complete these tasks. They're also more complicated to complete because you need to perform a number of additional steps.

The details of the steps needed to complete each type of migration (and the reasons for choosing each) are listed in the appropriate sections following. Table 3.2 summarizes these options.

TABLE 3.2  Summary of migration method characteristics
Migration Utility:
Copy Commands:
Automatic: Requires little DBA intervention Requires a new database buildRequires lots of attention
Requires minimal extra disk spaceCan use large amounts of disk space Requires both databases to be online
Time is factor of number of objects, not database size Very slow for large databasesVery slow for large databases
Can only migrate forwardCan migrate forward and backward Can migrate forward and backward
Can't use for release to releaseCan use for release to release Can use for release to release
All or nothingPartial migration possible Partial migration possible
No structural changes can be madeConcurrent defragmentation and reorganization Concurrent defragmentation and reorganization

Selecting Oracle's Migration Utility

You need to consider several key factors when planning to use the Migration utility:

Oracle's Migration utility is designed to perform the required structural changes to your existing database. It will actually build a new Oracle8 data dictionary in the same system tablespace as your current Oracle7 dictionary, and it will restructure your rollback segments and the header blocks of the database's data files on disk (see Figure 3.1). Users' objects, such as tables and indexes, stay just as they are, although the ways in which they're accessed-through the data dictionary and then to the files where they really reside-are changed. Again, the new internal structure is designed to make the database more efficient and to support a new set of features.

Figure 3.1 : The Migration utility changes database structures, including the data dictionary, rollback segments, and data file header blocks, in place.

The migration process requires that your current Oracle7 and your new Oracle8 data dictionary reside in the database for a short period of time. This means that the system tablespace, the dictionary's home, must be large enough to hold both versions simultaneously. Therefore, the first item you need to consider before deciding whether to use the utility is the space you have available for the system tablespace. Your Oracle8 data dictionary will be about 50 percent larger than your Oracle7 dictionary. Of course, you may already have some of this space available, but most DBAs will find they need to add more.

Space requirements for the Migration utility
For a period of time, you'll need to have space for two versions of the data dictionary in the system tablespace and for two releases of Oracle in their respective Oracle Home directory structures. The data dictionary will require about 2 1/2 times the space now consumed in your system tablespace. The Oracle8 installation will take 500MB-more if you select many options. If you don't have the required disk space, either consider another migration strategy or wait until you can add the required capacity.

Your next decision point for using, or not using, the Migration utility is whether you want to migrate the database as is, or if you also want to make some changes. The Migration utility is an all-or-nothing tool. You can't migrate portions of the database because the database is migrated in situ. Similarly, because the data isn't being moved, you can't move segments between tablespaces and you can't coalesce free space in your tablespaces as part of the migration process.

Some DBAs don't like to make too many changes at one time, so even if they want to complete some restructuring tasks, they'll make these changes independently of the migration, completing them before or after the migration itself. Others have limited time windows in which to complete maintenance work and so try to make all required changes at a single time. You need to consider what else, if anything, you want to achieve as part of the migration processing. You also need to consider how much time you can take away from your user community while working on these steps.

Consider test options for a migrated database
If you want to convert your data-base to perform functional and similar tests, you can't use the Migration utility for continued work under Oracle7. If you make a copy of it first, you can convert the copy and simultaneously run the production Oracle7 database and the test Oracle8 version. However, making a copy is time-consuming, usually accomplished with the Export/ Import tools or with some form of data unloader and SQL*Loader. You can create a partial test database by using the tables for just one or two representative application tasks. You'd still need to complete integrated tests (if you needed them) after a full database conversion.

If you only need to perform a migration, Oracle's utility is a good choice. First, it's relatively fast due to the changes being made on the current database structures. They don't have to be copied, moved, or otherwise duplicated-all relatively slow processes. In addition, the only factors that really affect the migration speed are the size of the System tablespace and the number of data files. The System tablespace is typically a small portion of the overall database, and the number of data files is limited to 1,022 in Oracle7. Thus, even the largest databases typically take no more than a day to migrate.

Piecewise migration with Export/Import
Using this technique to migrate your database one piece at a time requires you to keep both database versions available, which means maintaining the Oracle7 and Oracle8 executables online. Further complications from this approach occur if the data in the different versions is in any way related. You might need to have users switch between databases to perform different functions or temporarily build a distributed database environment. It may also require that parts of both databases be inactive when it's time to move additional segments from Oracle7 to Oracle8. If you're moving only part of your database because you don't need the rest of it, these issues become irrelevant.

Later in this chapter's "Executing the Migration Process with Oracle's Migration Utility" section you'll find a detailed description of how to complete a migration with the Migration utility. First look at the other migration options and the test plan you need to construct, regardless of the migration approach you'll take.

Using Export/Import

If you decide to use the Export/Import tools to migrate your database, you need to plan for the following resources to be available:

The amount of space and time needed for the initial export depends on the amount of data being exported. If you decide to move only part of your database to Oracle8, you need less time than if you are transferring the entire database. The time also depends on the speed of the devices to which you export. A fast disk drive allows a faster export than a slower tape drive. A very large database may also require a file too large for the operating system or for Oracle to handle. In this case you may need to use some form of operating system tool, such as a pipe, to move the data onto the appropriate media. Figure 3.2 shows the typical export/import steps. By using a pipe, you can send the output from your Export directly to the Import utility's input.

Figure 3.2 : Migrating your database with export/import requires two distinct steps; the export dump file is the intermediary.

Migrate your database via export/import

  1. Perform a full database export from Oracle7, after which you can remove the Oracle7 database and the Oracle7 home directory structure.
  2. Install Oracle8 and then alter the environment variables and your parameter file to point to the Oracle8 structures. (See Appendix C, "Installing Oracle8," for installation instructions.)
  3. Create an Oracle8 database.
  4. Add the required tablespaces to this database.
  5. Perform a full import.

Protect your current database
Before beginning your migration, it's recommended that you ensure you have a backup of the home directory and of the database. Minimally, it's recommended that you keep the scripts you used to build the database in the first place; that way you have at least one easy way to reconstruct the database in case you run into problems with the Oracle8 version.

To learn how to create an Oracle8 database,
To add the required tablespaces to the database,

A variant of this method is to use an unload/loader approach to move the data. You can do this by building your own unloader utility or by finding one in the public domain. An unloader utility needs to extract the rows of data from your tables, as well as the definitions of the tables and all the other database objects; that includes indexes, userids, stored procedures, synonyms, and so on. You can also consider a hybrid approach, using the export to create only the object definitions and the unloader simply to create the row entries.

Advantages of unloader/loader technique
The big advantage to the unloader/loader approach is that you can use Oracle's SQL*Loader utility to reinsert the data when the definitions are applied to the data-base. This utility, running in its direct path mode or-even better if you have the hardware to support it-in parallel direct mode, can complete the job of loading records much more quickly than the Import program.

To learn more about the Export and Import utilities,
To learn more about SQL*Loader,

Using Table Copying

As with export/import, you can use table copying to move just part of your database, to stage the migration, or simply to avoid migrating unneeded elements. The same caveats for incomplete database migration apply in this context as for the export/import approach.

To perform table copying, you need to have both databases (Oracle7 and Oracle8) available simultaneously, which includes not just the database storage but the two Oracle Home structures and the environments to run them both simultaneously. This makes the approach the most space-intensive of all three methods (see Figure 3.3).

Figure 3.3 : Both databases must remain online during a migration using table copying.

The other drawback to this approach is that it really does only copy table definitions and their contents. To build the same database in the Oracle8 environment that you started with-including all the users, stored procedures, synonyms, views, and so on-you still need to find a method to copy these from Oracle7. It's therefore likely that you'll need to perform a partial export/import or even a data unload/reload, as discussed in the preceding section.

General steps for migrating from Oracle7 to Oracle8

  1. Install Oracle8.
  2. Create an Oracle8 database.
  3. Add the required tablespaces and users.
  4. Select the Oracle7 or Oracle8 database as your primary database.
  5. Configure SQL*Net or Net8 with a listener to connect to your secondary database.
  6. Create or modify a TNSNAMES.ORA file to identify your secondary database.
  7. Use SQL*Plus to issue the required COPY commands for your primary database.
  8. Add the views, synonyms, stored procedures, and other objects dependent on the tables into your secondary database.
  9. Drop your Oracle7 environment.

Warning: Avoid points of no return
As always, before moving or destroying your production system, you should make a backup first.

Copying across database links
Rather than use the SQL*Plus COPY command, you can complete step 7 by creating data-base links in your primary database to access the secondary database and by using SQL CREATE TABLE...AS SELECT commands to copy table definitions and data between your database.

You find a detailed discussion of these steps later in the section "Executing the Migration with Export/Import or Table Copying."

Testing the Migration

Software development and maintenance efforts should always include a good test plan as part of the acceptance strategy. Your migration from Oracle7 to Oracle8 is no different. Indeed, because you already have a working production database, you need to devise a test strategy to ensure that the end results of the migration contain the same, or better, production capabilities. This means testing not only the capabilities, but the performance and results as well.

You can apply a number of types of tests to help assure you and the users that everything is working properly at the end of the migration. In the next sections you see what types of tests you can use and when to use each type. From these selections, you can build a test program and identify the resources needed to complete the tests. Oracle Corporation strongly recommends running all these tests before concluding the migration.

Identifying Types of Tests

You can perform six basic types of tests to validate the migration:

Migration Test

This test validates your migration strategy, whether it's to use the Migration utility or one approach to transfer the data from Oracle7 to Oracle8. It's intended to help you determine whether you've allotted sufficient resources for the migration, including disk space, time, and personnel.

Running a migration test

  1. Create either a test copy of your whole database, or a subset if you don't have the resources to test the complete database migration. You don't have to complete this step if you're using export/import or table copying.
  2. Execute the migration with the utility you plan to use when migrating your production database.
  3. Upgrade the tools used by your application.

Resolving problems with migration tests
If this test fails, you may need to rethink the chosen migration strategy and possibly choose another method. For example, if the time taken is greater than your users are willing to allow, you may need to plan a staged migration, or if you were hoping to use export/import, you may need to consider the Migration utility instead.

Minimal Test

This type of testing involves migrating all or part of an application and simply attempting to run it. No changes are made to the application, and performance and value testing should be attempted. This test simply confirms that the application can be started against the migrated database; it's not intended to reveal all problems that could occur.

Resolving problems with minimal tests
A failure of a minimal test typically indicates problems with the migration itself, such as missing tables, synonyms, views, or stored procedures. It's most likely to fail if you've tried a partial database migration or if you're using one of the data-transfer strategies rather than the Migration utility.

You should perform this test after a successful migration test and before moving on to the more rigorous tests. It will require relinking the tools used by the applications, so you'll need to maintain a separate copy of the application if the users need to continue using the production Oracle7 database.

Running a minimal test

  1. Complete the migration test.
  2. Have users, developers, or a test suite run selected programs from the applications.

Functional Test

The functional test follows the minimal test and ensures that the application runs just as it did before the migration. This involves having users, or simulated users, executing the different application components and verifying that the outcome is the same as in the pre-migrated database. The results of any queries, reports, or DML should be the same as they were on the pre-migrated database.

If you're using Oracle8 to enhance the application, you may also want to add the new functionality to each application during this test phase to ensure that the application continues to provide reliable results with the new features in place.

Conducting a functional test

  1. Complete the migration and minimal tests.
  2. If you intend to add new functionality to your applications, have the developers make these changes.
  3. Have users, developers, or a test suite execute the applications, testing all functions and features.

Tracking the cause of errors detected during functional testing may involve close cooperation between the DBA and the application developers. It's important, therefore, to ensure that the development organization is apprised of this testing phase and can commit the necessary resources. If you're running third-party application software, you may need to get help from your vendor should this test fail.

Testing third-party applications
Some vendors may not be aware of all the changes made in Oracle8. If you're using third-party applications, you shouldn't commit to a completed migration until the functional tests have been rigorously completed.

Integration Test

Integrated testing involves executing the application just as you did in the pre-migrated database. This includes establishing client/server connections, using any GUI interfaces, and executing testing online and batch functions. This test ensures that all the application's components continue to work together as before.

Resolving problems with integration tests
Should you run into problems with these tests, you'll have to isolate whether the cause is in a single component, such as SQL*Net or Net8, or whether it's part of the overall migration. Generally, if you've completed the functional testing successfully, the likelihood is that the problem is with one component, or the interface between a pair of components.

Running an integration test

  1. Complete the migration, minimal, and functional tests.
  2. Install and configure any communication software, such as Net8, for client/server or multi-tier architectures.
  3. Install and configure any drivers, such as ODBC drivers, that the applications use.
  4. Have the users, developers, or a test suite run the applications across the network, using the same front-end tools and middleware that are now planned for database access.

Performance Test

Although the kernel code tree has been optimized in Oracle8, you might discover that some parts of your applications aren't running as well as before the migration. This could be due to a number of factors, such as tuning efforts that were made to avoid a problem in the earlier release. You need to run the performance tests to ensure that overall processing throughput is at least the same as, if not better than, the Oracle7 performance.

Resolving problems with performance tests
If you find performance problems, you should attempt to resolve them by using the database tuning techniques described in Chapter 20, "Tuning Your Memory Structures and File Access," through Chapter 23, "Diagnosing and Correcting Problems."

Conducting a performance test

  1. Complete the previous tests to ensure that you're running the equivalent of a full production system.
  2. Have users run their interactive and batch programs as they would in a production environment.
  3. Monitor and record the database performance by using queries against the various dynamic performance tables or by using such tools as the UTLBSTAT.SQL and UTLESTAT.SQL scripts.
  4. Solicit feedback from users as to their perceptions of performance and response times compared to the current production system.

If you've been monitoring your Oracle7 database with the various analytic and diagnostic tools, you can easily make comparisons by using the same tools on the migrated database.

For an overview of the dynamic performance tables,
A detailed description of the UTLBSTAT.SQL and ULTESTAT.SQL utilities begins on

Volume/Load Stress Test

Ideally, you should be able to test your migrated database against a realistic workload. This includes the amount of data being processed (volume) and the concurrent demands on database (load). To perform such testing, you may need to set up automated procedures rather than expect your user community to test your database under realistic conditions while continuing work on the unmigrated production version. This test will ensure that the database is ready for the workload intended for it and should also display any other problems that the other tests didn't uncover.

Performing volume/load stress tests

  1. Assemble either a workforce or automated scripts to represent a normal, everyday workload.
  2. Exercise the system by having the users or scripts execute the applications concurrently.
  3. Monitor and record the system performance as in the performance testing.

Building a load test
If you have software that can capture the keystrokes entered during an interactive session, you can use this to collect the session work completed by the users in earlier tests. You can use these to build scripts that emulate those sessions. Run multiple concurrent copies of these scripts to simulate different levels of system load.

Due to changes in the structure and use of internal structures-the data dictionary, rollback segments, and ROWIDs-you may find that the behavior of the database changes differently from the way it did in Oracle7. Although most resources won't reach a performance threshold as quickly as they might in Oracle7, you can't depend on this. It's therefore not advisable to assume that if you achieve performance equal to or better than Oracle7 with a small number of concurrent sessions manipulating a few tables, this performance level will be maintained under full volume and load.

Addressing problems with a volume/load stress test
Problems encountered while testing for volume and load should be addressed by applying the tuning strategies discussed in Chapters 20 through 23 of this book.

Setting Up a Test Program

Keeping in mind the various tests you need to perform, your test program should address the when, where, what, who, and how questions associated with each test. The test program should also address the methods you'll use to compare the actual results with what should be expected if the test is successful. This may include creating test suites that can be run on the current production database and on the Oracle8 test database; on the other hand, it could include simply recording the sizes of such objects as temporary segments and rollback segments in the test database so that you'll be prepared to size the associated tablespaces appropriately when the migration is performed for real.

When to perform the tests depends on the resources you need and their availability. For example, DBAs are used to working on major database changes during periods of low activity, such as late at night, weekends, and holidays. If your test needs the participation of developers or end users, however, you may have to plan the test during normal working hours.

Ensure the integrity of your test environment
There's no point performing a test for validity after migration if the original version is flawed. Similarly, if you plan to test just part of your database, you need to ensure that you'll get a valid subset of the data. For example, if you're going to test a function that adds new records to a table and a sequence generator is used for the primary key values, you'll have to ensure that the sequence generator is avail-able in the pre-migration set of objects.

Where to perform your tests depends on your computer environment. Ideally, you want to test as much of the database as you can-all of it if possible. This may require using a separate machine if one is available. A test or development machine is probably the best place to run the various tests. Remember, however, that you may have to schedule this machine if it's regularly used by the developers; some tests may require shutting down the Oracle7 database(s) running there.

What to test depends on the test you're performing. By referring to the previous section's descriptions of the different tests, make sure that you have the resources to complete the test and record the findings in a meaningful way. For example, you won't learn anything about whether the Oracle8 performance is equal to, better than, or even worse than the Oracle7 performance if you don't have a method to record the performance characteristics you want to measure in each environment.

Who to involve in the testing also depends on the type of test. The earlier test descriptions should help you identify the type of personnel needed for each one. You may want to form a migration team with members from your system support, developer, and end-user communities if you're going to migrate a large database. This team can help you schedule the tests in such a way that they don't cause major conflicts with other groups. For example, you would want to avoid running a test that needs input from the users during times of heavy workloads, such as month-end processing. The team can also help you find the best resources within their respective groups to aid with the tests and can act as your communication channel back to the various groups regarding the migration progress.

How to complete the tests depends on your environment as well as the test type. You need to decide if you'll run tests on the whole database or on partial applications. This, of course, depends on the resources you have available. Similarly, you need to ensure that you have the resources, including people and tools, to fix any problems encountered during the testing so that you can keep the migration project on track. The individuals needed to fix a problem may not be the same as those involved in the test itself.

The how question needs to include how you'll obtain your test data. If you want to test against the entire database, you'll need a method to create an exact copy of it, possibly on a separate machine. This could involve an export/import or some form of Unload/Reload utility. If using the latter, you need a verification test suite to ensure that the copy was successful.

After your test plan is in place, you can begin the process of fully testing a migration. Ideally, you'll run every test on a complete test version of the migrated database before tackling the migration of the production system.

Testing and Retesting

As you complete each test in your test plan, you should be able to determine whether it's successful. If the test is successful, you can move to the next one; if it isn't, you need to fix the problem and retry the test. This part of the testing isn't always as straightforward as it sounds.

Suppose you encounter an error that involves a missing view that should contain the join of two tables. The error could reflect that one of the two underlying tables is missing, or that the view definition is no longer available or valid. If you've performed a full migration, you need to determine whether the Migration utility "lost" the view or table or whether the view (or table) was missing before the migration was performed. If you don't have a copy of the pre-migrated database (or at least a way to reconstruct it), you can't determine the cause of the error. You'll have to go back and redo all the steps you took to get to this testing point, which may include copying the current production database over to your testing environment. Of course, because it has been in active use since you made your initial copy, the current copy you make won't be the same as the one you used for the test that failed. For example, the view may now have been deliberately dropped. You'll have to repeat all the tests to validate this new version of the database.

If you're testing a subset of the database, the missing view or table may not have been created because it wasn't included in the objects selected for migration. In this case, you need to decide whether you can just add it now and continue with your testing. Otherwise, as in the preceding case, you'll need to start over with the migration of the test set from an Oracle7 source and repeat all the testing.

If you run into a problem that you can't easily resolve, you have two options:

Performing the Migration

After you complete an acceptable test plan, you should use it to migrate and test a non-production version of your database. When you're certain that you're ready, you can perform your production system's migration.

Skip sections that don't relate to your chosen migration approach
If you're planning to use the Migration utility, continue with the following section. If you intend to use export/ import for your migration, skip to "Executing the Migration with Export/Import or Table Copying" in this chapter.

Executing the Migration Process with Oracle's Migration Utility

The database is addressed as a whole in the following, detailed descriptions of the tasks you'll have to perform to complete your database migration. If you need to migrate only a portion of your production database, you must create a temporary Oracle7 database to hold just that portion. Apply the migration processing to this temporary database only.

General steps for preparing to migrate with the Migration utility

  1. Load the Migration utility by using the Oracle8 installer.
  2. Ensure that you have sufficient free space in the system tablespace.
  3. Recover or remove any offline objects.
  4. Remove any user with an ID of MIGRATE.
  5. Override any in-doubt transactions.

After you complete the first task (loading the Migration utility), you may want to put your database into restricted mode to prevent users from making unwanted changes as you prepare it for the migration. To do this, perform a shutdown and then reopen it with the RESTRICTED option. This will disconnect all current users and allow only those with the restricted session privilege to reconnect. If you're one of many DBAs with such a privilege, you should coordinate with your colleagues to ensure that only one of you is working on the migration process.

For details on the various options for starting a database, including the RESTRICTED option,

Loading the Migration Utility

The Migration utility for converting from Oracle7 to Oracle8 is provided as part of the Oracle8 installation media. You can load just the Migration utility by running the standard installation program for your specific hardware platform (the orainst program in UNIX and the SETUP.EXE program in Windows NT, for example).

Suggested responses to the installer's questions

  1. Choose Install, Upgrade, or De-install on the Select the Installer Activity screen.
  2. Choose Migrate from Oracle7 to Oracle8 on the Select Installer Option screen.
  3. Choose Install Migration Utility on the Select an Oracle7 to Oracle8 Migration Option screen.

The installer will place a number of files into the Oracle7 home directory structure, including the following:

Confirm success of your installation
Following the installation, you should check the log file to confirm that the files were successfully installed.

Checking for Sufficient Space in the System Tablespace

The Migration utility needs space for the Oracle7 and Oracle8 data dictionaries in the system tablespace. You can determine whether you have sufficient space available by using a special option in the Migrate utility. Simply run the utility with the CHECK_ONLY option set to TRUE:

mig check_only=true
mig80 check_only=true

Depending on your operating system, the name of the utility and the format of the results will vary. You'll typically need free space equivalent to about 1 1/2 times the space consumed by your current data dictionary.

Confirming That No Tablespaces or Data Files Need Recovery

All offline tablespaces should be brought back online unless you're certain that they were taken offline by using the TEMPORARY or IMMEDIATE option. After you bring them back online, you can use one of these options to take them back offline.

Unusable tablespaces
If you can't bring a tablespace back online because it needs recovery that can't be completed, you need to drop it; it will be unusable under Oracle8 anyway.

All data files must also be online. You can check the DBA_DATA_FILES view for the status. If any are offline and you can't bring them back online because they need recovery, the Migration utility will fail with errors.

Don't have a user called MIGRATE
The migration process will create a user called MIGRATE. Because this user is eventually dropped with the Oracle7 data dictionary objects, you should ensure that you don't already have a database user with this name. If you do, create a new schema to contain the MIGRATE user's objects, or use a user-level export and plan to reimport the user following the migration. In either case, remember to drop the MIGRATE user after you save the objects from the schema. See Chapter 9 "Creating and Managing User Accounts," for information about user and schema management.

For a brief discussion of views,

Ensuring That You Don't Have Any Pending In-Doubt Transactions

If you've used distributed transactions in your Oracle7 database, you need to check that none are still pending due to problems with the two-phase commit mechanism, such as lost network connections or offline databases. You can find such transactions by examining the DBA_2PC_PENDING table. If you have any such transactions, you need to commit or roll them back manually. You can find the instructions on how to do this in your Distributed Database documentation, including details on how to determine if you should commit or roll back.

Performing a Normal Shutdown of the Oracle7 Database

When you've readied your database for the migration by performing the preceding tasks, you can shut down your database. You need to shut it down cleanly-that is, with the NORMAL or IMMEDIATE option. If you can't do this and have to use the ABORT option, you need to restart the database and then shut it down again with one of the other options. This ensures that there are no pending transactions or incomplete checkpoints, leaving your database in the appropriate state for the migration.

For details on database shutdown options and commands,

Backing Up the Database in Case of Problems

After your database is shut down, you should make a full backup just in case the migration process needs to be repeated, as discussed in the earlier section on testing. The backup needs to be made any time you plan to migrate a database that has been opened subsequent to your last pre-migration backup-unless you don't mind losing the changes made during that period.

Hot backup option before migration
If you don't have the time to complete an offline backup, you can complete an online backup immediately before shutting it down for the migration. Remember that as soon as it's closed, you should back up the online redo logs as well. If you need to restore the Oracle7 version for another migration attempt, you have to recover the backup to a stable point, which requires the contents of the online redo.

For an overview of hot backup strategies,
Detailed descriptions of hot backup steps are available on

Run the Migration Utility

You may need to set certain system values before running the Migration utility program. These will vary between operating systems, and you need to examine your platform-specific documentation for details on what to set and what values they require. For example, the TWO_TASK and ORA_NLS33 variables have to be set appropriately. You also need to use this documentation to find out how to run the migration program and provide the appropriate options. The options for the migration program are documented in Table 3.3.

Table 3.3  Options for the Migration Program
Description and Use:
CHECK_ONLY or NO_SPACE_CHECK These mutually exclusive options are used to determine whether the System tablespace is large enough to complete the migration or to avoid making this check. You should need the CHECK_ONLY option only in the premigration steps, as discussed earlier.
DBNAMEThis option specifies the name of the database to migrate.
NEW_DBNAMEThis option specifies the new name for the database. By default, the new name is DEFAULT, so you're strongly encouraged to set this value.
MULTIPLIERThis option changes the initial size of one specific data dictionary index. A value of 30 makes it three times larger, for example. The default value (15) should be adequate for most users.
NLS_CHARBy setting this option, you can change the National Language Standard (NLS) NCHAR character set used for your database. Not setting this option leaves your Oracle7 character set in place.
PFILEThis is the name of the parameter file to be used by the instance in which the migration will occur. Not setting this option causes the default file to be used.
SPOOLThis option names the full path and filename where the Migration utility will write its log file. When the Migration utility completes its processing, you should check the spool file to see if any errors occurred.

Don't open the database as an Oracle7 database at this point; further conversion steps need to be completed before the database is usable again. Prematurely opening the database corrupts this intermediate version and you won't be able to complete the migration process successfully.

Time to take a backup
You should make a backup of this version of the database because it can be used as your first Oracle8 backup, as well as an intermediate starting point for another migration attempt.

Moving or Copying the Convert File

The Migration utility created a convert file for you in the Oracle7 environment. This file will be found in the DBS, or related directory, under the Oracle7 home directory and will be named CONVSID.DBF (where SID is the Oracle7 instance name). You'll need to move this file to the corresponding directory in the Oracle8 home directory, renaming it to reflect the Oracle8 instance name if this is different. If you aren't going to uninstall Oracle7 at this time, you can wait and complete the file transfer in a single step. If you're going to uninstall Oracle7, make a copy of this file outside the Oracle directory structure so that you can find it later.

Installing the Oracle8 Version of Oracle

If you don't have space for the Oracle8 installation, you can remove the Oracle7 directory structure before beginning this step. However, it is recommended that you back it up first, in case you need to use your Oracle7 database again. Use the Oracle7 installer to uninstall Oracle7 and the Oracle8 installer to add the Oracle8 files. Your platform-specific documentation explains how to run the installer for both operations.

When installing Oracle8, be sure to select the Install/Upgrade option in order to prevent Oracle from creating a brand-new database that you won't need.

Adjusting Your Environment Variables and Parameter File

You need to ensure that your operating system is aware of and using the new Oracle8 code before continuing with the migration process. The remaining migration tasks require the Oracle8 executables to manipulate your database. This means resetting the pointers to Oracle Home and related structures, whatever they might be for your operating system. Again, you need to refer to your platform-specific documentation if you aren't sure what these are.

You also need to check your Oracle7 parameter file for obsolete or changed parameters. These are listed in the Oracle8 Server Migration Manual, available as part of the Oracle8 distribution media. Table 3.4 lists the non-platform-specific parameters that you need to address.

Table 3.4  Obsolete and changed parameters
Oracle7 Name:
Oracle8 Name:

Use your favorite editor to make any necessary changes to your parameter file. You may also want to move it to a new directory so that it stays with your other Oracle8 files. If you use the default conventions for your parameter filename and location, see the Oracle8 documentation for your specific system to identify what these need to be.

Removing or Renaming the Current Control and Convert Files

You'll perform one conversion step a little later that will create new control files for your database. At this time, therefore, you should remove the control files your database was using. Drop them (if they're safely backed up) or rename them so that you can find them again if needed.

If you've already uninstalled Oracle7, you should have copied the convert file to a safe place as discussed earlier in "Moving or Copying the Convert File." You should now move this copy to the appropriate directory in your Oracle8 Home directory structure. If you haven't uninstalled Oracle7, simply copy the file, renaming it if necessary, to the corresponding directory under Oracle8; see the earlier section titled "Moving or Copying the Convert File" for details.

Starting an Instance

Use Server Manager and the INTERNAL user to start an instance. You should then start a spool file to track the remaining conversion tasks performed on the database. You can use the following script to complete these steps by using Server Manager running in line mode:

SPOOL convert

Complete the remaining database conversion activities

  1. Issue the command ALTER DATABASE CONVERT to build new control files and update the data file header information.

This is a point of no return!
After ALTER DATABASE CONVERT completes, you can no longer use your database with Oracle7 code or programs.

  1. Open the database, which will convert the rollback segments to their Oracle8 format.
  2. Run the CAT8000.SQL script to do the following:

Locating the CAT8000.SQL script and the log file
If you aren't in the directory where the CAT8000.SQL script is located, you need to include the full path name. You'll find this script in the Oracle home directory, under the ADMIN directory, which is under the RDBMS directory. After issuing the host prompt to check for the session's log, you should find the log in your current directory. It will be named CONVERT.LST, but the name may be case sensitive on some operating systems.

  1. Shut down the database if the preceding tasks are all successful.
  2. Complete the post-migration tasks.

Perform these steps while still connected to your Server Manager session by using the following commands:


Start the CONVERT.LOG file here to check for errors, and then EXIT back to Server Manager.

If you find errors in the log file, you may need to repeat the tasks discussed in this section; you may instead, depending on the severity of the problem, have to repeat most or all of the migration process after correcting the cause of the errors.

If you've completed your migration at this point, you can skip the following discussion of alternate migration techniques. Continue with the section "Completing Post-Migration Steps" to learn how to make your Oracle8 database available to your applications and users.

Executing the Migration with Export/Import or Table Copying

In this section you look at two other options for migrating your database that you may want to use instead of the Migration utility. Both require you to move data between an Oracle7 and Oracle8 database. Therefore, unlike the Migration utility process, you have to build an Oracle8 database yourself, or let the installer build one for you. In all probability, you will need to customize any database you build to match the structure of your Oracle7 database.

The main difference between these two approaches is that the export/import option lets you work on each database independently, so you can remove your Oracle7 database before building and populating your Oracle8 database. This can be useful if space is at a premium. The table-copying method requires that the Oracle7 and the Oracle8 databases be online during the migration process. If you have the space, you can also leave both databases in place during an export/import. However, if the Oracle7 database is available to users following the export, the changed data will have to be identified and migrated separately.

In the following sections, it's assumed that you're going to keep your Oracle7 database in place during the whole migration process, but it's pointed out when you could drop it depending on what method you're using. The following steps are based on this assumption.

Step 1: Install Oracle8

Install Oracle8 on your system by using the Oracle installer as described in your platform-specific documentation. You can choose to have the installer build your initial Oracle8 database if you prefer.

Step 2: Prepare Your Oracle8 Database

If you've let the installer build your database, you simply need to add the tablespaces that match your current Oracle7 structure. You should also add the same number of rollback segments and redo logs as you're now using in Oracle7. If you're using the table-copying method, you also need to create the users at this point.

Copying tables across database links
If you plan to use CREATE TABLE...AS SELECT commands to make table copies, you also need to build database links that allow the Oracle7 and Oracle8 databases to work together. Data-base links themselves are described in the Oracle8 SQL manual and in the Distributed Database documentation. If you aren't familiar with distributed processing and database links, this is probably not a good method to use for your migration.

Step 3: Prepare to Migrate

If you're performing the export/import process, you should now create the export file of your full database, or whatever pieces of the database you want to migrate. After this, you can shut down your Oracle7 database and uninstall Oracle7 if you want.

If you're performing table copying, you need to define the network protocol and addresses for SQL*Net or Net8.

If you don't already have these tools configured, you might as well use Net8, which is discussed on

Step 4: Move the Data

Now you can move the data into the Oracle8 database. By using export/import, you simply execute the Oracle8 import command and provide the name of the file you exported in step 3. If you're performing table copying, you can use either the COPY command available in SQL*Plus or the SQL CREATE TABLE...AS SELECT command. The former identifies the target (Oracle8) or the source (Oracle7) database, or both, using SQL*Net or Net8 aliases from the TNSNAMES.ORA file. The latter uses a database link name in the new table name or the name of the table being copied, depending on where the command is running. If you're in the Oracle7 database, the link name is appended to the new table name; if you're in Oracle8, the link name goes on the source table name.

Your database should be ready-if you used export/import-after you complete the data transfer. If you performed table copying, you may still need to duplicate the other objects in your Oracle7 database, such as indexes, views, synonyms, and privileges. The simplest way to do this is with an export/import of the full database. In this case, though, you wouldn't export the table rows and would have to allow the import to ignore errors due to existing tables.

Completing Post-Migration Steps

The following sections cover the steps needed to make the database accessible by the applications and the users of those applications. You might not need to follow each step exactly, depending on your system and application mix.

Precompiler Applications

Even if you don't intend to make any changes to your precompiler applications, you need to relink the applications before they will run against the Oracle8 database. You should relink them to the SQLLIB runtime library provided with the Oracle8 precompiler. Of course, if you want to take advantage of some new features of Oracle8, you need to modify your code and observe the standard precompile and compile steps.

OCI Applications

You can use your Oracle7 OCI applications with Oracle8 unchanged. If you have constraints in your applications, however, you should relink the applications with the Oracle8 runtime OCI library, OCILIB. You can choose a non-deferred mode to relink, in which case you'll experience Oracle7 performance levels, or you can use deferred mode linking to improve performance. The latter may not report any linking, bind, and define errors until later in the execution of the statements than you're used to seeing. Specifically, they will occur during DESCRIBE, EXECUTE, or FETCH calls rather than immediately after the bind and define operations.

Obsolete OCI calls
Two calls used in OCI programs, ORLON and OLON, are no longer supported in Oracle8; you should use OLOG in their place. Although OLOG was originally introduced for multithreaded applications, it's now required for single-threaded code.

SQL*Plus Scripts

Ensure that your SQL*Plus scripts don't contain a SET COMPATIBILITY V7 command. If they do, change it to SET COMPATIBILITY V8. Also remember to check any LOGIN.SQL scripts for this command.


The only severe problem you might run into with SQL*Net is if you're still using version 1. Oracle8 will only communicate via SQL*Net version 2 or Net8. The SQL*Net v2.0 Administrator's Guide and SQL*Net version 2 Migration Guide explain how to upgrade to version 2. As with other Oracle8 products, Net8 gives you a lot of additional features that you may want to consider using.

Enterprise Backup Utility (EBU)

Oracle8 has replaced the Enterprise Backup utility (EBU) with Recovery Manager (RMAN). Therefore, any code and routines you've developed around EBU will need to be replaced. In addition, the backup volumes created under EBU aren't usable by Oracle7. EBU and RMAN both use the same Media Management Language to talk to third-party storage subsystems, so you should still be able to use any tape subsystems and tape management modules that you used with EBU when you convert your backup routines to RMAN.

Standby Databases

A standby database must run on the exact same release as the production database that it mirrors. Therefore, you need to upgrade any standby database after you upgrade your Oracle7 production database.

Migrate your standby database to Oracle8

  1. Apply all redo logs created under Oracle7.
  2. Ensure that the primary database is successfully opened under Oracle8.
  3. Install Oracle8 on the standby database platform.
  4. Copy the production database's control file and first data file to the standby site.
  5. Make a new control file for the standby database.

Impact of using new Oracle8 features
If you begin using Oracle8's new features, you may have to make further changes to applications by using the products already discussed, and you may have to change code and procedures related to the tools listed here. For example, you have to run a CATEXP7.SQL script if you want to export Oracle8- partitioned tables to an Oracle7 database.

Migration: Final Considerations

The following Oracle products will run unchanged against your Oracle8 database:

You should consider the possible improvements you might obtain, however, if you begin using some of the appropriate Oracle8 enhancements. This doesn't have to be done immediately, of course, but over a period of weeks or months, as time permits. You should also ensure that the application developers are aware of the possible enhancements to their code.

© Copyright, Macmillan Computer Publishing. All rights reserved.