修复已经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 applicationDBV-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 applicationSQL> 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-