修復已經CORRUPTED的資料塊

kewin發表於2010-05-12

修復已經CORRUPTED的資料塊
2010-5-11
在PS系統的開發環境，開發人員說遇到了錯誤：

ORA-01578: ORACLE data block corrupted (file # 91, block # 812462) 
ORA-01110: data file 91: '/oraindex11/hr9pre/psindex.dbf' 
ORA-26040: Data block was loaded using the NOLOGGING option

這個環境是上個星期才從DR環境重新整理過來的，怎麼會出現這種情況呢？
執行了DBV命令發現真的有多個block已經corruected。
DBVERIFY - Verification complete
Total Pages Examined         : 876800
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 638922
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 120146
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 117732
Total Pages Marked Corrupt   : 13159
Total Pages Influx           : 0
Highest block SCN            : 546059801 (0.546059801)

想在rman下檢測有多少個block處於corrupted的狀態，資料庫處於非歸檔模式下，backup validate 命令無法使用。

RMAN> backup validate tablespace PSINDEX
2> ;
Starting backup at 10-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=478 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/10/2010 20:35:51
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
RMAN> backup validate check  logical database
2> ;
Starting backup at 10-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/10/2010 20:47:10
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/10/2010 20:47:11
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/10/2010 20:47:11
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/10/2010 20:47:10
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
檢視這個TABLESPACE的物件發現都是INDEX，那出來問題的辦法就簡單了，刪除INDEX，重新建立就好了。
可到底有哪些INDEX塊是出於CORRUPTED的呢？
由於沒有執行RMAN 的檢查，無法通過v$database_block_corruption試圖來獲取到準確的資訊。不可能從DBV工具來一個一個來查詢吧，一萬多個BLOCK啊，一個一個去查詢這個會弄的我吐血的啊。
想到了DR環境。
先把DR環境切換到只讀狀態。
然後執行下面的SQL:
SELECT distinct tablespace_name, segment_type, owner, segment_name 
FROM dba_extents ex, v$database_block_corruption v 
WHERE  ex.owner='SYSADM' and segment_type='INDEX'
and ex.file_id = v.file# and v.BLOCK# between ex.block_id AND ex.block_id + ex.blocks - 1;
找到那些INEX需要REBUILD：
TABLESPACE_NAME
------------------------------
SEGMENT_TYPE
------------------------------------------------------
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PSINDEX
INDEX
SYSADM
PS_Z_PI_ORDERNO

先找到INDEX的定義：

Select  sys.dbms_metadata.get_ddl('INDEX','PS_Z_PI_ORDERNO', 'SYSADM') From DUAL;
 CREATE UNIQUE INDEX "SYSADM"."PS_Z_PI_ORDERNO" ON "SYSADM"."PS_Z_PI_ORDERNO
" ("RUN_ID", "PI_RUN_NUM", "ROW_COUNT2")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 40960 NEXT 1048576 MINEXTENTS 1 MAXEXTE
NTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU
LT)
  TABLESPACE "PSINDEX"
然後先DROP "SYSADM"."PS_Z_PI_ORDERNO",再重建。

這裡沒有辦法可以偷懶，只能一個一個去執行。
重建後，讓使用者去驗證，問題已經解決。
可是在DBV介面中，還是有錯誤的block。奇怪

DBV-00201: Block, DBA 382524587, marked corrupt for invalid redo application
DBV-00201: Block, DBA 382524588, marked corrupt for invalid redo application
DBV-00201: Block, DBA 382524589, marked corrupt for invalid redo application
DBV-00201: Block, DBA 382524590, marked corrupt for invalid redo application
DBV-00201: Block, DBA 382524591, marked corrupt for invalid redo application
DBV-00201: Block, DBA 382524592, marked corrupt for invalid redo application
DBV-00201: Block, DBA 382524593, marked corrupt for invalid redo application

根據BLOCK去找物件，沒有找到相關的物件。 

DBV-00201: Block, DBA 382524589, marked corrupt for invalid redo application
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(382524592) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(382524592)
------------------------------------------------
                                          842928
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(382524592) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(382524592)
-----------------------------------------------
                                             91
SQL> SELECT tablespace_name, segment_type, owner, segment_name 
  2  FROM dba_extents
WHERE file_id = 91 and &BL between block_id AND block_id + blocks - 1;  3  
Enter value for bl: 842928
old   3: WHERE file_id = 91 and &BL between block_id AND block_id + blocks - 1
new   3: WHERE file_id = 91 and 842928 between block_id AND block_id + blocks - 1
no rows selected
難道是空白塊嗎？
DUMP這個block的資料檢視，真的是空塊：
*** 2010-05-11 01:10:53.222
*** ACTION NAME:() 2010-05-11 01:10:53.222
*** MODULE NAME:(sqlplus@sjdhcmds (TNS V1-V3)) 2010-05-11 01:10:53.221
*** SERVICE NAME:(SYS$USERS) 2010-05-11 01:10:53.221
*** SESSION ID:(478.2574) 2010-05-11 01:10:53.221
Start dump data blocks tsn: 92 file#: 91 minblk 842928 maxblk 842928
buffer tsn: 92 rdba: 0x16ccdcb0 (91/842928)
scn: 0x0000.1e721a08 seq: 0xff flg: 0x04 tail: 0x1a0800ff
frmt: 0x02 chkval: 0x2b57 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x0000000111BC5000 to 0x0000000111BC7000
111BC5000 00A20000 16CCDCB0 1E721A08 0000FF04  [.........r......]
111BC5010 2B570000 FFFFFFFF FFFFFFFF FFFFFFFF  [+W..............]
111BC5020 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF  [................]
        Repeat 508 times
111BC6FF0 FFFFFFFF FFFFFFFF FFFFFFFF 1A0800FF  [................]
End dump data blocks tsn: 92 file#: 91 minblk 842928 maxblk 842928
為什麼空塊也被Oracle的 DBV 程式來檢查呢？
請遇到過這個問題的，告訴我一下。
-THE END-

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/40239/viewspace-662497/，如需轉載，請註明出處，否則將追究法律責任。

相關文章