Bad block processing on Oracle index

Sql> CREATE TABLE Test as SELECT * from Dba_objects where rownum<1001;

Table created.

Sql> CREATE index idx_test on test (object_id);

Index created.

Sql> Select file_id, block_id, blocks from dba_extents where owner = ' lilc ' and segment_name = ' idx_test ';

file_id block_id BLOCKS

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

6 6032 8

Store from the 4th block, construct a bad block,

Rman> Recover datafile 6 block 6035 clear;

Starting recover at 23-sep-15

Using target database control file instead of recovery catalog

Allocated Channel:ora_disk_1

Channel ora_disk_1:sid=75 Device Type=disk

Allocated Channel:ora_disk_2

Channel ORA_DISK_2:SID=14 Device Type=disk

Finished recover at 23-sep-15

[Email protected] ~]$ DBV Userid=grid/grid file=+data/phub/datafile/llc01.dbf

Dbverify:release 11.2.0.4.0-production on Wed Sep 23 08:51:16 2015

Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.

Dbverify-verification Starting:file = +data/phub/datafile/llc01.dbf

Page 6035 is marked corrupt

Corrupt block relative dba:0x01801793 (file 6, block 6035)

Bad check value found during DBV:

Data in bad block:

Type:6 Format:2 rdba:0x01801793

Last Change scn:0x0000.001e13c3 seq:0x1 flg:0x04

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

Consistency value in tail:0x13c30601

Check value in block header:0xc307

Computed Block Checksum:0x5f27

Dbverify-verification Complete

Total Pages examined:655360

Total Pages processed (Data): 7507

Total Pages Failing (Data): 0

Total Pages processed (Index): 1181

Total Pages Failing (Index): 0

Total Pages processed (other): 646167

Total Pages processed (SEG): 0

Total Pages Failing (Seg): 0

Total Pages empty:504

Total Pages Marked corrupt:1

Total Pages influx:0

Total Pages encrypted:0

Highest block scn:0 (0.0)

Verify that there is a bad block on the index:

Sql> SELECT tablespace_name, Segment_type, Owner,segment_name, partition_name from Dba_extents WHERE file_id=6 and 603 5 between block_id and block_id+blocks-1; Tablespace_name segment_ty OWNER segment_name partition_name----------------------------------------------------- ------------------------Llcindex LILC Idx_test

In this case, if the full table scan, is normal, index scan error:

Sql> select object_id from test;

Selected rows.

Execution Plan

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

Plan Hash value:1357081020

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 | SELECT STATEMENT |   | 1000 |     13000 | 6 (0) | 00:00:01 |

|  1 | TABLE ACCESS full|  TEST | 1000 |     13000 | 6 (0) | 00:00:01 |

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

Note

-----

-Dynamic sampling used for this statement (level=2)

Statistics

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

0 Recursive calls

0 db Block gets

Bayi consistent gets

0 physical Reads

0 Redo Size

17797 Bytes sent via sql*net to client

1250 Bytes received via sql*net from client

Sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Processed rows

Sql> Select object_id from Test where object_id<100;

Select object_id from Test where object_id<100

*

ERROR at line 1:

Ora-01578:oracle data Block corrupted (file # 6, Block # 6035)

Ora-01110:data file 6: ' +DATA/PHUB/DATAFILE/LLC01.DBF

The index state is still valid:

Sql> Select status from Dba_indexes where index_name= ' idx_test ';

STATUS

--------

VALID

Can add hint full table scan will not error:

Sql> Select/*+ Full (test) */object_id from test where object_id<100;

98 rows selected.

Execution Plan

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

Plan Hash value:1357081020

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 | SELECT STATEMENT |     |   65 |     845 | 6 (0) | 00:00:01 |

|* 1 | TABLE ACCESS full|    TEST |   65 |     845 | 6 (0) | 00:00:01 |

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

predicate information (identified by Operation ID):

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

1-filter ("object_id" <100)

Note

-----

-Dynamic sampling used for this statement (level=2)

Statistics

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

0 Recursive calls

0 db Block gets

Consistent gets

0 physical Reads

0 Redo Size

2137 Bytes sent via sql*net to client

590 Bytes received via sql*net from client

8 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

98 rows Processed

WORKAROUND: Rebuild the index online

sql> ALTER index idx_test rebuild online;

Index altered.

Sql> Select object_id from Test where object_id<100;

98 rows selected.

Execution Plan

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

Plan Hash value:1128569081

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 | SELECT STATEMENT |    |   98 | 1274 | 2 (0) | 00:00:01 |

|* 1 | INDEX RANGE scan| Idx_test |   98 | 1274 | 2 (0) | 00:00:01 |

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

predicate information (identified by Operation ID):

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

1-access ("object_id" <100)

Note

-----

-Dynamic sampling used for this statement (level=2)

Statistics

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

0 Recursive calls

0 db Block gets

9 Consistent gets

0 physical Reads

0 Redo Size

2137 Bytes sent via sql*net to client

590 Bytes received via sql*net from client

8 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

98 rows Processed

Through DBV and RMAN

[Email protected] ~]$ DBV Userid=grid/grid file=+data/phub/datafile/llc01.dbf

