Oracle export and import by pipe

EXPORT pipe

#!/bin/ksh
rm -f export_pipe
mknod export_pipe p
chmod 666 export_pipe
nohup gzip -c < export_pipe > expdat.dmp.gz &
imp "\"/ as sysdba\"" file=export_pipe full=yes ignore=yes
log=exportTESTDB.log
rm -f export_pipe


IMPORT pipe
To import, I use gunzip and do the opposite!

mknod exp.pipe p
gunzip < expdat.dmp.gz > exp.pipe &
imp userid=system/manager file=exp.pipe full=y

Importar datos en Oracle hechos con PIPE

#!/bin/ksh
rm -f import_pipe
mknod import_pipe p
chmod 666 import_pipe
nohup gunzip -c expdat.dmp.gz > import_pipe &
imp userid=system/manager file=import_pipe full=yes ignore=yes
log=importTESTDB.log
rm -f import_pipe

EXPORT by Pipe file

#!/bin/ksh
######################################################################
# PROGRAM NAME: xport_ux
# PURPOSE: Performs export of the database
# Compresses the export file on the fly while gziped.
#
# USAGE: xport_ux SID OWNER
######################################################################
CURRENT_DATE=`date "+%d%m%y-%H:%M"`
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_verify(): Verify that database is online
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_verify(){
STATUS=`ps -fu ${ORA_OWNER} |grep -v grep| grep ora_pmon_${ORA_SID}`
funct_chk_unix_command_status "Database is down for given SID($ORA_SID),Owner($ORA_OWNER). Can't perform export "
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_cleanup(): Cleanup interim files
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_cleanup() {
rm -f $PIPE_DEVICE
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_splitcompress_pipe(): Creates pipe for compressing of file
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_splitcompress_pipe() {
# Creates pipe for compressing
if [ ! -r ${PIPE_DEVICE} ]; then
/etc/mknod ${PIPE_DEVICE} p
fi

nohup gzip < ${PIPE_DEVICE} > ${ZFILE} &
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_build_parfile(): Creates parameter file
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_build_parfile() {
# This line makes sure that it always creates a new parameter file
echo " " >${PARFILE}
echo "userid="\"/ as sysdba\""">>${PARFILE}
echo "Full=Y">>${PARFILE}
#echo "tables=scott.t1">>${PARFILE}
#echo "owner=scott">>${PARFILE}
#echo "Grants=Y">>${PARFILE}
#echo "Indexes=Y">>${PARFILE}
#echo "Rows=Y">>${PARFILE}
#echo "Constraints=Y">>${PARFILE}
#echo "Compress=N">>${PARFILE}
echo "Consistent=Y">>${PARFILE}
echo "File=${PIPE_DEVICE}">>${PARFILE}
echo "Direct=Y">>${PARFILE}
echo "Statistics=none">>${PARFILE}
echo "Log=${EXPORT_DIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log">>${PARFILE}
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_export(): Export the database
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_export() {
# Remove old export file
rm -f ${ZFILE}

${ORACLE_HOME}/bin/exp parfile=${PARFILE}
if [ $? != 0 ]; then
echo `date` >> $LOGDIR/fullexp_${ORA_SID}_${CURRENT_DATE}.log
echo "EXPORT_FAIL: ${ORA_SID}, Export Failed" >> $LOGDIR/fullexp_${ORA_SID}_${CURRENT_DATE}.log
funct_cleanup
exit 1
fi
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_parm(): Check for input parameters
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_chk_parm() {
if [ ${NARG} -ne 2 ]; then
echo "EXPORT_FAIL: ${ORA_SID}, Not enough arguments passed"
exit 1
fi
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_bkup_dir(): Create backup directories if not already existing
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_chk_bkup_dir() {
EXPORT_DIR=${BACKUPDIR}
if [ ! -d ${EXPORT_DIR} ]; then mkdir -p ${EXPORT_DIR}; fi
if [ ! -d ${LOGDIR} ]; then mkdir -p ${LOGDIR}; fi
ZFILE="${EXPORT_DIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.dmp.gz"
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_get_vars(): Get environment variables
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_get_vars(){
ORA_HOME=`sed /#/d ${ORATABDIR}|grep -i ${ORA_SID}|nawk -F ":" '{print $2}'`
ORA_BASE=`echo ${ORA_HOME}|nawk -F "/" '{for (i=2; i<=NF-2; i++) print "/"$i}'` ORACLE_BASE=`echo $ORA_BASE|tr -d " "` ORACLE_HOME=${ORA_HOME}; export ORACLE_HOME ORACLE_SID=${ORA_SID}; export ORACLE_SID } #::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: # funct_chk_unix_command_status(): Check exit status of Unix command #:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: funct_chk_unix_command_status() { if [ $? != 0 ]; then echo "`date`" >> ${LOGDIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log
echo "EXPORT_FAIL: ${1} " >> ${LOGDIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log
exit 1
fi
}

#######################################
# MAIN
#######################################
NARG=$#
ORA_SID=$1
ORA_OWNER=$2

# Set up environment
BACKUPDIR="/backups"
ORATABDIR=/var/opt/oracle/oratab
TOOLS="/u01/oracomn/admin/my_dba"

DYN_DIR="${TOOLS}/DYN_FILES"
PARFILE="/oracle/oracle9i/admin/scripts/export_${ORA_SID}.par"
LOGDIR="/backups/"

PIPE_DEVICE="/backups/${ORA_SID}_pipe"

echo "... Now exporting .... ${ORA_SID}"

funct_chk_parm
funct_get_vars
funct_verify
funct_chk_bkup_dir
funct_splitcompress_pipe
funct_build_parfile
funct_export
funct_cleanup

echo `date` >> $LOGDIR/${ORA_SID}_${CURRENT_DATE}.log
echo "${ORA_SID}, export completed successfully" >> $LOGDIR/${ORA_SID}_${CURRENT_DATE}.log

####################### END MAIN ###############################