Export/Import per Shell Script

Inhalt

Problem

Beim Datapump Export/Import sieht man im Logfile nicht welche Parameter verwendet wurden. Die folgenden Shell Scripte koennen leicht angepasst werden, sodass mehrere Schemata oder nur bestimmte Tabellen exportiert/importiert werden.

top

Analyse

top

Loesung

Shell Script zum Datapump Export

 

#!/bin/bash
#
# file: expdp.sh
#
function initexpdp {
  V_USER=$1
  V_DB_SERVICE=$2
#  V_DUMPFILE=${V_DB_SERVICE,,} # lowercase mit bash 4.x
  V_DUMPFILE=$(echo $V_DB_SERVICE | tr '[:upper:]' '[:lower:]')
  V_DUMPFILE=${V_DUMPFILE%%.dmp*}
  RTIME=$(date +"%Y%m%d%H%M")
  FILENAME="expdp_ppmsd_${V_DUMPFILE}_${RTIME}"
  DFILE=${FILENAME}_%U.dmp
  LFILE=${FILENAME}.log
  JOB_NAME="EXPDP_${V_DB_SERVICE}"
  SCHEMAS="PLA_DEV,PLA_DEV_SYS"
#  TABLES="SCDAT.VAO_LOGS,SCDAT.AO_RUNS,SCDAT.AO_LOG_ENTRIES"
  DIRECTORY="DATA_PUMP_DIR"
  EXPDPPARFILE=$(mktemp)
#  FLASHBACK_TIME="\"TO_TIMESTAMP('10-06-2015 17:00:05','DD-MM-YYYY HH24:MI:SS')\""
  FLASHBACK_TIME="\"TO_TIMESTAMP(to_char(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')\""
  # echo "=${}" >> $EXPDPPARFILE
  echo "USERID=${V_USER}@${V_DB_SERVICE}" > $EXPDPPARFILE
  echo "DUMPFILE=${DFILE}" >> $EXPDPPARFILE
  echo "LOGFILE=${LFILE}" >> $EXPDPPARFILE
  echo "JOB_NAME=${JOB_NAME}" >> $EXPDPPARFILE
  echo "SCHEMAS=${SCHEMAS}" >> $EXPDPPARFILE
#  echo "TABLES=${TABLES}" >> $EXPDPPARFILE
  echo "DIRECTORY=${DIRECTORY}" >> $EXPDPPARFILE
  echo "FLASHBACK_TIME=${FLASHBACK_TIME}" >> $EXPDPPARFILE
}

function cleanup()
{
  rm -rf $EXPDPPARFILE
  unset EXPDPPARFILE
}

function usage
{
  echo "Usage: "
  echo "$0 <<user> <db-service>"
  echo " "
  echo "Beispiel  "
  echo "$0 system vpsi"
  echo " "
  exit -1
}
####### MAIN  
if [[ "$1" == "" ||  "$2" == "" ]] ; then
 usage
else
  echo OK
  initexpdp $1 $2
  expdp $(cat ${EXPDPPARFILE})
#  cat $EXPDPPARFILE
  cleanup
  exit 0
fi

Shell Script zum Datapump Import

 
#!/bin/bash
#
# file: impdp.sh
#
function initimpdp {
 V_USER=$1
 V_DB_SERVICE=$2
 # V_DUMPFILE=${3,,}
 V_DUMPFILE=${3}
 # V_DUMPFILE=${V_DUMPFILE%%.dmp*}
 RTIME=$(date +"%Y%m%d-%H%M")
 FILENAME=${V_DUMPFILE}
 DFILE=${V_DUMPFILE}
 LFILE=impdp_${V_DB_SERVICE}_${RTIME}.log
 JOB_NAME="IMPDP_${V_DB_SERVICE}"
 SCHEMAS="PLA_DEV,PLA_DEV_SYS"
 REMAP_SCHEMA="PLA_DEV_SYS:PLA_DEMO_SYS,PLA_DEV:PLA_DEMO"
 REMAP_TABLESPACE="PLA_DEV_DATA:PLA_DEMO_DATA"
 TABLE_EXISTS_ACTION="SKIP"
 DIRECTORY="DATA_PUMP_DIR"
 EXCLUDE="PASSWORD_HISTORY"
 IMPDPPARFILE=$(mktemp)
 FLASHBACK_TIME="\"TO_TIMESTAMP(to_char(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')\""
 # echo "=${}" >> $IMPDPPARFILE
 echo "USERID=${V_USER}@${V_DB_SERVICE}" > $IMPDPPARFILE
 echo "DUMPFILE=${DFILE}" >> $IMPDPPARFILE
 echo "LOGFILE=${LFILE}" >> $IMPDPPARFILE
 echo "JOB_NAME=${JOB_NAME}" >> $IMPDPPARFILE
 echo "SCHEMAS=${SCHEMAS}" >> $IMPDPPARFILE
 echo "REMAP_SCHEMA=${REMAP_SCHEMA}" >> $IMPDPPARFILE
 echo "REMAP_TABLESPACE=${REMAP_TABLESPACE}" >> $IMPDPPARFILE
 echo "DIRECTORY=${DIRECTORY}" >> $IMPDPPARFILE
# echo "EXCLUDE=${EXCLUDE}" >> $IMPDPPARFILE
# echo "FLASHBACK_TIME=${FLASHBACK_TIME}" >> $IMPDPPARFILE
}

function cleanup()
{
 rm -rf $IMPDPPARFILE
 unset IMPDPPARFILE
}

function usage
{
 echo "Usage: "
 echo "$0 <<user> <db-service>  <dumpfile>"
 echo " "
 echo "Beispiel "
 echo "$0 system vpsi expdp_vpsp_201504090928_01.dmp"
 echo " "
 exit -1
}

####### MAIN  
if [[ "$1" == "" || "$2" == "" || "$3" == "" ]] ; then
 usage
else
 echo OK
 initimpdp $1 $2 $3
 impdp $(cat ${IMPDPPARFILE})
# cat $IMPDPPARFILE
 cleanup
 exit 0
fi

top

Fazit

top

Referenzen

top