oracle rman 检查坏块,Oracle中使用RMAN来检验坏块

RMAN即recovery manager可以通过在执行的备份任务中，仅仅验证数据文件中的数据块而不生成实际的备份，这样做的目的是纯粹为了了解数据文件中是否有坏块。

RMAN在这个环境中：

验证数据文件是否存在

数据文件位于正确的存放位置

在RMAN尝试检验这些数据文件时，将逻辑或物理讹误的块找出来并临时记录到V$DATABASE_BLOCK_CORRUPTION视图中

具体的使用为

检验整个数据库

backup check logical validate database;

检验个别数据文件

backup check logical validate datafile 1;

检验多个指定数据文件

backup check logical validate datafile 1, 2;

Script:

1) $ rman target / nocatalog

2) 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;

}

3) select * from V$DATABASE_BLOCK_CORRUPTION ;

REM www.oracledatabase12g.com & www.askmaclean.com

4) If V$DATABASE_BLOCK_CORRUPTION contains rows please run this query to

find the objects that contains the corrupted blocks:

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,

null 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,

'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#;

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents

WHERE file_id = &fileid

and &blockid between block_id AND block_id + blocks - 1;