Snapshot Too Old after SAN outrage

Recently we experienced an SAN outrage. The databases was brought up after SAN was fixed. However we received an ORA-01555 error “Snapshot too old” when we run a SQL statement, e.g.

1 2 3

select *   from scott.orders  where order_date between trunc(sysdate - 1) and trunc(sysdate);

ORA-01555 is caused by Oracle read consistency mechanism. When the long run query is not able to find the before-image from undo segment, ORA-01555 occurs. But I was very sure it is not our case, since the application server was still down and there were sessions only from dba. And I also noticed the SQL statement was successful without ORA-01555 if

1 2 3

select *   from scott.orders  where order_date between trunc(sysdate - 2) and trunc(sysdate - 1);

It makes me think there is data block corruption for the data in the time range of trunc(sysdate - 1) and trunc(sysdate).


Oracle Metalink document 787004.1 describes a very similar situation, except that we have xml data type. So I continued the investigation.

1 2 3

select order_id   from scott.orders  where order_date between trunc(sysdate - 1) and trunc(sysdate);

1 2 3

select order_xml   from scott.orders  where order_date between trunc(sysdate - 1) and trunc(sysdate);

The first SQL was successful. The second got the same ORA-01555 error. Now it is confirmed order_xml column was corrupted.


I wrote a script to find out the corrupt data block,

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

DECLARE    n   NUMBER;    s   VARCHAR2 (4000); BEGIN    FOR c1 IN (SELECT *                 FROM scott.orders                WHERE empno)    LOOP       BEGIN          s := XMLType.getClobVal (c1.order_xml);       EXCEPTION          WHEN OTHERS          THEN             DBMS_OUTPUT.put_line ('ORA' || SQLCODE || ': order_id = ' || c1.order_id);       END;    END LOOP; END;