[20181106]模拟ora-00600[4194]错误.txt

最新推荐文章于 2024-03-24 20:34:46 发布

cuixun7780

最新推荐文章于 2024-03-24 20:34:46 发布

阅读量135

点赞数

[20181106]模拟ora-00600[4194]错误.txt

ORA-600[4194]内部错误一般由重做记录与回滚记录不匹配引发。Oracle在验证Undo record number时，会对比redo change 和回滚段中

的undo record number，若发现2者存在差异则报该4194错误。其错误argument[a][b]，a代表回滚块中的最大undo record number，b代

表重做日志中记录的undo record number。这个错误可能由回滚段或者redo log日志文件讹误引起。

ORA-00600[4194]错误的根本原因是 redo记录与回滚段(rollback/undo)记录之间的不一致。当ORACLE在验证undo记录时相对应的变化需

要应用到undo数据块的最大undo记录上，此时若检验出错则会报ORA-00600[4194]

此错误不像ORA-600[2662]或ORA-600[4000]错误那样必然导致数据库无法打开，因为它很少出现在前滚阶段；当数据库被打开，smon开始

执行事务恢复或一些回滚段的管理工作时则很有可能触发该错误。

--//花一点点时间重新读了链接:

http://www.askmaclean.com/archives/%e3%80%90oracle%e6%95%b0%e6%8d%ae%e6%81%a2%e5%a4%8d%e3%80%91%e9%80%9a%e8%bf%87bbed%e4%bf%ae%e5%a4%8dora-6004193%e5%92%8cora-6004194%e7%9a%84%e4%be%8b%e5%ad%90.html

如果4193/4194 相关的undo/rollback block在system rollback segment，则不可以使用隐藏参数来绕过该问题

_CORRUPTED_ROLLBACK_SEGMENTS隐藏参数。

--//常规方法就是设置_CORRUPTED_ROLLBACK_SEGMENTS参数,但是对于system rollback segment无效,因为system回滚段无法offline以及

--//在_CORRUPTED_ROLLBACK_SEGMENTS参数里面设定.

--//我看链接实际上对于system回滚段执行如下.

assign ktuxc.ktuxcnfb=0x0000

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

--//assign ktuxc.ktuxcfbp[1].ktufbuba=0x00000000

sum apply

--//应该就ok了.

--//这样应该可以模拟该错误.

1.环境:

SCOTT@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

2.建立测试环境:

--//session 1:

SCOTT@book> create table deptx as select * from dept ;

Table created.

SCOTT@book> update deptx set dname = lower(dname) where deptno=10;

1 row updated.

SCOTT@book> @ xid

XIDUSN_XIDSLOT_XIDSQN

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

10.3.20183

C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG

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

ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 3 20183; 10 3 20183 3 1278 3879 20 ACTIVE 1 1 0A000300D74E0000 00000000823D1B68 2018-11-06 08:46:32 3587

ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';

ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1278;

--//确定使用回滚段_SYSSMU10_1197734989$.

--//session 2:

SYS@book> alter system checkpoint;

System altered.

SYS@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';

System altered.

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

Undo Segment: _SYSSMU10_1197734989$ (10)

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

Extent Control Header

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

Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271

last map 0x00000000 #maps: 0 offset: 4080

Highwater:: 0x00c004fe ext#: 3 blk#: 126 ext size: 128

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 3

Unlocked

Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000

Extent Map

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

0x00c00111 length: 7

0x00c000a8 length: 8

0x00c00280 length: 128

0x00c00480 length: 128

Retention Table

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

Extent Number:0 Commit Time: 1541451633

Extent Number:1 Commit Time: 1541451633

Extent Number:2 Commit Time: 1541458835

Extent Number:3 Commit Time: 1541458835

TRN CTL:: seq: 0x0f27 chd: 0x0007 ctl: 0x0000 inc: 0x00000000 nfb: 0x0001

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

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

uba: 0x00c004fe.0f27.14 scn: 0x0003.17621299

Version: 0x01

FREE BLOCK POOL::

uba: 0x00000000.0f27.13 ext: 0x3 spc: 0x1760

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

uba: 0x00c004fd.0f27.1a ext: 0x3 spc: 0x13fa

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

uba: 0x00000000.0f27.20 ext: 0x3 spc: 0x7b6

uba: 0x00000000.0d09.38 ext: 0x2 spc: 0x64e

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

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

0x00 9 0x00 0x4ed7 0xffff 0x0003.17621409 0x00c004fe 0x0000.000.00000000 0x00000001 0x00000000 1541465177

0x01 9 0x00 0x4ed2 0x000d 0x0003.176212b6 0x00c004f9 0x0000.000.00000000 0x00000001 0x00000000 1541465094

0x02 9 0x00 0x4ed8 0x0009 0x0003.176213c9 0x00c004fd 0x0000.000.00000000 0x00000001 0x00000000 1541465156

