We can use backup validate command to check the database for any physical / logical block corruptions.
Step 1:
1) Connect to RMAN
2) backup validate check logical database;
3) We can omit backup keyword from Oracle 11g onward and the above command will not take the backup of the database. It only check for any logical / physical corruptions in the database.
4) To complete the command faster configure number of channels its depends on number of CPUs as well as database edition.
RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;
OR
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}
5) If any of the datafiles are missing then use clause SKIP INACCESSIBLE in the above command to get succeed.
6) The CHECK LOGICAL option checks for both PHYSICAL and LOGICAL Block corruptions.
7) For a single or specific datafiles use
check logical validate datafile 1;
8) Monitoring Validate command progress
select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork;
9) In 11g RMAN generates a trace file with the details of the corruption description
10) From 11g, the validation of a single datafile can be made parallel by using the section clause. RMAN divides the file into sections and processes each file section in parallel. The following example divides the datafile 5 into 1 gb sections when multiple channels are configured or allocated and each section is run in parallel
backup validate check logical datafile 5 SECTION SIZE 1024M;
11) From 11g onward a range of blocks can be checked within a datafile using the BLOCK TO clause. The following command will check blocks from 5 to 20 of datafile 1:
validate check logical datafile 1 BLOCK 5 TO 20;
Step 2: Identify the corrupt segments
$ sqlplus / as sysdba
set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_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#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||' 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 - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_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#) + 1 blocks_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 - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
An output example is:
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_ FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- ----------------- --------------- ---------------- -------------
SCOTT TABLE EMP 6 10 10 1
SCOTT TABLE PARTITION ORDER ORDER_JAN 6 26 28 3
6 29 29 1 Free Block
SCOTT TABLE BONUS 6 34 34 1
6 35 35 1 Free Block
SCOTT TABLE DEPT 6 42 42 1 Segment Header
SCOTT TABLE INVOICE 6 50 50 1
