Remote Oracle Database Support : Export dump compression using pipe

This post will help you to run traditional export import backup utility with compression

Background:

Although compression is supported by imp/exp but it is limited to segment extents only & does not solve the purpose.

It will be very much difficult to perform export import operations where db servers holding less space on local file system to keep dump files

Performing export import over network could be a workaround to this issue but due to network delay it could become more tedious

Export/ Import using mknod or pipe will help to zip/unzip the dump files during export or import operation respectively without specifying it explicitly

New 10g (onwards) feature Data Pump COMPRESSION is replaceable with the mknod export/ import method

Export Import Pipe should be used in pre-10g environments to reduce actual export import time, OS space usage & to minimize delay in transport of dump files across the db servers due to less size of the dump

Let us see how it works in export import operation with single & multiple dump files

Export (Single dump file):

Step 1:

First identify the size of the segments getting backed up using dba_segments, you can calculate the possible dump files size e.g. 10-15 % of the segment size

However such calculation is applicable to regular data type like varchar/date/number & not LONG/LOB’s.

Choose file system based on dump file size calculation, get the absolute path & create nod using mknod command which is somewhat similar to soft link here know as pipe

Pipe will be used as medium to perform real time zipping operation

mknod /u01/orabak/export/exp_pipe p

Step 2:

Execute below to pass dump file zip command to be operated during export , use nohup to avoid discontinuity due to session timeout settings on OS

gzip < /u01/orabak/export/exp_pipe > /u01/orabak/export/exp_T2212.dmp.gz &

nohup gzip < /u01/orabak/export/exp_pipe > /u01/orabak/export/exp_T2212.dmp.gz &

Step 3

Pass the file name as pipe name instead of dump file path , as we have already directed dump file path to pipe with zip command

exp system file=/u01/orabak/export/exp_pipe tables=aradmin.T2212 log=/u01/orabak/export/exp_aradmin_T2212_zip.log

Progress...

Completed…

You can re-verify the size of the dump file with estimated size i.e. 10-15% of the segment size.

Export (Multiple dump file):

Step 1

Here, pass multiple nod’s to single pipe (p) depending upon the file size of each dump file.

Number of nod’s equal to number of dump files

Considered two 5 GB (without zip) dump files for table sizing 9 GB

mknod /u01/orabak/export/exp_pipe1 p

mknod /u01/orabak/export/exp_pipe2 p

Step 2

Pass two zip file locations to nod’s

nohup gzip < /u01/orabak/export/exp_pipe1 > /u01/orabak/export/exp_T2109_1.dmp.gz &

nohup gzip < /u01/orabak/export/exp_pipe2 > /u01/orabak/export/exp_T2109_2.dmp.gz &

Step 3

Supply nod pipe location in file clause & execute

exp system filesize=5368709120 file=/u01/orabak/export/exp_pipe1, /u01/orabak/export/exp_pipe2 tables=aradmin.T2109 log=/u01/orabak/export/exp_aradmin_T2109_zip.log

Executed…

Progress…

Now situation arises where I have miss calculated number of export dump files & exp utility asking me for path of the 3^rd file , So I have created a nod & pipe and nod name is supplied to exp as below

mknod /u01/orabak/export/exp_pipe3 p

nohup gzip < /u01/orabak/export/exp_pipe3 > /u01/orabak/export/exp_T2109_3.dmp.gz &