Go Rman detects database bad blocks

Backup validate check logical database;

SELECT * from V$database_block_corruption;

Rman>Backup validate check logical database; Starting backup at Geneva-sep- theAllocated Channel:ora_disk_1channel Ora_disk_1:sid=141Device type=diskallocated Channel:ora_disk_2channel Ora_disk_2:sid=9Device type=diskchannel ora_disk_1:starting full datafile backupSetChannel ora_disk_1:specifying datafile (s)inchBackupSetinput datafile file number=00001name=/u02/app/oradata/orcl/system01.dbfinput datafile File number=00006name=/u02/app/oradata/orcl/rlst01.dbfinput datafile File number=00005name=/u02/app/oradata/orcl/mssm01.dbfchannel ora_disk_2:starting full datafile backupSetChannel ora_disk_2:specifying datafile (s)inchBackupSetinput datafile file number=00002name=/u02/app/oradata/orcl/sysaux01.dbfinput datafile File number=00003name=/u02/app/oradata/orcl/undotbs01.dbfinput datafile File number=00004name=/u02/app/oradata/orcl/Users01.dbfchannel ora_disk_2:backupSetComplete, Elapsed time:xx:xx: -List of Datafiles=================File Status Marked corrupt Empty Blocks Blocks examined high SCN---- ------ -------------- ------------ --------------- ----------2Ok0              17775        84503           3456527File Name:/u02/app/oradata/orcl/sysaux01.dbf Block Type Blocks failing Blocks processed---------- -------------- ----------------Data0              20940Index0              17430 Other0              28335File Status Marked corrupt Empty Blocks Blocks examined high SCN---- ------ -------------- ------------ --------------- ----------3Ok0              1            26893           3456665File Name:/u02/app/oradata/orcl/undotbs01.dbf Block Type Blocks failing Blocks processed---------- -------------- ----------------Data0              0Index0              0 Other0              26879File Status Marked corrupt Empty Blocks Blocks examined high SCN---- ------ -------------- ------------ --------------- ----------4Ok0               -           643             2732847File Name:/u02/app/oradata/orcl/users01.dbf Block Type Blocks failing Blocks processed---------- -------------- ----------------Data0               -Index0              2 Other0              576Channel ora_disk_2:starting full datafile backupSetChannel ora_disk_2:specifying datafile (s)inchBackupSetincluding current control fileinchBackupSetChannel Ora_disk_2:backupSetComplete, Elapsed time:xx:xx: onList of Control File and SPFILE===============================File Type Status Blocks failing Blocks examined------------ ------ -------------- ---------------Control File OK0              614Channel ora_disk_2:starting full datafile backupSetChannel ora_disk_2:specifying datafile (s)inchBackupSetincluding current SPFILEinchBackupSetChannel Ora_disk_2:backupSetComplete, Elapsed time:xx:xx:xxList of Control File and SPFILE===============================File Type Status Blocks failing Blocks examined------------ ------ -------------- ---------------SPFILE OK0              2Channel Ora_disk_1:backupSetComplete, Elapsed time:xx:xx: theList of Datafiles=================File Status Marked corrupt Empty Blocks Blocks examined high SCN---- ------ -------------- ------------ --------------- ----------1Ok0              13138        88333           3456665File Name:/u02/app/oradata/orcl/system01.dbf Block Type Blocks failing Blocks processed---------- -------------- ----------------Data0              59322Index0              12665 Other0              3195File Status Marked corrupt Empty Blocks Blocks examined high SCN---- ------ -------------- ------------ --------------- ----------5Ok0              12670        12800           1408151File Name:/u02/app/oradata/orcl/mssm01.dbf Block Type Blocks failing Blocks processed---------- -------------- ----------------Data0              2Index0              0 Other0               -File Status Marked corrupt Empty Blocks Blocks examined high SCN---- ------ -------------- ------------ --------------- ----------6Ok0              22441        25600           2098889File Name:/u02/app/oradata/orcl/rlst01.dbf Block Type Blocks failing Blocks processed---------- -------------- ----------------Data0              2739Index0              158 Other0              262finished backup at Geneva-sep- the

Querying for bad blocks with a data dictionary table

sql> desc v$database_block_corruption Name                                      Null?      ----------------------------------------------------------------------------- file# number                                              block#                                             Number BLOCKS number                                             corruption_change# number                                 corruption_type                                    VARCHAR2 (9) SQL  Select from v$database_block_corruption;no rows selected

If a bad block is present, use the following script query:

SELECT E.owner, E.segment_type, E.segment_name, E.partition_name, c.file#, Greatest (E.BL ock_id, c.block#) corr_start_block#, least (e.block_id+ E.blocks-1, c.block# + c.blocks-1) corr_end_block#, least (e.block_id+ E.blocks-1, c.block# + c.blocks-1) -Greatest (e.block_id, c.block#)+1blocks_corrupted,NULLdescription from Dba_extents E, v$database_block_corruption c WHERE e.file_id=c.file# and e.block_id<= c.block# + c.blocks-1and e.block_id+ E.blocks-1>=C.block#unionselect S.owner, S.segment_type, S.segment_name, S.partition_name, c.file#, Header_block corr_start_block#, Header_block corr_end_block#,1blocks_corrupted,'Segment Header'description from dba_segments s, v$database_block_corruption c WHERE s.header_file=c.file# and S.header_block between c.block# and c.block#+ C.blocks-1UnionselectNULLowner,NULLSegment_type,NULLSegment_name,NULLPartition_name, c.file#, Greatest (f.block_id, c.block#) corr_start_block#, least (f.block_id+ F.blocks-1, c.block# + c.blocks-1) corr_end_block#, least (f.block_id+ F.blocks-1, c.block# + c.blocks-1) -Greatest (f.block_id, c.block#)+1blocks_corrupted,'Free Block'description from Dba_free_space F, v$database_block_corruption c WHERE f.file_id=c.file# and f.block_id<= c.block# + c.blocks-1and f.block_id+ F.blocks-1>=c.block# ORDER by file#, corr_start_block#;

= &1;

Reference: http://www.cnblogs.com/macleanoracle/archive/2013/03/19/2968101.html

Go Rman detects database bad blocks