compress export file

[Syed Shaffat Ali]

Hi!

We run oracle full db export on a daily basis. Due to the growth in the data the export file is taking alot of space.Oracle gives the following code for compressing the export file while the export is running.

1) Make the pipe
mknod /tmp/exp_pipe p
2)Compress in background
compress < /tmp/exp_pipe > export.dmp.Z &
-or-
cat p | compress > output.Z &
-or-
cat p > output.file & )
3) Export to the pipe
exp file=/tmp/exp_pipe userid=scott/tiger tables=X

My question is do I need to give the command 1 on a daily basis or shall i drop the directory created by mknod after the export finishes.

Regards.
Shaffat.

[Peter Godron]

Shaffat,
command 1 creates a pipe not a directory, so no need to remove.

The process works by the export pushing data into the pipe, where the compress program picks it up and compresses it into export.dmp.Z
We have the same process and it works very well.

Note: Ensure your filesystem can cope with > 2Gb files, if youe are producing large dmp.
Regards

[Syed Shaffat Ali]

Thanks Gordon for ur prompt reply.
The full db export is almost reaching 50GB now.


Regards.
Shaffat.

[Yogeeraj_1]

hi Shaffat,

I would also recommend that you break your full export into schema level exports and launch them in parallel so as to reduce the total time taken to export the whole database. This also helps when recovering.

Above all, i hope you are not using it as the sole backup method!!

RMAN would be the best choice for backup of your database.

hope this helps too!
regards
yogeeraj

[Syed Shaffat Ali]

Thanks Yogee for ur reply. Actually the export is done overnight and it doesnt cause any problems till now. Apart from the daily export we have a monthly full Data protector backup.

Regards.
Shaffat.

[TwoProc]

Just on the idea of more information -
You can export with a variable called FILESIZE.
FILESIZE sets the max value that a file will have, then it create the next file using the FILE parameter...
From the manuals on 9.2.x

When the amount of data Export must write exceeds the maximum value you specified for FILESIZE, it will get the name of the next export file from the FILE parameter (see FILE for more information) or, if it has used all the names specified in the FILE parameter, it will prompt you to provide a new export filename. If you do not specify a value for FILESIZE (note that a value of 0 is equivalent to not specifying FILESIZE), then Export will write to only one file, regardless of the number of files specified in the FILE parameter.
...
The FILESIZE value can also be specified as a number followed by KB (number of kilobytes). For example, FILESIZE=2KB is the same as FILESIZE=2048. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to obtain the final file size (FILESIZE=2048B is the same as FILESIZE=2048).

Now on the FILE parameter...
FILE
Default: expdat.dmp
Specifies the names of the export files. The default extension is .dmp, but you can specify any extension. Because Export supports multiple export files (see the parameter FILESIZE), you can specify multiple filenames to be used. For example:

exp scott/tiger FILE = dat1.dmp, dat2.dmp, dat3.dmp FILESIZE=2048

When Export reaches the value you have specified for the maximum FILESIZE, Export stops writing to the current file, opens another export file with the next name specified by the FILE parameter, and continues until complete or the maximum value of FILESIZE is again reached. If you do not specify sufficient export filenames to complete the export, Export will prompt you to provide additional filenames.