20260731 修复bash子 shell 进程问题20260722 更新统一日志输出。20251215 更新1、自动配置并行。2、同时只能进行一个expdp任务避免资源占用。自动跟据数据库版本调用exp或expdp备份完成后自动移到备份当天日期目录可配置备份保留次数自动清理过期备份备份DG配置、用户非系统用户如SYSSYSTEM等可自行修改、用户权限、字符集信息表空间信息导出profile脚本指定用户备份时按下图修改备份结果脚本如下#!/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.ownerj.owner_name AND o.object_namej.job_name WHERE j.stateNOT 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 21) # 【关键优化】进程替换替代管道不再产生子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_SIDxxxx export ORACLE_BASExxxx export ORACLE_HOMExxxx export PATH$PATH:$HOME/bin:$ORACLE_HOME/bin export LD_LIBRARY_PATH$ORACLE_HOME/lib:/usr/lib export PS1$ORACLE_SID:$PWD export LANGen_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 #备份路径 REDUNDANCY1 #备份保留份数 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_FILEFULL_exp_${db_name}_${ALTIME} F_EXPDP_FILEFULL_expdp_${db_name}_${ALTIME} #DUMP_FILESIZE30G #分片大小可按需调整 } # 计算并行度 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_version0 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_version6 # # 2. CPU 核心数 (兼容性最好的方式) # local cpu_cores if command -v nproc /dev/null 21; 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/6MemFree Buffers Cached mem_based$(awk /^MemFree:/ {free$2} /^Buffers:/ {buf$2} /^Cached:/ {cache$2} END {printf %.0f, (freebufcache)/1024/1024} /proc/meminfo) fi fi mem_based${mem_based:-1} # 默认至少 1GB # # 4. 磁盘 I/O 评估 (无侵入式通过文件系统类型启发式计算) # local io_parallel4 # 默认基准值 local fs_typeunknown # 获取 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_parallel8 ;; # 高性能 CoW 文件系统 nfs|cifs|smbfs) io_parallel2 ;; # 网络存储延迟高不宜过高并行 ext4|xfs) io_parallel4 ;; # 常见本地高性能文件系统 ocfs2|gfs2) io_parallel3 ;; # 集群文件系统 *) io_parallel3 ;; # 未知或老式 ext3 等 esac fi # # 5. CPU 基准与系统上限计算 (适配现代与老旧系统) # local cpu_based1 local max_parallel8 # 针对 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_parallel16 else case $os_version in 5) cpu_based$((cpu_cores / 2)); max_parallel4 ;; 6) cpu_based$((cpu_cores * 3 / 5)); max_parallel8 ;; 7) cpu_based$((cpu_cores * 3 / 4)); max_parallel16 ;; *) cpu_based$((cpu_cores / 2)); max_parallel8 ;; 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_SET1 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_code0 #执行备份 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 directy compressY recordlength65535 statisticsnone fully;#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\ DIRECTORYEXPDIR DUMPFILE${F_EXPDP_FILE}_%U.dmp LOGFILE${F_EXPDP_FILE}_00.log parallel${parallel_SET} compressionALL excludeSTATISTICS ACCESS_METHODEXTERNAL_TABLE FLASHBACK_TIMESYSDATE fully;#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 profileORA_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_statusOPEN 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 21) # 控制台输出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 $
