Linux/Unix shell 自动导入Oracle数据库

文章由LinuxBoy分享于2019-03-30 07:03:07热评（29）

Linux/Unix shell 自动导入Oracle数据库

使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担，如常见的对实例的监控，监听的监控，告警日志的监控，以及数据库的备份，AWR report的自动邮件等。本文给出Linux 下使用 shell 脚本来实现自动导入Oracle数据库。

Linux Shell以及导入导出的相关参考：

Linux/Unix shell 脚本中调用SQL,RMAN脚本

Linux/Unix shell sql 之间传递变量

Linux Unix shell 调用 PL/SQL

1、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
fi

ORACLE_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} \n">>${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....\n" >>$LOG_FILE

echo "
DECLARE
VERIFICATION VARCHAR2(200);

BEGIN
VERIFICATION := 'BO_ADMIN';

BO_ADMIN.GO_UTIL_DROP_SCHEMA_OBJECTS ( VERIFICATION );
COMMIT;
END;
/
exit ">/tmp/remove_obj.sql

if [ -s /tmp/remove_obj.sql ]; then
echo -e "Running pl/sql script to remove objects for specific schema... \n" >>${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
fi

res=`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. \n" >>${LOG_FILE}
fi

rm /tmp/remove_obj.sql >/dev/null

# ----------------------------------------------------------
# Start import data to target database
# ----------------------------------------------------------

echo -e "Starting import data to target database ...\n" >>${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 !!!! \n" >>$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 ....\n" >>$LOG_FILE

echo "
SET LINESIZE 145
SET PAGESIZE 9999

clear columns
clear breaks
clear computes

column 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.sql

sqlplus -silent "/ as sysdba" <<EOF
@$ORACLE_HOME/rdbms/admin/utlrp.sql
@/tmp/list_invalid_obj.sql
EOF

echo -e "List all invalid objects \n" >>${LOG_FILE}
echo "------------------------------------------------------------">>${LOG_FILE}
cat /tmp/invalid_obj.log >>$LOG_FILE

flag=`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` ...\n" >>${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`...\n" >>${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

推荐文章：

Linux/Unix shell 自动导入Oracle数据库