0x03 10 0x80 0x4ed7 0x0003 0x0003.17621409 0x00c004fe 0x0000.000.00000000 0x00000001 0x00000000 0

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

--//注意看下划线内容.

--//session 1:

SCOTT@book> commit;

Commit complete.

--//session 2:

SYS@book> alter system checkpoint;

System altered.

SYS@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';

System altered.

--//检查转储

TRN CTL:: seq: 0x0f27 chd: 0x0007 ctl: 0x0003 inc: 0x00000000 nfb: 0x0002

~~~~~~~~~~~

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

uba: 0x00c004fe.0f27.14 scn: 0x0003.17621299

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

Version: 0x01

FREE BLOCK POOL::

uba: 0x00c004fe.0f27.14 ext: 0x3 spc: 0x16b2

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

uba: 0x00c004fd.0f27.1a ext: 0x3 spc: 0x13fa

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

uba: 0x00000000.0f27.20 ext: 0x3 spc: 0x7b6

uba: 0x00000000.0d09.38 ext: 0x2 spc: 0x64e

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

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

0x00 9 0x00 0x4ed7 0x0003 0x0003.17621409 0x00c004fe 0x0000.000.00000000 0x00000001 0x00000000 1541465177

0x01 9 0x00 0x4ed2 0x000d 0x0003.176212b6 0x00c004f9 0x0000.000.00000000 0x00000001 0x00000000 1541465094

0x02 9 0x00 0x4ed8 0x0009 0x0003.176213c9 0x00c004fd 0x0000.000.00000000 0x00000001 0x00000000 1541465156

0x03 9 0x00 0x4ed7 0xffff 0x0003.17621555 0x00c004fe 0x0000.000.00000000 0x00000001 0x00000000 1541465492

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=> 已经提交.

SYS@book> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='_SYSSMU10_1197734989$';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK

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

_SYSSMU10_1197734989$ 3 272

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/undotbs01.dbf' block 272;

System altered.

TRN CTL:: seq: 0x0f28 chd: 0x0018 ctl: 0x0010 inc: 0x00000000 nfb: 0x0001

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

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

uba: 0x00c00114.0f28.15 scn: 0x0003.176216a8

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

Version: 0x01

FREE BLOCK POOL::

uba: 0x00c00114.0f28.15 ext: 0x0 spc: 0x13b8

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

uba: 0x00000000.0f27.1a ext: 0x3 spc: 0x13fa

uba: 0x00000000.0f27.20 ext: 0x3 spc: 0x7b6

uba: 0x00000000.0d09.38 ext: 0x2 spc: 0x64e

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

3.模拟:

--//0x00c00114=12583188,12583188= alter system dump datafile 3 block 276

BBED> set dba 3,276

DBA 0x00c00114 (12583188 3,276)

BBED> p ktubh.ktubhidx[14]

ub2 ktubhidx[14] @62 0x18c4

BBED> p ktubh.ktubhidx[15]

ub2 ktubhidx[15] @64 0x1824

--// 0x18c4=6340 0x1824=6180 ,6340-6180 = 160,要回退160.

--// 0x13b8=5048 5048+160 = 5208 5208=0x1458

uba: 0x00c00114.0f28.15 ext: 0x0 spc: 0x13b8

--//修改为

uba: 0x00c00114.0f28.14 ext: 0x0 spc: 0x1458

--//272*8192 = 2228224,bbed无法识别undo的块信息,我使用bvi修改.:set columns=40,记住最后使用bbed重新计算检查和.

$ bvi -b 2228224 -s 8192 /mnt/ramdisk/book/undotbs01.dbf

--//0x00c00114.0f28.15 反转 1401c000280f15

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

System altered.

TRN CTL:: seq: 0x0f28 chd: 0x0018 ctl: 0x0010 inc: 0x00000000 nfb: 0x0001

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

uba: 0x00c00114.0f28.14 scn: 0x0003.176216a8

Version: 0x01

FREE BLOCK POOL::

uba: 0x00c00114.0f28.14 ext: 0x0 spc: 0x1458

uba: 0x00000000.0f27.1a ext: 0x3 spc: 0x13fa

uba: 0x00000000.0f27.20 ext: 0x3 spc: 0x7b6

uba: 0x00000000.0d09.38 ext: 0x2 spc: 0x64e

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

TRN TBL::

--//OK修改完成.

4.打开数据库看看.

SYS@book> alter database open ;

Database altered.

--//为了测试使用该回滚段,设置事务指定回滚段.

alter system set "_smu_debug_mode" = 45 scope=memory;

set transaction use rollback segment "_SYSSMU10_1197734989$";

update deptx set dname = lower(dname) where deptno=20;

--//我的测试还没有执行以上命令,就出现以下错误.

ERROR:ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],[], [], [], []

--//不过我的测试没有后面的[a],[b]参数.

4.修复:

--//简单一点就是设置参数_CORRUPTED_ROLLBACK_SEGMENTS.

SYS@book> create pfile='/tmp/@.ora' from spfile;

