月归档：四月 2019

分享一次由于一个表异常导致数据库报类似：ORACLE Instance XFF (pid = 18) – Error 600 encountered while recovering transaction故障的案例
一个10.2.0.4的数据库,正常运行的库突然出现如下错误

Sun Apr 07 11:07:12 2019
Thread 1 advanced to log sequence 602883 (LGWR switch)
  Current log# 3 seq# 602883 mem# 0: L:\ORADATA\XFF\REDO03.LOG
Sun Apr 07 11:10:38 2019
Thread 1 advanced to log sequence 602884 (LGWR switch)
  Current log# 1 seq# 602884 mem# 0: L:\ORADATA\XFF\REDO01.LOG
Sun Apr 07 11:11:56 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\udump\XFF_ora_22956.trc:
ORA-00600: 内部错误代码, 参数: [ktspgfb-1], [], [], [], [], [], [], []

Sun Apr 07 11:12:46 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\udump\XFF_ora_27408.trc:
ORA-00600: 内部错误代码, 参数: [kcbnew_3], [0], [1], [168354056], [], [], [], []

Sun Apr 07 11:13:57 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\udump\XFF_ora_6632.trc:
ORA-00600: 内部错误代码, 参数: [ktspgfb-1], [], [], [], [], [], [], []

过一段时间报，然后实例直接crash

Tue Apr 09 07:47:35 2019
ORACLE Instance XFF (pid = 18) - Error 600 encountered while recovering transaction (1, 1) on object 113718002.
Tue Apr 09 07:47:35 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_smon_12948.trc:
ORA-00600: internal error code, arguments: [kcbgcur_3], [168454497], [8], [4], [0], [], [], []

Tue Apr 09 07:55:23 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_pmon_22652.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:24 2019
PMON: terminating instance due to error 474
Tue Apr 09 07:55:24 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_lgwr_28608.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:34 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_psp0_12544.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:34 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_j000_5216.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:35 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_ckpt_28204.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:36 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_mman_9320.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:44 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_q002_24384.trc:
ORA-00474: SMON process terminated with error

Tue Apr 09 07:55:53 2019
Errors in file c:\oracle\product\10.2.0\admin\XFF\bdump\XFF_reco_24124.trc:
ORA-00474: SMON process terminated with error

根据以上报错，数据库crash的原因是由于undo异常导致，通过对undo进行重建，解决掉异常undo，但是业务运行之后，一样的问题又重现，最后通过分析确认是对象异常导致

SQL> create table XFF.T_XIFENFEI_xff as select * from XFF.T_XIFENFEI;
create table XFF.T_XIFENFEI_xff as select * from XFF.T_XIFENFEI
                                                           *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []

屏蔽相关block obj的check之后
SQL> create table XFF.T_XIFENFEI_xff as select * from XFF.T_XIFENFEI;
create table XFF.T_XIFENFEI_xff as select * from XFF.T_XIFENFEI
                                                           *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktspScanInit-l1], [], [], [], [],[], [], []

比较明显该表对象出现逻辑异常，通过基于rowid的方式对该表数据进行抽取

SQL> create table XFF.T_XIFENFEI_new
  2  as
  3  select * from XFF.T_XIFENFEI where 1=0;

Table created.

SQL> set serveroutput on
SQL> set concat off
SQL> DECLARE
  2   nrows number;
  3   rid rowid;
  4   dobj number;
  5   ROWSPERBLOCK number;
  6  BEGIN
  7   ROWSPERBLOCK:=1000;
  8   nrows:=0;
  9   select data_object_id  into dobj
 10   from dba_objects
 11   where owner = 'XFF'
 12   and object_name = 'T_XIFENFEI'
 13   ;
 14   for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
 15             from dba_extents
 16             where owner = 'XFF'
 17               and segment_name = 'T_XIFENFEI'
 18            order by extent_id)
 19   loop
 20     for br in i.block_id..i.totblocks loop
 21      for j in 1..ROWSPERBLOCK loop
 22      begin
 23        rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
 24        insert into XFF.T_XIFENFEI_NEW
 25        select /*+ ROWID(A) */ *
 26        from XFF.T_XIFENFEI A
 27        where rowid = rid;
 28        if sql%rowcount = 1 then nrows:=nrows+1; end if;
 29        if (mod(nrows,10000)=0) then commit; end if;
 30      exception when others then null;
 31      end;
 32      end loop;
 33    end loop;
 34   end loop;
 35   COMMIT;
 36   dbms_output.put_line('Total rows: '||to_char(nrows));
 37  END;
 38  /
Total rows: 227000

PL/SQL procedure successfully completed.

再次观察数据库恢复正常,也不再crash和报错,恢复完成