20251215 更新，1、自动配置并行。2、同时只能进行一个expdp任务，避免资源占用
- 自动跟据数据库版本调用 exp 或 expdp
- 备份完成后自动移到备份当天日期目录
- 可配置备份保留次数，自动清理过期备份
- 备份 DG 配置、用户（非系统用户：如 SYS，SYSTEM 等，可自行修改）、用户权限、字符集信息
- 表空间信息
- 导出profile脚本
- 指定用户备份时按下图修改
备份结果：
脚本如下：
#!/bin/bash
#==============================================================#
# File : expbackup.sh #
# Ctime : 2022/07/22 #
# Mtime : 2025/12/30 #
# Desc : Oracle Database EXPDP for single/standlone/rac #
# Version : 3.0.0 #
#==============================================================#
if [[ ! $USER == "oracle" ]];then
echo -e "此脚本必须以\033[31;1m oracle \033[0m权限运行"
exit 1
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备份目录
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 -lt 1 ]]; then
parallel_NO=1
elif [[ $parallel_NO -gt $max_parallel ]]; then
parallel_NO=$max_parallel
fi
}
database_back(){
EXPID=$(ps aux|grep expdp|grep -v grep|awk '{print $2}')
if [[ -z ${EXPID} ]];then
#执行备份
if [[ ${DB_VER_PRI} -eq 10 ]];then
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
expdp \"/ as sysdba\" DIRECTORY=EXPDIR DUMPFILE=${F_expdp_file}_%U.dmp logfile=${F_expdp_file}_00.log parallel=${parallel_NO} compression=ALL cluster=N exclude=STATISTICS full=y;#11
fi
datainfo_back
else
echo -e ${RC}"有EXPDP备份进行中"${EC};
fi
}
datainfo_back(){
#导出附加信息
sqlplus -s / as sysdba <<EOF
create pfile='${BACKPATH}/pfile_${FILENAME}.ora' from spfile;
set line 900
set newpage none
set heading off
set feedback off
set pagesize 0
set echo on
set newp none
set trimout on
set trimspool on
set 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 v.sql_todo FROM (select profile, decode(PROFILE, 'DEFAULT','alter profile '||PROFILE||' limit '||reslist||';','MONITORING_PROFILE','alter profile '||PROFILE||' limit '||reslist||';', 'create profile '||PROFILE||' limit '||reslist||';') 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 order by 1)) v;
spool ${BACKPATH}/DB_user_create_${FILENAME}.sql
select '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,sys.user\$ b where a.username not in ('APEX_050000','AUDSYS','ADAMS','ANONYMOUS','AURORA$ORB$UNAUTHENTICATED', 'AWR_STAGE', 'APEX_030200','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','BLAKE','CLARK',' CS MIG','CTXSYS','DBSNMP','DIP','DMSYS','DSSYS','DEMO','DVSYS','DVF','DBSFWUSER','EXFSYS','FLOWS_FILES','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','JONES','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','OJVMSYS','PE RFS TAT','REMOTE_SCHEDULER_AGENT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SPATIAL_CSW_ADMIN_USR','SYSBACKUP','SYSKM','SYSDG','SYSRAC','SYS$UMF','SYSTEM','TRA CES VR','T SMS YS','WMSYS','XDB','XS$NULL','GSMROOTUSER','ZABBIX') and account_status='OPEN' and a.username= b.name ;
spool ${BACKPATH}/Privs_table_${FILENAME}.sql
select 'grant '||PRIVILEGE||' on '||grantor||'.'||TABLE_NAME||' to '||GRANTEE||';' CMD from dba_tab_privs where ( grantee in (select username from dba_users where username not in ('APEX_050000','AUDSYS','ADAMS','ANONYMOUS','AURORA$ORB$UNAUTHENTICATED', 'AWR_STAGE', 'APEX_030200','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','BLAKE','CLARK',' CS MIG','CTXSYS','DBSNMP','DIP','DMSYS','DSSYS','DEMO','DVSYS','DVF','DBSFWUSER','EXFSYS','FLOWS_FILES','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','JONES','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','OJVMSYS','PE RFS TAT','REMOTE_SCHEDULER_AGENT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SPATIAL_CSW_ADMIN_USR','SYSBACKUP','SYSKM','SYSDG','SYSRAC','SYS$UMF','SYSTEM','TRA CES VR','T SMS YS','WMSYS','XDB','XS$NULL','GSMROOTUSER','ZABBIX') and account_status='OPEN'))
union
select 'grant '||PRIVILEGE||' on '||grantor||'.'||TABLE_NAME||' to '||GRANTEE||';' CMD from dba_tab_privs where ( owner in (select username from dba_users where username not in ('APEX_050000','AUDSYS','ADAMS','ANONYMOUS','AURORA$ORB$UNAUTHENTICATED', 'AWR_STAGE', 'APEX_030200','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','BLAKE','CLARK',' CS MIG','CTXSYS','DBSNMP','DIP','DMSYS','DSSYS','DEMO','DVSYS','DVF','DBSFWUSER','EXFSYS','FLOWS_FILES','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','JONES','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','OJVMSYS','PE RFS TAT','REMOTE_SCHEDULER_AGENT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SPATIAL_CSW_ADMIN_USR','SYSBACKUP','SYSKM','SYSDG','SYSRAC','SYS$UMF','SYSTEM','TRA CES VR','T SMS YS','WMSYS','XDB','XS$NULL','GSMROOTUSER','ZABBIX') and account_status='OPEN'));
spool ${BACKPATH}/Privs_user_${FILENAME}.sql
DECLARE
l_privs CLOB;
BEGIN
FOR rec IN (SELECT PRIVILEGE, ADMIN_OPTION, GRANTEE FROM DBA_SYS_PRIVS where grantee in (select username from dba_users where username not in ('APEX_050000','AUDSYS','ADAMS','ANONYMOUS','AURORA$ORB$UNAUTHENTICATED', 'AWR_STAGE', 'APEX_030200','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','BLAKE','CLARK',' CS MIG','CTXSYS','DBSNMP','DIP','DMSYS','DSSYS','DEMO','DVSYS','DVF','DBSFWUSER','EXFSYS','FLOWS_FILES','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','JONES','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','OJVMSYS','PE RFS TAT','REMOTE_SCHEDULER_AGENT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SPATIAL_CSW_ADMIN_USR','SYSBACKUP','SYSKM','SYSDG','SYSRAC','SYS$UMF','SYSTEM','TRA CES VR','T SMS YS','WMSYS','XDB','XS$NULL','GSMROOTUSER','ZABBIX') and account_status='OPEN')) LOOP
l_privs := 'GRANT ' || rec.PRIVILEGE || ' TO ' || rec.GRANTEE ||
CASE WHEN rec.ADMIN_OPTION = '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(){
#备份文件整理
filelist=$(ls -l ${BACKPATH}/*${ALTIME}*)
if [[ -d ${BACKPATH}/${BAKDIR} ]];then
if [[ -n ${filelist} ]];then
mv ${BACKPATH}/*${ALTIME}* ${BACKPATH}/${BAKDIR}/
fi
else
mkdir -p ${BACKPATH}/${BAKDIR};
if [[ -n ${filelist} ]];then
mv ${BACKPATH}/*${ALTIME}* ${BACKPATH}/${BAKDIR}/
fi
fi
}
clean_file(){
#配置备份保留次数（默认值为2，保留1份）
REDUNDANCY=2
rmdir_no=$(find ${BACKPATH} -maxdepth 1 -type d -name "*_${db_name}_exp"|sort|tail -n ${REDUNDANCY}|wc -l)
if [[ ${rmdir_no} -ge ${REDUNDANCY} ]];then
rmdir=$(find ${BACKPATH} -maxdepth 1 -type d -name "*_${db_name}_exp"|sort|tail -n ${REDUNDANCY}|head -n 1);
if [[ -n ${rmdir} ]];then
rm -rf ${rmdir};
fi
fi
}
main(){
env_set
calculate_parallelism
database_back
file_archive
clean_file
}
main "$@"最后修改时间：2025-12-30 18:37:04
文章转载自楚枫默寒，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