File created.

--//修改/tmp/book.ora文件,加入:

*._CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU10_1197734989$)

SYS@book> startup pfile='/tmp/@.ora'

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.

SYS@book> select * from v$rollname;

USN NAME

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

0 SYSTEM

1 _SYSSMU1_3724004606$

2 _SYSSMU2_2996391332$

3 _SYSSMU3_1723003836$

4 _SYSSMU4_1254879796$

5 _SYSSMU5_898567397$

6 _SYSSMU6_1263032392$

7 _SYSSMU7_2070203016$

8 _SYSSMU8_517538920$

9 _SYSSMU9_1650507775$

10 rows selected.

--//没有_SYSSMU10_1197734989$.

SYS@book> select * from sys.undo$;

US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# UGRP# KEEP OPTIMAL FLAGS SPARE1

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

0 SYSTEM 0 1 128 0 0 0 0 0 3 0 0

1 _SYSSMU1_3724004606$ 1 3 128 392345366 3 1516 763 0 3 2 2

2 _SYSSMU2_2996391332$ 1 3 144 392345117 3 1742 585 0 3 2 2

3 _SYSSMU3_1723003836$ 1 3 160 392345119 3 1728 942 0 3 2 2

4 _SYSSMU4_1254879796$ 1 3 176 392345363 3 1524 817 0 3 2 2

5 _SYSSMU5_898567397$ 1 3 192 392345162 3 1751 1083 0 3 2 2

6 _SYSSMU6_1263032392$ 1 3 208 392345123 3 1894 863 0 3 2 2

7 _SYSSMU7_2070203016$ 1 3 224 392345148 3 1517 703 0 3 2 2

8 _SYSSMU8_517538920$ 1 3 240 392345158 3 1780 891 0 3 2 2

9 _SYSSMU9_1650507775$ 1 3 256 392345165 3 2674 924 0 3 2 2

10 _SYSSMU10_1197734989$ 1 3 272 392304394 3 20195 3881 0 5 2 2

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

11 _SYSSMU11_894599432$ 1 5 128 923330 0 2 1 0 1 5 2

12 _SYSSMU12_1573055333$ 1 5 144 0 0 1 1 0 1 5 2

13 _SYSSMU13_3860906822$ 1 5 160 923661 0 2 1 0 1 5 2

14 _SYSSMU14_3319140121$ 1 5 176 923323 0 2 1 0 1 5 2

15 _SYSSMU15_1436577151$ 1 5 192 923332 0 2 1 0 1 5 2

16 _SYSSMU16_1689093467$ 1 5 208 923314 0 2 1 0 1 5 2

17 _SYSSMU17_1049158485$ 1 5 224 923296 0 2 1 0 1 5 2

18 _SYSSMU18_1557221903$ 1 5 240 923320 0 2 1 0 1 5 2

19 _SYSSMU19_2284825117$ 1 5 256 923294 0 2 1 0 1 5 2

20 _SYSSMU20_2312497597$ 1 5 272 923262 0 2 1 0 1 5 2

21 rows selected.

--//STATUS$=5.

5.选择手工修复后.

$ bvi -b 2228224 -s 8192 /mnt/ramdisk/book/undotbs01.dbf

--//1401c000280f15=>1401c000280f14

--//5814 => b813

BBED> set dba 3,272

DBA 0x00c00110 (12583184 3,272)

BBED> sum apply ;

Check value for File 3, Block 272:

current = 0x43c5, required = 0x43c5

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.

SYS@book> @ &r/hide _smu_debug_mode

NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

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

_smu_debug_mode <debug-flag> - set debug event for testing SMU operations TRUE 0 0

SCOTT@book> alter system set "_smu_debug_mode" = 45 scope=memory;

System altered.

SCOTT@book> set transaction use rollback segment "_SYSSMU10_1197734989$";

Transaction set.

SCOTT@book> update deptx set dname = lower(dname) where deptno=20;

1 row updated.

SCOTT@book> @ xid

XIDUSN_XIDSLOT_XIDSQN

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

10.31.20164

C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG

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

ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 31 20164; 10 31 20164 3 277 3880 10 ACTIVE 1 1 0A001F00C44E0000 0000000082383488 2018-11-06 09:56:42 -2147480061

ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';

ALTER SYSTEM DUMP DATAFILE 3 BLOCK 277;

--//使用_SYSSMU10_1197734989$回滚段没有问题.flag=-2147480061不知道为什么呢?

SCOTT@book> commit ;

Commit complete.

--//附上xid.sql脚本:

column XIDUSN_XIDSLOT_XIDSQN format a30

select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ;

--select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC, UBASQN,STATUS,used_ublk,USED_UREC,xid,ADDR from v$transaction;

/* Formatted on 2016/03/08 17:12:37 (QP5 v5.252.13127.32867) */

SELECT 'ALTER SYSTEM DUMP UNDO BLOCK '''

|| (SELECT name