修复已经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/,如需转载,请注明出处,否则将追究法律责任。