[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開始

執行事務恢復或一些回滾段的管理工作時則很有可能觸發該錯誤。

--//花一點點時間重新讀了連結:

%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

FROM sys.undo$

WHERE us# = xidusn)

|| ''' XID '

|| xidusn

|| ' '

|| xidslot

|| ' '

|| xidsqn

|| ';'

|| CHR (10)

|| 'ALTER SYSTEM DUMP UNDO HEADER '''

|| (SELECT name

FROM sys.undo$

WHERE us# = xidusn)

|| ''''

|| ';'

|| CHR (10)

|| 'ALTER SYSTEM DUMP DATAFILE '

|| UBAFIL

|| ' BLOCK '

|| UBABLK

|| ';'

c70

,XIDUSN

,XIDSLOT

,XIDSQN

,UBAFIL

,UBABLK

,UBASQN

,UBAREC

,STATUS

,used_ublk

,USED_UREC

,xid

,ADDR

,START_DATE

,flag

FROM v$transaction;

[20181106]模擬ora-00600[4194]錯誤.txt

相關文章