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