Block corrupt 的处理

最新推荐文章于 2021-08-04 09:46:16 发布

cuichenkai7892

最新推荐文章于 2021-08-04 09:46:16 发布

阅读量350

点赞数

文章标签：数据库操作系统 awk

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 Block corrupt 的处理

一般情况下，当出现 block corrupt的时候，会伴随着 ORA-1578 / ORA-26040 ora-600 等错误，而当出现 block corrupt 的时候，我们就需要去定位那个 block，那个 file id，哪个 object 受到了影响，那么，怎么定位呢？

可以使用 RMAN/DBV/ANALYZE

1、RMAN - Identify Datafile Block Corruptions

To identify both Physical and Logical Block Corruptions use the "CHECK LOGICAL" option. The next command checks the complete database for both corruptions without actually doing a backup:

$ rman target /

RMAN> backup check logical validate database;

The next command checks the complete database for both corruptions in a backup:

$ rman target /

RMAN> backup check logical database

Chek the view V$DATABASE_BLOCK_CORRUPTION to identify the block corruptions detected by RMAN.

Use Note 472231.1 (section "Step 2: Identify the corrupt segments") to identify all the Corrupted Objects in the Database reported by RMAN.

The above command can use PARALLELISM using multiple channels to make the validation faster. See Note 472231.1 for examples of PARALLELISM.

By Default RMAN backups (without the CHECK LOGICAL option) only detect Physical Block Corruptions.

In 10g and when using Locally Managed Tablespaces (LMT) RMAN may not check for blocks that belong to free extents (Blocks in extents shown by dba_free_space).

Identify the corrupt segments

The next query can be run to map each block to a segment in the database. It will map each block from v$database_block_corruption to either a segment or if the block is free.

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

Metalink：ID 819533.1

How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY –讲述了如何根据 v$database_block_corruption 去定位 object

Metalink：ID 836658.1

Identify the corruption extension using RMAN/DBV/ANALYZE etc

Metalink：ID 472231.1

How to identify all the Corrupted Objects in the Database reported by RMAN – 这个也比较详细得专题介绍了 rman 去定位 block corrupt的用法

2、DBVerify - Identify Datafile Block Corruptions

DBVERIFY identify Physical and Logical Intra Block Corruptions by default. Dbverify cannot be run for the whole database in a single command. It does not need a database connection either:

dbv file= blocksize=

dbv 还有一些参数，可参考：

Options:

Keyword Description Meaning

--------- ------------------ -----------------

FILE File to Verify This is the name of the file to verify.

See "Limitations" below if your datafile

name has no suffix.

START Start Block This is the first datablock to check in

the file. This defaults to the first

block in the file and need only be

specified if you want to check just

a portion of a given file.

END End Block This is the last datablock to check in the

file. This defaults to the last block of

the file but may need specifying for RAW

devices (See "Limitations" below)

BLOCKSIZE Logical Block Size This is the database block size of the

datafile you wish to scan. The value

defaults to "2048".

This parameter must be set to the

DB_BLOCK_SIZE of the datafile to be

scanned.

LOGFILE Output Log This is the name of file to output the

results to. The default is "NONE" and

output is sent to terminal.

FEEDBACK Display Progress If set to a value above 0 (the default)

then DBV outputs a "." for every N pages

of the datafile checked. This is useful

to see that DBV is working through the

file.

PARFILE Parameter file Parameters can be specified in a

parameter file and PARFILE used to cause

the file contents to be used as input

parameters. The PARFILE can contain any

of the above options.

HIGH_SCN Scn Highest Block SCN To Verify

(scn_wrap.scn_base OR scn)

Find the blocks exceeding the SCN.

Available in version 9.2.0.6 and above.

USERID Username/Password If the file you are verifying is an

Automatic Storage Management (ASM) file,

you must supply a USERID. This is because

DBVERIFY needs to connect to an Oracle

instance to access ASM files.

SEGMENT_ID TS#.FILE#.BLOCK# Specifies the segment that you want to

verify. For more info, review Note:139962.1

For help on command line parameters in a given version type "dbv help=y" at the command line.

Dbv 的一些具体应用（比如如何根据dbv 的内容查找哪些块出现的问题）

Getting the AFN from DBVERIFY output

A corrupt block might be reported by dbverify in different ways. DBVERIFY normally provides the RDBA associated to the affected block. Then the RFN is used to get the AFN in the query from dba_data_files below. Here are some examples:

