Shulou(Shulou.com)06/01 Report--

Oracle Bad Block problem handling

1. Description

Bad block problems often occur in database systems, and if there is no proper way to deal with them, it will often lead to unavailability of bad block objects or data loss. This paper starts with the generation of bad blocks, focusing on the treatment of different situations after the occurrence of bad blocks.

2. The cause of the bad block:

Hardware Icano error

Operating system Icano error or buffering problem

Memory or paging issu

Disk repair tool

Part of a data file is being overwritten

Oracle failed to access an unformatted system block

Partial overflow of data file

Oracle or bug of the operating system

3. Discovery of bad blocks:

3.1Error report in Alter log

Tue Aug 17 10:48:07 2010

Corrupt Block Found

TSN = 7, TSNAME = BTEST

RFN 6, BLK = 839, rdba = 25166663

OBJN = 49205, BJD = 49205, BJECT= BOBJ, SUBOBJECT =

Segment wner= AN, Segment Type = Table Segment

The RFN represents the 839 blocks of the relative_fno,6 file, the segment type is the table segment, and the bad block appears in the table bobj.

3.2. Query and report error

SQL > select count (*) from bobj

Select count (*) from bobj

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 839)

ORA-01110: data file 6:'F:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ DB10\ BTEST.DBF'

3.3. Error report in the analysis table

SQL > analyze table bobj validate structure cascade

Analyze table bobj validate structure cascade

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 839)

ORA-01110: data file 6:'F:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ DB10\ BTEST.DBF'

3.4. error report for Rman backup

RMAN > backup tablespace btest

Starting backup at 17-August-10

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting full datafile backupset

Channel ORA_DISK_1: specifying datafile (s) in backupset

Input datafile fno=00006 name=F:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ DB10\ BTEST.DBF

Channel ORA_DISK_1: starting piece 1 at 17-August-10

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/17/2010 11:03:09

ORA-19566: exceeded limit of 0 corrupt blocks for file F:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ DB10\ BTEST.DBF

3.5. Dbv check and report error

F:\ oracle\ product\ 10.1.0\ oradata\ db10 > dbv file=BTEST.DBF blocksize=8192

DBVERIFY: Release 10.1.0.2.0-Production on Tuesday August 17 10:49:02 2010

Copyright (c) 1982, 2004, Oracle. All rights reserved.

DBVERIFY-Verification starting: FILE = BTEST.DBF

Page 839 is marked corrupt

Corrupt block relative dba: 0x01800347 (file 6, block 839)

Bad check value found during dbv:

Data in bad block:

Type: 6 format: 2 rdba: 0x01800347

Last change scn: 0x0000.0005246f seq: 0x1 flg: 0x04

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0x246f0601

Check value in block header: 0x50c2

Computed block checksum: 0x2751

DBVERIFY-Verification complete

Total Pages Examined: 5376

Total Pages Processed (Data): 5165

Total Pages Failing (Data): 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 9

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 201

Total Pages Marked Corrupt: 1

Total Pages Influx: 0

3.6. Query the data files of bad blocks and the corresponding tablespaces

Select file_name,tablespace_name,file_id "AFN", relative_fno "RFN"

From dba_data_files

Select file_name,tablespace_name,file_id, relative_fno "RFN"

From dba_temp_files

3.7. Query what the object with bad block is:

SELECT tablespace_name, segment_type, owner

Segment_name, partition_name FROM

Dba_extents WHERE file_id = v_file_id and v_block_id

Between block_id AND block_id + blocks-1

4. Common objects with bad blocks:

Objects under Sys users

Rollback segment

Temporary section

Index or partitioned index

Table

5. The handling of the problem

5.1. Recovery without backup

5.1.1. Objects under Sys users need to be handled carefully.

5.1.2. The bad block of the rollback section can be deleted by using the treatment method similar to ora-600 (4000). Can cause things to fail and data to be lost.

5.1.3, Index: rebuild, during which the table will be locked and the business will be affected, and the data will not be lost.

5.1.4. Table:

5.1.4.1 、 EVENT10231

SQL > select count (*) from bobj

COUNT (*)

-

376240

SQL > analyze table bobj validate structure cascade

Analyze table bobj validate structure cascade

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 495)

ORA-01110: data file 6:'F:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ DB10\ BTEST.DBF'

SQL > alter session set events = '10231 trace name context forever,level 10'

Session altered.

SQL > select count (*) from bobj

COUNT (*)

-

376169

The bad blocks are ignored for reading and writing, so there is a change in the amount of data, and some of the data is lost.

5.1.4.2 、 ROWID RANGE SCAN

FUNCTION ROWID_CREATE RETURNS ROWID

Argument Name Type In/Out Default?

ROWID_TYPE NUMBER IN

OBJECT_NUMBER NUMBER IN

RELATIVE_FNO NUMBER IN

BLOCK_NUMBER NUMBER IN

