记一次 ORA-600 [12700] 故障案例

记一次 ORA-600 [12700] 故障案例  

2011-02-18 20:19:04|  分类： Oracle |  标签： |字号大中小 订阅

http://francs3.blog.163.com/blog/static/405767272011118819481/

  

          这篇记录是以前做数据库巡检项目时遇到的，今天记录到博客上当作经验分享。

   09年12月31日下午3：30左右，在对 myschema 用户做EXP导出时有ORA-00600报错，并会生成一跟踪报错文件，
随后每次导出均有此错误产生，详细如下：

--exp script
exp system/system@txczyy owner=myschema statistics=Y rows=N file='/arch/tf/1.dmp' log='/arch/tf/exp.log'

--exp.log
. . exporting table                        WF_PASS
. . exporting table                       WF_STATE
. . exporting table                 WF_STATE_VALUE
. . exporting table               WF_TASK_EXECUTOR
. . exporting table                WF_TASK_GRANTER
. . exporting table                   WF_TASK_TERM
. . exporting table                    WF_TEMPLATE
. . exporting table                    WF_VARIABLE
. . exporting table              WF_VARIABLE_VALUE
. exporting synonyms
. exporting view
EXP-00008: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26], [0], [24], [], []
EXP-00056: ORACLE error 1403 encountered
ORA-01403: no data found
EXP-00000: Export terminated unsuccessfully

--alert.log
Wed Jan  6 22:02:01 2010
Errors in file /oracle/app/admin/txczyy/udump/txczyy_ora_917632.trc:
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26], [0], [24], [], []
Wed Jan  6 22:02:02 2010
Errors in file /oracle/app/admin/txczyy/udump/txczyy_ora_1249630.trc:
ORA-00600: 内部错误代码，参数: [12700], [2], [4206885], [26], [0], [24], [], []
Wed Jan  6 22:07:34 2010

--trace file
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26], [0], [24], [], []
Current SQL statement for this session:
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS FROM SYS.EXU8COL WHERE TOBJID = :1 ORDER BY INTCOLID
----- Call Stack Trace -----

1.开SR 3-1284405961  METALINK上分析trc发现在查询对象46907时出错,笔记如下

Generic Note
------------
Hi,

Trace file shows value of the bind variable as:

******************************************************
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=0 size=24 offset=0
bfp=1104bb330 bln=22 avl=04 flg=05
value=46907
End of cursor dump
***************** dump of cursor xsc=1104dbdd8 **********************

Action Plan
=========
Please check the value of the following query:

SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects WHERE status != 'VALID' or object_id=46907 ORDER BY 4,2;

Regards
Anudeep
GCS

2 既然ORACLE分析绑定变量46907有问题,而出错的sql里也有个绑定变量,所以尝试将46907代到以下sql执行试试 ,经查ID为46907的对象，是myschema用户下的视图： myschema.v_cp_av_data,
执行以下sql(将TOBJID值改成46907 ,trace文件里出错的sql)
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID,
COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM,
FSPRECISION, LFPRECISION, CHARLEN, TFLAGS FROM SYS.EXU8COL
WHERE TOBJID = 46907 ORDER BY INTCOLID

查询此视图,下好抛出
ORA-00600: 内部错误代码，参数: [12700], [2], [4206885], [26], [0], [24], [], []
说明,问题就出现在这个视图上,当Oracle在查询SYS.EXU8COL时出错,发现SYS.exu8col是个视图

3.即然这张view有问题,于是尝试重建视图 myschema.v_cp_av_data，看是否有效果
16:19:10 SYS@txczyy> drop view myschema.v_cp_av_data;
drop view myschema.v_cp_av_data
*
第 1 行出现错误:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-08102: 未找到索引关键字，obj# 46，dba 4249996 (2)

4.查看obj# 46对象为col$表的i_col2索引

5.重建view行不通,于是,打算重建view引用相关表上的索引 即TABLE:myschema.CP_VOUCHER,依然行不通

6. 在经过以上操作行不通后,于是怀疑系统表col$有问题,
6.1 select * from col$ 正常

6.2 31:30 SYS@txczyy> select obj#,col# from col$ where obj#=46907;

      OBJ#       COL#
---------- ----------
     46907          1
     46907          2
     46907          3
     46907          4
     46907          5
     46907          6
     46907          7

已选择7行。

6.3 select /*+ index(c i_col2) */ * from  col$ c where obj#=46907;
ERROR:
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26],
[4249996], [25], [], []

说明,查询走索引 i_col2时,ORACLE正好报这个600错误
问题就是col$表 和 i_col2索引的交叉引用有问题，
ORA-600 [12700]是索引项中的ROWID指向的数据没有，
ORA-8102  是表中的某条数据没有对应的索引项。

7 尝试重建系系统表col$上的索引i_col2
SQL> drop index i_col2;
drop index i_col2
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

8 metalink回复
Generic Note
------------
Hi,

I am Balaji and I Have reviewed the SR as per your request.

Looking into the SR I understand that , the index I_COL2 which is created on the table COL$ is corrupted.

Unfortunately , the object is a bootstrap segment , which cannot be modified using the sql scrpts.

