Migrating Using Export/Import
This chapter guides you through the migration procedure using the Export/Import method. If you are exporting from Version 5, be certain to read the "Notes for Version 5" section .
The Export/Import method can also be used to upgrade and downgrade releases of the same version. For example, the transformation of a Release 7.1 database into a Release 7.2 database can be accomplished using the Export/Import method. For more information on upgrading and downgrading, see Chapter 9 "Upgrading and Downgrading between Oracle7 Releases".
Topics covered in this chapter include
If you are exporting from, or importing to, a Trusted Oracle database, there are additional features of which you must be aware. For more information about Export/Import and migration, see the Trusted Oracle7 Server Administrator's Guide.
The Export/Import method of migrating a database involves the following two steps:
- Export the data from the database that you wish to migrate (the source database).
For example, if you wish to migrate from a Version 6 database to a Version 7 database, you would first export the desired data from the Version 6 database. Then you would import the exported data into the Version 7 database.
- Import the exported data into the database to which you wish to migrate (the target database).
To use the Export/Import method, the Oracle Corporation recommends that you use the version of the Export utility shipped with the version or release of the source database. Once you have exported the desired data, you must then use the version of the Import utility shipped with the version or release of the target database. You should use the Export and Import utilities for migration only after you have carefully read Part I of Oracle7 Server Utilities.
The Export/Import method of migration allows you to selectively choose tables and users to migrate. A possibly advantageous side effect of using the Export/Import method of migration is that data is automatically compressed.
Data Definition Conversion
When importing data from one version to another version, the Import utility makes changes to data definitions as it reads in the export file. For more information on Version 6 to Version 7 data definitions, see "Data Definition Conversion" in Chapter 7 "Migrating from Version 6 to Version 7".
Space Requirements for Export/Import
The amount of space required for an export depends upon the amount of data you are exporting. Examine the views USER_SEGMENTS or DBA_SEGMENTS to determine the amount of space occupied by the data. These views give you the number of segments allocated, but keep in mind that some segments can be allocated but unused. Refer to the Oracle7 Server Administrator's Guide for more information on estimating space usage.
Time Requirements for Export/Import
The Export/Import method of migration may require several hours. Therefore, you may need to schedule your migration during non-peak, production hours. The time required to complete a migration will, of course, increase for databases that contain large amounts of data or a large number of indexes.
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.
Step 5: Migrate the Source Database (using Export/Import)
This section contains the following reference to Oracle, Version 5:
The following Export/Import steps are completely general and can be applied to any Oracle version or release.
Note: Once an Export/Import migration has started, the database being migrated is unavailable for all production tasks.
To migrate an Oracle database using Export/Import, perform the following steps:
1. Export all desired objects from the original database using the Export utility shipped with the original database.
Additional Information: Installation of a target database is operating system specific. See your operating system-specific Oracle documentation. Read the README.DOC file included with your target database installation for any late additions or modifications to the product.
4. Open the target database and start an instance. From the Server Manager prompt, issue the following commands:
SVRMGR> CONNECT INTERNAL
5. You should pre-create tables, tablespace, and users in the target database as necessary, for example, to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus or Server Manager, you must either run in the original database compatibility mode or specifically make allowances for the data definition conversions outlined on the previous page that occur during import.
6. Import the data and tables from the original database export using the Import utility shipped with target database. Refer to Oracle7 Server Utilities for a complete description of the Import utility.
7. After migrating, you may discover that your tables were not imported properly. Views and synonyms may not be created in the correct order when dependencies exist (for example, when a view is based on a synonym). You may have to perform one of the following procedures to finish importing correctly:
- If all of the rows for all tables were not successfully imported, repeat the import until it completes successfully. Be certain that IGNORE=Y and ROWS=N. IGNORE=Y causes Import to overlook "object already exists" errors and ROWS=N indicates that you do not want to import the rows of table data.
- If some tables were imported successfully, while others were not even created, repeat the import with IGNORE=N and ROWS=Y to ignore the "object already exists" errors and re-import the rows.
For more information on the use of the Export and Import utilities,
see Oracle7 Server Utilities.
- If the tables had some, but not all rows imported, drop the incomplete tables and repeat the previous step.
Notes for Version 5
If you are currently using Version 5 of Oracle, it is recommended that you upgrade first to Version 6. If you choose to export your Version 5 files and import them directly to an Oracle7 release, you should be aware of the following.
- Column level grants are not imported and must be regranted after import.