【資料恢複】NOLOGGING UNRECOVERABLE ORA-26040解析

SQL> select count(*) from abc;

select count(*) from abc

*

第 1 行出現錯誤:

ORA-01578: ORACLE 資料塊損壞 (檔案号 17, 塊号 131)

ORA-01110: 資料檔案 17:

‘C:\APP\XIANGBLI\ORADATA\MACLEAN\DATAFILE\O1_MF_NLOGGING_9475OCS5_.DBF’

ORA-26040: 資料塊是使用 NOLOGGING 選項加載的

SQL> select UNRECOVERABLE_CHANGE# , UNRECOVERABLE_time from v$datafile where file#=17;

UNRECOVERABLE_CHANGE# UNRECOVERABLE_

——————— ————–

6486756 26-9月 -13

把 (檔案号 17, 塊号 131) dump出來看一下

*** 2013-09-26 10:07:46.584

Start dump data blocks tsn: 17 file#:17 minblk 131 maxblk 131

Block dump from cache:

Dump of buffer cache at level 4 for pdb=0 tsn=17 rdba=71303299

Block dump from disk:

buffer tsn: 17 rdba: 0x04400083 (17/131)

scn: 0x0.62faac seq: 0xff flg: 0x04 tail: 0xfaac00ff

frmt: 0x02 chkval: 0xa2a1 type: 0x00=unknown

Hex dump of block: st=0, typ_found=0

Dump of memory from 0x000000000BFF2200 to 0x000000000BFF4200

00BFF2200 0000A200 04400083 0062FAAC 04FF0000 [……@…b…..]

00BFF2210 0000A2A1 FFFFFFFF FFFFFFFF FFFFFFFF […………….]

00BFF2220 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF […………….]

Repeat 508 times

00BFF41F0 FFFFFFFF FFFFFFFF FFFFFFFF FAAC00FF […………….]

End dump data blocks tsn: 17 file#: 17 minblk 131 maxblk 131

scn: 0x0.62faac seq: 0xff

==》 對應的SCN為6486700，可以看到内容除了頭部一點外 全是0XFF

dump 對應redo logfile 可以看到

REDO RECORD – Thread:1 RBA: 0x000074.00015418.0078 LEN: 0x003c VLD: 0x01 CON_UID: 0

SCN: 0x0000.0062faac SUBSCN: 1 09/26/2013 10:04:39

CHANGE #1 INVLD CON_ID:0 AFN:17 DBA:0x04400083 BLKS:0x000d OBJ:123054 SCN:0x0000.0062faac SEQ:1 OP:19.2 ENC:0

Direct Loader invalidate block range redo entry

OP:19.2=》Layer 19 : Direct Loader Log Blocks – KCOCODLB Opcode 2 : Invalidate range – KCBLCOIR

==》這裡在redo裡标記了 直接路徑加載造成塊失效的範圍，在redo logfile dump中可以看到大量類似資料

即當recover時讀取redo，讀到“Direct Loader invalidate block range redo entry”資訊時，則将對應的資料塊的内容除了kcbh頭部外全部記錄為0XFF

當Oracle讀取到這些塊時就會知道這些塊是SOFT Corrupt ，原因是nologging造成的。

Block is marked as SOFT Corrupt and has 0xff along the block. This is the

format used by Oracle to mark a block as corrupt due to redo invalidation

(NOLOGGING).

NOLOGGING OPERATION in redo:

原文位址：http://www.askmaclean.com/archives/nologging-unrecoverable.html