RFN=11 BL=34:

Page 34 is marked corrupt

Corrupt block relative dba: 0x02c00022 (file 11, block 34)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x02c00022

last change scn: 0x0771.4eebe71c seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0xe71c0602

check value in block header: 0xd3ce

computed block checksum: 0x2

Dbverify always reports the relative data block address (rdba/dba) in its output. In the above case the Relative dba is the hexadecimal value 0x02c00022 taken from Message "Corrupt block relative dba: 0x02c00022 (file 11, block 34)". The rdba/dba provides the RFN. The RFN is then 11. Use the query from dba_data_files below to get the AFN.

Another example from dbverify is:

RFN=11 BL=35:

Dbv output:

DBV-200: Block, dba 46137379, already marked corrupted"

To get the RFN and Block# use the next query:

select dbms_utility.data_block_address_file(&&rdba) RFN,

dbms_utility.data_block_address_block(&&rdba) BL

from dual;

Example:

SQL> select dbms_utility.data_block_address_file(&&rdba) RFN,

2 dbms_utility.data_block_address_block(&&rdba) BL

3 from dual;

Enter value for rdba: 46137379

RFN BL

---------- ----------

11 35

Get the AFN from the RFN using dba_data_files:

select file_id AFN, relative_fno, tablespace_name

from dba_data_files

where relative_fno=&RFN;

Example:

SQL> select file_id AFN, relative_fno, tablespace_name

2 from dba_data_files

3 where relative_fno=&RFN;

Enter value for rfn: 11

AFN RELATIVE_FNO TABLESPACE_NAME

---------- ------------ ------------------------------

5 11 USERS

The AFN is 5

Metalink：id 35512.1

DBVERIFY - Database file Verification Utility (7.3.2 - 11.2) –具体讲述 dbv 的用法，以及里面参数的含义

Metalink：id 819533.1

How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY –详细得描述了 dbv 的一个具体的例子，从 定位 file id 和block 开始

3、Identify TABLE / INDEX Inconsistency using analyze

Table / Index inconsistencies is when an entry in the Table does not exist in the Index or vice versa. The common errors are ORA-8102, ORA-600 [kdsgrp1], ORA-1499 by "analyze validate structure cascade".

The tool to identify TABLE / INDEX inconsistencies is the ANALYZE command:

analyze table

validate structure cascade;

When an inconsistency is identified, the above analyze command will produce error ORA-1499 and a trace file.

Use Note 100419.1 to run ANALYZE with the CASCADE option for a specific tablespace.

Use <<563070.1>> when the trace file produced by ANALYZE command has the message "Table/Index row count mismatch"

4、Recovery from corruptions

4．1. Find the object throwing the error messages.

o If not known explicitly, identify the datablock throwing corruption (from the error message, the objects

involved in the SQL throwing the error message or the trace file produced)

o Map it to the object it belongs to.

4．2. If it is index, drop and recreate the index, if feasible. However, if it is a table and you have data to populate the table, you can drop, recreate and then reload the data. In 10g, the feature of flashback table can be useful as well to take you to a point before corruptions. If above methods are not possible, then we need to extract the data from the corrupted table by skipping the corrupted block(s) using various methods. One of the method is to salvage the data using rowid range scan. Refer Note 61685.1 -> Extracting Data from a Corrupt Table using ROWID Range Scans in Oracle8 and higher

3. If above steps are not feasible or not helping getting rid of corruptions, we need to use recovery techniques. For this, find the datafile / tablespace containing the object. Offline this datafile(s)/tablespace. Take a backup of these files. Restore the datafiles(s) from the last good backup and recover completely using archive logs and the online redo logs.

4. If the object having corruption belongs to system tablespace, restore and recovery of the system datafile to be done while the database is mounted. For user objects, the rest of the tablespaces can be online when restore and recovery of the affected datafile / tablespace is being implemented.

If the errors introduced are due to Oracle bugs, they may get re-introduced while applying the archive/redo logs, when you are doing a full recovery. The best option in this case is to do a PIT recovery to the time just before the corruptions were introduced.

另外，当没办法修复或者临时想跳过这些坏块的时候，可以使用 DBMS_REPAIR，而我认为 dbms_repair 的最大作用就在于能够跳过坏块，查询没有坏的表，并且同步索引和表数据的一致，具体用法，详见

Metalink：id 556733.1

DBMS_REPAIR SCRIPT