Steps to speed up your export/import process

Steps to speed up your export/import process:

There are different ways and tool provided by oracle to move/migrate the data from one database to other database or within the database. With the latest version of oracle like 9i,10g, 11i you have rman and other tools to help carry out the data migration/move, But still there are times when we need to use export/import to move/migrate the data.

Traditionally export/import is the last options used for migration/move of data. One of the important reasons was its performance. Below are the various options that can be used to improve the performance and other things like how to do compress export.

Use direct export.

Direct=y is one of the option that can be used to speed up the export process.
Eg. Exp system/ file=myexport.dmp full=y direct=y feedback=10000 log=myexport.log

Use buffer parameter to improve the exp/import process.

Eg. Exp system/ file=myexport.dmp full=y direct=y feedback=10000 buffer=65536 log=myexport.log

Steps to do compress export.

export DIR=/opt/oracle/exports/QCTRP1
export ORACLE_SID=QCTRP1
export ORACLE_HOME=/opt/oracle/product/9.2
export PATH=$ORACLE_HOME/bin/$PATH

# Create a pipe

rm -f $DIR/exp_pipe.dmp

mknod $DIR/exp_pipe.dmp p

# Start compress export

/usr/bin/compress < $DIR/exp_pipe.dmp >$DIR/exp_pipe.dmp.Z &

# Sleep for some time

#sleep 5

# Start the export

exp system@TDPROD file=$DIR/exp_pipe.dmp owner=mytest log=$DIR/exp_$ORACLE_SID.log

Steps to do export/import using single thread.

Cd $ORACLE_HOME/rdbms/lib
Make –f ins_rdbms.mk singletask
# make -f ins_rdbms.mk expst
# make -f ins_rdbms.mk impst
# make -f ins_rdbms.mk sqlldrst
# mv expst $ORACLE_HOME/bin/
# mv impst $ORACLE_HOME/bin/
# mv sqlldrst $ORACLE_HOME/bin/

$ORACLE_HOME/bin/expst system file=exp.dmp full=y log=exp.log

Please note the above steps/procedure is not recommended/supported by oracle. Test this in test environment and do it at your own risk. I have tried and test it. I helped in improving the performance of my export.

Script to do export/import directly without writing to disk.

cat expfinal.sh

#!/bin/bash -x

export DIR=/opt/oracle/exports/QCTRP1
export ORACLE_HOME=/opt/oracle/product/9.2
export PATH=$ORACLE_HOME/bin:$PATH

rm -f $DIR/exp_pipe

mknod $DIR/exp_pipe p

mknod $DIR/imp_pipe.dmp p

echo invoke import
imp mytest@TDPROD file=$DIR/exp_pipe fromuser=mytest touser=second log=imp_test.log imp_log.out &

exp mytest@TDPROD owner=MYTEST file=$DIR/exp_pipe statistics=none < href="mailto:oracle@unixram">oracle@unixram >

Please note that this has been tried and tested by doing export and import from the target database. The above script can be modified and there are various other things that can be done. Even though its been tested, advice to test this in test environment first. The advantage of using these steps is as follows

· The export/import process was approx 70% faster than the conventional export/import.
· This process does not require space of the f/s. This will be of grate use if the database size is very big and f/s space is a big constraint since the size of the export will be in GB.