一、脚本说明：
File : rmanbackup.sh
Ctime : 2022-07-11 12:32:09
Mtime : 2024-08-28 11:50:28
Desc : Oracle Database RMAN for single/standlone/rac
Version : 2.0.3
Function: 零级备份、归档备份
Script logic: 1、有备份进程，则退出程序
2、无备份进程且无备份目录，则进行零级备份
3、无备份进程且有备份目录，则进行归档备份
4、full\arc 指定备份类型
Command Format:
rmanbackup.sh #零级备份(默认方式)
rmanbackup.sh full #强制零级备份
rmanbackup.sh 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小时一次归档备份
三、备份结果：
#!/bin/bash
#==============================================================#
# File : rmanbackup.sh #
# Ctime : 2022-07-11 12:32:09 #
# Mtime : 2024-08-28 11:50:28 #
# Desc : Oracle Database RMAN for single/standlone/rac #
# Version : 2.0.3 #
#==============================================================#
# Function: 零级备份、归档备份 #
# Script logic: 1、有备份进程，则退出程序 #
# 2、无备份进程且无备份目录，则进行零级备份 #
# 3、无备份进程且有备份目录，则进行归档备份 #
# 4、full\arc 指定备份类型 #
# #
# Command Format: #
# rmanbackup.sh 零级备份(默认方式) #
# rmanbackup.sh full 强制零级备份 #
# rmanbackup.sh arc 强制归档备份 #
#==============================================================#
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) #备份时间
COMM=$(which rman) #备份命令
RMANID=`ps x |grep ${BACKPATH}|grep -v grep|awk '{ print $1 }'`
#====================================================#
# 全备脚本 #
#====================================================#
rman_full=$(
cat <<-RMAN
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
allocate channel ch5 type disk;
allocate channel ch6 type disk;
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;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
RMAN
)
rman_arc=$(
cat <<-RMAN
allocate channel ch11 type disk;
allocate channel ch12 type disk;
allocate channel ch13 type disk;
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;
release channel ch11;
release channel ch12;
release channel ch13;
RMAN
)
#导数据配置(用户、表空间、权限、DG配置、字符集、pfile)
echo "create pfile='${BACKPATH}/pfile_${ALTIME}.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
spool ${BACKPATH}/DG_${ALTIME}.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;
col parameter for a30
spool ${BACKPATH}/CHARACTERSET_${ALTIME}.conf
select * from nls_database_parameters where parameter like '%CHARACTERSET%' order by 1;
spool ${BACKPATH}/DB_username_${ALTIME}.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_${ALTIME}.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_${ALTIME}.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_${ALTIME}.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;" > datainfo.sql
#====================================================#
# 执行备份 #
#====================================================#
if [[ -z ${RMANID} ]];then #无备份进程时备
if [[ -z ${1} ]];then #检查是否有输入
if [[ ! -d ${BACKPATH} ]];then #检查目录是否存在
mkdir ${BACKPATH}; #目录不存在,建目录进行零级备份
echo $(date +"%Y-%m-%d %H:%M:%S")"开始零级备份" >> /${BACKPATH}/rmanbackup_${DBTIME}.log;
$COMM target / msglog ${BACKPATH}/rmanDB_${DBTIME}.log <<EOF
run
{
$rman_full
}
EOF
sqlplus -s / as sysdba < datainfo.sql;
echo $(date +"%Y-%m-%d %H:%M:%S")"零级备份结束" >> /${BACKPATH}/rmanbackup_${DBTIME}.log;
else #目录存在,进行归档备份
echo $(date +"%Y-%m-%d %H:%M:%S")"开始归档备份" >> /${BACKPATH}/rmanbackup_${DBTIME}.log;
$COMM target / msglog ${BACKPATH}/rman_AL_$ALTIME.log <<EOF
run
{
$rman_arc
}
EOF
echo $(date +"%Y-%m-%d %H:%M:%S")"归档备份结束" >> /${BACKPATH}/rmanbackup_${DBTIME}.log;
fi
else
if [[ ${1^^} == "FULL" ]];then #输入为full时
if [[ ! -d ${BACKPATH} ]];then mkdir ${BACKPATH}; fi #目录不存在,建目录进行零级备份
echo $(date +"%Y-%m-%d %H:%M:%S")"开始零级备份" >> /${BACKPATH}/rmanbackup_${DBTIME}.log;
$COMM target / msglog ${BACKPATH}/rmanDB_${DBTIME}.log <<EOF
run
{
$rman_full
}
EOF
sqlplus -s / as sysdba < datainfo.sql;
echo $(date +"%Y-%m-%d %H:%M:%S")"零级备份结束" >> /${BACKPATH}/rmanbackup_${DBTIME}.log;
elif [[ ${1^^} == "ARC" ]];then #输入为arc时
if [[ ! -d ${BACKPATH} ]];then mkdir ${BACKPATH}; fi #目录不存在,建目录,进行归档备份
echo $(date +"%Y-%m-%d %H:%M:%S")"开始归档备份" >> /${BACKPATH}/rmanbackup_${DBTIME}.log;
$COMM target / msglog ${BACKPATH}/rman_AL_$ALTIME.log <<EOF
run
{
$rman_arc
}
EOF
echo $(date +"%Y-%m-%d %H:%M:%S")"归档备份结束" >> /${BACKPATH}/rmanbackup_${DBTIME}.log;
fi
fi
else
echo "有备份进行中";
fi
#清理历史备份目录（默认清理10天前的目录）
REDUNDANCY=10
rmdir_no=`find ${BPATH} -maxdepth 1 -type d -name "*_rman"|sort|tail -n ${REDUNDANCY}|wc -l`
if [ ${rmdir_no} -gt ${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
扫一扫
290
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
