20251216 更新，添加并行配置
20241122 更新添加备份profile
一、脚本说明：
1、有备份进程，则退出程序
2、无备份进程且无备份目录，则进行零级备份
3、无备份进程且有备份目录，则进行归档备份
4、full\arc 指定备份类型
二、调用方法：
- 手工调用：
rmanbackup.sh # 零级备份 (默认方式)
rmanbackup.sh full # 强制零级备份
rmanbackup.sh arc # 强制归档备份 - 计划任务
30 0 * * * sh +x /home/oracle/rmanbackup.sh #每天 0:30 备份一次
15 */4 * * * sh +x /home/oracle/rmanbackup.sh # 每天每隔 4 小时备份一次，每天的第一次根据是否存在备份目录进行零备或归档备份
15 0 * * 6 sh +x /home/oracle/rmanbackup.sh full
45 */4 * * * sh +x /home/oracle/rmanbackup.sh arc #每周六 0:15 零备一次，其它日期每 4 小时一次归档备份
三、备份结果：
检查备份大小：
列出上次零级备份大小及之后归档备份大小
编辑
select 'ZERO_BAK'||'|'||device_type||'|'||round(sum(OUTPUT_BYTES)/1024/1024/1024,2)||'GB|'||to_char(start_time,'yyyy-mm-dd')||'|'||to_char(start_time,'day') bak_time from v$backup_set_details where INCREMENTAL_LEVEL=0 group by to_char(start_time,'yyyy-mm-dd'),to_char(start_time,'day'),device_type
union
select 'ARCH_BAK'||'|'||device_type||'|'||round(sum(OUTPUT_BYTES)/1024/1024/1024,2)||'GB|'||min(to_char(start_time,'yyyy-mm-dd'))||'~'||max(to_char(start_time,'yyyy-mm-dd')) bak_time from v$backup_set_details where INCREMENTAL_LEVEL is null and start_time >= (select distinct(to_date(to_char(start_time,'YYYY-MM-DD'),'YYYY-MM-DD')) start_time from v$backup_set_details where INCREMENTAL_LEVEL=0) group by device_type order by 1 desc
#!/bin/bash
#==============================================================#
# File : rmanbackup.sh #
# Ctime : 2022/07/11 #
# Mtime : 2025/12/16 #
# Desc : Oracle Database RMAN for single/standlone/rac #
# Version : 3.0.0 #
#==============================================================#
# Function: 零级备份、归档备份 #
# Script logic: 1、有备份进程，则退出程序 #
# 2、无备份进程且无备份目录，则进行零级备份 #
# 3、无备份进程且有备份目录，则进行归档备份 #
# 4、full\arc 指定备份类型 #
# #
# Command Format: #
# rmanbackup.sh 零级备份(默认方式) #
# rmanbackup.sh full 强制零级备份 #
# rmanbackup.sh arc 强制归档备份 #
#==============================================================#
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
}
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>'
BPATH='/rman/' #备份主路径
BACKPATH=${BPATH}$(date +%Y%m%d)_rman #备份子路径
DBTIME=$(date +%Y%m%d) #备份日期
ALTIME=$(date +%Y%m%d_%H%M%S)_$(hostname) #备份时间
FILENAME=$(hostname)_${ALTIME}
COMM=rman #备份命令
sql_file="/home/oracle/datainfo.sql"
rman_plan_log="rman_plan_${DBTIME}.log"
rman_details_log="rman_details_${DBTIME}.log"
if [[ -e /etc/os-release ]];then
Verfile='/etc/os-release'
elif [[ -e /etc/system-release ]];then
Verfile='/etc/system-release'
elif [[ -e /etc/redhat-release ]];then
Verfile='/etc/redhat-release'
fi
os_version=$(cat ${Verfile}|egrep -o '[0-9]{1,3}'|head -n 1)
case ${os_version} in
5)
cpu_sum=$(cat /proc/cpuinfo | grep "processor" | wc -l);;
*)
cpu_sum=$(nproc);;
esac
# 根据CPU核心数设置并行度
if [ $cpu_sum -ge 16 ]; then
parallel_NO=10
elif [ $cpu_sum -le 4 ]; then
parallel_NO=1
else
parallel_NO=$((cpu_sum - 2))
fi
}
scripts_set(){
rman_full=$(
cat <<-RMAN
CONFIGURE DEVICE TYPE DISK PARALLELISM ${parallel_NO};
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKPATH}/%F';
crosscheck backup;
crosscheck archivelog all;
backup filesperset 20 as backupset incremental level=0 database skip inaccessible format '${BACKPATH}/DB_%n_%I_%T_%U' plus archivelog delete all input format '${BACKPATH}/AL_%n_%I_%T_%U';
report obsolete;
delete noprompt expired archivelog all;
delete noprompt expired backup;
delete noprompt obsolete;
RMAN
)
rman_arc=$(
cat <<-RMAN
CONFIGURE DEVICE TYPE DISK PARALLELISM ${parallel_NO};
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKPATH}/%F';
crosscheck backup;
crosscheck archivelog all;
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input format '${BACKPATH}/AL_%n_%I_%T_%U';
report obsolete;
delete noprompt expired archivelog all;
delete noprompt expired backup;
delete noprompt obsolete;
RMAN
)
#导数据配置(用户、表空间、权限、DG配置、字符集、pfile)
echo "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}/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}/CHARACTERSET_${FILENAME}.conf
select * from nls_database_parameters where parameter like '%CHARACTERSET%' order by 1;
spool ${BACKPATH}/DB_username_${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 ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','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 ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','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 ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','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 ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','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) 总计_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) 已使用_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) 总计_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) 已使用_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;" > ${sql_file}
}
rman_zero(){
echo -e $(date +"%Y-%m-%d %H:%M:%S")" 开始${AC}零级${EC}备份" >> /${BACKPATH}/${rman_plan_log};
$COMM target / msglog ${BACKPATH}/${rman_details_log} <<EOF
run
{
$rman_full
}
EOF
sqlplus -s / as sysdba < ${sql_file};
echo -e $(date +"%Y-%m-%d %H:%M:%S")" ${AC}零级${EC}备份结束" >> /${BACKPATH}/${rman_plan_log};
}
rman_arc(){
echo -e $(date +"%Y-%m-%d %H:%M:%S")" 开始${AC}归档${EC}备份" >> /${BACKPATH}/${rman_plan_log};
$COMM target / msglog ${BACKPATH}/rman_AL_$ALTIME.log <<EOF
run
{
$rman_arc
}
EOF
echo -e $(date +"%Y-%m-%d %H:%M:%S")" ${AC}归档${EC}备份结束" >> /${BACKPATH}/${rman_plan_log};
}
exe_rman(){
RMANID=$(ps aux |grep ${BACKPATH}|grep -v grep|awk '{ print $2}')
local option=$1
if [[ -z ${RMANID} ]];then
case ${option} in
full|FULL)
if [[ ! -d ${BACKPATH} ]];then mkdir ${BACKPATH}; fi
rman_zero ;;
arc|ARC)
if [[ ! -d ${BACKPATH} ]];then mkdir ${BACKPATH}; fi
rman_arc ;;
*)
if [[ -d ${BACKPATH} ]];then
rman_arc
else
mkdir ${BACKPATH}
rman_zero
fi ;;
esac
else
echo -e ${RC}"有RMAN备份进行中"${EC};
fi
}
#清理历史备份目录（默认清理10天前的目录）
clear_file(){
REDUNDANCY=10
rmdir_no=`find ${BPATH} -maxdepth 1 -type d -name "*_rman"|sort|tail -n ${REDUNDANCY}|wc -l`
if [ ${rmdir_no} -ge ${REDUNDANCY} ];then
rmdir=`find ${BPATH} -maxdepth 1 -type d -name "*_rman"|sort|tail -n ${REDUNDANCY}|head -n 1`;
if [ ${rmdir} ];then
rm -rf ${rmdir};
fi
fi
rm -f ${sql_file}
}
main(){
color_setting
env_set
scripts_set
exe_rman $1
clear_file
}
main "$@"
最后修改时间：2025-12-16 11:40:15
文章转载自楚枫默寒，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
