Oracle数据库如何模拟ORA-600 [4193]错误？如何解决？

原创已于 2024-11-16 20:43:59 修改 · 366 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

于 2024-11-16 20:36:04 首次发布

图片.png

ORA-600 [4193]错误说明:

ORA-600 [4193] "seq# mismatch while adding undo record" (Doc ID 39282.1)
Format: ORA-600 [4193] [a] [b]
ARGUMENTS:
  Arg [a] Undo record seq number
  Arg [b] Redo record seq number

数据库启动时会验证 Undo record seq number 和 Redo record seq number，如不匹配，就会报错ora-600[4193]。

解决方案:

1.最常用的解决方案是重建UNDO:
(1)修改参数
undo_management= MANUAL
undo_tablespace= SYSTEM
(2)打开数据库，删除当前undo空间，重建新undo空间
(3)修改参数
undo_management= AUTO
undo_tablespace= UNDOTBSNEW
(4)重新启动数据库

2.或者用隐含参数屏蔽有问题的undo segment：
_offline_rollback_segments和_corrupted_rollback_segments。

3.bbed修改system header block
此方法不常见，仅供参考，请勿用于生产环境。

本文主要通过 bbed 工具模拟和解决ORA-600 [4193]问题。

模拟 ora-600[4193 故障:

SQL> select header_file,header_block from dba_segments where segment_name='SYSTEM';
HEADER_FILE HEADER_BLOCK
----------- ------------
          1          128

SQL> alter system dump datafile 1 block 128;
SQL> oradebug setmypid

SQL> oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_4016.trc

查看

[oracle@cjc-db-01 ~]$ vi /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_4016.trc
......
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 6      #blocks: 47
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x00400082  ext#: 0      blk#: 1      ext size: 7
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
                   Unlocked
     Map Header:: next  0x00000000  #extents: 6    obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x00400081  length: 7
   0x00400088  length: 8
   0x00400210  length: 8
   0x00400218  length: 8
   0x00400220  length: 8
   0x00400228  length: 8

  TRN CTL:: seq: 0x001e chd: 0x0038 ctl: 0x003d inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00400082.001e.0f scn: 0x0000.00115a53
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00400082.001e.0f ext: 0x0  spc: 0xffc
    uba: 0x00000000.001a.02 ext: 0x2  spc: 0x1f16
    uba: 0x00000000.0018.3a ext: 0x0  spc: 0x80e
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0018  0x0003  0x0000.0011601d  0x0040022e  0x0000.000.00000000  0x00000001   0x00000000
   0x01    9    0x00  0x0018  0x0007  0x0000.00116022  0x0040022f  0x0000.000.00000000  0x00000001   0x00000000
......
   0x61    9    0x00  0x0017  0x0005  0x0000.00116011  0x0040022e  0x0000.000.00000000  0x00000001   0x00000000

关闭数据库:

SQL> shutdown immediate

登录BBED，修改system header block

[oracle@cjc-db-01 ~]$ bbed
BBED> set file 1 block 128
BBED> map /v
BBED> p ktuxc
struct ktuxc, 104 bytes                     @4148    
   struct ktuxcscn, 8 bytes                 @4148    
      ub4 kscnbas                           @4148     0x00115a54
      ub2 kscnwrp                           @4152     0x0000
   struct ktuxcuba, 8 bytes                 @4156    
      ub4 kubadba                           @4156     0x00400082
      ub2 kubaseq                           @4160     0x001e
      ub1 kubarec                           @4162     0x10
......
   ub4 ktuxcopt                             @4188     0x7ffffffe
   struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes              @4192    
         ub4 kubadba                        @4192     0x00400082
         ub2 kubaseq                        @4196     0x001e
         ub1 kubarec                        @4198     0x19
      sb2 ktufbext                          @4200     0
      sb2 ktufbspc                          @4202     2000
......

查看

BBED> set dba 0x00400082
BBED> map /v
BBED> p ktubh
struct ktubh, 66 bytes                      @20      
   struct ktubhxid, 8 bytes                 @20      
      ub2 kxidusn                           @20       0x0000
      ub2 kxidslt                           @22       0x0038
      ub4 kxidsqn                           @24       0x00000018
   ub2 ktubhseq                             @28       0x001e
   ub1 ktubhcnt                             @30       0x19
......

修改 ktubhseq 值，由 1e 改成 1f

BBED> modify /x 1f offset 28
BBED> sum apply
BBED> p ktubh
......
ub2 ktubhseq                             @28       0x001f

启动数据库，模拟出ORA-600 [4193]问题:

SQL> startup
ORACLE instance started.

Total System Global Area  563691520 bytes
Fixed Size		    2255232 bytes
Variable Size		  222299776 bytes
Database Buffers	  335544320 bytes
Redo Buffers		    3592192 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],[], [], [], []
Process ID: 4816
Session ID: 1 Serial number: 5

