Oracle DBA Tips and Techniques

By Arjun Raja

Export and Import using unix pipes and compression

Posted by Gavin Soorma on June 29, 2009

EXPORT AND IMPORT USING UNIX PIPES.

Sometimes, the space on disk may not be enough to hold a full export dump if uncompressed.

EXAMPLE – export schema ARJUN from Primary database and import into secondary database.

To avoid space running out, unix pipes and compression can be used.


EXPORT IN Primary DATABASE

cd /u02/oradata/export

CREATE UNIX PIPE IN THIS AREA – WHERE THE EXPORT DUMP WILL BE WRITTEN TO.

mknod pipe p

CREATE PAR FILE –

parfile is arjun.par

vi arjun.par

buffer=2097152
recordlength=65535
consistent=y
owner=arjun
log=/u02/oradata/export/arjun.log
file=/u02/oradata/export/pipe

Now export schema ARJUN.

1. nohup gzip -c </u02/oradata/export/pipe > /u02/oradata/export/arjun.dmp.gz &

Immediately enter next command –

2. nohup exp \’/ as sysdba\’ parfile=/u02/oradata/export/arjun.par &

Export of ARJUN schema completes – compressed dump arjun.dmp.gz created.

ftp or copy the dump file arjun.dmp.gz to the secondary database box.

IMPORT IN secondary DATABASE – – Presume same directory structure exists on secondary box.

Create UNIX PIPE in area where dump is copied to in DEV box.

cd /u02/oradata/export

mknod import_pipe p

Create import parfile – called imp.par

vi imp.par

fromuser=arjun
touser=arjun
commit=y
buffer=2097152
ignore=y
file=/u02/oradata/export/import_pipe
log=/u02/oradata/export//imp_arjun.log

Enter commands –

1. nohup gzip -dc </u02/oradata/export/arjun.dmp.gz > import_pipe &

Immediately enter next command –

2. nohup imp \’/ as sysdba\’ parfile=/u02/oradata/export/imp.par

Check the logs for output of import.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: