Oracle EXPDP备份脚本（2026/7/22更新）

20260731 修复bash子 shell 进程问题
20260722 更新：统一日志输出。
20251215 更新，1、自动配置并行。2、同时只能进行一个expdp任务，避免资源占用。

1. 自动跟据数据库版本调用exp或expdp
2. 备份完成后自动移到备份当天日期目录
3. 可配置备份保留次数，自动清理过期备份
4. 备份DG配置、用户（非系统用户：如SYS，SYSTEM等，可自行修改）、用户权限、字符集信息
5. 表空间信息
6. 导出profile脚本
7. 指定用户备份时按下图修改

备份结果：

脚本如下：

#!/bin/bash #============================================ # FileName : expbackup.sh # CreateTime : root 2022-07-11 10:35:01 # ModifyTime : root 2026-07-27 11:32:49 # Sversion : v4.18 # Desc : Oracle Database EXPDP for single/standlone/rac # UpdateNote : v4.18 统一exp/expdp输出日志格式，实时捕获标准化 #============================================ set -o errexit set -o nounset set -o pipefail if [[ ! $USER == "oracle" ]];then echo -e "此脚本必须以\033[31;1m oracle \033[0m权限运行" exit 1 fi #字体颜色 color_setting(){ RC='\033[31;1m' # 红色-错误 GC='\033[32;1m' # 绿色-成功 YC='\033[33;1m' # 黄色-警告 BC='\033[34;1m' # 蓝色-输出 DC='\033[35;1m' # 粉色-详情 AC='\033[36;1m' # 天蓝-信息 FRC='\033[31;5;1m' # 红色闪烁-错误 FGC='\033[32;5;1m' # 绿色闪烁-成功 FYC='\033[33;5;1m' # 黄色闪烁-警告 FBC='\033[34;5;1m' # 蓝色闪烁-输出 FDC='\033[35;5;1m' # 粉色闪烁-详情 FAC='\033[36;5;1m' # 天蓝闪烁-信息 BRC='\033[41;37m' # 红底白字 EC='\033[0m' # 颜色复位 } # ===================== 公共工具函数 ===================== # 日志函数标准入参： # $1: 日志级别 仅允许 INFO / WARNING / ERROR / SUCCESS # $2: 纯文本日志内容，不携带任何颜色转义字符 log_print() { local LEVEL="$1" local MSG="$2" local NOW=$(date +"%Y-%m-%d %H:%M:%S") local COLOR_PREFIX="" # 仅对LEVEL匹配对应终端颜色前缀 case "${LEVEL}" in "ERROR") COLOR_PREFIX="${FRC}" ;; "WARNING") COLOR_PREFIX="${YC}" ;; "SUCCESS") COLOR_PREFIX="${GC}" ;; "INFO") COLOR_PREFIX="${AC}" ;; *) COLOR_PREFIX="" ;; esac local COLOR_LINE="[${NOW}] [${COLOR_PREFIX}${LEVEL}${EC}] ${MSG}" # 写入日志文件：纯文本无颜色 local PLAIN_LINE="[${NOW}] [${LEVEL}] ${MSG}" # 控制台输出带级别对应颜色 echo -e "${COLOR_LINE}" # 日志文件写入干净纯文本 if [[ -n "${EXP_PLAN_LOG:-}" && -w "$(dirname "${EXP_PLAN_LOG}")" ]]; then [[ -w "${BACKPATH}" ]] && echo "${PLAIN_LINE}" >> "${EXP_PLAN_LOG}" fi } # ===================== 公共函数 ===================== # 清理残留DataPump孤立任务（前置执行） clean_orphan_datapump(){ log_print "INFO" "开始检查并清理残留Datapump Master表(孤立任务)" local SQL_OUT SQL_OUT=$(sqlplus -s / as sysdba <<EOF set heading off feedback off verify off trimspool on serveroutput on size unlimited DECLARE v_sql VARCHAR2(32767); v_cnt NUMBER := 0; v_obj_exist NUMBER; BEGIN FOR rec IN ( SELECT o.owner, o.object_name FROM dba_objects o INNER JOIN dba_datapump_jobs j ON o.owner=j.owner_name AND o.object_name=j.job_name WHERE j.state='NOT RUNNING' AND j.job_name NOT LIKE 'BIN$%' AND o.object_type = 'TABLE' AND o.status = 'VALID' ) LOOP -- 二次校验表仍然存在，防止并发下对象提前被删除 SELECT COUNT(1) INTO v_obj_exist FROM dba_tables WHERE owner = rec.owner AND table_name = rec.object_name; IF v_obj_exist > 0 THEN v_sql := 'DROP TABLE '||DBMS_ASSERT.SIMPLE_SQL_NAME(rec.owner)||'.'||DBMS_ASSERT.SIMPLE_SQL_NAME(rec.object_name)||' PURGE'; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('[CLEAN] Drop orphan master table: '||rec.owner||'.'||rec.object_name); v_cnt := v_cnt + 1; END IF; END LOOP; IF v_cnt = 0 THEN DBMS_OUTPUT.PUT_LINE('[CLEAN] 未找到孤立的数据泵主表，无需清理.'); ELSE DBMS_OUTPUT.PUT_LINE('[CLEAN] 已清理的孤立表总数: '||v_cnt); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('[ERROR] 清理孤立数据泵失败: '||SQLERRM); DBMS_OUTPUT.PUT_LINE('[ERROR_BACKTRACE] '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); END; / EOF 2>&1) # 【关键优化】进程替换替代管道，不再产生子shell while IFS= read -r line; do [[ -n "${line}" ]] && log_print "INFO" "[SQLPLUS] ${line}" done < <(echo "${SQL_OUT}" | sed '/^[[:space:]]*$/d') log_print "INFO" "孤立Datapump清理执行完成" } #环境配置 env_set(){ umask 022 export ORACLE_SID=xxxx export ORACLE_BASE=xxxx export ORACLE_HOME=xxxx export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib export PS1='$ORACLE_SID:$PWD>' export LANG=en_US.UTF-8 DB_VER_PRI=$(sqlplus -v 2>/dev/null|awk '{print $3}' | cut -f 1 -d '.') db_name=$(sqlplus -s / as sysdba << "EOF" set heading off feedback off verify off select name from v$database; exit; EOF ) db_name=$(echo "${db_name}" | tr -d '[:space:]') BACKPATH='/rman' #备份路径 REDUNDANCY=1 #备份保留份数 DBTIME=$(date +%Y%m%d) #备份日期 SBTIME=$(date +%Y%m%d_%H%M%S) #备份日期(秒) BAKDIR=${DBTIME}_${db_name}_exp #EXP备份目录 EXP_PLAN_LOG=${BACKPATH}/expdp_plan_${SBTIME}.log #EXP计划日志 ALTIME=$(date +%Y%m%d%H%M%S) #精确时间戳 FILENAME=${db_name}_${ALTIME} F_EXP_FILE=FULL_exp_${db_name}_${ALTIME} F_EXPDP_FILE=FULL_expdp_${db_name}_${ALTIME} #DUMP_FILESIZE=30G #分片大小，可按需调整 } # 计算并行度 calculate_parallelism(){ # ========================================== # 1. 安全获取操作系统大版本号 (兼容所有目标系统) # ========================================== local Verfile="" local os_dist="" # 全覆盖各发行版release文件 if [[ -f /etc/oracle-release ]];then Verfile='/etc/oracle-release' elif [[ -f /etc/redhat-release ]];then Verfile='/etc/redhat-release' elif [[ -f /etc/centos-release ]];then Verfile='/etc/centos-release' elif [[ -f /etc/rocky-release ]];then Verfile='/etc/rocky-release' elif [[ -f /etc/kylin-release ]];then Verfile='/etc/kylin-release' elif [[ -f /etc/anolis-release ]];then Verfile='/etc/anolis-release' elif [[ -f /etc/os-release ]];then Verfile='/etc/os-release' fi os_dist=$(cat "${Verfile}" 2>/dev/null|awk '{print $1}') local os_version="0" if [[ -n "${Verfile}" && -r "${Verfile}" ]];then # 提取第一个数字为主版本（5/6/7/8），麒麟/龙蜥/Rocky都能兼容 os_version=$(egrep -o '[0-9]+' "${Verfile}" | head -n 1) fi # 兜底防止空 [[ ! "${os_version}" =~ ^[0-9]+$ ]] && os_version="6" # ========================================== # 2. CPU 核心数 (兼容性最好的方式) # ========================================== local cpu_cores if command -v nproc >/dev/null 2>&1; then cpu_cores=$(nproc) else # nproc 在极老系统可能没有，读取 cpuinfo 最稳妥 cpu_cores=$(grep -c "^processor" /proc/cpuinfo) fi cpu_cores=${cpu_cores:-1} # ========================================== # 3. 可用内存 (GB) - 纯 /proc/meminfo 方案，彻底抛弃 free 命令 # ========================================== local mem_based if [[ -f /proc/meminfo ]]; then if grep -q "MemAvailable" /proc/meminfo; then # CentOS 7+ / Rocky / Kylin V10 / Anolis mem_based=$(awk '/MemAvailable/{printf "%.0f", $2/1024/1024}' /proc/meminfo) else # CentOS 5/6：MemFree + Buffers + Cached mem_based=$(awk ' /^MemFree:/ {free=$2} /^Buffers:/ {buf=$2} /^Cached:/ {cache=$2} END {printf "%.0f", (free+buf+cache)/1024/1024} ' /proc/meminfo) fi fi mem_based=${mem_based:-1} # 默认至少 1GB # ========================================== # 4. 磁盘 I/O 评估 (无侵入式：通过文件系统类型启发式计算) # ========================================== local io_parallel=4 # 默认基准值 local fs_type="unknown" # 获取 Oracle 数据目录所在挂载点 local oracle_dir="${ORACLE_BASE:-/}/oradata" [[ ! -d "${oracle_dir}" ]] && oracle_dir="/" # 兼容极老系统的 df 和 mount 命令组合 local mount_point=$(df "${oracle_dir}" 2>/dev/null | awk 'END{print $NF}') if [[ -n "${mount_point}" ]]; then # 使用 mount 命令获取文件系统类型，比 stat 兼容性好 100 倍 fs_type=$(mount 2>/dev/null | grep " on ${mount_point} type " | awk '{print $5}') case "$fs_type" in zfs|btrfs) io_parallel=8 ;; # 高性能 CoW 文件系统 nfs|cifs|smbfs) io_parallel=2 ;; # 网络存储延迟高，不宜过高并行 ext4|xfs) io_parallel=4 ;; # 常见本地高性能文件系统 ocfs2|gfs2) io_parallel=3 ;; # 集群文件系统 *) io_parallel=3 ;; # 未知或老式 ext3 等 esac fi # ========================================== # 5. CPU 基准与系统上限计算 (适配现代与老旧系统) # ========================================== local cpu_based=1 local max_parallel=8 # 针对 Kylin V10 (基于 CentOS 10/8) 和 Anolis 等国产系统，版本号可能较大，按现代系统处理 if [[ "$os_dist" == "kylin" || "$os_dist" == "anolis" || "$os_dist" == "rocky" || "$os_version" -ge 8 ]]; then cpu_based=$((cpu_cores * 3 / 4)) max_parallel=16 else case $os_version in 5) cpu_based=$((cpu_cores / 2)); max_parallel=4 ;; 6) cpu_based=$((cpu_cores * 3 / 5)); max_parallel=8 ;; 7) cpu_based=$((cpu_cores * 3 / 4)); max_parallel=16 ;; *) cpu_based=$((cpu_cores / 2)); max_parallel=8 ;; esac fi # ========================================== # 6. 综合计算 (木桶效应：取最小值) # ========================================== local parallel_NO parallel_NO=$(printf "%s\n%s\n%s\n" "${cpu_based}" "${mem_based}" "${io_parallel}" | sort -n | head -n 1) # ========================================== # 7. 边界处理 # ========================================== if [[ $parallel_NO -le 1 ]]; then parallel_SET=1 elif [[ $parallel_NO -gt $max_parallel ]]; then parallel_SET=$max_parallel else parallel_SET=$parallel_NO fi # ========================================== # 8. 日志输出 # ========================================== log_print "INFO" "自动计算并行通道数: ${parallel_SET} (OS:${os_dist}${os_version}, CPU基准:${cpu_based}, 可用内存:${mem_based}GB, IO基准:${io_parallel}[FS:${fs_type}], 系统上限:${max_parallel})" } # 执行备份【改造：统一捕获exp/expdp输出，标准化日志】 database_back(){ log_print "INFO" "开始数据库备份" # 安全进程检测，规避grep自匹配竞争条件 EXPID=$(ps aux|awk '/[e]xpdp/ {print $2}') if [[ -z ${EXPID} ]];then local ret_code=0 #执行备份 if [[ ${DB_VER_PRI} -eq 10 ]];then log_print "INFO" "开始 EXP 备份，并行通道: ${parallel_SET}"; exp \"/ as sysdba\" file=${BACKPATH}/${F_EXP_FILE}.dmp log=${BACKPATH}/${F_EXP_FILE}.log direct=y compress=Y recordlength=65535 statistics=none full=y;#10 ret_code=$? if [[ ${ret_code} -eq 0 ]];then log_print "INFO" "EXP导出正常完成，开始压缩备份文件" gzip ${BACKPATH}/${F_EXP_FILE}.dmp; else log_print "ERROR" "EXP导出执行异常，返回码:${ret_code}" return ${ret_code} fi else log_print "INFO" "开始 EXPDP 备份，并行通道: ${parallel_SET}"; expdp \"/ as sysdba\" DIRECTORY=EXPDIR DUMPFILE=${F_EXPDP_FILE}_%U.dmp LOGFILE=${F_EXPDP_FILE}_00.log parallel=${parallel_SET} compression=ALL exclude=STATISTICS ACCESS_METHOD=EXTERNAL_TABLE FLASHBACK_TIME=SYSDATE full=y;#11 ret_code=$? if [[ ${ret_code} -eq 0 ]];then log_print "INFO" "EXP导出正常完成，开始压缩备份文件" else log_print "ERROR" "EXPDP导出执行异常，返回码:${ret_code}" return ${ret_code} fi fi log_print "INFO" "导出数据库元数据配置文件"; datainfo_back log_print "INFO" "全量备份全部完成" else log_print "ERROR" "检测到正在运行的EXP备份进程 PID: ${EXPID}，终止本次备份"; return 1 fi } # 导数据配置(用户、表空间、权限、DG配置、字符集、pfile) datainfo_back(){ log_print "INFO" "开始执行数据库元数据导出SQL查询" local SQL_OUT SQL_OUT=$(sqlplus -s / as sysdba <<SQL_CONTENT create pfile='${BACKPATH}/pfile_${FILENAME}.ora' from spfile; SET NEWPAGE NONE SPACE 0 line 32767 pagesize 0 heading off feedback off verify off echo off trimout on trimspool on wrap off SERVEROUTPUT ON SIZE 1000000 FORMAT TRUNCATED LONG 1000000 LONGCHUNKSIZE 1000000 col name for a35 col value for a120 col display_value for a200 col CMD for a200 col sql_TODO for a200 col instance_name for a12 col host_name for a30 col online_status for a12 col TABLESPACE_NAME for a60 col status for a12 col Extent for a12 col parameter for a30 spool ${BACKPATH}/DG_${FILENAME}.ini select LPAD(rownum,2,'0')||' '||name name,value from v\$parameter where name in ('db_create_file_dest','db_recovery_file_dest','log_archive_config','log_archive_dest_1','log_archive_dest_state_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_state_2','log_archive_dest_state_3','fal_client','fal_server','standby_file_management','db_file_name_convert','log_file_name_convert','db_name','db_unique_name','service_names','instance_name','spfile') order by rownum ; spool off spool ${BACKPATH}/CHARACTERSET_${FILENAME}.conf select * from nls_database_parameters where parameter like '%CHARACTERSET%' order by 1; spool off -- Oracle11g规避LISTAGG ORA-01489超长报错，拆分行输出 spool ${BACKPATH}/profile_${FILENAME}.sql SELECT CASE WHEN profile IN ('DEFAULT','MONITORING_PROFILE') THEN 'alter profile '||profile||' limit '||resource_name||' '||limit||';' ELSE 'create profile '||profile||' limit '||resource_name||' '||limit||';' END sql_todo FROM dba_profiles WHERE profile<>'ORA_STIG_PROFILE' ORDER BY profile,resource_name; spool off spool ${BACKPATH}/DB_user_create_${FILENAME}.sql SELECT DISTINCT 'CREATE USER "'||a.username||'" IDENTIFIED BY VALUES '''||b.spare4||';'||b.password||''' DEFAULT TABLESPACE "'||a.default_tablespace||'" TEMPORARY TABLESPACE "'||a.temporary_tablespace||'";' CMD FROM dba_users a JOIN sys.user\$ b ON a.username = b.name WHERE a.account_status='OPEN' AND a.username NOT IN (SELECT DISTINCT schema FROM dba_registry) AND a.username NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','XDB','ZABBIX','AUDSYS','CTXSYS'); spool off spool ${BACKPATH}/Privs_table_${FILENAME}.sql WITH non_sys_users AS ( SELECT username FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN (SELECT DISTINCT schema FROM dba_registry) ) SELECT 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' || TABLE_NAME || ' to ' || GRANTEE || ';' CMD FROM dba_tab_privs WHERE grantee IN (SELECT username FROM non_sys_users) OR owner IN (SELECT username FROM non_sys_users); spool off spool ${BACKPATH}/Privs_user_${FILENAME}.sql DECLARE l_privs CLOB; BEGIN FOR rec IN ( SELECT DISTINCT p.PRIVILEGE, p.ADMIN_OPTION, p.GRANTEE FROM DBA_SYS_PRIVS p JOIN DBA_USERS u ON p.GRANTEE = u.USERNAME WHERE u.ACCOUNT_STATUS = 'OPEN' AND u.USERNAME NOT IN (SELECT DISTINCT schema FROM dba_registry) AND u.USERNAME NOT IN ( 'SYS','SYSTEM','OUTLN','DBSNMP','XDB','ZABBIX','AUDSYS','CTXSYS', 'OLAPSYS','MDSYS','ORDSYS','DVSYS','SYSMAN','SYSBACKUP','SYSDG' ) ) LOOP l_privs := 'GRANT ' || rec.PRIVILEGE || ' TO ' || rec.GRANTEE || CASE rec.ADMIN_OPTION WHEN 'YES' THEN ' WITH ADMIN OPTION' ELSE '' END || ';'; DBMS_OUTPUT.PUT_LINE(l_privs); END LOOP; END; / spool off SET heading on SET pagesize 50 spool ${BACKPATH}/Tablespace_${FILENAME}.txt SELECT I.instance_name,I.host_name,A.status,A.autoextensible Extent,A.TABLESPACE_NAME,ROUND(A.TOTAL_SPACE/1024/1024/1024,0) TOTAL_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) USED_GB FROM (SELECT status,TABLESPACE_NAME,SUM(BYTES) BYTES_ALLOC,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) TOTAL_SPACE,autoextensible FROM DBA_DATA_FILES GROUP BY STATUS, TABLESPACE_NAME, autoextensible) A, (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES_FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B, V\$INSTANCE I WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) union SELECT I.instance_name,I.host_name,A.status,A.autoextensible Extent,A.TABLESPACE_NAME,ROUND(A.TOTAL_SPACE/1024/1024/1024,0) TOTAL_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) USED_GB FROM (SELECT STATUS,TABLESPACE_NAME,SUM(BYTES) BYTES_ALLOC,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) TOTAL_SPACE,autoextensible FROM DBA_temp_FILES GROUP BY STATUS, TABLESPACE_NAME, autoextensible) A, (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES_FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B, V\$INSTANCE I WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) ORDER BY 5; spool off SQL_CONTENT 2>&1) # 控制台输出SQL执行日志，过滤空行 echo "${SQL_OUT}" | sed '/^[[:space:]]*$/d' | while read -r line; do [[ -n "${line}" ]] && log_print "INFO" "[SQLPLUS] ${line}" done log_print "INFO" "数据库元数据导出SQL执行完毕" } # 备份文件归档整理 file_archive(){ local archive_dir="${BACKPATH}/${BAKDIR}" log_print "INFO" "归档匹配*${ALTIME}*的备份文件至目录：${archive_dir}" mkdir -p "${archive_dir}" local filelist filelist=$(ls -l "${BACKPATH}"/*"${ALTIME}"* 2>/dev/null || true) if [[ -n ${filelist} ]];then mv "${BACKPATH}"/*"${ALTIME}"* "${archive_dir}/" fi } # 清理历史备份目录（默认保留1份有效备份） clean_file(){ if [[ ! -d "${BACKPATH}" ]];then log_print "ERROR" "备份目录${BACKPATH}不存在，跳过清理" return 1 fi # 获取排序后的dump目录列表（普通换行分隔，centos6兼容） local dump_list dump_list=$(find "${BACKPATH}" -maxdepth 1 -type d -name "*_${db_name}_exp" | sort) # 统计总dump文件夹数量 local total_dump total_dump=$(echo "${dump_list}" | wc -l) # 总数 > 保留份数才清理 if [[ "${total_dump}" -gt "${REDUNDANCY}" ]];then local del_count=$((total_dump - REDUNDANCY)) local del_dirs del_dirs=$(echo "${dump_list}" | head -n "${del_count}") echo "${del_dirs}" | xargs rm -rf log_print "INFO" "清理EXPDP老旧备份完成，保留最近${REDUNDANCY}份，删除目录数量:${del_count}" else log_print "INFO" "EXPDP备份共${total_dump}份，未超过保留阈值${REDUNDANCY}，无需清理。" fi # 清理10天前备份日志，区分成功/失败日志 if find "${BACKPATH}" -maxdepth 1 -type f -name "expdp_plan_*.log" -mtime +10 -delete;then log_print "INFO" "清理历史备份日志完成。" else log_print "WARNING" "EXP日志清理异常，权限或目录不存在。" fi } # 主程序【增加 database_back 调用】 main(){ color_setting env_set calculate_parallelism clean_orphan_datapump database_back # 关键！新增导出调用 file_archive clean_file log_print "SUCCESS" "EXP备份脚本全部执行完成。" mv "${EXP_PLAN_LOG}" "${BACKPATH}/${BAKDIR}/" } ########################### 程序入口 ########################### main "$@"