利用RMAN检测数据库坏块的脚本

虽然我们也可以通过dbv(db file verify)工具做到对单个数据文件的坏块检测,但是直接使用RMAN的”backup validate check logical database;”结合V$DATABASE_BLOCK_CORRUPTION视图要方便地多。

如果自己搞不定可以找ASKMACLEAN专业ORACLE数据库修复团队成员帮您恢复!

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.askmac.cn & www.askmac.cn

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;

Comments

  1. maclean says

    有同学提出backup validate check logical database; 是否会对数据块做物理讹误检测 或 逻辑讹误检测的问题。 实际上关于这一点官方文档有明确的说明, 见http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmvalid.htm

    Basic Concepts of RMAN Validation

    The database prevents operations that result in unusable backup files or corrupted restored datafiles. The database automatically does the following:

    Blocks access to datafiles while they are being restored or recovered

    Permits only one restore operation for each datafile at a time

    Ensures that incremental backups are applied in the correct order

    Stores information in backup files to allow detection of corruption

    Checks a block every time it is read or written in an attempt to report a corruption as soon as it has been detected

    Checksums and Corrupt Blocks

    A corrupt block is a block that has been changed so that it differs from what Oracle Database expects to find. Block corruptions can be caused by a number of different failures including, but not limited to the following:

    Faulty disks and disk controllers

    Faulty memory

    Oracle Database software defects

    DB_BLOCK_CHECKSUM is a database initialization parameter that controls the writing of checksums for the blocks in datafiles and online redo log files in the database (not backups). If DB_BLOCK_CHECKSUM is typical, then the database computes a checksum for each block during normal operations and stores it in the header of the block before writing it to disk. When the database reads the block from disk later, it recomputes the checksum and compares it to the stored value. If the values do not match, then the block is corrupt.

    By default, the BACKUP command computes a checksum for each block and stores it in the backup. The BACKUP command ignores the values of DB_BLOCK_CHECKSUM because this initialization parameter applies to datafiles in the database, not backups.
    Physical and Logical Block Corruption

    In a physical corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid, the block contains all zeros, or the header and footer of the block do not match.

    Note:
    By default, the BACKUP command computes a checksum for each block and stores it in the backup. If you specify the NOCHECKSUM option, then RMAN does not perform a checksum of the blocks when creating the backup.

    In a logical corruption, the contents of the block are logically inconsistent. Examples of logical corruption include corruption of a row piece or index entry. If RMAN detects logical corruption, then it logs the block in the alert log and server session trace file.

    By default, RMAN does not check for logical corruption. If you specify CHECK LOGICAL on the BACKUP command, however, then RMAN tests data and index blocks for logical corruption, such as corruption of a row piece or index entry, and log them in the alert log located in the Automatic Diagnostic Repository (ADR). If you use RMAN with the following configuration when backing up or restoring files, then it detects all types of block corruption that are possible to detect:

    In the initialization parameter file of a database, set DB_BLOCK_CHECKSUM=typical so that the database calculates datafile checksums automatically (not for backups, but for datafiles in use by the database)

    Do not precede the BACKUP or RESTORE command with SET MAXCORRUPT so that RMAN does not tolerate any block corruptions

    In a BACKUP command, do not specify the NOCHECKSUM option so that RMAN calculates a checksum when writing backups

    In BACKUP and RESTORE commands, specify the CHECK LOGICAL option so that RMAN checks for logical as well as physical corruption

  2. maclean says

    RMAN> run {
    2> allocate channel d1 type disk;
    3> allocate channel d2 type disk;
    4> allocate channel d3 type disk;
    5> allocate channel d4 type disk;
    6> backup validate check logical database;
    7> }

    released channel: ORA_DISK_1
    allocated channel: d1
    channel d1: SID=14 instance=VPROD1 device type=DISK

    allocated channel: d2
    channel d2: SID=160 instance=VPROD1 device type=DISK

    allocated channel: d3
    channel d3: SID=159 instance=VPROD1 device type=DISK

    allocated channel: d4
    channel d4: SID=29 instance=VPROD1 device type=DISK

    Starting backup at 10-DEC-11
    channel d1: starting full datafile backup set
    channel d1: specifying datafile(s) in backup set
    input datafile file number=00001 name=+DATA/vprod/datafile/system.490.769483911
    input datafile file number=00007 name=+DATA/vprod/datafile/undo11.483.769484067
    channel d2: starting full datafile backup set
    channel d2: specifying datafile(s) in backup set
    input datafile file number=00006 name=+DATA/vprod/datafile/undotbs2.487.769483991
    input datafile file number=00005 name=+DATA/vprod/datafile/example.486.769484027
    channel d3: starting full datafile backup set
    channel d3: specifying datafile(s) in backup set
    input datafile file number=00002 name=+DATA/vprod/datafile/sysaux.489.769483855
    channel d4: starting full datafile backup set
    channel d4: specifying datafile(s) in backup set
    input datafile file number=00004 name=+DATA/vprod/datafile/users.488.769483967
    input datafile file number=00003 name=+DATA/vprod/datafile/undo22.485.769484051
    channel d2: backup set complete, elapsed time: 00:02:46
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    5 OK 0 33656 44242 8912531
    File Name: +DATA/vprod/datafile/example.486.769484027
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 6595
    Index 0 1148
    Other 0 2841

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    6 OK 0 1 67200 9674203
    File Name: +DATA/vprod/datafile/undotbs2.487.769483991
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 0
    Index 0 0
    Other 0 67199

    channel d2: starting full datafile backup set
    channel d2: specifying datafile(s) in backup set
    including current control file in backup set
    channel d3: backup set complete, elapsed time: 00:02:51
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    2 OK 0 25919 111362 9850434
    File Name: +DATA/vprod/datafile/sysaux.489.769483855
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 28527
    Index 0 24857
    Other 0 32057

    channel d3: starting full datafile backup set
    channel d3: specifying datafile(s) in backup set
    including current SPFILE in backup set
    channel d2: backup set complete, elapsed time: 00:00:04
    List of Control File and SPFILE
    ===============================
    File Type Status Blocks Failing Blocks Examined
    ———— —— ————– —————
    Control File OK 0 1136
    channel d3: backup set complete, elapsed time: 00:00:00
    List of Control File and SPFILE
    ===============================
    File Type Status Blocks Failing Blocks Examined
    ———— —— ————– —————
    SPFILE OK 0 2
    channel d1: backup set complete, elapsed time: 00:03:02
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    1 OK 0 14338 93480 9850357
    File Name: +DATA/vprod/datafile/system.490.769483911
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 62319
    Index 0 13177
    Other 0 3606

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    7 OK 0 37758 38400 9849139
    File Name: +DATA/vprod/datafile/undo11.483.769484067
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 0
    Index 0 0
    Other 0 642

    channel d4: backup set complete, elapsed time: 00:03:10
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    3 OK 0 38249 38400 9850434
    File Name: +DATA/vprod/datafile/undo22.485.769484051
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 0
    Index 0 0
    Other 0 151

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    4 OK 0 6388 71873 9849137
    File Name: +DATA/vprod/datafile/users.488.769483967
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 50510
    Index 0 13091
    Other 0 1851

    Finished backup at 10-DEC-11
    released channel: d1
    released channel: d2
    released channel: d3
    released channel: d4

  3. chengwill says

    Hello

    I have trial the script and got the right result.

    So I think this is an excellent effective measure!

    BTW:Thanks the Maclean provide it to us!

    There are some backup logs from the oracle 11GR1 RAC test-bed environment:

    RMAN> backup validate check logical database;
    Starting backup at 06-FEB-12
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=1326 instance=ssp1 device type=DISK
    allocated channel: ORA_DISK_2
    channel ORA_DISK_2: SID=1300 instance=ssp2 device type=DISK
    file 13 is excluded from whole database backup
    file 14 is excluded from whole database backup
    file 15 is excluded from whole database backup
    file 16 is excluded from whole database backup
    file 17 is excluded from whole database backup
    file 19 is excluded from whole database backup
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00020 name=/dev/raw/raw36
    input datafile file number=00022 name=/dev/raw/raw38

    input datafile file number=00037 name=/dev/raw/raw45
    input datafile file number=00044 name=/dev/raw/raw19
    input datafile file number=00010 name=/dev/raw/raw26
    input datafile file number=00012 name=/dev/raw/raw28
    channel ORA_DISK_2: starting full datafile backup set
    channel ORA_DISK_2: specifying datafile(s) in backup set
    input datafile file number=00018 name=/dev/raw/raw34
    input datafile file number=00021 name=/dev/raw/raw37

    input datafile file number=00011 name=/dev/raw/raw27
    channel ORA_DISK_1: backup set complete, elapsed time: 00:33:56
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    3 OK 0 1 256000 285534307
    File Name: /dev/raw/raw18
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 0
    Index 0 0
    Other 0 255999

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    —- —— ————– ———— ————— ———-
    43 OK 0 255276 256000 285347887
    File Name: /dev/raw/raw7
    Block Type Blocks Failing Blocks Processed
    ———- ————– —————-
    Data 0 466
    Index 0 70
    Other 0 188

    Finished backup at 06-FEB-12

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号