Friday, December 3, 2010

Oracle export / import in multiple files



-------------
#EXPORT

#!/bin/ksh
# Bruno Reis - 19/06/01
# Realiza Export Comprimido e Splitado em 10MB (Performance Maior em Multiplos Pequenos Arquivos)

date

ORACLE_SID=sae1
NLS_LANG=AMERICAN_AMERICA.WE8DEC
EXP_DIR=/oracle/app/oracle/admin/saet/imp
EXP_FILE=$EXP_DIR/exp${ORACLE_SID}.dmp.Z
LOG=$EXP_DIR/exp${ORACLE_SID}.log
MAXFILESIZE=10m

# Creating Pipes
PIPE1=$EXP_DIR/exppipe1
mkfifo $PIPE1

export ORACLE_SID NLS_LANG EXP_DIR EXP_FILE LOG MAXFILESIZE PIPE1

# Supporting Compression & Split
dd if=$PIPE1 | compress | split -b $MAXFILESIZE - $EXP_FILE &

# Export - Finally
exp userid=sys/morango@sae1 buffer=5000000 file=$PIPE1 full=y compress=Y log=$LOG consistent=Y

# Completion
rm $PIPE1

date


-------------

#IMPORT

#!/bin/ksh
# Bruno Reis - 19/06/01
# Realiza Import Comprimido e Splitado em 10MB (Performance Maior em Multiplos Pequenos Arquivos)

# set -x

date

ORACLE_SID=saet
NLS_LANG=AMERICAN_AMERICA.WE8DEC
EXP_DIR=/oracle/app/oracle/admin/saet/imp
LOG=$EXP_DIR/imp${ORACLE_SID}.log
EXP_FILE=expsae1.dmp.Z

export ORACLE_SID NLS_LANG EXP_DIR LOG EXP_FILE

# Creating Pipes For Compress & Splitting
PIPE_CAT=$EXP_DIR/impcat.Z
PIPE_UNCOMP=$EXP_DIR/impunc
rm -Rf $PIPE_CAT
rm -Rf $PIPE_UNCOMP
mkfifo $PIPE_CAT
mkfifo $PIPE_UNCOMP

# Generating List of Files do CAT | UNCOMPRESS | IMPORT
cd $EXP_DIR
ARQUIVOS=`ls ${EXP_FILE}* | sort -k1`

cat $ARQUIVOS > $PIPE_CAT &
uncompress < $PIPE_CAT > $PIPE_UNCOMP &

imp sys/manager file=$PIPE_UNCOMP IGNORE=Y FULL=Y LOG=$LOG