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

最新推荐文章于 2023-01-28 12:50:57 发布

crs1995

最新推荐文章于 2023-01-28 12:50:57 发布

阅读量277

点赞数

文章标签：数据库

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

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

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

连接到目标数据库: 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工具檢測該數據文件以確定哪些塊出現問題(也可以用rman的backup 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

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

知道了是因COSTMATM與INDX_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)