Oracle EXPDP备份脚本（2025/12/30更新）

原创已于 2026-06-16 14:25:14 修改 · 930 阅读
1 ·
本内容遵循CC 4.0 BY-SA版权协议
GEO检测
标签
#oracle #数据库
于 2024-05-13 11:56:45 首次发布
Oracle 专栏收录该内容
11 篇文章
订阅专栏
20251215 更新，1、自动配置并行。2、同时只能进行一个expdp任务，避免资源占用。
自动跟据数据库版本调用exp或expdp
备份完成后自动移到备份当天日期目录
可配置备份保留次数，自动清理过期备份
备份DG配置、用户（非系统用户：如SYS，SYSTEM等，可自行修改）、用户权限、字符集信息
表空间信息
导出profile脚本
指定用户备份时按下图修改
备份结果：
脚本如下：
#!/bin/bash
#==================================================================
# FileName    :  expbackup.sh
# CreateTime  :  root 2022-07-22 10:35:01
# ModifyTime  :  root 2026-06-16 14:13:10
# Sversion    :  v4.14
# Desc        :  Oracle Database EXPDP for single/standlone/rac    
#==================================================================

if [[ ! $USER == "oracle" ]];then
    echo -e "此脚本必须以\033[31;1m oracle \033[0m权限运行"
    exit 1
fi

#字体颜色
color_setting(){
    RC='\033[31;1m'        #红色 error
    GC='\033[32;1m'        #绿色 success
    YC='\033[33;1m'        #黄色 warning
    BC='\033[34;1m'        #蓝色 output
    PC='\033[35;1m'        #粉色 detail
    AC='\033[36;1m'        #天蓝 info
    EC='\033[0m'           #黑白 EC
}

# 输出日志（控制台+计划日志双输出）
log_print() {
    local LEVEL="$1"
    local MSG="$2"
    local NOW=$(date +"%Y-%m-%d %H:%M:%S")
    echo -e "[${NOW}] [${LEVEL}] ${MSG}"
    if [[ -n "${EXP_PLAN_LOG:-}" && -w "$(dirname "${EXP_PLAN_LOG}")" ]]; then
        echo -e "[${NOW}] [${LEVEL}] ${MSG}" >> "${EXP_PLAN_LOG}"
    fi
}

#环境配置
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|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'                                      #备份路径
    DBTIME=$(date +%Y%m%d)                                #备份时间
    BAKDIR=${DBTIME}_${db_name}_exp                       #EXP备份目录
    EXP_PLAN_LOG=${BACKPATH}/expdp_plan_${DBTIME}.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}
}

# 计算并行度
calculate_parallelism(){
    if [[ -e /etc/os-release ]];then
        local Verfile='/etc/os-release'
    elif [[ -e /etc/system-release ]];then
        local Verfile='/etc/system-release'
    elif [[ -e /etc/redhat-release ]];then
        local Verfile='/etc/redhat-release'
    fi 
    local os_version=$(cat ${Verfile}|egrep -o '[0-9]{1,3}'|head -n 1)
    #磁盘IOPS
    local temp_file="/tmp/rman_io_test_$$.tmp"
    local io_parallel=$(if command -v dd >/dev/null 2>&1; then
        io_result=$(dd if=/dev/zero of="$temp_file" bs=1M count=1024 2>&1)
        
        # 清理临时文件
        rm -f "$temp_file"
        
        # 提取IO速度
        if echo "$io_result" | grep -q "MB/s"; then
            echo "$io_result" | awk '/MB\/s/{sub(/ MB\/s/, ""); printf "%.0f\n",$8/100}'
        elif echo "$io_result" | grep -q "GB/s"; then
            echo "$io_result" | awk '/GB\/s/{sub(/ GB\/s/, ""); printf "%.0f\n", $NF*1024/100}'
        else
            echo "1"  # 默认值
        fi
    else
        echo "1"  # dd命令不可用时的默认值
    fi)
    #CPU核心数
    local cpu_cores=$(if command -v nproc >/dev/null 2>&1; then
        nproc
    else
        grep -c "^processor" /proc/cpuinfo
    fi)
    #可用内存/GB
    local mem_based=$(if [[ -f /proc/meminfo ]] && grep -q "MemAvailable" /proc/meminfo; then
        # CentOS 7+ 使用MemAvailable
        awk '/MemAvailable/{printf "%.0f", $2/1024/1024}' /proc/meminfo
    else
        # CentOS 5/6 使用free命令
        free -m | awk '/Mem:/{print int($4/1024)}'
    fi)
    case $os_version in
        5) cpu_based=$((cpu_cores / 2)) ;;
        6) cpu_based=$((cpu_cores * 3 / 5)) ;;
        7) cpu_based=$((cpu_cores * 3 / 4)) ;;
        *) cpu_based=$((cpu_cores / 2)) ;;
    esac
    case $os_version in
        5) max_parallel=4 ;;
        6) max_parallel=8 ;;
        7) max_parallel=16 ;;
        *) max_parallel=8 ;;
    esac
    parallel_NO=$(echo "$cpu_based $mem_based $io_parallel" | tr ' ' '\n' | sort -n | head -1)
    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
    log_print "INFO" "自动计算并行通道数: ${parallel_SET} (CPU基准:${cpu_based},内存:${mem_based},IO:${io_parallel},系统上限:${max_parallel})"
}

