Block corruption I

Sod’s law – the penultimate dress rehearsal for our 11gR2 upgrade showed that we have suddenly have a block corruption issue on our 9i production database.

Mind you better to find out now than go-live weekend.

It’s only one block and actually it only seems to be two rows in that one block.

The response to the severity 1 SR was less than impressive.

Anyway… as you might expect, the impact of a block corruption can be unpredictable.

Some observations from this particular case:

1. Can’t access the table block by PK.

SQL> explain plan for
  2  select * from trade where trad_tag = 185263584;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id  | Operation                          |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |   332 |    13   (8)|       |       |
|   1 |  PARTITION LIST ALL                |             |       |       |            |     1 |    11 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TRADE       |     1 |   332 |    13   (8)|     1 |    11 |
|*  3 |    INDEX RANGE SCAN                | TRADE_IDX   |     1 |       |    24   (5)|     1 |    11 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TRADE"."TRAD_TAG"=185263584)

14 rows selected.

SQL> select * from trade where trad_tag = 185263584;
select * from trade where trad_tag = 185263584
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

2. Can access the index with no table access.

SQL> explain plan for
  2  select rowid rd, trad_tag from trade where trad_tag = 185263584;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    14 |    12   (0)|       |       |
|   1 |  PARTITION LIST ALL  |             |       |       |            |     1 |    11 |
|*  2 |   INDEX RANGE SCAN   | TRADE_IDX   |     1 |    14 |    24   (5)|     1 |    11 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TRADE"."TRAD_TAG"=185263584)

13 rows selected.

SQL> select rowid rd, trad_tag from trade where trad_tag = 185263584;

RD                   TRAD_TAG
------------------ ----------
AAAYBNAAYAAEK/bAAR  185263584

3. But can access the corruption directly via rowid:

SQL> explain plan for
  2  select trad_tag, int_override_ind, length(int_override_ind)
  3  from   trade
  4  where  rowid = 'AAAYBNAAYAAEK/bAAR';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------
| Id  | Operation                  |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |    16 |     2  (50)|       |       |
|   1 |  TABLE ACCESS BY USER ROWID| TRADE       |     1 |    16 |     2  (50)| ROWID | ROW L |
-----------------------------------------------------------------------------------------------

7 rows selected.

SQL> select trad_tag, int_override_ind, length(int_override_ind)
  2  from   trade
  3  where  rowid = 'AAAYBNAAYAAEK/bAAR';

  TRAD_TAG I LENGTH(INT_OVERRIDE_IND)
---------- - ------------------------
           N                        8

SQL> 

4. But an index of one of the corrupted values in the corrupt block is also affected:

SQL> explain plan for 
  2  select route_code, count(*) from trade group by route_code;

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------
| Id  | Operation              |  Name        | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     3 |     6 | 15098  (57)|       |       |
|   1 |  SORT GROUP BY         |              |     3 |     6 | 15098  (57)|       |       |
|   2 |   PARTITION LIST ALL   |              |       |       |            |     1 |    11 |
|   3 |    INDEX FAST FULL SCAN| TRADE_7_IDX  |    30M|    59M|  7452  (13)|     1 |    11 |
--------------------------------------------------------------------------------------------

9 rows selected.


SQL> select route_code, count(*) from trade group by route_code;
ERROR:
ORA-00600: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]

For one illustration of the corruption, see this VARCHAR2(1) column:

SQL> desc trade
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- -----------------
 ...
 INT_OVERRIDE_IND                                                  NOT NULL VARCHAR2(1)
 ...

SQL> select int_override_ind, length(int_override_ind) lngth, dump(int_override_ind) dmp
  2  from trade
  3  where trad_tag = 185263584;

I      LNGTH DMP
- ---------- --------------------------------------------------
N          8 Typ=1 Len=8: 78,1,78,255,255,1,128,1

Back to access by rowid…

5. This works in Toad but not SQL*Plus, i.e. there must be some significant difference in how the
different clients deal with their resultsets:

SQL> select * 
  2  from   trade 
  3  where  rowid IN ( chartorowid('AAAYBNAAYAAEK/bAAR'), 
  5                    chartorowid('AAAYBNAAYAAEK/bAAT'));
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected


no rows selected

SQL> 

That’s it really.

Obviously we need to fix it.