RMAN备份整理

备份脚本整理：

每小时归档日志：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';