[轉]RMAN檢測資料庫壞塊

標籤：

 

backup validate check logical database;

select * from v$database_block_corruption;

 

RMAN> backup validate check logical database;Starting backup at 02-SEP-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=141 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=9 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u02/app/oradata/ORCL/system01.dbfinput datafile file number=00006 name=/u02/app/oradata/ORCL/rlst01.dbfinput datafile file number=00005 name=/u02/app/oradata/ORCL/mssm01.dbfchannel ORA_DISK_2: starting full datafile backup setchannel ORA_DISK_2: specifying datafile(s) in backup setinput datafile file number=00002 name=/u02/app/oradata/ORCL/sysaux01.dbfinput datafile file number=00003 name=/u02/app/oradata/ORCL/undotbs01.dbfinput datafile file number=00004 name=/u02/app/oradata/ORCL/users01.dbfchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:25List of Datafiles=================File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------2    OK     0              17775        84503           3456527     File Name: /u02/app/oradata/ORCL/sysaux01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       0              20940             Index      0              17430             Other      0              28335           File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------3    OK     0              1            26893           3456665     File Name: /u02/app/oradata/ORCL/undotbs01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       0              0                 Index      0              0                 Other      0              26879           File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------4    OK     0              14           643             2732847     File Name: /u02/app/oradata/ORCL/users01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       0              48                Index      0              2                 Other      0              576             channel ORA_DISK_2: starting full datafile backup setchannel ORA_DISK_2: specifying datafile(s) in backup setincluding current control file in backup setchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:01List of Control File and SPFILE===============================File Type    Status Blocks Failing Blocks Examined------------ ------ -------------- ---------------Control File OK     0              614             channel ORA_DISK_2: starting full datafile backup setchannel ORA_DISK_2: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:00List of Control File and SPFILE===============================File Type    Status Blocks Failing Blocks Examined------------ ------ -------------- ---------------SPFILE       OK     0              2               channel ORA_DISK_1: backup set complete, elapsed time: 00:00:42List of Datafiles=================File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------1    OK     0              13138        88333           3456665     File Name: /u02/app/oradata/ORCL/system01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       0              59322             Index      0              12665             Other      0              3195            File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------5    OK     0              12670        12800           1408151     File Name: /u02/app/oradata/ORCL/mssm01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       0              2                 Index      0              0                 Other      0              128             File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------6    OK     0              22441        25600           2098889     File Name: /u02/app/oradata/ORCL/rlst01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       0              2739              Index      0              158               Other      0              262             Finished backup at 02-SEP-15

 

利用資料字典表查詢是否有壞塊

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#UNIONSELECT 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 - 1UNIONSELECT 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_nameFROM dba_extentsWHERE file_id = &fileidand &blockid between block_id AND block_id + blocks - 1;

 

 

參考：http://www.cnblogs.com/macleanoracle/archive/2013/03/19/2968101.html

[轉]RMAN檢測資料庫壞塊