Unix shell 自动导入Oracle数据库

使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担，如常见的对实例的监控，监听的监控，告警日志的监控，以及数据库的备份，AWR report的自动邮件等。本文给出Linux 下使用 shell 脚本来实现自动导入Oracle数据库。Linux Shell以及导入导出的相关参考：Linux/Unix shell 脚本中调用SQL,RMAN脚本 http://www.linuxidc.com/Linux/2012-09/70310.htmLinux/Unix shell sql 之间传递变量 http://www.linuxidc.com/Linux/2013-01/78811.htmLinux Unix shell 调用 PL/SQL http://www.linuxidc.com/Linux/2013-01/78810.htm1、Linux/Unix shell 自动导入Oracle数据库脚本# +------------------------------------------------+
# | Import database by schema |
# | file_name: impdp.sh |
# | Parameter: Oracle_SID |
# | Usage: |
# | ./impdb.sh ${ORACLE_SID} |
# | Author : Robinson |
# | Blog : http://blog.csdn.net/robinson-0612 |
# +------------------------------------------------+
#
#！/bin/bash
# --------------------
# Define variable
# --------------------if [ -f ~/.bash_profile ]; then
. ~/.bash_profile
fi# --------------------------
# Check SID
# --------------------------
if [ -z "${1}" ];then
echo "Usage: "
echo " `basename $0` ORACLE_SID"
exit 1
fiORACLE_SID=$1; export ORACLE_SID
DT=`date +%Y%m%d`; export DT
SRC_ORA_SID=SY5221A export SRC_ORA_SID
TIMESTAMP=`date +%Y%m%d_%H%M`
LOG_DIR=/u02/database/${ORACLE_SID}/BNR/dump
LOG_FILE=$LOG_DIR/impdb_${ORACLE_SID}_${TIMESTAMP}.log
DUMP_DIR=/u02/database/${ORACLE_SID}/BNR/dump
TAR_FILE=EXP_${SRC_ORA_SID}_${DT}.tar.gz
DUMP_FILE=EXP_${SRC_ORA_SID}_${DT}.dmp
DUMP_LOG=IMP_${ORACLE_SID}_${DT}.log
LAST_EXP_DUMP_LOG=${DUMP_DIR}/EXP_${SRC_ORA_SID}_${DT}.log
RETENTION=1# ------------------------------------------------------------------------
# Check the target database status, if not available send mail and exit
# ------------------------------------------------------------------------db_stat=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep| cut -f3 -d_`
if [ -z "${db_stat}" ]; then
MAIL_SUB=" $ORACLE_SID is not available on `hostname` before try to import data ！！！"
# $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_SUB
echo ${MAIL_SUB} |mail -s " $ORACLE_SID is not available on `hostname` ！！！" dba@trade.com
exit 1
fi# ---------------------------------------------------
# Unzip the dump file
# ---------------------------------------------------if [ -s "${DUMP_DIR}/$TAR_FILE" ] ; then
cd ${DUMP_DIR}
tar -xvf ${TAR_FILE}
else
MAIL_SUB="No dumpfile was found for ${ORACLE_SID} before import."
echo "No dumpfile was found for ${ORACLE_SID} before import."|mail -s $MAIL_SUB dba@trade.com
exit 1
fi# -----------------------------------------------------------------------------
# Check dumpfile and export log file are correct, if no send mail and exit
# -----------------------------------------------------------------------------date >${LOG_FILE}
echo "The hostname is :`hostname`">>$LOG_FILE
echo "The source database is :${SRC_ORA_SID}" >>${LOG_FILE}
echo "The target database is :${ORACLE_SID}">>$LOG_FILE
echo " " >>${LOG_FILE}flag=`cat ${LAST_EXP_DUMP_LOG} | grep -i "successfully completed"`
if [ -n "${flag}" ] && [ -s "${DUMP_DIR}/${DUMP_FILE}" ] ; then
echo -e "The dumpfile exists and can be imported to ${ORACLE_SID} ">>${LOG_FILE}
else
echo "The dumpfile does not exist or exist with errors on `hostname` before try to import data ！！！" >>${LOG_FILE}
mail -s "The dumpfile does not exists or exist with errors for ${ORACLE_SID}" dba@trade.com <${LOG_FILE}
exit 1
fi# ------------------------------------------------------------------------------------------
# Remove all objects for specific schema before import data, if error send mail and exit
# ------------------------------------------------------------------------------------------echo -e "Prepare plsql script to remove all objects for specific schema.... " >>$LOG_FILEecho "
DECLARE
VERIFICATION VARCHAR2（200）;BEGIN
VERIFICATION := "BO_ADMIN"; BO_ADMIN.GO_UTIL_DROP_SCHEMA_OBJECTS （ VERIFICATION ）;
COMMIT;
END;
/
exit ">/tmp/remove_obj.sqlif [ -s /tmp/remove_obj.sql ]; then
echo -e "Running pl/sql script to remove objects for specific schema... " >>${LOG_FILE}
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" @/tmp/remove_obj.sql >/tmp/remove_obj_result.log
else
echo -e "No any plsql script found to remvoe objects. please remove them before import..." >>$LOG_FILE
MAIL_SUB="Import data to ${ORACLE_SID} error. Please remove objects for specific schema firstly"
mail -s $MAIL_SUB dba@trade.com <${LOG_FILE}
exit 1
fires=`cat /tmp/remove_obj_result.log | grep ORA- | grep -v grep`
if [ -n "${res}" ] ; then
echo -e "Some errors caught during remove object, ingore them. " >>${LOG_FILE}
firm /tmp/remove_obj.sql >/dev/null# ----------------------------------------------------------
# Start import data to target database
# ----------------------------------------------------------echo -e "Starting import data to target database ... " >>${LOG_FILE}
impdp "/ as sysdba " directory=db_dump_dir dumpfile=${DUMP_FILE} logfile=${DUMP_LOG} schemas=BO_ADMIN
table_exists_action=replace #parallel=3
RC=$？cat ${DUMP_DIR}/${DUMP_LOG}>>$LOG_FILE
if [ "${RC}" -ne 0 ]; then
echo -e " Some errors caught during import data. exit ！！！！ " >>$LOG_FILE
MAIL_SUB="Import data to ${ORACLE_SID} errors, exit, please check ！！！"
mail -s $MAIL_SUB dba@trade.com <${LOG_FILE}
exit 1
fi# ----------------------------------------------------------
# Compile invalid objects
# ----------------------------------------------------------echo "">>${LOG_FILE}
echo -e "Starting compile invalid objects .... " >>$LOG_FILEecho "
SET LINESIZE 145
SET PAGESIZE 9999clear columns
clear breaks
clear computescolumn owner format a25 heading "Owner"
column object_name format a30 heading "Object Name"
column object_type format a20 heading "Object Type"
column status format a10 heading "Status"
column object_name format a30 heading "Object Name"
column object_type format a20 heading "Object Type"
column status format a10 heading "Status"break on owner skip 2 on report
compute count label "" of object_name on owner
compute count label "Grand Total: " of object_name on report
spool /tmp/invalid_obj.log
SELECT
owner
, object_name
, object_type
, status
FROM dba_objects
WHERE status <> "VALID"
ORDER BY owner, object_name
/
spool off;
exit ">/tmp/list_invalid_obj.sqlsqlplus -silent "/ as sysdba" <<EOF
@$ORACLE_HOME/rdbms/admin/utlrp.sql
@/tmp/list_invalid_obj.sql
EOFecho -e "List all invalid objects " >>${LOG_FILE}
echo "------------------------------------------------------------">>${LOG_FILE}
cat /tmp/invalid_obj.log >>$LOG_FILEflag=`cat ${DUMP_DIR}/${DUMP_LOG} | grep "completed with [0-9][0-9] error"`
if [ -z "${flag}" ] ; then
echo -e "Import data to ${ORACLE_SID} completed successful at `date` ... " >>${LOG_FILE}
echo "--------------------------- End of the log file ---------------------------">>${LOG_FILE}
MAIL_SUB="Import data to ${ORACLE_SID} completed successful on `hostname`."
mail -s $MAIL_SUB dba@trade.com <${LOG_FILE}
else
echo -e "Import data to ${ORACLE_SID} completed with some errors at `date`... " >>${LOG_FILE}
MAIL_SUB="Import data to ${ORACLE_SID} completed with some errors on `hostname`"
echo "--------------------------- End of the log file ---------------------------">>${LOG_FILE}
mail -s $MAIL_SUB dba@trade.com <${LOG_FILE}
fi# ------------------------------------------------
# Removing files older than $RETENTION parameter
# ------------------------------------------------find ${LOG_DIR} -name "impdb*.*" -mtime +$RETENTION -exec rm {} ;
find ${DUMP_DIR} -name "IMP*.*" -mtime +$RETENTION -exec rm {} ;
find ${DUMP_DIR} -name "${DUMP_DIR}/EXP_${SRC_ORA_SID}*.gz" -exec rm {} ;exit

DataGuard 之物理STANDBY 的监控与管理Linux/Unix shell 自动 FTP 备份档案相关资讯 Oracle数据库 Unix shell Oracle导入数据库

Oracle数据库全球化（03月01日）
Oracle数据库日期过滤方法性能比较（02/02/2015 13:20:26）
Oracle数据库安装中端口被占用问题（10/29/2014 07:42:24）

在CentOS 6.6上搭建C++运行环境并（10/10/2015 19:44:40）
Oracle数据库无法使用localhost和（11/14/2014 16:39:10）
Java代码实现向Oracle数据库中导入（08/12/2014 11:22:30）

本文评论查看全部评论（0）