今天用戶打電話說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

(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

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

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]$

邭夂芎茅o能夠正常導出﹐如果在導出時遇到ORA-01578錯誤﹐那就只能通過設置10231事件(但會丟失損壞塊的數據)。

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

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

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