Why a Parallel Export Script?

This chapter describes a script that performs fast Oracle database exports using Symmetric Multi Processing (SMP) machines. The performance benefit this script provides is nearly linear relative to the number of CPUs. For example, with 32 CPUs available for backups, a speed of nearly 32 times the standard database export can be achieved. Therefore, this script can be valuable for situations where exports of large amounts of mission-critical data need to be performed.

The script was contributed by its authors, Neal Sundberg and Mike Ellison from Qualcomm Inc. This approach was presented by Qualcomm in a paper called “Parallel Export/Import Today on SMP Machines” at the 1995 Oracle Users Week conference. The script continues to add value to Oracle’s parallel features today. At Qualcomm, the IT staff routinely run this script on Sun Enterprise servers to perform an export of a 250 Gbyte database that takes approximately two hours. This task would be difficult to accomplish without using the parallel processing available on SMP machines.

The Oracle utilities for export and import are basically single-threaded processes that use a single CPU, with the exception of the parallel query feature. Under certain circumstances, the parallel query feature enables spawning of parallel processes that performs a SELECT operation against a large table. The degree of parallelism can be set for the table to the desired number of processes (such as four or eight). This parallel query feature takes advantage of multiple CPUs on an SMP machine, possibly breaking down the SELECT statement into separate smaller SELECT statements. However, it might not be desirable to apply parallelism to all tables since it could cause database problems.

Oracle provides features that support physical backup and restore, and logical backup and restore. The physical backup and restore features are the standard hot and cold backups, where a static image is taken of database files (although hot backups are not quite static). Basically, the database files are copied without really knowing what they contain. With this approach, if a disk drive is lost, or a controller writes some bad bytes to the database, a recovery can be made from the database point of view.

Export/import, on the other hand, takes the logical point of view. An export contains all the commands required to recreate the database, as if the commands were processed through the Oracle engine. For example, it includes commands such as CREATE DATABASE, ADD DATA FILES, and CREATE TABLE, as well as commands to add data to tables. Therefore, an export/import is similar to redoing the entire database by typing everything in by hand.

The export/import approach can be useful for disaster recovery or when migrating from one operating system to another. It also enables the logical restoration of part of a lost database. To restore one or two lost tables, the physical methodology cannot be used unless the tables are in their own table space, which is unlikely. However, there are cases where an export/import can be used to rebuild an entire system, or to defragment a database. This is probably the only way to do it without buying third-party tools.

The export/import approach can supplement a standard hot backup, and can be a lifesaver. For example, suppose your disk RAID and hot backup failed. In this situation, an available database export would serve as another layer of protection against failure in a system and would enable the recreation of the database. However, an export is only as good as the time it was taken as it is not possible to apply logs because of the system change numbers (SCNs).

Missing a days worth of transactions is better than having no data at all. However, it would not be advisable to keep database exports around for system failures, except as a third or fourth line of defense (depending on your configuration).

For example, suppose a new software program is released onto the system, and due to a lack of testing, the new program corrupts some tables in the database. In this case it would be desirable to have an export image to perform the imports against, and just restore the corrupted tables, thereby avoiding a rebuild of the entire database. There are other ways to recover from this situation, but the export/import method is probably the fastest. For example, the entire system on the test environment could be rebuilt, and brought up to the point in time just prior to the failure. By opening the test system and exporting the tables from there, the exports could be used to perform imports into the production system. However, this approach assumes that the test system can be down for the additional time it would take to implement the entire process.

Exports can be accomplished in either of two ways:

• Highly Available Systems

  The first layer of protection is from disk or hardware failure. This can be accomplished with a highly available system such as Sun™ Cluster and RAID.

• Hot Backup

  The second layer of protection is an Oracle hot backup. This is a common method of putting the database into archive log mode, then backing up the archive logs between full backups. In this way, if media failure or physical system failure occurs, the entire database can be recovered from the full and incremental tape backups.

• Export

  If there is a problem with the tape or data on it, an export can serve as a last line of defense.