Oracle Database Deep in ora-00600 [4193] solution

This is a netizen problem, 10201 windows environment, non-archive, no backup, common ORA-00600 4193 error reported when the database is open.
This error is very common and we have analyzed it many times. Here we will look at the situation of netizens here again. The alert log is as follows:

Fri Nov 07 13:58:18 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Nov 07 13:58:18 2014
SMON: enabling cache recovery
Fri Nov 07 13:58:19 2014
Errors in file e: \ oracle \ product \ 10.2.0 \ admin \ orcl \ udump \ orcl_ora_741_trc:
ORA-00600: Internal error code, parameter: [4193], [65], [71], [], [], [], [], [], []
 
Fri Nov 07 13:58:21 2014
Doing block recovery for file 1 block 415
Block recovery from logseq 6910, block 3 to scn 262520099
Fri Nov 07 13:58:21 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 6910 Reading mem 0
Mem #0 errs 0: E: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO03.LOG
Block recovery stopped at EOT rba 6910.5.16
Block recovery completed at rba 6910.5.16, scn 0.262520098
Doing block recovery for file 1 block 9
Block recovery from logseq 6910, block 3 to scn 262520097
Fri Nov 07 13:58:21 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 6910 Reading mem 0
Mem #0 errs 0: E: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO03.LOG
Block recovery completed at rba 6910.5.16, scn 0.262520098
Fri Nov 07 13:58:21 2014
Errors in file e: \ oracle \ product \ 10.2.0 \ admin \ orcl \ udump \ orcl_ora_741_trc:
ORA-00604: recursive SQL level 1 error
ORA-00607: Internal error when changing data blocks
ORA-00600: Internal error code, parameter: [4193], [65], [71], [], [], [], [], [], []
 
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604.
Oracle docs explains this for ORA-00600 4193 errors:

ERROR:
ORA-600 [4193] [a] [B]
 
VERSIONS:
Versions 6.0 to 10.1
 
DESCRIPTION:
 
A mismatch has been detected between Redo records and Rollback (Undo)
Records.
 
We are validating the Undo block sequence number in the undo block against
The Redo block sequence number relating to the change being applied.
 
This error is reported when this validation fails.
By checking the alert log information posted by netizens, we found that this buddy had performed a lot of operations and tried almost all the restoration operations. As follows:

System parameters with non-default values:
Processes = 300
Sessions = 335
Event = 10513 trace name context forever, level 2: 10512 trace name context forever, level 1: 10511 trace name context forever, level 2: 10510 trace name context forever, level 1
S ga_max_size = 1619001344
_ Shared_pool_size = 150994944
_ Large_pool_size = 8388608
_ Java_pool_size = 8388608
_ Streams_pool_size = 0
Sga_target = 1619001344
Control_files = E: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL01.CTL, E: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL02.CTL, E: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ CONTROL03.CTL
DB _ block_size = 8192
_ Db_cache_size = 1442840576
Compatible = 10.2.0.1.0
Db_file_multiblock_read_count = 16
Db_recovery_file_dest = E: \ oracle \ product \ 10.2.0/flash_recovery_area
Db_recovery_file_dest_size = 2147483648
_ Allow_resetlogs_upload uption = TRUE
Undo_management = MANUAL
Undo_tablespace = UNDOTBS1
_ Smu_debug_mode = 4
Remote_login_passwordfile = EXCLUSIVE
Db_domain =
Dispatchers = (PROTOCOL = TCP) (SERVICE = orclXDB)
Job_queue_processes = 10
Audit_file_dest = E: \ ORACLE \ PRODUCT \ 10.2.0 \ ADMIN \ ORCL \ ADUMP
Background_dump_dest = E: \ ORACLE \ PRODUCT \ 10.2.0 \ ADMIN \ ORCL \ BDUMP
User_dump_dest = E: \ ORACLE \ PRODUCT \ 10.2.0 \ ADMIN \ ORCL \ UDUMP
Core_dump_dest = E: \ ORACLE \ PRODUCT \ 10.2.0 \ ADMIN \ ORCL \ CDUMP
Db_name = orcl
Open_cursors = 300
Pga_aggregate_target = 203423744
We can see that the event is used to block the smon rollback segment, the implicit parameter is used to force the opening, and the undo parameter is used to modify the tablespace. It is said that force is also used
Some parameters of the offline rollback segment.
In fact, no matter how we solve this problem, first we need to analyze why Oracle reports this error here?
First, there are several questions:
1) What SQL statements does Oracle execute in the open process to report errors?
2) Why is an error reported?
3) If the rollback segment is forcibly blocked, is it affected?
For the first problem, it is very simple. We can easily find the trace to find that the SQL statement encountered an error during execution:

