Using mknode to perform export and import - Linux

MKNODE FOR EXPORT AND IMPORT

MKNODE exhibits its significance where in my case it solved many DISK SPACE issues; hope this should be helpful for you guys as well.

I would like to share one of the worst experiences I had where I was forced to use MKNODE option to refresh the environments. I was provided with a zipped production dump of size 20G. When I tried to unzip the file, the dump file size was around 100G. But the actual size of disk provided to me was 300G for the import activity. So, 100G was lost specifically for this unzipped dump file. Then started the import activity, after a day I observed unable to extend tablespace, and it was a weekend, but I need 20GB to complete the import activity. MKNODE helped me in this scenario.

SIGNIFICANCE OF MKNODE -

• We can zip the dump file while exporting
• We can import the zipped dump file without unzipping into the database

 LIMITATIONS OF MKNODE-

• MKNODE cannot be used in data pump

HOW TO USE MKNODE FOR EXPORT (exp/imp utility) -

Script to exp

export ORACLE_SID=<database_name>

mknod exp.pipe p;

gzip < exp.pipe > exp.dmp.gz &

exp "'/ as sysdba'" file=exp.pipe log=exp.log direct=y statistics=none owner=schema_name (or) tables = table_name1,table_name2...

rm exp.pipe

exit;

HOW TO USE MKNODE TO IMPORT (exp/imp utility) -

Script to imp

export ORACLE_SID=<database_name>

NAMED_PIPE=mypipe.pipe;

mknod $NAMED_PIPE p;

zcat exp.dmp.gz > $NAMED_PIPE &

imp "'/ as sysdba'" file=$NAMED_PIPE log=imp.log fromuser=<..name..> touser=<..name..>  commit=y ignore=y compile=n buffer=52428800 grants=n tables=table1,table2....

rm $NAMED_PIPE;

exit;

EXAMPLE - (WHILE EXPORT)

$ vi exp_pipe.sh

export ORACLE_SID=source

mknod exp.pipe p;

gzip < exp.pipe > exp.dmp.gz &

exp "'/ as sysdba'" file=exp.pipe log=exp.log direct=y statistics=none  tables=ramesh.customer,ramesh.account,ramesh.transactions

rm exp.pipe

exit;

$ chmod +x exp_pipe.sh

$ nohup ./exp_pipe.sh &

EXAMPLE - (WHILE IMPORT)

$ vi imp_pipe.sh

export ORACLE_SID=dest

NAMED_PIPE=mypipe.pipe;

mknod $NAMED_PIPE p;

zcat exp.dmp.gz > $NAMED_PIPE &

imp "'/ as sysdba'" file=$NAMED_PIPE log=imp.log fromuser=ramesh touser=ganesh  commit=y ignore=y compile=n buffer=52428800 grants=n

rm $NAMED_PIPE;

exit;

$ chmod +x imp_pipe.sh

$ nohup ./imp_pipe.sh &