如何使用 RMAN 识别数据库中损坏的对象
发布网友
发布时间：2022-04-30 10:11
共2个回答
懂视网
时间：2022-04-30 14:32
利用数据字典表查询是否有坏块
SQL> desc v$database_block_corruption Name Null? Type ----------------------------------------- -------- ---------------------------- FILE# NUMBER BLOCK# NUMBER BLOCKS NUMBER CORRUPTION_CHANGE# NUMBER CORRUPTION_TYPE VARCHAR2(9) SQL> select * from v$database_block_corruption; no rows selected
如果存在坏块可使用以下脚本查询：
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;
参考：http://www.cnblogs.com/macleanoracle/archive/2013/03/19/2968101.html
[转]RMAN检测数据库坏块
标签：
热心网友
时间：2022-04-30 11:40
执行下面的 RMAN 命令，使所有的坏块信息被记录在 v$database_block_corruption 视图中:
RMAN> backup validate check logical database;
注意：
这个命令只是检查数据库的坏块，而不会真正进行备份。从 11g 开始可以省略 backup 子句，而直接使用命令"validate check logical database"。
如果由于缺失文件导致命令失败，可以增加 'SKIP INACCESSIBLE' 子句来避免这个问题。
为了加快检查速度，可以设置 PARALLELISM 指定多个通道:
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;
}
输出
坏块信息会被记录在视图 V$DATABASE_BLOCK_CORRUPTION 中。11g RMAN 会生成一个 trace 文件，详细描述坏块信息：
RMAN VALIDATE 屏幕输出：
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 501 640 1950088
File Name: /oracle/dbs/users.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 9 9
Index 0 0
Other 0 130
validate found one or more corrupt blocks
See trace file /oracle/log/diag/rdbms/orcl/orcl/trace/orcl_ora_28424.trc for details
Finished validate at <Date>
Trace 文件输出坏块信息，这个例子描述了 2 个坏块，一个物理坏块（file 6 block 9）和一个逻辑坏块（file 6 block 10）：
Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found ring validation
Data in bad block:
type: 16 format: 2 rdba: 0x01000009
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000010ff
check value in block header: 0xb4e0
computed block checksum: 0xa800
Reread of blocknum=9, file=/oracle/dbs/users.dbf found same corrupt data
Block Checking: DBA = 25165834, Block Type = KTB-managed data block
data header at 0x2b2deb49e07c
kdbchk: fsbo(144) wrong, (hsz 78)
Error backing up file 6, block 10: logical corruption
坏块信息记录在视图 V$DATABASE_BLOCK_CORRUPTION 中:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
6 10 1 8183236781662 LOGICAL
6 42 1 0 FRACTURED
6 34 2 0 CHECKSUM
6 50 1 8183236781952 LOGICAL
6 26 4 0 FRACTURED
5 rows selected.
注意：
•CHECK LOGICAL 选项既会检查
