[20181105]ORA-00600[4000] 模拟故障(10g).txt

[20181105]ORA-00600[4000] 模拟故障(10g).txt

--//利用10912事件模拟ora-00600[400]错误.

http://www.traveldba.com/archives/650

1.环境:

SYS@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

$ oerr ora 10912

10912, 00000, "Used to perform admin operations on locally managed SYSTEM tablespace"

// *Cause: N/A

// *Action: The event should be used only under the supervision of

//          Oracle Support

--//利用10912事件,产生这个错误.千万不要在生产系统做这个测试!!

SYS@test> Alter session set events '10912 trace name context forever, level 1';

Session altered.

SYS@test> exec dbms_space_admin.tablespace_fix_segment_extblks('SYSTEM');

PL/SQL procedure successfully completed.

SYS@test> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

2.问题产生:

SYS@test> startup

ORACLE instance started.

Total System Global Area  486539264 bytes

Fixed Size                  2084872 bytes

Variable Size             360714232 bytes

Database Buffers          113246208 bytes

Redo Buffers               10493952 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

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

Mon Nov  5 11:22:11 2018

Errors in file /u01/app/oracle/admin/test/udump/test_ora_6098.trc:

ORA-00600: internal error code, arguments: [4000], [10], [], [], [], [], [], []

Mon Nov  5 11:22:12 2018

Errors in file /u01/app/oracle/admin/test/udump/test_ora_6098.trc:

ORA-00704: bootstrap process failure

ORA-00600: internal error code, arguments: [4000], [10], [], [], [], [], [], []

Mon Nov  5 11:22:12 2018

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 6098

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

$ oerr ora 1092

01092, 00000, "ORACLE instance terminated. Disconnection forced"

// *Cause:  The instance this process was connected to was terminated

//          abnormally, probably via a shutdown abort. This process

//          was forced to disconnect from the instance.

// *Action: Examine the alert log for more details. When the instance has been

//          restarted, retry action.

3.使用10046跟踪:

SYS@test> startup mount ;

ORACLE instance started.

Total System Global Area  486539264 bytes

Fixed Size                  2084872 bytes

Variable Size             360714232 bytes

Database Buffers          113246208 bytes

Redo Buffers               10493952 bytes

Database mounted.

SYS@test> @ &r/10046on 12

old   1: alter session set events '10046 trace name context forever, level &1'

new   1: alter session set events '10046 trace name context forever, level 12'

Session altered.

SYS@test> alter database open ;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

SYS@test> @ &r/10046off

ERROR:

ORA-03114: not connected to ORACLE

--//检查跟踪文件可以发现如下:

Permanent space    =        0

******************************************************

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

Cursor#2(0x2afbbb3a1778) state=FETCH curiob=0x2afbbb3ba1f0

 curflg=7 fl2=0 par=0x2afbbb3a1710 ses=0x7cf7ec98

 sqltxt(0x7cab5f00)=select line#, sql_text from bootstrap$ where obj# != :1

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

  hash=3216f21716c3cb546556c2bc7dd9f6b1

  parent=0x773dbc90 maxchild=01 plk=0x77b947f0 ppn=n

cursor instantiation=0x2afbbb3ba1f0 used=1541388230

 child#0(0x7ca9f7e0) pcs=0x773db8a0

  clk=0x77b94008 ci=0x773daf88 pn=0x7caaad50 ctx=0x76fbae08

 kgsccflg=0 llk[0x2afbbb3ba1f8,0x2afbbb3ba1f8] idx=0

 xscflg=e01414f6 fl2=45000401 fl3=4022210c fl4=100

 Bind bytecodes

  Opcode = 1   Unoptimized

  Offsi = 48, Offsi = 0

kkscoacd

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=2afbbb3b9da8  bln=22  avl=02  flg=05

  value=56

 Frames pfr 0x2afbbb3b9ed8 siz=10760 efr 0x2afbbb3b9f40 siz=10648

 Cursor frame dump

  enxt: 5.0x00000010  enxt: 4.0x00000fa0  enxt: 3.0x00000fe8  enxt: 2.0x00000020

  enxt: 1.0x000009f0

  pnxt: 2.0x00000008  pnxt: 1.0x00000068

 kxscphp  0x2afbbb3b0318 siz=984 inu=872 nps=824

 kxscdfhp 0x2afbbb3b06d8 siz=984 inu=88 nps=0

 kxscbhp  0x2afbbb3b04f8 siz=984 inu=168 nps=48

 kxscwhp  0x2afbbb3b05e8 siz=4056 inu=360 nps=0

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

  

...

Dump event group for SESSION

10046 trace name CONTEXT level 12, forever

Dump event group for SYSTEM

FETCH #2:c=1289804,e=1264847,p=1,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1505261945434858

ORA-00704: bootstrap process failure

ORA-00600: internal error code, arguments: [4000], [10], [], [], [], [], [], []

EXEC #1:c=1322799,e=2719711,p=138,cr=2,cu=0,mis=0,r=0,dep=0,og=1,tim=1505261946415577

ERROR #1:err=1092 tim=1005842347

--//实际上看到ORA-00704: bootstrap process failure很容易联想到sys.bootstrap$表存在问题.在仔细看如何发现如下:

WAIT #1: nam='db file sequential read' ela= 22 file#=1 block#=377 blocks=1 obj#=-1 tim=1505261944166703

=====================

