Wednesday, August 7, 2013

Export/Zip and Unzip/Import using UNIX Pipes

Export/Zip and Unzip/Import using UNIX Pipes

If you are creating and using large dump (.dmp) files with Oracle's export (exp) and import (imp) utilities, you can compress and uncompress the dump file as needed using gzip and gunzip (from GNU) or the unix compress and uncompress utilities.  One problem with this is that you will still have to have the large dump file on your disk at some point, which you may or may not have room for, and you may run up against the 2 Gig file size limit on some machines and Oracle versions.  Instead of doing the export and zip separately, creating an interim dump file (or doing unzip and import), unix has the ability to pipe the output from one program (such as exp) as input to another program (such as gzip) as they both run in parallel, without having to run the programs sequentially and without having to create interim files.
To do this, you would create a pipe (using the unix mknod command), run the programs (in either order), with the first one in the background (using "&" at the end), which use that pipe like a file, and then remove the pipe.   Below shows a full export to a zipped dump file:

Below shows a full export with that zipped dump file:

mknod /u02/backup/dpump/exp_pipe p
gzip -cNf </u02/backup/dpump/exp_pipe>prod.dmp.gz &
exp system/oracle file=/u02/backup/dpump/exp_pipe full=y compress=n log=prod.dmp.log
rm -f /u02/backup/dpump/exp_pipe

Below shows a full import with that zipped dump file:

    mknod /u02/backup/dpump/imp_pipe p
    gunzip -c prod.dmp.gz >/u02/backup/dpump/imp_pipe &
    imp system/oracle file=/u02/backup/dpump/imp_pipe fromuser=scott touser=scott ignore=y buffer=1024000 commit=y log=prod_imp.log
    rm -f /u02/backup/dpump/imp_pipe


  1. But is not working with expdp and impdp, have you got a solution to expdp compression files?

  2. You can using compression parameter with expdp/impdp Using compress parameter can reduce the size to great extent. If you want to furthur compress file you can use gzip on compressed backups. So

    something like this :
    expdp directory=DATA_PUMP_DIR1 dumpfile=exp_disc logfile=exp_disc_schema_scott.log schemas=scott compression=all

    Then do.
    gzip FileName