ORA-00474 – Database SOS

ORA-600 2032故障处理

标题：ORA-600 2032故障处理

有客户数据库,异常断电之后,数据库运行不稳定（经常性的重启）,通过分析发现

Wed Jun 29 01:04:39 2022
Completed: alter database open
Wed Jun 29 01:04:42 2022
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_3284.trc:
ORA-12012: error on auto execute of job 1
ORA-01578: ORACLE data block corrupted (file # 2, block # 552)
ORA-01110: data file 2: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA_2\ORCL\UNDOTBS01.DBF'
…………
Wed Jun 29 01:13:28 2022
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Wed Jun 29 01:15:34 2022
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5488.trc:
ORA-00600: internal error code, arguments: [6002], [6], [48], [5], [0], [], [], []
Wed Jun 29 01:20:54 2022
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 2 out of maximum 100 non-fatal internal errors.
Wed Jun 29 01:20:55 2022
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_6956.trc:
ORA-00600: internal error code, arguments: [2032], [8389160], [8389160], [8192], [2], [255], [0], [767]

Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 3 out of maximum 100 non-fatal internal errors.
Wed Jun 29 01:20:57 2022
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_6956.trc:
ORA-00600: internal error code, arguments: [2032], [8389160], [8389160], [8192], [2], [255], [0], [767]
………………
Wed Jun 29 01:21:41 2022
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_q001_2124.trc:
ORA-00474: SMON process terminated with error

Wed Jun 29 01:21:42 2022
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_3376.trc:
ORA-00474: SMON process terminated with error

Wed Jun 29 01:21:42 2022
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_reco_2412.trc:
ORA-00474: SMON process terminated with error

Instance terminated by PMON, pid = 7160

对ora-600 2032进行分析

*** 2022-06-29 01:13:26.907
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [2032], [8389160], [8389160], [8192], [2], [255], [0], [767]
Current SQL statement for this session:
insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas,  num_mappings, 
tim_scn_map) values (0, :1, :2, :3, :4, :5, :6, :7)
check trace file e:\oracle\product\10.2.0\db_2\rdbms\trace\orcl_ora_0.trc for preloading .sym file messages
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp+663           CALL???  ksedst+55            003C878B8 000000000 00FF57178
                                                   000000000
ksfdmp+19            CALL???  ksedmp+663           000000003 00ED07680 010453DA8
                                                   003CACC80
kgeriv+184           CALL???  ksfdmp+19            7FF2378C000 7FF5E2F81D8
                                                   7FF5EBB9300 7FF5EBB92F0
kgesiv+102           CALL???  kgeriv+184           000000002 00ED07040 000000002
                                                   00FF59110
ksesic7+125          CALL???  kgesiv+102           7FFFFFFF00800228 100000002
                                                   200000001 00FF59328
kcopcv+1014          CALL???  ksesic7+125          0000007F0 000000000 000800228
                                                   000000000
kcbchg1_main+3115    CALL???  kcopcv+1014          00001E778 7FF00000001
                                                   00001E980 00001E650
kcbchg1+238          CALL???  kcbchg1_main+3115    00ED07040 00ED07040 000040013
                                                   000000000
ktuchg+1331          CALL???  kcbchg1+238          7FF00000000 000000003
                                                   00FF597C8 00FF59780
ktbchg2+341          CALL???  ktuchg+1331          000000000 000000401 000000046
                                                   000BB5D21
kdtchg+916           CALL???  ktbchg2+341          000000001 000000000 000000240
                                                   000000000
kdtwrp+2582          CALL???  kdtchg+916           01077A268 01044404C 010444054
                                                   010441710
kdtInsRow+705        CALL???  kdtwrp+2582          01077A268 7FF5E2F8510
                                                   003CB3358 0009C20C4
insrowFastPath+125   CALL???  kdtInsRow+705        00ED07040 7FF55A86E30
                                                   7FF58E87918 00001F318
insdrvFastPath+478   CALL???  insrowFastPath+125   000000067 000000000 000000000
                                                   000000000
inscovexe+434        CALL???  insdrvFastPath+478   01077A268 000000000 010440070
                                                   00521E0C9
insExecStmtExecIniE  CALL???  inscovexe+434        7FF55A88FD8 7FF55A86E30
ngine+99                                           00FF5BAD8 0009FA3EC
insexe+453           CALL???  insExecStmtExecIniE  8B2D554D9623 000000006
                              ngine+99             000000006 0000000C0
opiexe+4991          CALL???  insexe+453           7FF55A885A0 00FF5BAD8
                                                   000000102 000000000
