ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用dbv和RMAN检查数据文件中的坏块

使用dbv和RMAN检查数据文件中的坏块

原创 Linux操作系统 作者:jichenghan 时间:2008-06-26 13:24:29 0 删除 编辑

 

使用dbvRMAN检查数据文件中的坏块

1.使用dbv检查

D:\oradata\eygle>dbv file=EYGLE.DBF blocksize=8192

DBVERIFY: Release 10.1.0.4.0 - Production on 星期六 6 11 17:36:37 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

DBVERIFY - 开始验证: FILE = EYGLE.DBF

219 标记为损坏

Corrupt block relative dba: 0x010000db (file 4, block 219)

Bad check value found during dbv:

Data in bad block:

 type: 6 format: 2 rdba: 0x010000db

 last change scn: 0x0000.0005ee6d seq: 0x2 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0xee6d0602

 check value in block header: 0x9779

computed block checksum: 0x6141

1258 标记为损坏

Corrupt block relative dba: 0x010004ea (file 4, block 1258)

Bad check value found during dbv:

Data in bad block:

 type: 6 format: 2 rdba: 0x010004ea

 last change scn: 0x0000.00042681 seq: 0x2 flg: 0x06

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x26810602

 check value in block header: 0x660b

computed block checksum: 0x9317

DBVERIFY - 验证完成

检查的页总数: 1280

处理的页总数 (数据): 150

失败的页总数 (数据): 0

处理的页总数 (索引): 127

失败的页总数 (索引): 0

处理的页总数 (其它): 1001

处理的总页数 ()  : 0

失败的总页数 ()  : 0

空的页总数: 0

标记为损坏的总页数: 2

流入的页总数: 0

Highest block SCN            : 428223 (0.428223)

 


2.
使用RMAN检查坏块

D:\oradata\eygle>rman target /

恢复管理器: 版本9.2.0.6.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

连接到目标数据库: EYGLE (DBID=1365961916)

RMAN> backup validate datafile 4;

启动 backup 11-6 -05

正在使用目标数据库控制文件替代恢复目录

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=17 devtype=DISK

通道 ORA_DISK_1: 正在启动 full 数据文件备份集

通道 ORA_DISK_1: 正在指定备份集中的数据文件

输入数据文件 fno=00004 name=D:\ORADATA\EYGLE\EYGLE.DBF

通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:03

完成 backup 11-6 -05

RMAN>

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

         4        219          1                  0 FRACTURED

         4       1258          1                  0 FRACTURED

 

數據塊損壞的恢復

今天用戶打電話說AIS系統不可用﹐檢查alert_HJAIS.log發現如下錯誤信息﹕

Errors in file /u1/admin/HJAIS/udump/ora_542.trc:

ORA-00600: internal error code, arguments: [2662], [2159], [3949335402], [53584], [3221134616], [153849508], [], []

Sun Aug  6 01:30:12 2006

Errors in file /u1/admin/HJAIS/udump/ora_542.trc:

ORA-00600: internal error code, arguments: [2662], [2159], [3949335404], [53584], [3221134616], [153849508], [], []

ORA-00600: internal error code, arguments: [2662], [2159], [3949335402], [53584], [3221134616], [153849508], [], []

Sun Aug  6 01:30:13 2006

Errors in file /u1/admin/HJAIS/udump/ora_542.trc:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [2159], [3949335404], [53584], [3221134616], [153849508], [], []

ORA-00600: internal error code, arguments: [2662], [2159], [3949335402], [53584], [3221134616], [153849508], [], []

***

Corrupt block relative dba: 0x07c09562 (file 31, block 38242)

Bad header found during preparing block for write

Data in bad block -

 type: 0 format: 0 rdba: 0x092b8ea4

 last change scn: 0xd150.bffe9d18 seq: 0x4e flg: 0x40

 consistency value in tail: 0x0d000601

 check value in block header: 0x9900, block checksum disabled

 spare1: 0x0, spare2: 0x0, spare3: 0xbffe

***

Sun Aug  6 02:04:48 2006

Errors in file /u1/admin/HJAIS/bdump/dbw0_4013.trc:

ORA-00600: internal error code, arguments: [kcbzpb_1], [130061666], [4], [0], [], [], [], []

Sun Aug  6 02:04:49 2006

Errors in file /u1/admin/HJAIS/bdump/dbw0_4013.trc:

ORA-00600: internal error code, arguments: [kcbzpb_1], [130061666], [4], [0], [], [], [], []

DBW0: terminating instance due to error 600

Instance terminated by DBW0, pid = 4013

Errors in file /u1/admin/HJAIS/bdump/snpc_19164.trc:

ORA-00600: internal error code, arguments: [25012], [54], [0], [], [], [], [], []

Sun Aug  6 10:53:10 2006

Restarting dead background process EMN0

EMN0 started with pid=31

Sun Aug  6 10:53:31 2006

Errors in file /u1/admin/HJAIS/bdump/snpc_19164.trc:

