备份与恢复系列八 续 RMAN备份的确认

作为一名数据库管理员，我们在设计了一个完整的备份策略后，我们关心的是这些备份是否有效。养兵千日用兵一时，在需要恢复的时候如果当初的备份不能使用那就惨了。
我们通过RMAN的常用命令 LIST,REPORT,SQL*PLUS,CHROSSCHECK 来确认备份是否有效。下面分别看看这些命令的使用方法。


1，用 LIST 确认备份
list命令显示的是我们已经得到了什么
list     incarnation | backup | copy
过滤   database | tablespace | datafile | controfile | archivelog
数据文件

list backup;
list backup of database;
list backup of tablespace 'SYSTEM','TOOLS';
list backup of datafile 3,4,5;
list backup of datafile '/oradata/PRACTICE/system01.dbf';

list copy;
list copy of database;
list copy of  tablespace 'SYSTEM','TOOLS';

list backup of controlfile;

list backup of archivelog all;
list bakcup of archivelog like '%oradata%';
list backup of archivelog from time 'SYSDATE - 14';
list bakuup of archivelog until time 'SYSDATE - 7';
list bakcup of archivelog from time 'SYSDATE - 14' until time 'SYSDATE - 7';
list backup of archivelog from SCN 100000;
list backup of archivelog until SCN 1100000;
list backup of archivelog from SCN 100000 until SCN 1100000;
list backup of archivelog from logseq 10;
list backup of archivelog until logseq 20;
list backup of archivelog from logseq 10 until logseq 20;

list backup completed after 'sysdate - 14';
list backup completed before 'sysdate - 7';
list backup completed between 'sysdate - 14' and 'sysdate - 7';
list backup tag='whole_inc0';
list backup recoverable;
list backup of tablespace 'RBS' completed  after 'last_day(add_months(sysdate,-1))+1' tag = whole_inc0;

2，用report确认备份
report显示的是我们需要什么。
两天之内还没有备份的数据库内容
report need backup days 2 database;

需要3个增量备份的
report need backup incremental 3 database;

需要备份的数据块中又nologing设置
report unrecoverable;

冗余条件之外的备份，不再需要的备份，可以删除的备份
report obsolete

report schema;
report schema at time 'sysdate - 2';
 report schema at time "to_date('3/30/2014','mm/dd/yyyy')";
report schema at logseq 131;

3，用sql查询备份集与备份片
connect sys/oracle@practice
select recid,set_count,
     decode(backup_type,'D','FULL','I','INCREMENTAL','L','LOGS') type,
     incremental_level ILevel,
     completion_time Completed
 from v$backup_set;

connect rmanadmin/rmanadmin
select recid,set_count,
     decode(backup_type,'D','FULL','I','INCREMENTAL','L','LOGS') type,
     incremental_level ILevel,
     completion_time Completed
 from rc_backup_set
where db_key=
     (select max(db_key) from rc_database where name='PRACTICE');

数据文件
connect sys/oracle@practice as sysdba
select file#, completion_time time,
     checkpoint_change# change#, name
from v$datafile_copy;

select file#, completion_time time,
     checkpoint_change# change#, set_count
from v$backup_datafile;

connect rmanadmin/rmanadmin
select file#, completion_time time,
     checkpoint_change# change#, name, cdf_key key,
     decode(Status,'A','AVAIL','U','UNAVAIL','D','DELETED') Status
from rc_datafile_copy
where db_name = 'PRACTICE';

select file#, completion_time time, checkpoint_change# change#, bdf_key key,
     set_count,
     decode(Status,'A','AVAIL','U','UNAVAIL','D','DELETED') Status
from rc_backup_datafile
where db_name = 'PRACTICE';

备份控制文件
v$controlfile_copy, v$backup_controlfile, re_controlfile_copy, rc_backup_controlfile
备份归档日志
检查所有备份的归档日志文件以及每个文件中的出现次数
connect sys/oracle@practice as sysdba
select sequence#,count(*)
from v$backup_redolog
group by sequence#;

connect rmanadmin/rmanadmin
select sequence#,count(*)
from rc_backup_redolog
where db_name = 'PRACTICE'
and status = 'A'
group by sequence#;

4 交叉检查crosscheck备份
 crosscheck backup;

如果磁带备份的产品级系统上必须谨慎使用，可以针对性的检验
crosscechk backup of datafile 1;
crosscheck backup completed between 'sysdate - 7' and 'sysdate';
crosscheck backup tag whole_inc0;

5,验证备份
validate选项具有模拟还原的能力
1）还原验证
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database validate;
restore archivelog all validate;
}

2) 验证备份集

list backupset summary;
------- -- -- - ----------- --------------- ------- ------- ---------- ---
114     B  A  A DISK        29-MAR-14       1       1       NO         TAG20140329T083414
115     B  A  A DISK        29-MAR-14       1       1       NO         TAG20140329T083414
116     B  A  A DISK        29-MAR-14       1       1       NO         TAG20140329T083414

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
validate backupset 114,115;
}

6,catalog的清理
1 找到孤立的备份
2 删除孤立的备份
3 删除陈旧的备份
4 查找过期的备份
5 删除过期的备份

1 找到孤立的备份
1，冗余范围
查找21天前生成的备份
report obsolete redundancy 1 until time 'sysdate - 21' device type disk;

2 删除孤立的备份
list script names;

RMAN> report obsolete redundancy 2 device type disk;
RMAN> change archivelog 45,46,47 delete;
RMAN> change backupset 145,146 delete;


3 删除过期的备份
RMAN> delete expired backup;