opiall0+1931         CALL???  opiexe+4991          7FF00000049 000000003
                                                   00FF5C160 000000020
opikpr+660           CALL???  opiall0+1931         000000065 000000022 00FF5C638
                                                   000000000
opiodr+1136          CALL???  opikpr+660           000000065 000000017 01044B798
                                                   07EEF1FCF
rpidrus+230          CALL???  opiodr+1136          000000065 000000017 01044B798
                                                   80005900000000
rpidru+112           CALL???  rpidrus+230          00FF5D3F0 000000003 01078D4A0
                                                   7FF5B6F1A80
rpiswu2+517          CALL???  rpidru+112           7FF5DA52BB8 000000000
                                                   000000000 000000008
kprball+1446         CALL???  rpiswu2+517          7FF5E408820 000000000
                                                   00FF5DAD0 000000002
ktf_scn_time+4951    CALL???  kprball+1446         01044B798 8B2D00000140
                                                   000000000 000000005
ktmmon+4107          CALL???  ktf_scn_time+4951    000000000 000000001 07FFFFFFF
                                                   00521A3C2
ktmSmonMain+26       CALL???  ktmmon+4107          0049CBAC0 000000004
                                                   8B2D554D9623 00001E768
ksbrdp+903           CALL???  ktmSmonMain+26       003CC2A18 0049CBADC 000000008
                                                   000000004
opirip+700           CALL???  ksbrdp+903           726F77740000001E 003C8B000
                                                   00FF5FA30 000000000
opidrv+860           CALL???  opirip+700           000000032 000000004 00FF5FD50
                                                   000000000
sou2o+52             CALL???  opidrv+860           000000032 000000004 00FF5FD50
                                                   000000003
opimai_real+272      CALL???  sou2o+52             000000000 000000000 000000000
                                                   000000000
opimai+96            CALL???  opimai_real+272      000000000 000000000 000000000
                                                   000000000
BackgroundThreadSta  CALL???  opimai+96            00FF5FEA8 000000001 000000000
rt+633                                             000000000
000000007738F56D     CALL???  BackgroundThreadSta  0069E4590 000000000 000000000
                              rt+633               000000000
0000000077703021     CALL???  000000007738F56D     000000000 000000000 000000000
                                                   000000000
 
--------------------- Binary Stack Dump ---------------------

通过该trace和alert日志信息可以确认是由于smon_scn_time的操作需要使用到undo,但是对应的undo block异常,从而使得该操作失败,进而引起数据库smon进程异常从而引起ORA-00474,数据库自动crash.处理问题比较简单：
1. 对异常undo进行处理,创建新undo,删除老undo
2. 对于smon_scn_time异常数据进行处理

ORACLE Instance XFF (pid = 18) – Error 600 encountered while recovering transaction

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE Instance XFF (pid = 18) – Error 600 encountered while recovering transaction

分享一次由于一个表异常导致数据库报类似：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和报错,恢复完成

ORA-600 kcbz_check_objd_typ_1 处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 kcbz_check_objd_typ_1 处理

客户数据库异常(ORA-600 kcbz_check_objd_typ_1),让我们远程给分析处理
ORA-600 kcbz_check_objd_typ_1异常

Mon Aug  8 12:19:28 2016
Completed: ALTER DATABASE OPEN
Mon Aug  8 12:19:29 2016
db_recovery_file_dest_size of 20480 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Aug  8 12:19:33 2016
Errors in file /home/oracle/admin/RT/bdump/rt_smon_1514.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Mon Aug  8 12:20:21 2016
Shutting down archive processes
Mon Aug  8 12:20:26 2016
ARCH shutting down
ARC3: Archival stopped
Mon Aug  8 13:12:25 2016
Thread 1 advanced to log sequence 13804
  Current log# 3 seq# 13804 mem# 0: /home/oracle/product/10.2.0/oradata/RT/redo03a.log
Mon Aug  8 14:01:37 2016
Thread 1 advanced to log sequence 13805
  Current log# 2 seq# 13805 mem# 0: /home/oracle/product/10.2.0/oradata/RT/redo02a.log
Mon Aug  8 14:20:51 2016
Errors in file /home/oracle/admin/RT/bdump/rt_smon_1514.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Mon Aug  8 15:54:47 2016
Thread 1 advanced to log sequence 13808
  Current log# 2 seq# 13808 mem# 0: /home/oracle/product/10.2.0/oradata/RT/redo02a.log
Mon Aug  8 16:21:48 2016
Errors in file /home/oracle/admin/RT/bdump/rt_smon_1514.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Mon Aug  8 16:22:05 2016
Errors in file /home/oracle/admin/RT/bdump/rt_pmon_1500.trc:
ORA-00474: SMON process terminated with error

