Monday, May 30, 2011

Compressed exports

Using RMAN to make backups of an Oracle database is very convenient, but sometimes you have to do exports of your database because RMAN is unsuitable; for example, if you need to copy or backup just a table, or you don't have archived logs activated due to storage constraints and you need to backup your database, and also you cannot shut down your instance while backing up, then exp is a good option.

But for some mysterious reason exp (at least up to Oracle 10g) does not have compression and you cannot redirect your backup to standard output so it might be difficult to backup large databases. Fortunately you can use named pipes to solve this situation; first you have to create a named pipe in order to connect the output of exp and the input of your compressor:

[oracle]$ mknod mypipe p
[oracle]$ ls -la
total 8
drwxrwx--- 2 oracle dba 4096 May 30 16:47 .
drwxrwx--- 7 oracle dba 4096 May 30 16:45 ..
prw-r----- 1 oracle dba 0 May 30 16:47 mypipe

Then you can launch your favorite compressor to work in background and finally execute the exp command, and you will have an export compressed on the fly:

[oracle]$ compress < mypipe > mydb.dmp.Z &
[oracle]$ exp / parfile=myparfile.txt log=mylog.txt file=mypipe

And remember, if you want to put this in a script add a wait sentence after the exp command.

No comments:

Post a Comment