修复已经CORRUPTED的数据块

最新推荐文章于 2024-04-28 10:28:04 发布

ctest011501

最新推荐文章于 2024-04-28 10:28:04 发布

阅读量282

点赞数

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

转载于:http://blog.itpub.net/40239/viewspace-662497/

ctest011501

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
修复已经CORRUPTED的数据块

修复已经CORRUPTED的数据块2010-5-11在PS系统的开发环境，开发人员说遇到了错误：ORA-01578: ORACLE data block corrupted (file # 91, block #...
复制链接

扫一扫