ROW_NUMBER NUMBER IN

SQL > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

6 839 1 0 CHECKSUM

SQL > SELECT dbms_rowid.rowid_create (1mr49205pr 6djm 839d0) from DUAL

DBMS_ROWID.ROWID_C

-

AAAMA1AAGAAAANHAAA

SQL > SELECT dbms_rowid.rowid_create (1mr49205pr 6pjm 840d0) from DUAL

DBMS_ROWID.ROWID_C

-

AAAMA1AAGAAAANIAAA

SQL > create table bbobj tablespace btest as select * from bobj where 1: 2

Table created.

SQL > insert into bbobj select / * + rowid (a) * / * from bobj where rowid insert into bbobj select / * + rowid (a) * / * from bobj where rowid > = 'AAAMA1AAG

AAAANIAAA'

315108 rows created.

SQL > commit

Commit complete.

SQL > select count (*) from bbobj

COUNT (*)

-

376101

You can find and deal with the bad block information in the current database from v$database_block_corruption.

By calling dbms_rowid.rowid_create to identify the rowid corresponding to the bad block, recreate the table with the same table structure, and skip the bad block to store the good data into the intermediate table on the condition of rowid, lose part of the data, and still ignore the impact of the bad block.

5.1.4.3 、 Dbms_repair

Execute as sys user

PROCEDURE SKIP_CORRUPT_BLOCKS

Argument Name Type In/Out Default?

SCHEMA_NAME VARCHAR2 IN

OBJECT_NAME VARCHAR2 IN

OBJECT_TYPE BINARY_INTEGER IN DEFAULT

FLAGS BINARY_INTEGER IN DEFAULT

SQL > Execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ('AN','BOBJ')

PL/SQL procedure successfully completed.

SQL > conn an/an

Connected.

SQL > select count (*) from bobj

COUNT (*)

-

376169

If a trial sys user calls skip_corrupt_blocks in dbms_repair to ignore the bad blocks in the object, the data in the bad blocks will also be lost. The package also contains a procedure FIX_CORRUPT_BLOCKS, which can fix bad blocks.

5.2. Recovery with backup

5.2.1 、 Blockrecover

RMAN > blockrecover datafile 5 block 425

Starting blockrecover at 17-August-10

Using channel ORA_DISK_1

Channel ORA_DISK_1: restoring block (s)

Channel ORA_DISK_1: specifying block (s) to restore from backup set

Restoring blocks of datafile 00005

Channel ORA_DISK_1: restored block (s) from backup piece 1

Piece handle=F:\ ORACLE\ PRODUCT\ 10.1.0\ FLASH_RECOVERY_AREA\ DB10\ BACKUPSET\ 2010 / 08 / 17\ O1_MF_NNNDF_TAG20100817T162508_66NKV5B8_.BKP tag=TAG20100817T162508

Channel ORA_DISK_1: block restore complete

Starting media recovery

Media recovery complete

Finished blockrecover at 17-August-10

SQL > select count (*) from an.atest

COUNT (*)

-

376240

5.2.2 、 Recover datafile

SQL > select count (*) from an.atest

Select count (*) from an.atest

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 963)

ORA-01110: data file 5:'F:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ DB10\ ATEST.DBF'

RMAN > sql "alter database datafile 5 offline"

Sql statement: alter database datafile 5 offline

RMAN > restore datafile 5

Starting restore at 17-August-10

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile backupset restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Restoring datafile 00005 to F:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ DB10\ ATEST.DBF

Channel ORA_DISK_1: restored backup piece 1

Piece handle=F:\ ORACLE\ PRODUCT\ 10.1.0\ FLASH_RECOVERY_AREA\ DB10\ BACKUPSET\ 2010 / 08 / 17\ O1_MF_NNNDF_TAG20100817T162508_66NKV5B8_.BKP tag=TAG20100817T162508

Channel ORA_DISK_1: restore complete

Finished restore at 17-August-10

RMAN > recover datafile 5

Starting recover at 17-August-10

Using channel ORA_DISK_1

Starting media recovery

Media recovery complete

Finished recover at 17-August-10

RMAN > sql "alter database datafile 5 online"

Sql statement: alter database datafile 5 online

SQL > select count (*) from an.atest

COUNT (*)

-

376240

5.3.Bad blocks are skipped by Rman backup

SET MAXCORRUPT FOR DATAFILE filename TO n

Example:

Run {

Allocate channel node1 type disk

SET MAXCORRUPT FOR DATAFILE 8 TO 3

Set limit channel node1 kbytes = 1800000

Backup as compressed backupset full database format'$BACKUPDIR/full_%d_%T_%s_%p' plus archivelog format'$BACKUPDIR/arch_%d_%T_%s_%p' delete all input

Backup current controlfile format'$BACKUPDIR/ctl_%d_%T_%s_%p' TAG "control.bak"

Release channel node1

}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.