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
  • Linux/Unix shell 监控Oracle实例(monitor instance)
  • Linux/Unix shell 监控Oracle监听器(monitor listener)
  • Linux/Unix shell 监控Oracle告警日志(monitor alter log file)
  • 数据泵 EXPDP 导出工具的使用
  • 数据泵IMPDP 导入工具的使用
  • 导入导出 Oracle 分区表数据
  • expdp impdp中 exclude/include 的使用
  • 使用 Oracle Datapump API 实现数据导出

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Linux/Unix shell 监控Oracle告警日志(monitor alter log file)

    使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担,如常见的对实例的监控,监听的监控,告警日志的监控,以及数据库的备份,A...

11920
来自专栏乐沙弥的世界

使用 DBMS_REPAIR 修复坏块

       对于Oracle数据块物理损坏的情形,在我们有备份的情况下可以直接使用备份来恢复。对于通过备份恢复,Oracel为我们提供了很多种方式,冷备,基于...

11320
来自专栏杨建荣的学习笔记

ORA-01113问题的简单分析(r6笔记第3天)

在启动数据库的时候,open阶段总是可能出现各种各样的问题, 比如让人胆战心惊的错误。 ORA-01113: file 1 needs media recov...

31750
来自专栏杨建荣的学习笔记

海量数据迁移之外部表加载(100天)

本地有一个小的环境,今天照例登上sqlplus,突然发现报了如下的错误。一看原来归档满了。我记得前几天做一个批量操作临时把temp文件resize了很大,限于本...

34980
来自专栏耕耘实录

记一次生产环境MySQL数据库的备份与还原

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢

13340
来自专栏乐沙弥的世界

Oracle 基于 RMAN 的不完全恢复(incomplete recovery by RMAN)

      Oracle 数据库可以实现数据库不完全恢复与完全恢复。完全恢复是将数据库恢复到最新时刻,也就是无损恢复,保证数据库无丢失的恢复。而不完全恢复则是根...

16810
来自专栏杨建荣的学习笔记

SQL*Loader-805的解决(r2笔记36天)

使用sql*loader是大型项目中数据迁移的利器。如果是外部系统,其他数据库到oracle的数据迁移,使用文本式文件是最兼容的方式。 sqlldr的加载效率是...

32580
来自专栏乐沙弥的世界

基于RMAN实现坏块介质恢复(blockrecover)

      对于物理损坏的数据块,我们可以通过RMAN块介质恢复(BLOCK MEDIA RECOVERY)功能来完成受损块的恢复,而不需要恢复整个数据库或所有...

9210
来自专栏乐沙弥的世界

ORA-02019 错误处理

       ORA-02019 错误提示是未找到远程数据库的连接说明,通常发生在本地数据库无法连接到远程数据库。引发该问题的原因很多,比如网络连接,连接方式(...

14610
来自专栏乐沙弥的世界

ORA-01652: unable to extend temp segment by 8192...

      最近在rebuild index时提示unable to extend temp segment by 8192 in tablespace..的错...

20410

扫码关注云+社区

领取腾讯云代金券