Friday, July 10, 2009

Is it possible to use EXPDP with GZIP ?

With the old EXPORT utility you can execute the export using exp command with gzip program as below:

mknod /u01/backup/exp/export_pipe p
nohup /usr/bin/gzip < /u01/backup/exp/export_pipe > /u01/backup/exp/dbaceh.dmp.gz 2>
/u01/backup/logs/gzip.log &
$ORACLE_HOME/bin/exp user/senha@dbaceh file=/u01/backup/exp/export_pipe buffer=40000000
log=/u01/backup/logs/dbaceh.log full=y >$ARQLOG 2>$ARQLOG

When trying to do the export using EXPDP (Data Pump) in the same way, the dump file doesn't work with the gzip program

Is it possible to use EXPDP with GZIP ? and if not, why?

Since Datapump Export is optimized to work from within the server, the export dumpfile information is no longer processed in a sequential manner.

As a result, sequential media, such as tapes and pipes, are no longer supported with Export DataPump (expdp) and Import DataPump (impdp).

So, There is no way to do this with DataPump, as you cannot use named pipes with datapump, so there is no way to pass the dumpfile to the gzip command through a pipe. You can only compress the dump file after the export is complete. It cannot be done during the export itself.

The only option would be to add disk space, or nfs mount a large enough filesystem to the server and use that as the export destination, do the DataPump export to that destination, then use GZIP to compress the output file.

In Oracle 10.2 release, Data Pump by default supports compressing the system metadata ( grants, schemas, roles, types, etc.) that's written to the dump file. This can be manually enabled/disabled using the compression parameter.

For example,
expdp userid/password DIRECTORY=dpump_dir DUMPFILE=scott SCHEMAS=scott
COMPRESSION=METADATA_ONLY

explicitly indicates that the system metadata be compressed. This is the default behavior. Using "COMPRESSION=NONE" would explicitly disable compression.

In 11.1 release of Oracle the feature was enhanced to allow for the compression for table data. The "compression" parameter now accepts the following values:
NONE
METADATA_ONLY
DATA_ONLY
ALL

For example,
expdp userid/password DIRECTORY=dpump_dir DUMPFILE=scott SCHEMAS=scott
COMPRESSION=ALL
will compress all system metadata and all table data in the dumpfile.

1 comment:

Felipe said...

compression=all uses compression similar to... gzip, or zip, or ??? In other words, am I better of spending the extra time gzip a large export, or is 'compression=all' going to do a good enough job for me? I don't want to spend hours testing, so I thought I'd ask here.


Cheers