PARSING IN CURSOR #2 len=188 dep=1 uid=0 oct=1 lid=0 tim=1505261944167689 hv=1365064427 ad='7caa0138'

create table bootstrap$ ( line#         number not null,   obj#           number not null,   sql_text   varchar2(4000) not null)   storage (initial 50K objno 56 extents (file 1 block 377))

END OF STMT

PARSE #2:c=1000,e=847,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1505261944167683

BINDS #2:

EXEC #2:c=0,e=238,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1505261944167997

=====================

PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=1505261944168762 hv=2111436465 ad='7cab5f00'

select line#, sql_text from bootstrap$ where obj# != :1

END OF STMT

PARSE #2:c=1000,e=639,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1505261944168757

BINDS #2:

kkscoacd

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=2afbbb3b9da8  bln=22  avl=02  flg=05

  value=56

EXEC #2:c=1000,e=1065,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1505261944169965

WAIT #2: nam='db file sequential read' ela= 33 file#=1 block#=377 blocks=1 obj#=-1 tim=1505261944170098

*** 2018-11-05 11:23:50.830

ksedmp: internal or fatal error

ORA-00600: internal error code, arguments: [4000], [10], [], [], [], [], [], []

Current SQL statement for this session:

select line#, sql_text from bootstrap$ where obj# != :1

--//执行这个select line#, sql_text from bootstrap$ where obj# != :1时发生错误(参数:1=56)

4.问题解决:

--//问题应该在WAIT #1: nam='db file sequential read' ela= 22 file#=1 block#=377 blocks=1 obj#=-1 tim=1505261944166703,时发生错误.

--//dba = 1,377 应该是bootstrap$的段头.

BBED> p  dba 1,1  kcvfh.kcvfhrdb

ub4 kcvfhrdb                                @96       0x00400179

BBED> set dba 0x00400179

        DBA             0x00400179 (4194681 1,377)

--//可以对上.

BBED> set dba 1,377

        DBA             0x00400179 (4194681 1,377)

BBED> p ktech.locker_ktech

struct locker_ktech, 8 bytes                @80

   ub2 kxidusn                              @80       0x000a

   ub2 kxidslt                              @82       0x0009

   ub4 kxidsqn                              @84       0x00012787

BBED> p ktech.flag_ktech

ub4 flag_ktech                              @88       0x00000001 (NONE)

/*  kxidusn 对应 undo seg number  */

/*  kxidslt 对应 slot number  */

/*  kxidsqn 对应 wrap number  */

/*  flag_ktech 对应 lock flag  */

--//可以发现上面有事务,实际上问题在于没有提交.设置为0就ok了.

assign dba 1,377 ktech.locker_ktech.kxidusn= 0

assign dba 1,377 ktech.locker_ktech.kxidslt= 0

assign dba 1,377 ktech.locker_ktech.kxidsqn= 0

assign dba 1,377 ktech.flag_ktech= 0

BBED> assign dba 1,377 ktech.locker_ktech.kxidusn= 0

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

ub2 kxidusn                                 @80       0x0000

BBED> assign dba 1,377 ktech.locker_ktech.kxidslt= 0

ub2 kxidslt                                 @82       0x0000

BBED> assign dba 1,377 ktech.locker_ktech.kxidsqn= 0

ub4 kxidsqn                                 @84       0x00000000

BBED> assign dba 1,377 ktech.flag_ktech= 0

ub4 flag_ktech                              @88       0x00000000 (NONE)

BBED> p ktech.locker_ktech

struct locker_ktech, 8 bytes                @80

   ub2 kxidusn                              @80       0x0000

   ub2 kxidslt                              @82       0x0000

   ub4 kxidsqn                              @84       0x00000000

BBED> p ktech.flag_ktech

ub4 flag_ktech                              @88       0x00000000 (NONE)

--//OK修改完成,

BBED> sum apply dba 1,377

Check value for File 1, Block 377:

current = 0x9a39, required = 0x9a39

5.重新启动看看:

SYS@test> startup

ORACLE instance started.

Total System Global Area  486539264 bytes

Fixed Size                  2084872 bytes

Variable Size             360714232 bytes

Database Buffers          113246208 bytes

Redo Buffers               10493952 bytes

Database mounted.

Database opened.

--//OK,现在已经修复.注意在11.2.0.4下以上命令无法模拟ora-00600[4000]错误.

--//11.2.0.3估计可以,没有环境无法测试.以下11.2.0.4测试:

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> Alter session set events '10912 trace name context forever, level 4';

Session altered.

SYS@book> exec dbms_space_admin.tablespace_fix_segment_extblks('SYSTEM');

BEGIN dbms_space_admin.tablespace_fix_segment_extblks('SYSTEM'); END;

*

ERROR at line 1:

ORA-10911: Locally managed SYSTEM tablespace bitmaps can be modified only under the supervision of Oracle Support

ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 309

ORA-06512: at line 1

--//设置level=4,报错.如果设置level=1

SYS@book> Alter session set events '10912 trace name context forever, level 1';

Session altered.

SYS@book> exec dbms_space_admin.tablespace_fix_segment_extblks('SYSTEM');

PL/SQL procedure successfully completed.

--//不报错,但是重新启动正常,无法模拟.

SYS@book> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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.

Database opened.

--//11.2.0.4 没有模拟出来.

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

转载于:http://blog.itpub.net/267265/viewspace-2218712/