expdp呼び出し
TODO
function registerUsage {
_USAGE=$1
}
function exitWithUsage {
echo "usage: $_USAGE"
exit 1
}
function exitError {
echo "ERROR: $*"
exit 1
}
function DEBUG {
echo debug > /dev/null
}
CONF_CONNECTION_CONF=/opt/oracle/adminscripts/conf/DBCONNECTION.conf
CONF_DUMPDIRECTORY=PUMP
registerUsage "$(basename $0) [-y][-c][-r][-m][-d dpdir][-p days] database username password ['export memo']"
OPT_NOCONFIRM=0
OPT_NOCOMPRESSION=0
OPT_CONTENT=ALL
OPT_PRESERVEDAYS=31
OPT_DUMPDIRECTORY=$CONF_DUMPDIRECTORY
while getopts ycrmd:p: opt
do
DEBUG "option=$opt args=$OPTARG ind=$OPTIND"
case $opt in
y) OPT_NOCONFIRM=1;;
c) OPT_NOCOMPRESSION=1;;
r) OPT_CONTENT=DATA_ONLY;;
m) OPT_CONTENT=METADATA_ONLY;;
d) OPT_DUMPDIRECTORY=$OPTARG;;
p) OPT_PRESERVEDAYS=$OPTARG;;
?) exitWithUsage;;
esac
done
shift $(($OPTIND - 1))
[ $# -lt 3 ] && exitWithUsage
ARG_DBNAME=$1
ARG_DBUSER=$2
ARG_DBPASS=$3
shift 3
ARG_EXPMEMO="$*"
DBCONFIG=$(grep "^$ARG_DBNAME@$(hostname):" $CONF_CONNECTION_CONF | head -n 1)
[[ -z "$DBCONFIG" ]] && exitError "database $ARG_DBNAME@$(hostname) NOT DEFINED in $CONF_CONNECTION_CONF."
DBDESC=$( echo $DBCONFIG | cut -d: -f2)
DBNAME=$( echo $DBCONFIG | cut -d: -f3)
DBDOMAIN=$( echo $DBCONFIG | cut -d: -f4)
DBORASID=$( echo $DBCONFIG | cut -d: -f5)
DBNLSLANG=$(echo $DBCONFIG | cut -d: -f6)
SCHEMACONFIG=$(grep "^$ARG_DBUSER@$DBNAME.$DBDOMAIN:" $CONF_CONNECTION_CONF | head -n 1)
[[ -z "$SCHEMACONFIG" ]] && exitError "user $ARG_DBUSER@$DBNAME.$DBDOMAIN NOT DEFINED in $CONF_CONNECTION_CONF."
DBUSER=$ARG_DBUSER
DBPASS=$ARG_DBPASS
SCHEMADESC=$(echo $SCHEMACONFIG | cut -d: -f2)
SCHEMARW=$( echo $SCHEMACONFIG | cut -d: -f3)
SCHEMATYPE=$(echo $SCHEMACONFIG | cut -d: -f4)
SCHEMATBS=$( echo $SCHEMACONFIG | cut -d: -f5)
DIRCONFIG=$(grep "^$OPT_DUMPDIRECTORY:" $CONF_CONNECTION_CONF | head -n 1)
[[ -z "$DIRCONFIG" ]] && exitError "directory $OPT_DUMPDIRECTORY NOT DEFINED in $CONF_CONNECTION_CONF."
DUMPDIRECTORY=$OPT_DUMPDIRECTORY
DUMPDIRECTORYPATH=$(echo $DIRCONFIG | cut -d: -f2)
TAGPRESERVEDAYS="$OPT_PRESERVEDAYS"
TAGEXPIREDAY=$(perl -e "@T=localtime(time+86400*$TAGPRESERVEDAYS);printf(\"%04d%02d%02d\",(\$T[5] + 1900),\$T[4]+1, \$T[3])")
TAGMEMO="$ARG_EXPMEMO"
[[ "$SCHEMARW" != "r" && "$SCHEMARW" != "rw" ]] && exitError "PERMISSION DENIED. user $DBUSER@$DBNAME.$DBDOMAIN is not exportable."
[[ ! -w $DUMPDIRECTORYPATH ]] && exitError "PERMISSION DENIED. $(whoami) cannot write to directory $DUMPDIRECTORY($DUMPDIRECTORYPATH)."
EXPTIMESTAMP=$(date '+%Y%m%d-%H%M%S')
EXPBASENAME=$DBUSER.$DBDOMAIN.$DBNAME.$EXPTIMESTAMP
EXPDUMPFILE=$EXPBASENAME.dpdmp
EXPLOGFILE=$EXPBASENAME.dplog
EXPTAGFILE=$DUMPDIRECTORYPATH/$EXPBASENAME.tag
EXPCONTENT=$OPT_CONTENT
[[ $OPT_NOCOMPRESSION -eq 1 ]] && EXPCOMPRESSION=METADATA_ONLY || EXPCOMPRESSION=ALL
OPTIONS[11]=DIRECTORY=$DUMPDIRECTORY
OPTIONS[12]=DUMPFILE=$EXPDUMPFILE
OPTIONS[13]=LOGFILE=$EXPLOGFILE
OPTIONS[14]=FLASHBACK_TIME=SYSTIMESTAMP
OPTIONS[21]=SCHEMAS=$DBUSER
OPTIONS[22]=CONTENT=$EXPCONTENT
OPTIONS[23]=COMPRESSION=$EXPCOMPRESSION
EXPCOMMAND="expdp $DBUSER/$DBPASS ${OPTIONS[*]}"
EXPCOMMANDWOPASS="expdp $DBUSER/******** ${OPTIONS[*]}"
cat <<EOS
********************************************************************************
EXPORT(Data Pump) Settings
DB : $DBNAME.$DBDOMAIN ($DBDESC)
ORACLE_SID : $DBORASID (@$(hostname))
NLS_LANG : $DBNLSLANG
SCHEMA : $DBUSER ($SCHEMADESC)
SCHEMATYPE : $SCHEMATYPE
MEMO : $TAGMEMO
EXPMODE : SCHEMA
TIMESTAMP : $EXPTIMESTAMP
DIRECTORY : $DUMPDIRECTORY ($DUMPDIRECTORYPATH)
DUMP : $EXPDUMPFILE
PRESERVE : $TAGPRESERVEDAYS days (EXPIRE: $TAGEXPIREDAY)
CONTENT : $EXPCONTENT
COMPRESS : $EXPCOMPRESSION
TABLESPACES: $SCHEMATBS
********************************************************************************
EOS
if [[ $OPT_NOCONFIRM -ne 1 ]]; then
echo
echo -n "execute EXPORT(Data Pump) with above settings? (Y or [n]): "
read yesno
if [[ "$yesno" != "Y" ]]; then
exitError "EXPORT(Data Pump) is CANCELLED by $(whoami)."
fi
fi
export ORACLE_SID=$DBORASID
export NLS_LANG=$DBNLSLANG
echo
echo
echo "EXPORT(Data Pump) begin at $(date)."
echo
echo $EXPCOMMANDWOPASS
EXPSTARTTIME=$(date '+%Y%m%d-%H%M%S')
$EXPCOMMAND < /dev/null 2>&1
rc=$?
EXPENDTIME=$(date '+%Y%m%d-%H%M%S')
[[ $rc -ne 0 ]] && exitError "EXPORT(Data Pump) FAILED! at $(date). (EXITCODE: $rc)"
cat <<EOT > $EXPTAGFILE
SCHEMA:$DBUSER
SCHEMADESC:$SCHEMADESC
SCHEMATYPE:$SCHEMATYPE
DBNAME:$DBNAME
DBDOMAIN:$DBDOMAIN
DBDESC:$DBDESC
TABLESPACES:$SCHEMATBS
MEMO:$TAGMEMO
EXPIRE:$TAGEXPIREDAY
BACKUP:$EXPSTARTTIME - $EXPENDTIME
EOT
[[ ! -w $EXPTAGFILE ]] && exitError "EXPORT(Data Pump) completed at $(date), but create $EXPTAGFILE failed."
echo "EXPORT(Data Pump) completed at $(date)."
exit 0
impdp呼び出し
TODO
function registerUsage {
_USAGE=$1
}
function exitWithUsage {
echo "usage: $_USAGE"
exit 1
}
function exitError {
echo "ERROR: $*"
exit 1
}
function DEBUG {
echo debug > /dev/null
}
CONF_CONNECTION_CONF=/opt/oracle/adminscripts/conf/DBCONNECTION.conf
CONF_DUMPDIRECTORY=PUMP
registerUsage "$(basename $0) [-y][-r][-m][-d dpdir][-t TRUNCATE|REPLACE|SKIP|APPEND] database username password dumpfile"
OPT_NOCONFIRM=0
OPT_CONTENT=ALL
OPT_DUMPDIRECTORY=$CONF_DUMPDIRECTORY
OPT_TABLE_EXISTS_ACTION=SKIP
while getopts yrmd:t: opt
do
DEBUG "option=$opt args=$OPTARG ind=$OPTIND"
case $opt in
y) OPT_NOCONFIRM=1;;
r) OPT_CONTENT=DATA_ONLY;;
m) OPT_CONTENT=METADATA_ONLY;;
d) OPT_DUMPDIRECTORY=$OPTARG;;
t) OPT_TABLE_EXISTS_ACTION=$(echo $OPTARG | tr 'a-z' 'A-Z');;
?) exitWithUsage;;
esac
done
shift $(($OPTIND - 1))
[ $# -lt 4 ] && exitWithUsage
ARG_DBNAME=$1
ARG_DBUSER=$2
ARG_DBPASS=$3
ARG_DUMPFILE=$4
case $OPT_TABLE_EXISTS_ACTION in
TRUNCATE|REPLACE|SKIP|APPEND);;
*) exitWithUsage;;
esac
DBCONFIG=$(grep "^$ARG_DBNAME@$(hostname):" $CONF_CONNECTION_CONF | head -n 1)
[[ -z "$DBCONFIG" ]] && exitError "database $ARG_DBNAME@$(hostname) NOT DEFINED in $CONF_CONNECTION_CONF."
DBDESC=$( echo $DBCONFIG | cut -d: -f2)
DBNAME=$( echo $DBCONFIG | cut -d: -f3)
DBDOMAIN=$( echo $DBCONFIG | cut -d: -f4)
DBORASID=$( echo $DBCONFIG | cut -d: -f5)
DBNLSLANG=$(echo $DBCONFIG | cut -d: -f6)
SCHEMACONFIG=$(grep "^$ARG_DBUSER@$DBNAME.$DBDOMAIN:" $CONF_CONNECTION_CONF | head -n 1)
[[ -z "$SCHEMACONFIG" ]] && exitError "user $ARG_DBUSER@$DBNAME.$DBDOMAIN NOT DEFINED in $CONF_CONNECTION_CONF."
DBUSER=$ARG_DBUSER
DBPASS=$ARG_DBPASS
SCHEMADESC=$(echo $SCHEMACONFIG | cut -d: -f2)
SCHEMARW=$( echo $SCHEMACONFIG | cut -d: -f3)
SCHEMATYPE=$(echo $SCHEMACONFIG | cut -d: -f4)
SCHEMATBS=$( echo $SCHEMACONFIG | cut -d: -f5)
DIRCONFIG=$(grep "^$OPT_DUMPDIRECTORY:" $CONF_CONNECTION_CONF | head -n 1)
[[ -z "$DIRCONFIG" ]] && exitError "directory $OPT_DUMPDIRECTORY NOT DEFINED in $CONF_CONNECTION_CONF."
DUMPDIRECTORY=$OPT_DUMPDIRECTORY
DUMPDIRECTORYPATH=$(echo $DIRCONFIG | cut -d: -f2)
if ! echo $ARG_DUMPFILE | egrep '[A-z0-9]+\.[A-z0-9]+\.[A-z0-9]+\.20[0-9][0-9][0-1][0-9][0-3][0-9]-[0-2][0-9][0-5][0-9][0-5][0-9].dpdmp' > /dev/null; then
exitError "The name of dumpfile is NOT SUPPORTED. It must match the pattern 'DBUSER.DBDOMAIN.DBNAME.YYYYMMDD-hhmmss.dpdmp'."
fi
EXPBASENAME=$(basename $ARG_DUMPFILE .dpdmp)
EXPDBUSER=$( echo $EXPBASENAME | cut -d. -f1)
EXPDBDOMAIN=$( echo $EXPBASENAME | cut -d. -f2)
EXPDBNAME=$( echo $EXPBASENAME | cut -d. -f3)
EXPTIMESTAMP=$(echo $EXPBASENAME | cut -d. -f4)
EXPTAGFILE=$DUMPDIRECTORYPATH/$EXPBASENAME.tag
[[ ! -r $EXPTAGFILE ]] && exitError "$EXPTAGFILE NOT FOULD."
EXPMEMO=$( grep "^MEMO:" $EXPTAGFILE | head -n 1 | cut -d: -f2)
EXPSCHEMATBS=$( grep "^TABLESPACES:" $EXPTAGFILE | head -n 1 | cut -d: -f2)
EXPSCHEMATYPE=$(grep "^SCHEMATYPE:" $EXPTAGFILE | head -n 1 | cut -d: -f2)
EXPSCHEMADESC=$(grep "^SCHEMADESC:" $EXPTAGFILE | head -n 1 | cut -d: -f2)
EXPDBDESC=$( grep "^DBDESC:" $EXPTAGFILE | head -n 1 | cut -d: -f2)
[[ "$EXPSCHEMATYPE" != "$SCHEMATYPE" ]] && exitError "The type of schema are NOT MATCH(from $EXPSCHEMATYPE, to $SCHEMATYPE)."
[[ "$SCHEMARW" != "w" && "$SCHEMARW" != "rw" ]] && exitError "PERMISSION DENIED. user $DBUSER@$DBNAME.$DBDOMAIN is not importable."
IMPTIMESTAMP=$(date '+%Y%m%d-%H%M%S')
IMPDUMPFILE=$EXPBASENAME.dpdmp
IMPLOGFILE=$EXPBASENAME.imp.$IMPTIMESTAMP.dplog
IMPCONTENT=$OPT_CONTENT
IMPACTION=$OPT_TABLE_EXISTS_ACTION
IMPREMAPTBS=$(echo $EXPSCHEMATBS,$SCHEMATBS | awk -F, '{ for (i=1; i<=NF/2; i++) printf("REMAP_TABLESPACE=%s:%s ",$i,$(NF/2+i)) }')
IMPREMAPTBSPRT=$(echo $EXPSCHEMATBS,$SCHEMATBS | awk -F, '{ for (i=1; i<=NF/2; i++) printf("%s%s to %s",(i > 1 ? ", " : ""),$i,$(NF/2+i)) }')
OPTIONS[11]=DIRECTORY=$DUMPDIRECTORY
OPTIONS[12]=DUMPFILE=$IMPDUMPFILE
OPTIONS[13]=LOGFILE=$IMPLOGFILE
OPTIONS[21]=SCHEMAS=$EXPDBUSER
OPTIONS[22]=CONTENT=$IMPCONTENT
OPTIONS[23]=TABLE_EXISTS_ACTION=$IMPACTION
OPTIONS[24]=REMAP_SCHEMA=$EXPDBUSER:$DBUSER
OPTIONS[25]="$IMPREMAPTBS"
IMPCOMMAND="impdp $DBUSER/$DBPASS ${OPTIONS[*]}"
IMPCOMMANDWOPASS="impdp $DBUSER/******** ${OPTIONS[*]}"
cat <<EOS
********************************************************************************
IMPORT(Data Pump) Settings
DB : $DBNAME.$DBDOMAIN ($DBDESC)
ORACLE_SID : $DBORASID (@$(hostname))
NLS_LANG : $DBNLSLANG
SCHEMA : $DBUSER ($SCHEMADESC)
SCHEMATYPE : $SCHEMATYPE
IMPMODE : SCHEMA
CONTENT : $IMPCONTENT
TIMESTAMP : $IMPTIMESTAMP
ACTION : ** $IMPACTION **
FROM...
DIRECTORY : $DUMPDIRECTORY ($DUMPDIRECTORYPATH)
DUMP : $IMPDUMPFILE
DB : $EXPDBNAME.$EXPDBDOMAIN ($EXPDBDESC)
SCHEMA : $EXPDBUSER ($EXPSCHEMADESC)
SCHEMATYPE : $EXPSCHEMATYPE
TIMESTAMP : $EXPTIMESTAMP
MEMO : $EXPMEMO
TBS REMAP : $IMPREMAPTBSPRT
********************************************************************************
EOS
if [[ $OPT_NOCONFIRM -ne 1 ]]; then
echo
echo -n "execute IMPORT(Data Pump) with above settings? (Y or [n]): "
read yesno
if [[ "$yesno" != "Y" ]]; then
exitError "IMPORT(Data Pump) is CANCELLED by $(whoami)."
fi
fi
export ORACLE_SID=$DBORASID
export NLS_LANG=$DBNLSLANG
echo
echo
echo "IMPORT(Data Pump) begin at $(date)."
echo
echo $IMPCOMMANDWOPASS
IMPSTARTTIME=$(date '+%Y%m%d-%H%M%S')
$IMPCOMMAND < /dev/null 2>&1
rc=$?
IMPENDTIME=$(date '+%Y%m%d-%H%M%S')
[[ $rc -ne 0 ]] && exitError "IMPORT(Data Pump) FAILED! at $(date). (EXITCODE: $rc)"
echo "IMPORT(Data Pump) completed at $(date)."
exit 0
設定ファイル
[DB MAPPING]
DB通称@ホスト名:日本語説明:DB名:ドメイン名:ORACLE_SID:NLS_LANG
[SCHEMAS]
スキーマ@DB名.ドメイン名:日本語説明:rw:スキーマ区分
[PUMPDIR]
DATA_PUMP_DIR:/var/oracle/oracle/dpdump
PUMP:/oradata/pump