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

我就是我 2022-03-18 17:42 275阅读 0赞

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

Linux Shell以及导入导出的相关参考：  
[Linux/Unix shell 脚本中调用SQL,RMAN脚本  
][Linux_Unix shell _SQL_RMAN_] [Linux/Unix shell sql 之间传递变量  
][Linux_Unix shell sql _] [Linux/Unix shell 调用 PL/SQL  
][Linux_Unix shell _ PL_SQL] [Linux/Unix shell 监控Oracle实例(monitor instance)][Linux_Unix shell _Oracle_monitor instance]  
[Linux/Unix shell 监控Oracle监听器(monitor listener)  
][Linux_Unix shell _Oracle_monitor listener_] [Linux/Unix shell 监控Oracle告警日志(monitor alter log file)  
][Linux_Unix shell _Oracle_monitor alter log file_] [数据泵 EXPDP 导出工具的使用][EXPDP]  
[数据泵IMPDP 导入工具的使用  
][IMPDP _] [导入导出 Oracle 分区表数据  
][Oracle _] [expdp impdp中 exclude/include 的使用][expdp impdp_ exclude_include]  
[使用 Oracle Datapump API 实现数据导出][Oracle Datapump API]

1、自动导出Oracle数据库shell脚本

# +-------------------------------------------------------+# +    Export database by schema every day                |# +    Author : Robinson                                  |# +    Blog   : http://blog.csdn.net/robinson_0612        |# +    Usage  :                                           | # +         expdb.sh $ORACLE_SID                          |# +-------------------------------------------------------+##!/bin/bash # --------------------# Define variable# --------------------if [ -f ~/.bash_profile ]; then. ~/.bash_profilefiif [ -z "${1}" ];then    echo "Usage: "    echo "      `basename $0` ORACLE_SID"    exit 1fiORACLE_SID=$1;                 export ORACLE_SID DT=`date +%Y%m%d`;             export DTTIMESTAMP=`date +%Y%m%d_%H%M`MAIL_LIST="robinson.chen@12306.com"; export MAIL_LISTMAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56MAIL_FM='oracle@szdb.com'LOG_DIR=/users/robin/dba_scripts/custom/logLOG_FILE=$LOG_DIR/expdb_${ORACLE_SID}_${TIMESTAMP}.logDUMP_FILE=EXP_${ORACLE_SID}_${DT}.dmpDUMP_LOG=EXP_${ORACLE_SID}_${DT}.logDUMP_DIR=/u02/database/${ORACLE_SID}/BNR/dumpRETENTION=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 export data  !!!"    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_SUB     exit 1fi;# ------------------------------------#  Check the dumpfile exist or not# ------------------------------------if [ -s "${DUMP_DIR}/${DUMP_FILE}" ]; then    MAIL_SUB=" The dump file ${DUMP_FILE} exists for ${ORACLE_SID} on `hostname`, exit !!!!"    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_SUB    exit 1fiecho "`date`" >>$LOG_FILEecho "The hostname is :`hostname`">>$LOG_FILEecho "The database is :${ORACLE_SID}">>$LOG_FILEecho "  Start to export data ..............." >>$LOG_FILE# --------------------------------------#  Start to export data# --------------------------------------expdp \'\/ as sysdba\' directory=db_dump_dir dumpfile=${DUMP_FILE} logfile=${DUMP_LOG} schemas=goex_admin #parallel=3RC=$?echo " ">>$LOG_FILEcat ${DUMP_DIR}/${DUMP_LOG} >>$LOG_FILEecho " ">>$LOG_FILEecho "------------------------- End of the log file ------------------------">>$LOG_FILEflag=`cat ${DUMP_DIR}/${DUMP_LOG} | grep -i "successfully completed"`if [ "${RC}" -eq 0 ] && [ -n "${flag}" ];then    MAIL_SUB=" Export database ${ORACLE_SID} finished successful on `hostname`  !!!"    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -o message-file=$LOG_FILEelse    MAIL_SUB=" Export database ${ORACLE_SID} failed on `hostname`  !!!"    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -o message-file=$LOG_FILEfi# ------------------------------------------------# Removing files older than $RETENTION parameter # ------------------------------------------------find ${LOG_DIR} -name "expdb*.*" -mtime +$RETENTION -exec rm {} \;find ${DUMP_DIR} -name "EXP*" -mtime +$RETENTION -exec rm {} \;exit

