Resolve large data processing problems in Oracle Exp/imp

Oracle's Exp/imp are two tools that many users often use. They are often used to do database logical backup, database reorganization and data transfer, and so on. The data is unloaded from exp to the file system first, resulting in one. DMP files, and then, when necessary, the data is loaded into the database by Imp.

For the general small and medium-sized databases, the full database of exp produced by the DMP file may be less than 2GB, but for a slightly larger database, exp generated data from dozens of to hundreds of gigabytes. At present, most operating systems are 32 bits and the maximum file system allowed is 2GB. This obviously cannot be stored by the file system for exp generated data. This is one of the problems. Another problem is that as the database grows, Exp takes longer and is actually difficult to implement. This paper discusses the corresponding countermeasures for the above two problems.

Let's take Unix for example first to see how to go beyond the 2GB limit. This requires the use of common UNIX technologies, such as pipelines (named pipe), data Copy tool DD, and data compression (compress). These techniques are discussed separately below.

。 A pipe is a pseudo file. It exists in memory for fast I/O operations. The pipeline's buffer uses the advanced first-out mechanism, that is, the write pipeline process writes to the buffer head and reads the pipeline process to read the pipe tail. The command to establish the pipe is "Mknod filename p".

。 DD allows us to copy data from one device to another device.

。 Compress is a UNIX data compression tool.

Before implementing Exp, we can first check the size of the resulting DMP file. The following steps can be implemented,

1. Build Pipeline:

$ mknod/tmp/mypipe P

2. Exp results output to the pipeline

$ exp file=/tmp/mypipe ..... Other Exp Options > &

3. Read the pipeline and output the results to the pipeline, only to view the amount of data:

$ dd If=/tmp/mypipe Of=/dev/null

The result returns an exp (. DMP) file size, in units of block (bytes).

Now we can start talking about concrete methods,

1. File compression

2. Direct exp to tape

3. Integrated approach

First look at the file compression method. Using file compression We can try to avoid the DMP file size exceeding the 2GB limit. We take advantage of the UNIX pipeline technology, as follows:

1. Start the compress process, which reads the data from the pipeline and outputs it to the disk file.

$ mknod/tmp/exp_pipe P

$ compress </tmp/exp_pipe > Export.dmp.z &

2. Exp to pipe.

$ exp File=/tmp/exp_pipe ... &

IMP is a similar situation.

Then look at the direct exp to tape method.

$ exp file=/dev/rmt/0m volsize=4g

$ imp file=/dev/rmt/0m volsize=4g

Finally look at the method of exp to the bare device,

$ exp FILE=/DEV/RDSK/C0T3D0S0 volsize=4g

Imp similar. All of the above methods allow us to avoid the 2GB limit.

The following is a discussion of how to shorten the EXP cable time requirement. Most users now use EXP to tape only because there is not enough space left for them to drive. Most of the time spent on the EXP process is written on tape. Through our discussion of the UNIX tools above, we can make the first exp to the pipeline, then compress the pipeline data and output it to the tape. This can greatly reduce the amount of data that is written to tape, thereby shortening the tape-writing time to a considerable extent. Also from Oracle's internal perspective, Oracle allows users to do direct path export from the 7.3 release, that is, to skip Oracle

$ make-f $ORACLE _home/rdbms/lib/oracle.mk expst

$ make-f $ORACLE _home/rdbms/lib/oracle.mk impst

The resulting executable file EXPST (exp single Task) and Impst are exactly the same as exp/imp usage. Using them can save up to 30% more time than using Exp/imp. However, when using them, it is important to ensure that the Oracle database is not available to any other user when using Expst/impst, or the database can have unpredictable consequences or even damage.