[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回滚段,就不能按照上面的方法处理.看了http://www.askmaclean.com/网站介绍.
--//链接:http://www.askmaclean.com/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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2284078/
扫一扫
455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