ORA-12012: error on auto execute of job 1142

ORA-20002: -600ORA-00600: internal error code, arguments: [25012], [54], [0], [], [], [], [], []

ORA-06512: at "COST.PRO_AUTO_IMPORT_COSTMATM", line 147

ORA-06512: at line 1

所有錯誤都指明是有壞塊出現。

先列出[2662][25012]錯誤的相關說明﹕

[2662]是指说明当前数据库的数据块的SCN早于当前的SCN,主要是和存储在UGA变量中的dependent SCN进行比较,

                    如果当前的SCN小于它,数据库就会产生这个ORA-600 [2662]的错误了

[2662]ARGUMENTS:

  Arg [a]  Current SCN WRAP

  Arg [b]  Current SCN BASE

  Arg [c]  dependent SCN WRAP

  Arg [d]  dependent SCN BASE

  Arg [e]  Where present this is the DBA where the dependent SCN came from.

ORA-600 [25012] [a]
VERSIONS:
  versions 8.0 to 10.1
DESCRIPTION:
  We are trying to generate the absolute file number given a tablespace
  number and relative file number and cannot find a matching file number
  or the file number is zero.
ARGUMENTS:         
  Arg [a] Tablespace Number
  Arg Relative file number

從以上信息可以得知是表空間號為54數據文件號為31的數據文件出現問題。

下面用dbv工具檢測該數據文件以確定哪些塊出現問題(也可以用rmanbackup validate datafile來檢測)

[oracle@acthjdb01 HJAIS]$ dbv file=PUBUSER.dbf blocksize=8192

DBVERIFY: Release 8.1.7.4.0 - Production on 星期日 8 6 12:12:48 2006

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = PUBUSER.dbf

Block Checking: DBA = 130061666, Block Type = KTB-managed data block

kdbchk: bad row offset slot 1 offs 24649 fseo 948 dtl 8168 bhs 48

Page 38242 failed with check code 6135

Block Checking: DBA = 130148819, Block Type = KTB-managed data block

**** actual rows locked by itl 2  = 1 != # in trans. header = 0

---- end index block validation

Page 125395 failed with check code 6401

DBVERIFY - Verification complete

Total Pages Examined         : 256000

Total Pages Processed (Data) : 115272

Total Pages Failing   (Data) : 1

Total Pages Processed (Index): 126456

Total Pages Failing   (Index): 1

Total Pages Processed (Other): 78

Total Pages Empty            : 14194

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

[oracle@acthjdb01 HJAIS]$

發現數據塊38242和索引塊125395有問題。用下面的sql語句檢查它們所對應的對象﹕

SQL> SELECT tablespace_name, segment_type, owner, segment_name
   FROM dba_extents
 WHERE file_id = 31
   and 38242 between block_id AND block_id + blocks - 1;
  
TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME
------------------------------ ------------------ ------------------------------ -------------------------
PUBUSER                        TABLE              PUBUSER                        COSTMATM

SQL> SELECT tablespace_name, segment_type, owner, segment_name
   FROM dba_extents
 WHERE file_id = 31
   and 125395 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME
------------------------------ ------------------ ------------------------------ --------------------
PUBUSER                        INDEX              PUBUSER                        INDX_EXPD_ACCEPTD

 知道了是因COSTMATMINDX_EXPD_ACCEPTD兩個對象的塊所引起的﹐處理辦法是分別重建這兩個對象。

INDX_EXPD_ACCEPTD的處理﹕

SQL> drop index INDX_EXPD_ACCEPTD;

Index dropped

SQL> CREATE INDEX INDX_EXPD_ACCEPTD ON M_EXPD

  2  (ACCP_NO, ACCP_ITEM, MAT_NO, PURM_NO, PURM_ITEM)

  3  LOGGING

  4  TABLESPACE PUBUSER

  5  /

Index created

對于COSTMATM表﹐先exp出﹐看是否能正常exp

[oracle@acthjdb01 bdump]$ exp pubuser/pubpswd file=/tmp/costmatm1.dmp tables=(costmatm)

Export: Release 8.1.7.4.0 - Production on 星期日 8 6 14:35:24 2006

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

With the Partitioning option

JServer Release 8.1.7.4.0 - Production

Export done in ZHT16BIG5 character set and ZHT16BIG5 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                       COSTMATM     161558 rows exported

Export terminated successfully without warnings.

[oracle@acthjdb01 bdump]$

運氣很好﹐能夠正常導出﹐如果在導出時遇ORA-01578錯誤﹐那就只能通過設置10231事件(但會丟失損壞塊的數據)

ALTER SYSTEM SET EVENTS=10231 trace name context forever,level 10 ;

接著drop table costmatm﹐然后再導入。

這樣就恢復成功了。可以查詢一切都正常。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13248003/viewspace-365997/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 用rman迁移数据库
请登录后发表评论 登录
全部评论

注册时间:2008-02-26

  • 博文量
    17
  • 访问量
    37103