you are a new oracle dba and you would like to know how to compress an oracle export file. you would like to take a export of your database and you are not sure if you have enough disk space. your colleague asked you to directly compress the file while the export is ongoing.
below example uses the exp utility and explains how to compress it while the export is ongoing. from oracle 10g onwards you have the expdp tool in place of exp so with expdp you dont have to use this compress functionality because with expdp you can export your data into different files and when there is no space the export job just hangs and will not crash so you can just manually compress those files and the expdp progresses. However if you want to explicitly use the exp tool to make your oracle export then below article will help you.
INTRODUCTION
————
The following article discusses the various ways you can perform an export
to tape on unix systems. This includes exporting directly to tape as well
as exporting via a unix named pipe.
EXPORTING TO TAPE
—————–
It is possible on most unix platforms, to perform a database export
directly to a magnetic tape device. This is not a preferred practice,
since it introduces extra points of failure into your export strategy,
but it may be necessary for the following reasons :
1. Lack of disk space – There is not enough disk space to perform
the export to disk.
2. Resultant export file will be greater than 2 Gigabytes –
On some platforms / Oracle versions there is a restriction of 2G
on the size of an export dump file.
Anyone who chooses to use this method to perform an export should test it
thoroughly before implementing it on their systems.
NOTES
In this article all of the examples are using the command line method of
export, rather than the interactive method. Exactly the same principles
are involved in using the interactive export, except export cannot
be run as a background job (Using ‘&’.). This means that if
you want to run export to tape via named pipes using the interactive
method, you will need to use two windows.
This article also only deals with parameters that directly affect exporting
to tape. Other command line parameters are referenced as <other options>.
To find out what the command line arguments are for your version of export
type ‘exp help=y’ as a valid oracle user.
Please note that some of the methods used in this article use unix concepts
e.g. named pipes, and their use may vary on different unix ports.
If you have a problem with any of the unix commands in this article please
check the syntax against the vendors manual pages, and then finally with
the vendor.
CALCULATING THE SIZE OF AN EXPORT FILE
————————————–
If the site is unsure how large a resultant export file will be, they
can use the following commands to calculate its size :
1. Create a unix named pipe :
os> mknod /tmp/exp_pipe p
2. Start the export in the background, specifying the named pipe as
the output file :
os> exp file=/tmp/exp_pipe <other options> &
3. dd in from the named pipe, out to /dev/null in 1K blocks :
os> dd if=/tmp/exp_pipe of=/dev/null bs=1024
This will return the size of the export file in 1K blocks as follows:
<no. of 1K blocks>+0 records in
<no. of 1K blocks>+0 records out
EXPORTING DIRECTLY TO TAPE
————————–
Once you have decided that you need to perform the export directly to tape,
you will need to change the syntax of your export statement so that
export knows the name of the tape device and how much data can be written
to that tape device.
There are only 2 parameters that you need to change to do this :
1. FILE – The name of the tape device you are exporting to
e.g. /dev/rmt/0 .
2. VOLSIZE – The amount of data that can be written to one tape.
If the entire export file will fit onto a single tape, then a volsize of
0 (variable length) can be used i.e. exp <other options> volsize=0.
If the resultant export file is larger than a single tape, then volsize
needs to be set accordingly e.g. exp <other options> volsize=<size>M.
Please note, there is a 4 Gigabyte limit to the volsize parameter.
EXAMPLE OF EXPORTING DIRECTLY TO TAPE
A full database export to a QIC 150 (150 Megabytes capacity) to a
tape device “/dev/rmt/0m”,where the resultant file is 200M would be
exported using the following syntax :
os> exp userid=system/manager full=y file=/dev/rmt/0M volsize=145M
The volsize is set to 145M, even though the tape is a QIC 150 because
there may not be exactly 150M of tape on the volume.
Once the export utility has written <volsize> to the tape, it will
prompt for the next tape:
“Please mount the next volume and hit <ret> when you are done.”
To import from the resultant export on tape, the following commands
would be used :
os> imp userid=system/manager full=y file=/dev/rmt/0M volsize=145M
EXPORTING TO TAPE VIA UNIX NAMED PIPES
————————————–
On some Oracle platforms/versions there may be problems with exporting
directly to tape using the Oracle export utility, you may need to perform
the export to tape via unix named pipes.
A unix named pipe is a FIFO special file, created using the unix mknod
command.The syntax of the mknod command may change from port to port so
please check the manual pages on your system.
Please note that exporting via a named pipe (to tape or disk), is slower
than using export directly. This is because you are limited by the size
of a unix named pipe, usually 8K.
The following commands should be used to export to tape via a named pipe:
1. Create a unix named pipe :
os> mknod /tmp/exp_pipe p
2. dd in from the named pipe, out to the tape device, in the background :
os> dd if=/tmp/exp_pipe of=<tape device> &
3. Start the export, specifying the named pipe as the output file:
os> exp file=/tmp/exp_pipe <other options>
To import from the resultant export on tape, the following commands
would be used :
1. Create a unix named pipe :
os> mknod /tmp/imp_pipe p
2. dd in from the tape device, out to the named pipe, in the background :
os> dd if=<tape device> of=/tmp/imp_pipe &
3. Start the import, specifying the named pipe as the input file :
os> imp file=/tmp/imp_pipe <other options>
CREATING A COMPRESSED EXPORT FILE
———————————
If you have calculated the size of the file your export will produce and
it is too large to fit onto disk, you may want to consider producing a
compressed export file as an alternative to exporting directly to tape.
Again this method should be thoroughly tested before being implemented.
1. Create a unix named pipe :
os> mknod /tmp/exp_pipe p
2. Start compress in the background reading in from the named pipe,
writing out to ‘export.dmp.Z’ :
os> compress < /tmp/exp_pipe > export.dmp.Z &
3. Start the export, specifying the named pipe as the output file :
os> exp file=/tmp/exp_pipe <other options>
To import from the resultant compressed export file, the following commands
would be used :
1. Create a unix named pipe :
os> mknod /tmp/imp_pipe p
2. Start uncompress in the background reading from ‘export.dmp.Z’,
writing out to the named pipe :
os> uncompress < export.dmp.Z > /tmp/imp_pipe
3. Start the import, specifying the named pipe as the input file :
os> imp file=/tmp/imp_pipe <other options>
EXPORTING TO A REMOTE TAPE DEVICE
———————————
Finally, you may want to perform an export directly to tape but you do not
have a local tape drive on your machine. There is, however a tape drive
on another machine on the network, that you have remote shell (rsh) access on.
The following commands allow you to perform the export to the remote
machine, either to file or to a tape device :
1. Create a unix named pipe :
os> mknod /tmp/exp_pipe p
2. dd in from the named pipe, and out to the remote tape device via a
remote shell :
os> dd if=/tmp/exp_pipe | rsh <hostname> dd of=<file or device> &
3. Start the export, specifying named pipe as the output file :
os> exp file=/tmp/exp_pipe <other options>
To import from the resultant export on tape, the following commands
would be used :
1. Create a unix named pipe :
os> mknod /tmp/imp_pipe p
2. Start a remote shell, in the background that dd’s from the remote tape,
piping to the local named pipe :
os> rsh <hostname> dd if=<file or device> | dd if=/tmp/imp_pipe &
3. Start the import, specifying the named pipe as the input file :
os> imp file=/tmp/imp_pipe <other options>
** Note: On some platforms ‘rsh’ may need to be run with a ‘-n’ or
similar option to ensure terminal messages are written
to /dev/null and avoid potential problems with timing.
eg: $ rsh -n <hostname> dd if=/tmp/expdat.dmp | dd of=/tmp/imp_pipe &
$ imp un/pw file=/tmp/imp_pipe <options>
