ITPub博客

首页 > 数据库 > Oracle > [20181204]模拟ora-00600[4193]错误.txt

[20181204]模拟ora-00600[4193]错误.txt

原创 Oracle 作者:lfree 时间:2018-12-04 16:00:36 0 删除 编辑

[20181204]模拟ora-00600[4193]错误.txt


--//ora-600[4193]:表示undo和redo不一致(Arg [a] Undo record seq number,Arg [b] Redo record seq number )


--//数据库在启动时需要进行一个前滚的操作,在前滚时会应用redo 到undo block上,操作时会检查undo record里的seq#和redo

--//record里的seq#。正常情况下,这2者的seq# 应该是一致的,在一致的情况下,我们才应用redo record 到undo record。如果不一

--//致就会出现ORA-600[4193][a][b]的错误。其中a是undo里的seq#记录,b是redo里的seq#值。 这里的值都是十六进程,可以通过

--//to_number()函数来转换。


--//一般常规解决ora-00600[4193][4194]思路:

--//方法一:

--//修改参数如下:

undo_management='MANUAL'

undo_tablespace='SYSTEM'


--//运气好,这样就可以open数据库.然后建立新的表空间UNDOTBSNEW,切换到新undo_tablespace表空间.


--//再修改修改参数:

undo_management= AUTO

undo_tablespace= UNDOTBSNEW


--//重新启动数据库,一般这类的数据库最好重新导入/导出,因为运行中可能会报各种ora-00600错误.


--//方法二:

--//利用隐含参数_offline_rollback_segments和_corrupted_rollback_segments屏蔽掉有问题的undo segment,然后打开数据库,最后

--//重建undo或者drop掉损坏的回滚鼹即可

undo_management='MANUAL'

undo_tablespace='SYSTEM'

*._offline_rollback_segments=('_SYSSMU1_3724004606$', '_SYSSMU2_2996391332$', '_SYSSMU3_1723003836$', '_SYSSMU4_1254879796$', '_SYSSMU5_898567397$','_SYSSMU6_1263032392$', '_SYSSMU7_2070203016$', '_SYSSMU8_517538920$', '_SYSSMU9_1650507775$', '_SYSSMU10_1197734989$');

*._corrupted_rollback_segments=('_SYSSMU1_3724004606$', '_SYSSMU2_2996391332$', '_SYSSMU3_1723003836$', '_SYSSMU4_1254879796$', '_SYSSMU5_898567397$','_SYSSMU6_1263032392$', '_SYSSMU7_2070203016$', '_SYSSMU8_517538920$', '_SYSSMU9_1650507775$', '_SYSSMU10_1197734989$');


--//其它同方法一.

--//如果问题发生在system回滚段,就不能按照上面的方法处理.看了网站介绍.

--//链接:archives/【oracle数据恢复】通过bbed修复ora-6004193和ora-6004194的例子.html


--//通过bbed修改系统回滚段的2个参数.在sum apply就ok了.

ktuxc.ktuxcnfb=0x0000

ktuxc.ktuxcfbp[0].ktufbuba.kubadba=0x00000000


--//反向思维一下,我想既然可以这样方法修复,也可以利用同样模拟问题的产生.通过测试环境测试看看.


1.环境:

SYS@book> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


SYS@book> select HEADER_FILE,HEADER_BLOCK,BLOCKS,EXTENTS from dba_segments where segment_name='SYSTEM' and SEGMENT_TYPE='ROLLBACK';

HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS

----------- ------------ ---------- ----------

          1          128         48          6


--//system表空间使用MSSM,bbed可以访问段头.

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.


SYS@book> startup mount

ORACLE instance started.

Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 bytes

Database mounted.


SYS@book> alter system dump datafile '/mnt/ramdisk/book/system01.dbf' block 128;

System altered.


  Extent Control Header

  -----------------------------------------------------------------

  Extent Header:: spare1: 0      spare2: 0      #extents: 6      #blocks: 47

                  last map  0x00000000  #maps: 0      offset: 4128

      Highwater::  0x00400086  ext#: 0      blk#: 5      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: 0x002a chd: 0x002c ctl: 0x0035 inc: 0x00000000 nfb: 0x0001

                                                                ~~~~~~~~~~~

            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

            uba: 0x00400086.002a.13 scn: 0x0003.37750657