# 执行备份
database_back(){
    EXPID=$(ps aux|grep expdp|grep -v grep|awk '{print $2}')
    if [[ -z ${EXPID} ]];then
        #执行备份
        if [[ ${DB_VER_PRI} -eq 10 ]];then
            log_print "INFO" "开始${AC}EXP${EC}备份，并行通道: ${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
            #压缩备份文件
            gzip ${BACKPATH}/${F_EXP_FILE}.dmp;
        else
            log_print "INFO" "开始${AC}EXPDP${EC}备份，并行通道: ${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
        fi
        log_print "INFO" "导出数据库元数据配置文件";
        datainfo_back 
        log_print "INFO" "${AC}全量${EC}备份全部完成"
    else
        log_print "ERROR" "${RC}检测到正在运行的EXP备份进程 PID: ${EXPID}，终止本次备份${EC}";
    fi
}

# 导数据配置(用户、表空间、权限、DG配置、字符集、pfile)
datainfo_back(){
#导出附加信息
sqlplus -s / as sysdba <<EOF
create pfile='${BACKPATH}/pfile_${FILENAME}.ora' from spfile;
set line 900 newpage none heading off feedback off pagesize 0 echo on newp none trimout on trimspool on SERVEROUTPUT ON size 1000000
col name for a20
col value for a50
col display_value for a50
col CMD for a200
col instance_name for a10 
col host_name  for a20 
col online_status for a10 
col TABLESPACE_NAME  for a40 
col status for a10
col Extent for a10 
col sql_TODO for a150
col parameter for a30
spool ${BACKPATH}/DG_${FILENAME}.ini
select name,value,display_value from v\$parameter where name='log_archive_config' or (name like 'log_archive_dest_%' and name not like 'log_archive_dest_state_%') and value is not null order by 1;
spool ${BACKPATH}/CHARACTERSET_${FILENAME}.conf
select * from nls_database_parameters where parameter like '%CHARACTERSET%' order by 1;
spool ${BACKPATH}/profile_${FILENAME}.sql
SELECT CASE WHEN profile IN ('DEFAULT','MONITORING_PROFILE') THEN 'alter profile '||profile||' limit '||reslist||';' ELSE 'create profile '||profile||' limit '||reslist||';' END sql_todo
FROM (
    SELECT profile,LISTAGG(resource_name||' '||limit,',') WITHIN GROUP (ORDER BY resource_name,limit) reslist
    FROM dba_profiles
    WHERE profile<>'ORA_STIG_PROFILE'
    GROUP BY profile
);
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 ${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 ${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;
/
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;
EOF
}

# 备份文件归档整理
file_archive(){
    local archive_dir="${BACKPATH}/${BAKDIR}"
    log_print "INFO" "归档匹配*${ALTIME}*的备份文件至目录：${archive_dir}"
    #备份文件整理
    local filelist=$(ls -l ${BACKPATH}/*${ALTIME}*)
    if [[ -d ${archive_dir} ]];then
        if [[ -n ${filelist} ]];then
            mv ${BACKPATH}/*${ALTIME}* ${archive_dir}/
        fi
    else 
        mkdir -p ${archive_dir};
        if [[ -n ${filelist} ]];then
            mv ${BACKPATH}/*${ALTIME}* ${archive_dir}/
        fi
    fi
}

# 清理历史备份目录（默认保留1份有效备份）
clean_file(){
    local REDUNDANCY=2
    local BACKPATH="$BACKPATH"

    # 校验备份目录是否存在
    if [[ ! -d "${BACKPATH}" ]];then
        log_print "ERROR" "${GC}备份目录${BACKPATH}不存在，跳过清理${EC}"
        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 threshold_dir
        threshold_dir=$(echo "${dump_list}" | tail -n "${REDUNDANCY}" | head -n 1)

        if [[ -n "${threshold_dir}" && -d "${threshold_dir}" ]];then
            # 删除所有早于临界目录的旧dump目录
            find "${BACKPATH}" -maxdepth 1 -type d -name "*_${db_name}_exp" ! -newer "${threshold_dir}" -exec rm -rf {} +
            log_print "INFO" "${GC}清理EXPDP老旧备份完成，保留最近${REDUNDANCY}份，临界目录：${threshold_dir}${EC}"
        fi
    else
        log_print "INFO" "${GC}EXPDP备份共${total_dump}份，未超过保留阈值${REDUNDANCY}，无需清理${EC}"
    fi

    # 清理10天前备份日志，区分成功/失败日志
    if find "${BACKPATH}" -maxdepth 1 -type f -name "expdp_plan_*.log" -mtime +10 -delete;then
        log_print "INFO" "${GC}清理历史备份日志完成${EC}"
    else
        log_print "WARN" "${RC}EXP日志清理异常，权限或目录不存在${EC}"
    fi
}
 
# 主程序
main(){
    color_setting
    env_set
    calculate_parallelism
    database_back
    file_archive
    clean_file
    log_print "SUCCESS" "${GC}EXP备份脚本全部执行完成${EC}"
    mv ${EXP_PLAN_LOG} ${BACKPATH}/${BAKDIR}/
}

########################### 程序入口 ###########################
main "$@"