Linux/Unix shell 自动导入Oracle数据库-阿里云开发者社区

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

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实现自动导入