RMAN验证数据文件及备份文件的有效性

往往DBA在检查备份时，只是查看备份日志，是否成功完成备份，未对备份文件进行验证，等真正用到备份文件时，发现备份文件corruption就傻眼了。所以在此做一个如何通过RMAN工具检查验证数据文件和备份文件的小实验，希望能够能够对DBA朋友们有所帮助。

环境：linux 单实例 oracle11g

实验1：对所以数据文件进行验证

RMAN> validate database;

Starting validate at 02-JUN-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=49 device type=DISK

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00001 name=/11g/app/oracle/oradata/enmoedu/system01.dbf

input datafile file number=00002 name=/11g/app/oracle/oradata/enmoedu/sysaux01.dbf

input datafile file number=00005 name=/11g/app/oracle/oradata/enmoedu/example01.dbf

input datafile file number=00003 name=/11g/app/oracle/oradata/enmoedu/undotbs01.dbf

input datafile file number=00006 name=/11g/app/oracle/oradata/enmoedu/pitr.dbf

input datafile file number=00007 name=/11g/app/oracle/oradata/enmoedu/pitr_ind.dbf

input datafile file number=00010 name=/11g/app/oracle/oradata/enmoedu/ts_users_1.dbf

input datafile file number=00004 name=/11g/app/oracle/oradata/enmoedu/users01.dbf

input datafile file number=00009 name=/11g/app/oracle/oradata/enmoedu/ts_users.dbf

input datafile file number=00011 name=/11g/app/oracle/oradata/enmoedu/ts_users1.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:15

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

1 OK 0 15404 94760 1582467

File Name: /11g/app/oracle/oradata/enmoedu/system01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 62940

Index 0 12776

Other 0 3600

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

2 OK 0 24676 83202 1582507

File Name: /11g/app/oracle/oradata/enmoedu/sysaux01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 15755

Index 0 12040

Other 0 30729

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

3 OK 0 1 14080 1582507

File Name: /11g/app/oracle/oradata/enmoedu/undotbs01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 0

Index 0 0

Other 0 14079

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4 OK 0 10 668 1554691

File Name: /11g/app/oracle/oradata/enmoedu/users01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 136

Index 0 37

Other 0 457

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

5 OK 0 33661 44241 1287313

File Name: /11g/app/oracle/oradata/enmoedu/example01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 6590

Index 0 1148

Other 0 2841

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

6 OK 0 57 1281 1578345

File Name: /11g/app/oracle/oradata/enmoedu/pitr.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 5

Index 0 0

Other 0 1218

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

7 OK 0 61 1281 1225471

File Name: /11g/app/oracle/oradata/enmoedu/pitr_ind.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 0

Index 0 1

Other 0 1218

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

9 OK 0 1 128 1531613

File Name: /11g/app/oracle/oradata/enmoedu/ts_users.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 110

Index 0 0

Other 0 17

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

10 OK 0 55 1280 1529985

File Name: /11g/app/oracle/oradata/enmoedu/ts_users_1.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 1072

Index 0 0

Other 0 153

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

11 OK 0 7 13 1531084

File Name: /11g/app/oracle/oradata/enmoedu/ts_users1.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 0

Index 0 0

Other 0 6

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

including current control file for validation

including current SPFILE in backup set

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Control File and SPFILE

===============================

File Type Status Blocks Failing Blocks Examined

------------ ------ -------------- ---------------

SPFILE OK 0 2

Control File OK 0 622

Finished validate at 02-JUN-14

实验2：对某个数据文件进行验证，此处对4号文件进行验证

RMAN> validate datafile 4;

Starting validate at 02-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/11g/app/oracle/oradata/enmoedu/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4 OK 0 10 668 1554691

File Name: /11g/app/oracle/oradata/enmoedu/users01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 136

Index 0 37

Other 0 457

Finished validate at 02-JUN-14

实验3 ：对某个备份集进行验证，此处对54号备份集进行验证

RMAN> validate backupset 54;

Starting validate at 02-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set

channel ORA_DISK_1: reading from backup piece /11g/app/backup/ENMOEDU/autobackup/2014_06_02/o1_mf_s_849214835_9rrxcmdr_.bkp

channel ORA_DISK_1: piece handle=/11g/app/backup/ENMOEDU/autobackup/2014_06_02/o1_mf_s_849214835_9rrxcmdr_.bkp tag=TAG20140602T210035

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

Finished validate at 02-JUN-14

实验4：对某个数据块进行验证，此处对4号数据文件，块100进行验证

（11g才开始有的新特性）

RMAN> validate datafile 4 block 100;

Starting validate at 02-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/11g/app/oracle/oradata/enmoedu/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4 OK 0 0 1 19117

File Name: /11g/app/oracle/oradata/enmoedu/users01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 0

Index 0 0

Other 0 1

Finished validate at 02-JUN-14

实验5：并行对数据文件进行验证，此处对1号数据文件进行验证，打开两个通道并且设置第个通道300M

RMAN> run

2> {

3> allocate channel a1 device type disk;

4> allocate channel a2 device type disk;

5> validate datafile 1 section size 300m;

6> }

allocated channel: a1

channel a1: SID=49 device type=DISK

allocated channel: a2

channel a2: SID=60 device type=DISK

Starting validate at 02-JUN-14

channel a1: starting validation of datafile

channel a1: specifying datafile(s) for validation

input datafile file number=00001 name=/11g/app/oracle/oradata/enmoedu/system01.dbf

validating blocks 1 through 38400

channel a2: starting validation of datafile

channel a2: specifying datafile(s) for validation

including current SPFILE in backup set

channel a2: validation complete, elapsed time: 00:00:01

List of Control File and SPFILE

===============================

File Type Status Blocks Failing Blocks Examined

------------ ------ -------------- ---------------

SPFILE OK 0 2

channel a2: starting validation of datafile

channel a2: specifying datafile(s) for validation

input datafile file number=00001 name=/11g/app/oracle/oradata/enmoedu/system01.dbf

validating blocks 38401 through 76800

channel a1: validation complete, elapsed time: 00:00:02

channel a1: starting validation of datafile

channel a1: specifying datafile(s) for validation

input datafile file number=00001 name=/11g/app/oracle/oradata/enmoedu/system01.dbf

validating blocks 76801 through 94720

channel a1: validation complete, elapsed time: 00:00:01

channel a1: starting validation of datafile

channel a1: specifying datafile(s) for validation

including current control file for validation

channel a2: validation complete, elapsed time: 00:00:02

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

1 OK 0 15242 94758 1592880

File Name: /11g/app/oracle/oradata/enmoedu/system01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 63073

Index 0 12805

Other 0 3598

channel a1: validation complete, elapsed time: 00:00:01

List of Control File and SPFILE

===============================

File Type Status Blocks Failing Blocks Examined

------------ ------ -------------- ---------------

Control File OK 0 622

Finished validate at 02-JUN-14

released channel: a1

released channel: a2

实验6：通过backup … validate进行备份验证，可以验证数据块是否存在corruption。并且确认数据文件的路径是否正确。（1.physic：只检验 physic corruption 2.logical：在physic 基础上还检查logical corruption），后面可以加archivelog all子句，但一定要有archivelog ，否则会报错。如果数据库较大，检查的时间会较长。

Physic:

RMAN> backup validate database archivelog all;

Starting backup at 02-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set