Optimizing Oracle Export and Import
Oracle provides the Export/Import utility combination for data transfer. The entire data transfer procedure can take a very long time for very large databases. Oracle expert Megh Thakkar shows several techniques that can be used to improve the performance of Export/Import.
You can populate an Oracle database with information from various sources. Oracle provides several utilities to facilitate the loading and unloading of data from the database.
Oracle provides the Export/Import utility combination for data transfer. Export generates an Oracle proprietary file that contains the necessary data and information to perform the data transfer. The dump file generated from Export can be read only by the Import utility. The entire data transfer procedure can take a very long time for very large databases. This article shows several techniques that can be used to improve the performance of Export/Import.
The Export dump file is in a binary format; it's not editable, but you can use a text editor to view portions of this file. The dump file contains data, as well as all the DDL commands to re-create the data dictionary. Editing the export dump file will lose some binary information, making the file unusable.
For the most part, export dump files aren't specific to a version of Oracle. Dump files also aren't OS-specific; as a result, you can use Export/Import to transfer data between databases on different platforms.
Export/Import can be used to move information as follows:
-
Between databases on the same version of Oracle
-
Between databases on different versions of Oracle
-
Between databases on different operating system platforms
-
From one schema to another schema
-
From one tablespace to another tablespace
-
In the entire database
Exports can be performed at three levels of data collection:
-
An incremental export backs up data that has changed since the last incremental backup.
-
A cumulative export backs up data that has changed since the last cumulative backup. It can be used to condense incremental backups.
-
A complete export backs up the entire database.
Optimizing Database Exports
Follow these guidelines to optimize your exports:
-
Minimize I/O contention by placing the export dump file on a disk separate from the disk containing the data files.
-
Break the export into smaller exports, and then run each export in parallel.
-
Export only the information needed during recovery. For example, don't export indexes if they can be easily re-created. If roles, profiles, and security information don't change after the initial setup, you don't need to export them every time.
-
Export to a high-speed disk rather than to a tape.
-
Determine whether incremental and cumulative exports will be really useful. Suppose that many changes are happening in the database and that about 80 percent or more must be backed up every time. In this case, you're better off using a full database export and thereby dealing with only one file instead of many files.
-
Use a large buffer size. The BUFFER parameter determines the number of rows in the array to be exported. The larger the buffer size, the better the performance. The setting for BUFFER is influenced by the amount of memory on your system; be careful not to introduce paging (by setting it too high).
-
Don't back up read-only tablespaces. Also, make sure that the read-only tablespace wasn't read-write during the last export and has switched to read-only since that time.
-
Schedule the export during periods of the least database activity.
-
Make sure that the frequency of performing an export is proper. In other words, if not much activity is going on in the database, you're better off exporting less frequently.