查看告警日志:

[oracle@cjc-db-01 trace]$ tail -100f alert_cjc.log
Errors in file /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_4816.trc  (incident=164597):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/cjc/cjc/incident/incdir_164597/cjc_ora_4816_i164597.trc
......
Block recovery completed at rba 22.11456.16, scn 0.1143751
Undo initialization errored: err:600 serial:0 start:2199864 end:2202304 diff:2440 (24 seconds)
Errors in file /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_4816.trc:
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Errors in file /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_4816.trc:
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 4816): terminating the instance due to error 600
Instance terminated by USER, pid = 4816

继续查看 Incident 日志:

vi /oracle/app/oracle/diag/rdbms/cjc/cjc/incident/incdir_164597/cjc_ora_4816_i164597.trc
......
========= Dump for incident 164597 (ORA 600 [4193]) ========
----- Beginning of Customized Incident Dump(s) -----
*** ktuc_diag_dmp: dump of current change vector
ktudb redo: siz: 268 spc: 2000 flg: 0x0012 seq: 0x001e rec: 0x1a
            xid:  0x0000.03f.00000018
ktubl redo: slt: 63 rci: 0 opc: 11.1 [objn: 15 objd: 15 tsn: 0]

KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0000.038.00000018 uba: 0x00400082.001e.19

查看 10046 日志

SQL> startup mount
SQL> oradebug setmypid
SQL> oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_5198.trc
SQL> oradebug event 10046 trace name context forever,level 12
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],[], [], [], []
Process ID: 5198
Session ID: 1 Serial number: 5

[oracle@cjc-db-01 trace]$ vi /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_5198.trc
......
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
......
  TRN CTL:: seq: 0x001e chd: 0x003f ctl: 0x0038 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00400082.001e.10 scn: 0x0000.00115a54
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00400082.001e.19 ext: 0x0  spc: 0x7d0
    uba: 0x00000000.001a.02 ext: 0x2  spc: 0x1f16
    uba: 0x00000000.0018.3a ext: 0x0  spc: 0x80e
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0018  0x0003  0x0000.0011601d  0x0040022e  0x0000.000.00000000  0x00000001   0x00000000
......
   0x60    9    0x00  0x0017  0x005c  0x0000.00116025  0x0040022f  0x0000.000.00000000  0x00000001   0x00000000
   0x61    9    0x00  0x0017  0x0005  0x0000.00116011  0x0040022e  0x0000.000.00000000  0x00000001   0x00000000
KQRCMT: Write failed with error=600 po=0x7b7f6400 cid=3
diagnostics : cid=3 hash=35e74caf flag=2a
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []

解决方案:

设置free block pool：

1、ktuxc.ktuxcnfb设置为 0x00
2、kubadba设为0x00000000

BBED> set file 1 block 128
BBED> map /v
BBED> p ktuxc
......
sb2 ktuxcnfb                             @4168     1
...
struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes           @4192    
         ub4 kubadba                     @4192     0x00400082

修改:

BBED> modify /x 00 offset 4168
BBED> modify /x 0x00000000 offset 4192
BBED> sum apply

启动数据库:

SQL> startup
ORACLE instance started.

Total System Global Area  563691520 bytes
Fixed Size		    2255232 bytes
Variable Size		  222299776 bytes
Database Buffers	  335544320 bytes
Redo Buffers		    3592192 bytes
Database mounted.
Database opened.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
cjc		 OPEN

参考文章:
Oracle特殊恢复原理与实战_10 恢复ora-600[4193][4194]的错误
参考链接如下:

https://www.cnblogs.com/fchdb/articles/17255197.html

###chenjuchao 20241027###
欢迎关注我的公众号《IT小Chen》