备份脚本整理:
每小时归档日志:arch.log
每天增量备份日志:level1.log
每周全备份:level0.log
arch.script
run
{
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
sql 'alter system archive log current';
backup filesperset 10 MAXSETSIZE 10G format 'arch_%t_%s_%c_%p.arc'
skip inaccessible
archivelog all delete input;
backup current controlfile format 'control_%t_%s_%c_%p.ctl';
crosscheck archivelog all;
release channel t1;
# allocate channel d1 type disk;
# copy current controlfile to '/db/backup/control/control.ctl';
# release channel d1;
}
arch.sh
CMDFILE=/db/backup/arch.script
LOGFILE=/db/backup/arch.log
su - oracle -c "rman target rman/********* cmdfile $CMDFILE msglog $LOGFILE"
level1.script
run{
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE section size 100G FORMAT 'df_%t_%s_%c_%p' DATABASE;
# sql 'alter system archive log current';
crosscheck archivelog all;
backup filesperset 10 MAXSETSIZE 10G format 'arch_%t_%s_%c_%p.arc'
skip inaccessible
archivelog all delete input;
backup current controlfile format 'control_%t_%s_%c_%p.ctl';
release channel t1;
release channel t2;
# allocate channel d1 type disk;
# copy current controlfile to '/db/backup/control/control.ctl';
# release channel d1;
}
level1.sh
CMDFILE=/db/backup/level1.script
LOGFILE=/db/backup/level1.log
su - oracle -c "rman target rman/Rman2014 cmdfile $CMDFILE msglog $LOGFILE"
level0.script
run
{
configure retention policy to recovery window of 90 days;
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup incremental level 0 section size 100G format 'df_%t_%s_%c_%p' database;
# sql 'alter system archive log current';
backup filesperset 10 MAXSETSIZE 10G format 'arch_%t_%s_%c_%p.arc' skip inaccessible archivelog all delete input;
backup current controlfile format 'control_%t_%s_%c_%p.ctl';
crosscheck backup;
crosscheck archivelog all;
# delete noprompt expired backup;
# delete noprompt expired archivelog all;
# delete noprompt obsolete;
release channel t1;
release channel t2;
# allocate channel d1 type disk;
# copy current controlfile to '/db/backup/control/control.ctl';
# release channel d1;
}
level0.sh
CMDFILE=/db/backup/level0.script
LOGFILE=/db/backup/level0.log
su - oracle -c "rman target rman/Rman2014 cmdfile $CMDFILE msglog $LOGFILE"
执行策略
30 03 * * 0 /db/backup/level0.sh
30 03 * * 1,2,3,4,5,6 /db/backup/level1.sh
00 01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23 * * * /db/backup/arch.sh
其他资料
/** -- 查看每天产生归档日志的数据量 */
select trunc(completion_time) as ARC_DATE,
count(*) as COUNT,
round((sum(blocks * block_size) / 1024 / 1024/1024), 2) as ARC_GB
from v$archived_log
group by trunc(completion_time)
order by trunc(completion_time);
/** 统计某个时间段的归档日志大小 */
select a.first_time,blocks*block_size/1024/1024 MB, blocks,block_size from v$archived_log a where a.first_time >=to_date('2019/07/06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.first_time<=to_date('2019/07/07 23:59:59','yyyy-mm-dd hh24:mi:ss')
order by a.first_time DESC;
select s.status as 备份状态,
trunc((s.END_TIME-s.START_TIME)*24*60,0) "备份用时(分钟)",
to_char(s.START_TIME, 'yyyy-mm-dd hh24:mi:ss') as 开始备份时间,
to_char(s.END_TIME, 'yyyy-mm-dd hh24:mi:ss') as 结束备份时间,
s.OPERATION as 命令,
trunc(s.INPUT_BYTES/1024/1024,2) as "INPUT/M",
trunc(s.OUTPUT_BYTES/1024/1024,2) as "OUTPUT/M",
s.OBJECT_TYPE as "对象类型",
s.MBYTES_PROCESSED as 百分比,
s.OUTPUT_DEVICE_TYPE as "设备类型"
from v$rman_status s
where to_char(s.START_TIME, 'yyyy-mm-dd hh24:mi:ss') > to_char(to_date('20190814','yyyymmdd'),'yyyy-mm-dd hh24:mi:ss')
order by s.START_TIME desc ;
SELECT sid, spid, client_info
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND client_info LIKE '%rman%';
SELECT t.COMMAND_ID as "备份名",
t.STATUS as "状态",
to_char(t.START_TIME, 'yyyy-mm-dd hh24:mi:ss') as 开始备份时间,
to_char(t.END_TIME, 'yyyy-mm-dd hh24:mi:ss') as 结束备份时间,
t.TIME_TAKEN_DISPLAY as "所用时间",
t.INPUT_TYPE as "类型",
t.OUTPUT_DEVICE_TYPE as "输出设备",
t.INPUT_BYTES_DISPLAY as "输入大小",
t.OUTPUT_BYTES_DISPLAY as "输出大小",
t.OUTPUT_BYTES_PER_SEC_DISPLAY as "输出速率(每秒)"
FROM V$RMAN_BACKUP_JOB_DETAILS t
where START_TIME >= trunc(sysdate) - 7
ORDER BY START_TIME DESC;
报错解决:
问题分析:
由于是数据库是RACA集群,因此控制文件不能放在本地,应该放在共享目录或者ASM上。
解决办法:
将控制文件备份到ASM上,执行命令如下:
su - oracle
rman target/
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATADG/CNOOCMDM/CONTROLFILE/snapcf_ccdd.f';