这里比较明显,数据库报大量ORA-600 kcbz_check_objd_typ_1错误之后,然后smon进程终止,实例crash.

smon trace文件

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name:	SunOS
Node name:	st104
Release:	5.10
Version:	Generic_141445-09
Machine:	i86pc
Instance name: RT
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 1514, image: oracle@st104 (SMON)
*** 2016-08-08 12:19:26.868
*** SERVICE NAME:() 2016-08-08 12:19:26.868
*** SESSION ID:(383.1) 2016-08-08 12:19:26.868
Dead transaction 0x003d.002.0000f964 recovered by SMON
Dead transaction 0x0041.017.00004d55 recovered by SMON
Dead transaction 0x0047.002.0000180c recovered by SMON
Dead transaction 0x004c.01c.00001b57 recovered by SMON
*** SESSION ID:(383.1) 2016-08-08 12:19:27.470
DATA seg.obj=0, on-disk obj=925949, dsflg=0, dsobj=923715, cls=4
Formatted dump of block:
buffer tsn: 4 rdba: 0x0100336b (4/13163)
scn: 0x09c6.b2c7f7a2 seq: 0x02 flg: 0x04 tail: 0xf7a20602
frmt: 0x02 chkval: 0x649b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
*** SESSION ID:(383.1) 2016-08-08 12:19:34.244
DATA seg.obj=0, on-disk obj=925950, dsflg=0, dsobj=923671, cls=4
Formatted dump of block:
buffer tsn: 4 rdba: 0x01003343 (4/13123)
scn: 0x09c6.b2c7f7dc seq: 0x02 flg: 0x04 tail: 0xf7dc0602
frmt: 0x02 chkval: 0x8013 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
*** SESSION ID:(383.1) 2016-08-08 12:19:35.197
DATA seg.obj=0, on-disk obj=925941, dsflg=0, dsobj=923657, cls=4
Formatted dump of block:
buffer tsn: 7 rdba: 0x01c03d53 (7/15699)
scn: 0x09c6.b2c7f570 seq: 0x02 flg: 0x04 tail: 0xf5700602
frmt: 0x02 chkval: 0xe5c5 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
*** SESSION ID:(383.1) 2016-08-08 12:19:38.965
DATA seg.obj=0, on-disk obj=925948, dsflg=0, dsobj=923656, cls=4
Formatted dump of block:
buffer tsn: 7 rdba: 0x01c03a6b (7/14955)
scn: 0x09c6.b2c7f745 seq: 0x02 flg: 0x04 tail: 0xf7450602
frmt: 0x02 chkval: 0x58c5 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

这里可以看出来有block中的obj和dataobj不匹配.

查询seg$.type=3
type=3为临时对象,由于异常原因导致smon在清理temp对象无法正常完成,从而使得smon终止,实例crash.

SQL> select file#, block#, ts# from seg$ where type# = 3;
     FILE#     BLOCK#        TS#
---------- ---------- ----------
         4      13163          4
         4      13123          4
         7      15699          7
         7      14955          7

ORA-600 kcbz_check_objd_typ_1处理方法

1) Check tablespace bitmap
SQL> oradebug setmypid
SQL> exec dbms_space_admin.tablespace_verify('&TBSP_NAME')
SQL> oradebug tracefile_name
or if the tablespace involved is an ASSM tablespace:
SQL> oradebug setmypid
SQL> exec dbms_space_admin.assm_tablespace_verify ('&TBSP_NAME',dbms_space_admin.TS_VERIFY_BITMAPS)
SQL> oradebug tracefile_name
I am expecting to fail
2) Corrupt these temp segments
SQL> exec dbms_space_admin.segment_corrupt('&TBSP_NAME', &FILE#, &BLOCK#)
3) Drop them
SQL> exec dbms_space_admin.segment_drop_corrupt('&TBSP_NAME', &FILE#, &BLOCK#)
4) Rebuild tablespace bitmap
exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('&TBSP_NAME')
5) Verify the tablespace again
SQL> oradebug setmypid
SQL> exec dbms_space_admin.tablespace_verify('&TBSP_NAME')
SQL> oradebug tracefile_name
or if the tablespace involved is an ASSM tablespace:
SQL> oradebug setmypid
SQL> exec dbms_space_admin.assm_tablespace_verify('&TBSP_NAME',dbms_space_admin.TS_VERIFY_BITMAPS)
SQL> oradebug tracefile_name

标签归档：ORA-00474