ORA-00600: Internal error code, parameter: [4193], [65], [71], [], [], [], [], [], []
Current SQL statement for this session:
Update undo $ set name =: 2, file # =: 3, block # =: 4, status $ =: 5, user # =: 6, undosqn =: 7, xactsqn =: 8, scnbas =: 9, scnwrp =: 10, inst # =: 11, ts # =: 12, spare1 =: 13 where us # =: 1
For further search, we can also locate the rollback segment during which Oracle reports an error when executing this recursive SQL statement (here is US #, which indicates the rollback segment number ):

Cursor #5 (050D0F84) state = BOUND curiob = 60F2AE90
Curflg = d fl2 = 0 par = 050D0E84 ses = 7AB23D28
Sqltxt (7A734404) = update undo $ set name =: 2, file # =: 3, block # =: 4, status $ =: 5, user # =: 6, undosqn =: 7, xactsqn =: 8, scnbas =: 9, scnwrp =: 10, inst #=: 11, ts #=: 12, spare1 =: 13 where us # =: 1
Hash = 9caba1288112094d5553173dd30cd6c3
Parent = 0CF83F1C maxchild = 01 plk = 0EEB3834 ppn = n
Cursor instantiation = 60F2AE90
Child #0 (7A7342C0) pcs = 0CF84120
Clk = 0EED3A90 ci = 0CF83774 pn = 0E493BC4 ctx = 0C686938
Kgsccflg = 0 llk [60F2AE94, 60F2AE94] idx = 0
Xscflg = e0100666 fl2 = d100400 fl3 = 4022218c fl4 = 0
Bind bytecodes
Opcode = 5 Bind Rpi Scalar SQL In (not out) Nocopy
Offsi = 36, Offsi = 0
......
Opcode = 1 Unoptimized
Offline = 36, offline = 240
Kkscoacd
Bind #0
Oacdty = 01 mxl = 32 (09) mxlc = 00 mal = 00 scl = 00 pre = 00
Oacflg = 18 fl2 = 0001 frm = 01 csi = 852 siz = 32 off = 0
Kxsbbbfp = 7a7450ba bln = 32 avl = 09 flg = 09
Value = "_ SYSSMU1 $"
Bind #1
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 = 60f26c6c bln = 24 avl = 02 flg = 05
Value = 2
..........
Value = 1
Bind #12
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 = 60f26c90 bln = 22 avl = 02 flg = 05
Value = 1
Frames pfr 60F27024 siz = 4832 efr 60F2706C siz = 4820
Cursor frame dump
Enxt: 3.0x00000710 enxt: 2.0x00000104 enxt: 1.0x00000ac0
Pnxt: 2.0x00000004 pnxt: 1.0x00000008
Kxscphp 05471F28 siz= 1000 inu = 0 nps = 416
Kxscehp 05472450 siz = 1000 inu = 0 nps = 744
----------------------------------------
Obviously, we can see that, in essence, Oracle encountered a problem when updating the rollback segment _ SYSSMU1 $.
Now let's answer 2nd questions. Why does Oracle report an error? Let's take a look at this ORA-00600 error:
ORA-00600: Internal error code, parameter: [4193], [65], [71], [], [], [], [], [], []
According to the document, this error means that the seq of redo record does not match the seq of undo record.
So what does 65 and 71 mean? The incorrect format is as follows: ORA-600 [4193] [a] [B]
A is 65, indicating undo record seq
B is 71, indicating redo record seq.
So why does Oracle get an inconsistent result? Obviously, 65 is not equal to 71.
We can see the following information from the Trace file provided by netizens:

Ktudb redo: siz: 252 spc: 6372 flg: 0x0012 seq: 0x0047 rec: 0x08
Xid: 0x0000. 026.00000052
Ktubl redo: slt: 38 rci: 0 opc: 11.1 objn: 15 objd: 15 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0040019f. 0047.07
Prev ctl max cmt scn: 0x0000. 0f92fecd prev tx cmt scn: 0x0000. 0f92fecf
Obviously, this is redo related information, where seq is 0 × 0047, after conversion is 71. Originally, this is the source of B value of ora-00600 error.
So ora-00600 error a value, 65 Where is it? From the previous UBA information, we can know that the undo block corresponding to this transaction is: 0x0040019f
So let's take a look at the content in this undo block? Search for the block address. We found that this is a system block, which is obviously a system rollback segment.
As follows:

Block after image is UPT:
Buffer tsn: 0 rdba: 0x0040019f (1/415)
Scn: 0x0000. 0f468845 seq: 0x01 flg: 0x04 tail: 0x88450201
Frmt: 0x02 chkval: 0x911f type: 0x02 = KTU UNDO BLOCK
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0x08DB6000 to 0x08DB8000
8DB6000 2017a202 0040019F 0F468845 04010000 [...... @. E. F ......]
.........
8DB7FF0 2E3004C1 332E02C1 323202C1 88450201 [... 0... 3... 22... E.]
 
**************************************** ****************************************
Undo blk:
Xid: 0x0000. 043.00000047 seq: 0x41 cnt: 0x22 irb: 0x22 icl: 0x0 flg: 0x0000
 
Rec Offset
---------------------------------------------------------------------------
0x01 0x1f2c 0x02 0x1e30 0x03 0x1d34 0x04 0x1c38 0x05 0x1b3c
0x06 0x1a40 0x07 0x1944 0x08 0x1848 0x09 0x174c 0x0a 0x1650
0x0b 0x1554 0x0c 0x1458 0x0d 0x135c 0x0e 0x1260 0 0x1164
0x10 0x1068 0x11 0x0f6c 0x12 0x0e70 0x13 0x0d74 0x14 0x0c78
0x15 0x0b7c 0x16 0x0a80 0x17 0x09c4 0x18 0x0908 0x19 0x084c
0x1a 0x0790 0x1b 0x06d4 0x1c 0x0618 0x1d 0x055c 0x1e 0x04a0
0x1f 0x03e4 0x20 0x02e8 0x21 0x01ec 0x22 0x00f0
 
......
.......
*-----------------------------
* Rec #0x7 slt: 0x22 objn: 15 (0x0000000f) objd: 15 tblspc: 0 (0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
Rdba: 0x00000000
*-----------------------------
Uba: 0x0040019f. 0041.06 ctl max scn: 0x0000. 0f3d6a95 prv tx scn: 0x0000. 0f3d6a97
Txn start scn: 0x0000. 0f44c775 logon user: 0
Prev brb: 4194714 prev bcl: 0
KDO undo record:
KTB Redo
Op: 0x04 ver: 0x01
Op: L itl: xid: 0x0000. 031.00000047 uba: 0x0040019f. 0041.06
Flg: C --- lkc: 0 scn: 0x0000. 0f44c774
KDO Op code: URP row dependencies Disabled
Xtype: XA flags: 0x00000000 bdba: 0x0040006a hdba: 0x00400069
Itli: 1 ispac: 0 maxfr: 4863
Tabn: 0 slot: 3 (0x3) flag: 0x2c lock: 0 ckix: 93
Ncol: 17 nnew: 12 size: 0
Col 1: [9] 5f 53 53 53 4d 55 33 24
Col 2: [2] c1 02
Col 3: [2] c1 03
Col 4: [2] c1 2a
Col 5: [6] c5 03 39 11 3a 05
Col 6: [1] 80
Col 7: [4] c3 13 4c 39
Col 8: [4] c3 04 52 5b
Col 9: [1] 80
Col 10: [2] c1 03
Col 11: [2] c1 02
Col 16: [2] c1 02

According to the previous UBA: uba: 0x0040019f. 0047.07, the transaction information is located in the 7th records of the undo block. When we locate 7th records,
We can see that the seq here is actually 41, the conversion to 10 is 65. This is the origin of the ORA-00600 error.
Finally, let's answer the 3rd questions. Will it be affected if the rollback segment is forcibly blocked? Obviously, the object here is obj 15, which is a core object.
It is definitely inappropriate to forcibly block Rollback segments. In fact, there are many ways to deal with such problems. I have already talked about these issues many times in the special recovery course of the Oracle training in Dawson.