Version: 0x01

  FREE BLOCK POOL::

    uba: 0x00400086.002a.1c ext: 0x0  spc: 0x4a6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    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  0x002b  0x0003  0x0003.37752d36  0x00400084  0x0000.000.00000000  0x00000001   0x00000000

...

   0x60    9    0x00  0x002a  0x005c  0x0003.37752d44  0x00400084  0x0000.000.00000000  0x00000001   0x00000000

   0x61    9    0x00  0x002a  0x0005  0x0003.37752d30  0x00400084  0x0000.000.00000000  0x00000001   0x00000000

End dump data block from file /mnt/ramdisk/book/system01.dbf minblk 128 maxblk 128


--//注意下划线部分内容与bbed的观察对上.关闭数据库.


2.通过bbed观察:

BBED> set dba 1,128

        DBA             0x00400080 (4194432 1,128)


BBED> p ktuxc

struct ktuxc, 104 bytes                     @4148

   struct ktuxcscn, 8 bytes                 @4148

      ub4 kscnbas                           @4148     0x37750657

      ub2 kscnwrp                           @4152     0x0003

   struct ktuxcuba, 8 bytes                 @4156

      ub4 kubadba                           @4156     0x00400086

      ub2 kubaseq                           @4160     0x002a

      ub1 kubarec                           @4162     0x13

   sb2 ktuxcflg                             @4164     1 (KTUXCFSK)

   ub2 ktuxcseq                             @4166     0x002a

   sb2 ktuxcnfb                             @4168     1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   ub4 ktuxcinc                             @4172     0x00000000

   sb2 ktuxcchd                             @4176     44

   sb2 ktuxcctl                             @4178     53

   ub2 ktuxcmgc                             @4180     0x8002

   ub4 ktuxcopt                             @4188     0x7ffffffe

   struct ktuxcfbp[0], 12 bytes             @4192

      struct ktufbuba, 8 bytes              @4192

         ub4 kubadba                        @4192     0x00400086

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

         ub2 kubaseq                        @4196     0x002a

         ub1 kubarec                        @4198     0x1c

      sb2 ktufbext                          @4200     0

      sb2 ktufbspc                          @4202     1190

   struct ktuxcfbp[1], 12 bytes             @4204

      struct ktufbuba, 8 bytes              @4204

         ub4 kubadba                        @4204     0x00000000

         ub2 kubaseq                        @4208     0x001a

         ub1 kubarec                        @4210     0x02

      sb2 ktufbext                          @4212     2

      sb2 ktufbspc                          @4214     7958

   struct ktuxcfbp[2], 12 bytes             @4216

      struct ktufbuba, 8 bytes              @4216

         ub4 kubadba                        @4216     0x00000000

         ub2 kubaseq                        @4220     0x0018

         ub1 kubarec                        @4222     0x3a

      sb2 ktufbext                          @4224     0

      sb2 ktufbspc                          @4226     2062

   struct ktuxcfbp[3], 12 bytes             @4228

      struct ktufbuba, 8 bytes              @4228

         ub4 kubadba                        @4228     0x00000000

         ub2 kubaseq                        @4232     0x0000

         ub1 kubarec                        @4234     0x00

      sb2 ktufbext                          @4236     0

      sb2 ktufbspc                          @4238     0

   struct ktuxcfbp[4], 12 bytes             @4240

      struct ktufbuba, 8 bytes              @4240

         ub4 kubadba                        @4240     0x00000000

         ub2 kubaseq                        @4244     0x0000

         ub1 kubarec                        @4246     0x00

      sb2 ktufbext                          @4248     0

      sb2 ktufbspc                          @4250     0


--//注意看下划线,如果出现问题,正常就是修改这2处.


BBED> set dba 0x00400086

        DBA             0x00400086 (4194438 1,134)


BBED> p ktubh