2、补充说明  
a、该脚本实现了基于schema的数据库导出，如果要整个导出数据库，需要修改导出部分代码  
b、导出的dump文件的格式为EXP\_$ORACLE\_SID\_yyyymmdd.dmp，EXP\_$ORACLE\_SID\_yyyymmdd.log  
c、由于每天仅导出一次，所以对上面文件的命名使用了yyyymmdd，如果多次导出应考虑修改代码或文件名格式  
d、注意导出命令中转义字符的使用  
e、使用了sendEmail邮件发送程序来发送邮件。参阅：[不可或缺的 sendEmail  
][sendEmail]f、该脚本在Oracle 10g测试可用，Oracle 11g待测  
g、要实现自动导出，当然是将其部署到crontab，此不赘述

3、更多参考

有关Oracle RAC请参考  
[使用crs\_setperm修改RAC资源的所有者及权限  
][crs_setperm_RAC_] [使用crs\_profile管理RAC资源配置文件  
][crs_profile_RAC_] [RAC 数据库的启动与关闭  
][RAC _] [再说 Oracle RAC services  
][Oracle RAC services] [Services in Oracle Database 10g  
][Services in Oracle Database 10g] [Migrate datbase from single instance to Oracle RAC  
][Migrate datbase from single instance to Oracle RAC] [Oracle RAC 连接到指定实例  
][Oracle RAC _] [Oracle RAC 负载均衡测试(结合服务器端与客户端)  
][Oracle RAC _ 1] [Oracle RAC 服务器端连接负载均衡(Load Balance)  
][Oracle RAC _Load Balance_] [Oracle RAC 客户端连接负载均衡(Load Balance)  
][Oracle RAC _Load Balance_ 1] [ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)][ORACLE RAC _listener.ora tnsnames.ora]  
[ORACLE RAC 监听配置 (listener.ora tnsnames.ora)  
][ORACLE RAC _ _listener.ora tnsnames.ora_] [配置 RAC 负载均衡与故障转移  
][RAC _ 1] [CRS-1006 , CRS-0215 故障一例][CRS-1006 _ CRS-0215]  
[基于Linux (RHEL 5.5) 安装Oracle 10g RAC][Linux _RHEL 5.5_ _Oracle 10g RAC]  
[使用 runcluvfy 校验Oracle RAC安装环境][runcluvfy _Oracle RAC]

有关Oracle 网络配置相关基础以及概念性的问题请参考：  
[配置非默认端口的动态服务注册][Link 1]  
[配置sqlnet.ora限制IP访问Oracle  
][sqlnet.ora_IP_Oracle] [Oracle 监听器日志配置与管理][Oracle]  
[设置 Oracle 监听器密码(LISTENER)  
][Oracle _LISTENER_] [配置ORACLE 客户端连接到数据库][ORACLE]

有关基于用户管理的备份和备份恢复的概念请参考  
[Oracle 冷备份  
][Oracle _ 1] [Oracle 热备份  
][Oracle _ 2] [Oracle 备份恢复概念  
][Oracle _ 3] [Oracle 实例恢复  
][Oracle _ 4] [Oracle 基于用户管理恢复的处理  
][Oracle _ 5] [SYSTEM 表空间管理及备份恢复  
][SYSTEM _] [SYSAUX表空间管理及恢复  
][SYSAUX_] [Oracle 基于备份控制文件的恢复(unsing backup controlfile)][Oracle _unsing backup controlfile]

