使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担,如常见的对实例的监控,监听的监控,告警日志的监控,以及数据库的备份,AWR report的自动邮件等。本文给出Linux 下使用 shell 脚本来实现自动导入Oracle数据库。
Linux Shell以及导入导出的相关参考:
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 := 'GOEX_ADMIN';
GOEX_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=GOEX_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 "EXP_${SRC_ORA_SID}*" -mtime +$RETENTION -exec rm {} \;
exit
2、移除schema所有对象的过程
CREATE OR REPLACE PROCEDURE GOEX_ADMIN."GO_UTIL_DROP_SCHEMA_OBJECTS" (verification VARCHAR2)
IS
CURSOR c1
IS
SELECT *
FROM (SELECT object_name, object_type, 1 AS grp
FROM user_objects
WHERE object_type IN
('PACKAGE',
'PROCEDURE',
'FUNCTION',
'TABLE',
'VIEW',
'TYPE',
'SEQUENCE'
)
AND object_name <> 'BO_SYS_DATAPUMP_PKG'
AND object_name <> 'GO_UTIL_DROP_SCHEMA_OBJECTS'
AND object_name <> 'SYS_BACKUP_PARAM_TBL'
UNION ALL
SELECT object_name, object_type, 2 AS grp
FROM user_objects
WHERE object_type IN ('TYPE')
AND object_name <> 'BO_SYS_DATAPUMP_PKG'
AND object_name <> 'GO_UTIL_DROP_SCHEMA_OBJECTS'
AND object_name <> 'SYS_BACKUP_PARAM_TBL')
ORDER BY grp,
CASE object_type
WHEN 'PACKAGE'
THEN 1
WHEN 'PROCEDURE'
THEN 2
WHEN 'FUNCTION'
THEN 3
WHEN 'VIEW'
THEN 4
ELSE 5
END;
objname VARCHAR2 (255);
objtype VARCHAR2 (255);
objgrp PLS_INTEGER;
verification_str CONSTANT VARCHAR2 (10) := 'GOEX_ADMIN';
BEGIN
IF verification = verification_str
THEN
OPEN c1;
LOOP
FETCH c1
INTO objname, objtype, objgrp;
EXIT WHEN c1%NOTFOUND;
BEGIN
NULL;
IF objtype = 'TABLE'
THEN
EXECUTE IMMEDIATE 'drop '
|| objtype
|| ' '
|| objname
|| ' cascade constraints purge';
ELSE
EXECUTE IMMEDIATE 'drop ' || objtype || ' ' || objname;
END IF;
DBMS_OUTPUT.put_line ( objtype
|| ' - '
|| objname
|| ' dropped successfully'
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
DBMS_OUTPUT.put_line ( objtype
|| ' - '
|| objname
|| ' dropped failed - '
|| SQLERRM
);
END;
END LOOP;
CLOSE c1;
ELSE
DBMS_OUTPUT.put_line
('Failed to verify the operation, please input verification.');
END IF;
END;
/
3、补充说明 a、该shell脚本实现了基于schema的自动导入到指定的数据库,如果是导入整个数据库应作相应的修改 b、对于使用的dump文件的格式定义请参阅文章,Linux/Unix shell 自动导出Oracle数据库,本文描述的格式与导出时的定义相应 c、由于我们导出如导入的db不同,所以使用SRC_ORA_SID,ORACLE_SID则是导入目标数据库的SID d、尝试导入前先判断数据库是否处于可用模式,并且在导入前先解压tar文件 e、导入schema之前,调用过程GO_UTIL_DROP_SCHEMA_OBJECTS移出指定schema的所有对象,以减少导入过程中的错误数 f、接下来导入整个schema,导入完毕后,编译无效对象,并将无效对象列出到日志文件后发送邮件 g、在第2点中,给出了移除整个schema的过程代码 h、可以将自动ftp dump 文件shell整合到该脚本,自动ftp dump文件请参考上一篇,Linux/Unix shell 自动 FTP 备份档案 i、注意shell脚本中的转移字符的使用,最后将其部署到crontab实现自动导入