struct ktubh, 72 bytes                      @20

   struct ktubhxid, 8 bytes                 @20

      ub2 kxidusn                           @20       0x0000

      ub2 kxidslt                           @22       0x0035

      ub4 kxidsqn                           @24       0x0000002b

   ub2 ktubhseq                             @28       0x002a

   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   ub1 ktubhcnt                             @30       0x1c

   ub1 ktubhirb                             @31       0x1c

   ub1 ktubhicl                             @32       0x00

   ub1 ktubhflg                             @33       0x00

   ub2 ktubhidx[0]                          @34       0x1fe8

   ub2 ktubhidx[1]                          @36       0x1edc

   ub2 ktubhidx[2]                          @38       0x1dd0

   ub2 ktubhidx[3]                          @40       0x1cc4

   ub2 ktubhidx[4]                          @42       0x1bb8

   ub2 ktubhidx[5]                          @44       0x1aac

   ub2 ktubhidx[6]                          @46       0x19a0

   ub2 ktubhidx[7]                          @48       0x1894

   ub2 ktubhidx[8]                          @50       0x1788

   ub2 ktubhidx[9]                          @52       0x167c

   ub2 ktubhidx[10]                         @54       0x1570

   ub2 ktubhidx[11]                         @56       0x1464

   ub2 ktubhidx[12]                         @58       0x1358

   ub2 ktubhidx[13]                         @60       0x124c

   ub2 ktubhidx[14]                         @62       0x1140

   ub2 ktubhidx[15]                         @64       0x1034

   ub2 ktubhidx[16]                         @66       0x0f28

   ub2 ktubhidx[17]                         @68       0x0e18

   ub2 ktubhidx[18]                         @70       0x0d08

   ub2 ktubhidx[19]                         @72       0x0bf8

   ub2 ktubhidx[20]                         @74       0x0b30

   ub2 ktubhidx[21]                         @76       0x0a68

   ub2 ktubhidx[22]                         @78       0x09a0

   ub2 ktubhidx[23]                         @80       0x08d8

   ub2 ktubhidx[24]                         @82       0x0810

   ub2 ktubhidx[25]                         @84       0x0748

   ub2 ktubhidx[26]                         @86       0x0680

   ub2 ktubhidx[27]                         @88       0x05b8

   ub2 ktubhidx[28]                         @90       0x04f0


--//注意看下划线与如下dba 1,128  ktuxc.ktuxcfbp[0].ktufbuba.kubaseq一致:

BBED> p dba 1,128  ktuxc.ktuxcfbp[0].ktufbuba.kubaseq

ub2 kubaseq                                 @4196     0x002a


--//修改如下:

BBED> set dba 0x00400086

        DBA             0x00400086 (4194438 1,134)


BBED> p  dba 1,134 ktubh.ktubhseq

ub2 ktubhseq                                @28       0x002a


BBED> assign  dba 1,134 ktubh.ktubhseq=0x002b

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

ub2 ktubhseq                                @28       0x002b


BBED> sum apply dba 1,134

Check value for File 1, Block 134:

current = 0x7b48, required = 0x7b48


SYS@book> startup

ORACLE instance started.

Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []

Process ID: 24151

Session ID: 274 Serial number: 3


--//检查alert.log可以发现如下错误:

Undo initialization errored: err:600 serial:0 start:3978938248 end:3978939378 diff:1130 (11 seconds)

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24151.trc:

ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24151.trc:

ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []

Error 600 happened during db open, shutting down database

USER (ospid: 24151): terminating the instance due to error 600

Instance terminated by USER, pid = 24151

ORA-1092 signalled during: ALTER DATABASE OPEN...

opiodr aborting process unknown ospid (24151) as a result of ORA-1092

Tue Dec 04 12:05:35 2018

ORA-1092 : opitsk aborting process


--//很奇怪这样的模拟看不到后面的[a][b]参数.


--//继续测试,改小看看

BBED> assign  dba 1,134 ktubh.ktubhseq=0x0029

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

ub2 ktubhseq                                @28       0x0029


BBED> sum apply dba 1,134

Check value for File 1, Block 134:

current = 0x7b4a, required = 0x7b4a


SYS@book> startup

ORACLE instance started.

Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []

Process ID: 24262

Session ID: 274 Serial number: 3


--//一样产生ora-00600[4193]错误.

--//alert.log记录如下:

Undo initialization errored: err:600 serial:0 start:3979178998 end:3979180418 diff:1420 (14 seconds)

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24262.trc:

ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24262.trc:

ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []

Error 600 happened during db open, shutting down database

USER (ospid: 24262): terminating the instance due to error 600

Instance terminated by USER, pid = 24262

ORA-1092 signalled during: ALTER DATABASE OPEN...

opiodr aborting process unknown ospid (24262) as a result of ORA-1092

Tue Dec 04 12:09:36 2018

ORA-1092 : opitsk aborting process


--//修改回来.

BBED> assign  dba 1,134 ktubh.ktubhseq=0x002a

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

ub2 ktubhseq                                @28       0x002a


BBED> sum apply dba 1,134

Check value for File 1, Block 134:

current = 0x7b49, required = 0x7b49


SYS@book> startup open read only;

ORACLE instance started.


Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 bytes

Database mounted.

ORA-16005: database requires recovery

--//已经不能只读打开.


SYS@book> select open_mode from v$database ;

OPEN_MODE

---------

MOUNTED


SYS@book> alter database open ;

Database altered.


3.测试在正常状态下修改如何?

--//关闭数据库.

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.


--//设置free block pool,也就是假设free block pool无可用的undo block.


BBED> set dba 1,128

        DBA             0x00400080 (4194432 1,128)


BBED> p ktuxc

struct ktuxc, 104 bytes                     @4148

   struct ktuxcscn, 8 bytes                 @4148

      ub4 kscnbas                           @4148     0x377509ad

      ub2 kscnwrp                           @4152     0x0003

   struct ktuxcuba, 8 bytes                 @4156

      ub4 kubadba                           @4156     0x00400087

      ub2 kubaseq                           @4160     0x002a

      ub1 kubarec                           @4162     0x12

   sb2 ktuxcflg                             @4164     1 (KTUXCFSK)

   ub2 ktuxcseq                             @4166     0x002a

   sb2 ktuxcnfb                             @4168     1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   ub4 ktuxcinc                             @4172     0x00000000

   sb2 ktuxcchd                             @4176     55

   sb2 ktuxcctl                             @4178     60

   ub2 ktuxcmgc                             @4180     0x8002

   ub4 ktuxcopt                             @4188     0x7ffffffe

   struct ktuxcfbp[0], 12 bytes             @4192

      struct ktufbuba, 8 bytes              @4192

         ub4 kubadba                        @4192     0x00400087

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

         ub2 kubaseq                        @4196     0x002a

         ub1 kubarec                        @4198     0x1b

      sb2 ktufbext                          @4200     0

      sb2 ktufbspc                          @4202     1460


--//修改下划线内容.

assign dba 1,128 ktuxc.ktuxcnfb=0x0000

assign dba 1,128 ktuxc.ktuxcfbp[0].ktufbuba.kubadba=0x00000000


BBED> assign dba 1,128 ktuxc.ktuxcnfb=0x0000

sb2 ktuxcnfb                                @4168     0


BBED> assign dba 1,128 ktuxc.ktuxcfbp[0].ktufbuba.kubadba=0x00000000

ub4 kubadba                                 @4192     0x00000000


BBED> sum apply dba 1,128

Check value for File 1, Block 128:

current = 0xbae1, required = 0xbae1


SYS@book> startup open read only;

ORACLE instance started.

Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 bytes

Database mounted.

Database opened.


--//最后总结方法三:

assign dba 1,128 ktuxc.ktuxcnfb=0x0000

assign dba 1,128 ktuxc.ktuxcfbp[0].ktufbuba.kubadba=0x00000000


--//我估计如果不是ktuxc.ktuxcnfb=0x0001,比如是0x0002.

--//可能还要清空ktuxc.ktuxcfbp[1].ktufbuba.kubadba=0x00000000.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2284078/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2859
  • 访问量
    6646433