有关RMAN的备份恢复与管理请参考  
[RMAN 概述及其体系结构  
][RMAN _] [RMAN 配置、监控与管理  
][RMAN _ 1] [RMAN 备份详解  
][RMAN _ 2] [RMAN 还原与恢复  
][RMAN _ 3] [RMAN catalog 的创建和使用  
][RMAN catalog _] [基于catalog 创建RMAN存储脚本  
][catalog _RMAN_] [基于catalog 的RMAN 备份与恢复  
][catalog _RMAN _] [RMAN 备份路径困惑  
][RMAN _ 4] [使用RMAN实现异机备份恢复(WIN平台)  
][RMAN_WIN_] [使用RMAN迁移文件系统数据库到ASM  
][RMAN_ASM] [linux 下RMAN备份shell脚本  
][linux _RMAN_shell_] [使用RMAN迁移数据库到异机][RMAN]

有关ORACLE体系结构请参考  
[Oracle 表空间与数据文件  
][Oracle _ 6] [Oracle 密码文件  
][Oracle _ 7] [Oracle 参数文件  
][Oracle _ 8] [Oracle 联机重做日志文件(ONLINE LOG FILE)  
][Oracle _ONLINE LOG FILE_] [Oracle 控制文件(CONTROLFILE)  
][Oracle _CONTROLFILE_] [Oracle 归档日志  
][Oracle _ 9] [Oracle 回滚(ROLLBACK)和撤销(UNDO)  
][Oracle _ROLLBACK_UNDO_] [Oracle 数据库实例启动关闭过程  
][Oracle _ 10] [Oracle 10g SGA 的自动化管理  
][Oracle 10g SGA _] [Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[Linux_Unix shell _SQL_RMAN_]: http://blog.csdn.net/robinson_0612/article/details/7965916
[Linux_Unix shell sql _]: http://blog.csdn.net/robinson_0612/article/details/7978179
[Linux_Unix shell _ PL_SQL]: http://blog.csdn.net/robinson_0612/article/details/8556639
[Linux_Unix shell _Oracle_monitor instance]: http://blog.csdn.net/robinson_0612/article/details/8563115
[Linux_Unix shell _Oracle_monitor listener_]: http://blog.csdn.net/robinson_0612/article/details/8563744
[Linux_Unix shell _Oracle_monitor alter log file_]: http://blog.csdn.net/robinson_0612/article/details/8569759
[EXPDP]: http://blog.csdn.net/robinson_0612/article/details/5925592
[IMPDP _]: http://blog.csdn.net/robinson_0612/article/details/5926276
[Oracle _]: http://blog.csdn.net/robinson_0612/article/details/6246738
[expdp impdp_ exclude_include]: http://blog.csdn.net/robinson_0612/article/details/7034839
[Oracle Datapump API]: http://blog.csdn.net/robinson_0612/article/details/7195849
[sendEmail]: http://blog.csdn.net/robinson_0612/article/details/8314570
[crs_setperm_RAC_]: http://blog.csdn.net/robinson_0612/article/details/8219242
[crs_profile_RAC_]: http://blog.csdn.net/robinson_0612/article/details/8192571
[RAC _]: http://blog.csdn.net/robinson_0612/article/details/8129506
[Oracle RAC services]: http://blog.csdn.net/robinson_0612/article/details/8124232
[Services in Oracle Database 10g]: http://blog.csdn.net/robinson_0612/article/details/8089812
[Migrate datbase from single instance to Oracle RAC]: http://blog.csdn.net/robinson_0612/article/details/8090095
[Oracle RAC _]: http://blog.csdn.net/robinson_0612/article/details/8073620
[Oracle RAC _ 1]: http://blog.csdn.net/robinson_0612/article/details/8072367
[Oracle RAC _Load Balance_]: http://blog.csdn.net/robinson_0612/article/details/8064599
[Oracle RAC _Load Balance_ 1]: http://blog.csdn.net/robinson_0612/article/details/8060634
[ORACLE RAC _listener.ora tnsnames.ora]: http://blog.csdn.net/robinson_0612/article/details/8053007
[ORACLE RAC _ _listener.ora tnsnames.ora_]: http://blog.csdn.net/robinson_0612/article/details/8020800
[RAC _ 1]: http://blog.csdn.net/robinson_0612/article/details/6791667
[CRS-1006 _ CRS-0215]: http://blog.csdn.net/robinson_0612/article/details/7926241
[Linux _RHEL 5.5_ _Oracle 10g RAC]: http://blog.csdn.net/robinson_0612/article/details/7906282
[runcluvfy _Oracle RAC]: http://blog.csdn.net/robinson_0612/article/details/6533094
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/5925526
[sqlnet.ora_IP_Oracle]: http://blog.csdn.net/robinson_0612/article/details/6629141
[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6629043
[Oracle _LISTENER_]: http://blog.csdn.net/robinson_0612/article/details/6629249
[ORACLE]: http://blog.csdn.net/robinson_0612/article/details/5917103
[Oracle _ 1]: http://blog.csdn.net/robinson_0612/article/details/5791597
[Oracle _ 2]: http://blog.csdn.net/robinson_0612/article/details/5816012
[Oracle _ 3]: http://blog.csdn.net/robinson_0612/article/details/5791585
[Oracle _ 4]: http://blog.csdn.net/robinson_0612/article/details/5768233
[Oracle _ 5]: http://blog.csdn.net/robinson_0612/article/details/6041217
[SYSTEM _]: http://blog.csdn.net/robinson_0612/article/details/6041207
[SYSAUX_]: http://blog.csdn.net/robinson_0612/article/details/5810732
[Oracle _unsing backup controlfile]: http://blog.csdn.net/robinson_0612/article/details/7962187
[RMAN _]: http://blog.csdn.net/robinson_0612/article/details/6032525
[RMAN _ 1]: http://blog.csdn.net/robinson_0612/article/details/6032597
[RMAN _ 2]: http://blog.csdn.net/robinson_0612/article/details/6032739
[RMAN _ 3]: http://blog.csdn.net/robinson_0612/article/details/6041247
[RMAN catalog _]: http://blog.csdn.net/robinson_0612/article/details/6043649
[catalog _RMAN_]: http://blog.csdn.net/robinson_0612/article/details/6073020
[catalog _RMAN _]: http://blog.csdn.net/robinson_0612/article/details/6073069
[RMAN _ 4]: http://blog.csdn.net/robinson_0612/article/details/6414338
[RMAN_WIN_]: http://blog.csdn.net/robinson_0612/article/details/6639064
[RMAN_ASM]: http://blog.csdn.net/robinson_0612/article/details/6453954
[linux _RMAN_shell_]: http://blog.csdn.net/robinson_0612/article/details/8029245
[RMAN]: http://blog.csdn.net/robinson_0612/article/details/8076841
[Oracle _ 6]: http://blog.csdn.net/robinson_0612/article/details/5611738
[Oracle _ 7]: http://blog.csdn.net/robinson_0612/article/details/5611672
[Oracle _ 8]: http://blog.csdn.net/robinson_0612/article/details/5559174
[Oracle _ONLINE LOG FILE_]: http://blog.csdn.net/robinson_0612/article/details/5749556
[Oracle _CONTROLFILE_]: http://blog.csdn.net/robinson_0612/article/details/5737527
[Oracle _ 9]: http://blog.csdn.net/robinson_0612/article/details/5784713
[Oracle _ROLLBACK_UNDO_]: http://blog.csdn.net/robinson_0612/article/details/5731158
[Oracle _ 10]: http://blog.csdn.net/robinson_0612/article/details/5542983
[Oracle 10g SGA _]: http://blog.csdn.net/robinson_0612/article/details/5534832
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/article/details/5529239
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485