RMAN恢复之后, Oracle Lob 1578坏块问题

在任何平台的Oracle版本上（from 8i to 11g），

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.1.0.7

Oracle Server - Standard Edition - Version: 8.1.7.0 to 11.1.0.7

都可能遇到这个问题：

LOB segment has been defined as NOLOGGING and LOB Blocks were marked as corrupted by Oracle after a datafile recovery.

[@more@]

1 现象

应用日志错误如下：错误信息是Ora-1578伴随 Ora-26040，首先感觉是一个坏块。

如果是物理坏块就会比较麻烦。

Errors: A persistence error occurred. System message follows:

Nested exception is: wt.util.WTIOException: A persistence error occurred. System message follows:

Nested exception is: java.io.IOException: ORA-01578: ORACLE data block corrupted (file # 9, block # 2844051)

ORA-01110: data file 9: '/oradata/oradata/wind/windblobs03.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

wt.pom.DatastoreException: A persistence error occurred. System message follows:

Nested exception is: wt.util.WTIOException: A persistence error occurred. System message follows:

java.io.IOException: ORA-01578: ORACLE data block corrupted (file # 9, block # 2844051)

ORA-01110: data file 9: '/oradata/oradata/wind/windblobs03.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

at oracle.jdbc.driver.DatabaseError.SQLToIOException(DatabaseError.java:517)

at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:214)

at oracle.jdbc.driver.OracleBlobOutputStream.close(OracleBlobOutputStream.java:179)

at wt.pds.oracle81.StandardLobHandler81.write(StandardLobHandler81.java:253)

2 那张表坏了

下面可以确认坏块发生在PLM80的WFPROCESS表的BLOB$CONTEXT段上

SQL> col segment_name for a30

SQL> col owner for a15

SQL> select owner, segment_name, segment_type from dba_extents

2 where file_id=9 and 2844051 between block_id and block_id+blocks -1;

OWNER SEGMENT_NAME SEGMENT_TYPE

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

PLM80 SYS_LOB0000034941C00003$$ LOBSEGMENT

SQL>

SQL> select table_name, column_name

2 from dba_lobs

3 where segment_name = 'SYS_LOB0000034941C00003$$'

4 and owner = 'PLM80';

TABLE_NAME COLUMN_NAME

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

WFPROCESS BLOB$CONTEXT

SQL>

3 DBV verify

SQL> SELECT TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK FROM sys.SYS_DBA_SEGS WHERE SEGMENT_NAME ='SYS_LOB0000034941C00003$$';

TABLESPACE_ID HEADER_FILE HEADER_BLOCK

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

4 15 2579515

SQL>

dbv userid=plm80/plm80qas segment_id=4.15.2579515

………………………..

DBV-00201: Block, DBA 20380224, marked corrupt for invalid redo application

DBV-00201: Block, DBA 20380225, marked corrupt for invalid redo application

DBV-00201: Block, DBA 20380226, marked corrupt for invalid redo application

DBV-00201: Block, DBA 20380227, marked corrupt for invalid redo application

DBV-00201: Block, DBA 20380228, marked corrupt for invalid redo application

DBVERIFY - Verification complete

Total Pages Examined : 39040

Total Pages Processed (Data) : 0

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 39001

Total Pages Processed (Seg) : 1

Total Pages Failing (Seg) : 0

Total Pages Empty : 38

Total Pages Marked Corrupt : 5774

Total Pages Influx : 0

Highest block SCN : 765121807 (0.765121807)

[wind:oracle] /home/oracle>

有5774个坏页。

4 物理坏块还是逻辑坏块

可以确认是逻辑坏块

SQL> select FILE#, BLOCK#, BLOCKS, CORRUPTION_CHANGE#, MARKED_CORRUPT, CORRUPTION_TYPE from V$BACKUP_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MARKED_CORRUPT CORRUPTION_TYPE

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

4 519 6 751553402 NO LOGICAL

4 529 4 751554721 NO LOGICAL

4 557 2 751554862 NO LOGICAL

4 573 2 751556498 NO LOGICAL

4 939 2 744707570 NO LOGICAL

4 943 2 744952649 NO LOGICAL

4 3582657 2 757610492 NO LOGICAL

4 3582689 2 757630002 NO LOGICAL

………………………………………………..

10 3596555 2 757718518 NO LOGICAL

18 519 52 757165435 NO LOGICAL

18 573 166 756969823 NO LOGICAL

18 741 18 757190852 NO LOGICAL

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MARKED_CORRUPT CORRUPTION_TYPE

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

18 761 120 757114777 NO LOGICAL

18 883 12 757317421 NO LOGICAL

18 897 132 757095600 NO LOGICAL

716 rows selected

SQL>

5 修复问题

create table plm80.corrupted_data (corrupted_rowid rowid);

set concat off

declare

error_1578 exception;

pragma exception_init(error_1578,-1578);

n number;

begin

for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

begin

n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

exception

when error_1578 then

insert into plm80.corrupted_data values (cursor_lob.r);

commit;

end;

end loop;

end;

/

undefine lob_column

When prompted by variable values:

Enter value for lob_column: BLOB$CONTEXT

Enter value for table_owner: PLM80

Enter value for table_with_lob: WFPROCESS

set concat off

update &table_owner.&table_with_lob set &lob_column = empty_blob()

where rowid in (select corrupted_rowid from corrupted_data);

If &lob_column is a CLOB datatype, replace empty_blob by empty_clob.

5 From Metalink

- Note that the data inside the corrupted lob blocks is not salvageable.

- Setting the corrupted lob to empty lob will add the blocks formerly mapped to this lob to the freelist. Eventually when PCTVERSION or RETENTION criteria cause the space to be salvaged and reused for new data, error ORA-1578/ORA-26040 can be seen again in the same LOB blocks. In that case and after applying the above procedure the lob segment can be moved to a new segment:

alter table &table_owner.&table_with_lob move LOB (&&lob_column) store as (tablespace &tablespace_name);

- dbverify will still produce errors DBV-200 / DBV-201 until the extent of the block marked as corrupted is reused by another segment.

- In the plsql code above, the value 889911 passed to procedure hextoraw in dbms_lob.instr is a fake value to verify the lob content. dbms_lob.instr is not supposed to find that string so the variable "n" should always return 0.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/593324/viewspace-1034384/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/593324/viewspace-1034384/