Dbverify:release 11.2.0.4.0-production on Wed Sep 23 09:25:38 2015

Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.

Dbverify-verification Starting:file = +data/phub/datafile/llc01.dbf

Page 6035 is marked corrupt

Corrupt block relative dba:0x01801793 (file 6, block 6035)

Bad check value found during DBV:

Data in bad block:

Type:6 Format:2 rdba:0x01801793

Last Change scn:0x0000.001e13c3 seq:0x1 flg:0x04

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

Consistency value in tail:0x13c30601

Check value in block header:0xc307

Computed Block Checksum:0x5f27

Dbverify-verification Complete

Total Pages examined:655360

Total Pages processed (Data): 7507

Total Pages Failing (Data): 0

Total Pages processed (Index): 1179

Total Pages Failing (Index): 0

Total Pages processed (other): 646169

Total Pages processed (SEG): 0

Total Pages Failing (Seg): 0

Total Pages empty:504

Total Pages Marked corrupt:1

Total Pages influx:0

Total Pages encrypted:0

Highest block scn:0 (0.0)

rman> backup Check logical validate DataFile 6;

Starting backup at 23-sep-15

Using channel Ora_disk_1

Using channel Ora_disk_2

Channel ora_disk_1:starting full DataFile backup set

Channel ora_disk_1:specifying DataFile (s) in backup set

Input datafile file number=00006 name=+data/phub/datafile/llc01.dbf

Channel Ora_disk_1:backup set complete, elapsed time:00:00:35

List of Datafiles

=================

File Status Marked corrupt Empty Blocks Blocks examined high SCN

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

6 FAILED 0 504 655364 1974761

File Name: +data/phub/datafile/llc01.dbf

Block Type Blocks failing Blocks processed

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

Data 0 7507

Index 1 1177

Other 0 646172

Validate found one or more corrupt blocks

See Trace FILE/U01/APP/ORACLE/DIAG/RDBMS/PHUB/PHUB/TRACE/PHUB_ORA_26417.TRC for details

Finished backup at 23-sep-15

Sql> Analyze index lilc.idx_test validate structure;

Index analyzed.

Rman> Recover datafile 6 block 6035;

Starting recover at 23-sep-15

Using target database control file instead of recovery catalog

Allocated Channel:ora_disk_1

Channel ora_disk_1:sid=73 Device Type=disk

Allocated Channel:ora_disk_2

Channel ora_disk_2:sid=141 Device Type=disk

Finished standby search, restored 1 blocks

Starting Media recovery

Media recovery complete, elapsed time:00:00:03--repair from standby

Finished recover at 23-sep-15

After the index is deleted, the index is re-created and the bad block still exists, but the index can be used

sql> DROP Index idx_test;

Index dropped.

rman> backup Check logical validate DataFile 6;

Starting backup at 23-sep-15

Using target database control file instead of recovery catalog

Allocated Channel:ora_disk_1

Channel ora_disk_1:sid=141 Device Type=disk

Allocated Channel:ora_disk_2

Channel ora_disk_2:sid=13 Device Type=disk

Channel ora_disk_1:starting full DataFile backup set

Channel ora_disk_1:specifying DataFile (s) in backup set

Input datafile file number=00006 name=+data/phub/datafile/llc01.dbf

Channel Ora_disk_1:backup set complete, elapsed time:00:00:35

List of Datafiles

=================

File Status Marked corrupt Empty Blocks Blocks examined high SCN

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

6 FAILED 0 504 655364 1977414

File Name: +data/phub/datafile/llc01.dbf

Block Type Blocks failing Blocks processed

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

Data 0 7507

Index 1 1177

Other 0 646172

Validate found one or more corrupt blocks

See Trace FILE/U01/APP/ORACLE/DIAG/RDBMS/PHUB/PHUB/TRACE/PHUB_ORA_26936.TRC for details

Finished backup at 23-sep-15

Bad block processing on Oracle index