The best option here is to perform the restore / recovery from the last good backup .

In case this is not possible then the only option way ahead is to perform an export of the entire database and import it.

You can go for schema level export.

Regards
Balaji

   由于故障根源为系统表col$的rowid 和其上的 i_col2索引标识的rowid不匹配，而引起“数据块的”损坏，要清出此“坏块”必须重建此索引，但由于此表为系统表，如对其做重建操作，很可能发生意想不到的事故，故推荐用以下方案来解决此问题。

方案1.重新建立一数据库，然后将当前故障库中有用数据导入新建库中，而将此故障库保留并试图对索引做重建操作来解决。

方案2.停机并将故障库数据完全拷贝至其它相应服务器恢复测试。

8. 方案比较
方案1:
       保守方案,风险小,速度快（目前数据量较小）;  历时短
方案2:
       需要对Oracle系统表操作,首先在测试库上执行,风险评估可行方在生产库上执行,历时长;

9. 最后采用方案一新建DB,并导入TXCZ10用户的数据,问题解决

--附件一 view myschema.v_cp_av_data  object_id=46907
create or replace view myschema.v_cp_av_data as
select cp.org_code,cp.nd,cp.process_inst_id,cp.co_code, cp.org_money, cp.cur_money, cp.fund_code from cp_voucher cp
where cp.is_sum_cp_voucher='0' and cp.control_type='1' and cp.cp_adjust_code='101' and cp.is_valid='1' and cp.a_status_code='2';

--附件二 关于  ORA-600 [12700] [a] [b] [c]
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26]

Note: For additional ORA-600 related information please read Note:146580.1

PURPOSE:           
  This article discusses the internal error "ORA-600 [12700]", what
  it means and possible actions. The information here is only applicable
  to the versions listed and is provided only for guidance.
 
ERROR:
  ORA-600 [12700] [a] [b] [c]
 
VERSIONS:
  versions 6.0 to 9.2
 
DESCRIPTION:
   
  Oracle is trying to access a row using its ROWID, which has been
  obtained from an index.

  A mismatch was found between the index rowid and the data block it is
  pointing to. The rowid points to a non-existent row in the data block.
  The corruption can be in data and/or index blocks.
 
  ORA-600 [12700] can also be reported due to a consistent read (CR)
  problem.

  The information dumped to the trace file varies greatly between releases:

- in Oracle 7.3.x it is ORA-600 [12700][a][b] , where
  Arg [a] dba (Data Block Address)
  Arg [b] slot number (number of the row in the block pointed by the dba)

- in Oracle 8.x and 9.x, it is ORA-600 [12700][a][b][c] , where
  Arg [a] dataobj# from sys.obj$
  Arg [b] relative dba of the data block
  Arg [c] slot number of the row in the data block

  The arguments of the ORA-600 [12700] contains information obtained
  from the index we are using.

FUNCTIONALITY:
  USER/ORACLE INTERFACE LAYER

IMPACT:
  POSSIBLE CORRUPTION

SUGGESTIONS:

  Please refer to Note:155933.1 "Resolving an ORA-600 [12700] error"
  for additional help with diagnosing this problem.

  If the above note does not help, then please log the issue with Oracle
  Support Services.

  Known Issues:

--根据DBA,查询块号
15:29:39 SYS@txczyy> select obj#,name from sys.obj$ where dataobj#=2;

      OBJ# NAME
---------- ------------------------------
         4 TAB$
         2 C_OBJ#
        19 IND$
        21 COL$
        20 ICOL$
         5 CLU$
       156 LOB$
       283 COLTYPE$
       286 SUBCOLTYPE$
       288 ATTRCOL$
       290 VIEWTRCOL$

      OBJ# NAME
---------- ------------------------------
       295 TYPE_MISC$
       352 NTAB$
       359 LIBRARY$
       362 REFCON$
       365 OPQTYPE$
       367 ICOLDEP$

15:31:00 SYS@txczyy> select dbms_utility.data_block_address_file(4206885),dbms_utility.data_block_address_block(4206885) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4206885) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4206885)
--------------------------------------------- ----------------------------------------------
                                            1                                          12581

19:31:30 SYS@txczyy> select obj#,col# from col$ where obj#=46907;

      OBJ#       COL#
---------- ----------
     46907          1
     46907          2
     46907          3
     46907          4
     46907          5
     46907          6
     46907          7

已选择7行。

20:06:29 SYS@txczyy> select rowid,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)  from col$ c where obj#=46907;

ROWID              DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------ ------------------------------------
AAAAACAABAAADElAAX                                    1                                12581
AAAAACAABAAADElAAY                                    1                                12581
AAAAACAABAAADElAAZ                                    1                                12581
AAAAACAABAAADElAAo                                    1                                12581
AAAAACAABAAADElAAp                                    1                                12581
AAAAACAABAAADElAAq                                    1                                12581
AAAAACAABAAADElAAr                                    1                                12581
 

说明 block=12581发生坏块

--查看数据行在哪个块
select depid,dept,dbms_rowid.rowid_block_number(rowid) blcokno from hw;