[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

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.

[20181204]模擬ora-00600[4193]錯誤.txt

相關文章