OracleORA-600 [4193] solution description

OracleORA-600 [4193] solution description, ORA-600 [41XX] This error is basically related to UNDO. For more information about ORA-600 parameters, refer to my Blog: O

Oracle ORA-600 [4193] solution description, ORA-600 [41XX] This error is basically related to UNDO. For more information about ORA-600 parameters, refer to my Blog: O

ORA-600 [41XX] This error is basically related to UNDO. For more information about ORA-600 parameters, refer to my Blog: ORA-600 parameters description

ORA-600 [4193] This error is also related to the UNDO, MOS has a few related articles.

I. MOS description

1.1 ORA-600 [4193] WhenTrying To Open The Database [ID 763566.1]

Symptoms

Copying databasefrom one server to another server and getting an ORA-600 [4193] error whentrying to open the database on the destination server.

-- This error is reported when you try to enable the copy database from one server to another.

Cause

The online redologs were copied when the source database was open, online redo logsshoshould never be copied when the database is open.

-- The cause is that the online redo logs is also copied together when the database is open. In the database open state, online redo logs should not be copied.

Solution

In this instancethe datafiles were being copied properly after the tablespaces were put in tobackup mode, however, online redo logs shocould only be copied if the sourcedatabase is shutdown first before copying the online redo logs. thesource database needed to remain open so, the datafiles were copied again (withthe tablespaces in backup mode) and then a number of archivelogs weretransferred over to the new server and after the last archivelog was appliedthe database cocould be opened with resetlogs and new online redo logs werecreated on the destination server.

-- When the tablespace is set to backup mode, you can copy data files, but the onlineredo log can only be copied after the database is shut down. If the database is always open, you can only copy the datafile, transfer the archive file to the database, and open the database using openresetlogs. The online redo log is automatically rebuilt when open.

1.2 Ora-600 [4193] WhenOpening Or Shutting Down A Database [ID 452662.1]

1.2.1 Symptoms

Errors in alert. log:

Tue Jul 17 13:38:13 2007
Errors in file/home/Oracle/oracle/product/10.2.0/yms/rdbms/log/yms_smon_8337.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4193], [3552], [3554], [], [], []

Yms_smon_8337.trc:

SO: 0xdfaec728, type: 24, owner: 0xdf266580, flag: INIT/-/0x00

(Buffer) PR: 0xdf1f1338 FLG: 0x1000
Class bit: 0x80000
Kcbbfbp: [BH: 0xded4bf40, LINK: 0xdfaec768]
Kcbbfbx [0]: [BH: 0xdece41d8, LINK: 0xdfaec788]
Where: ktuwh01: ktugus, why: 0
Buffer tsn: 2 rdba: 0x00c00002 (3/2)
Scn: 0x0000. 03c95628 seq: 0x01 flg: 0x00 tail: 0x56280e01
Frmt: 0x02 chkval: 0x0000 type: 0x0e = ktu undo header w/UNLIMITED EXTENTS
BH (0xdece41d8) file #: 3 rdba: 0x00c003b6 (3/950) class: 20 ba: 0x11d6ba000
Set: 6 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 0
Dbwrid: 0 obj:-1 objn: 0 tsn: 2 afn: 3
Hash: [df870f70, df870f70] lru: [dece4488, dece4028]
Obj-flags: object_ckpt_list
Ckptq: [dedac4a0, ded47cb8] fileq: [dedac500, ded47cc8] objq: [ded47d78, db7bfd78]
Use: [dfaec788, dfaec788] wait: [NULL]
St: XCURRENT md: EXCL tch: 0
Flags: mod_started gotten_in_current_mode block_written_once
Change state: ACTIVE
Change count: 1
LRBA: [0xac3. 4de07. 0] HSCN: [0xffff. ffffffff] HSUB: [65535]
Using State Objects
----------------------------------------
SO: 0xdfaec728, type: 24, owner: 0xdf266580, flag: INIT/-/0x00
(Buffer) PR: 0xdf1f1338 FLG: 0x1000
Class bit: 0x80000
Kcbbfbp: [BH: 0xded4bf40, LINK: 0xdfaec768]
Kcbbfbx [0]: [BH: 0xdece41d8, LINK: 0xdfaec788]
Where: ktuwh01: ktugus, why: 0
Buffer tsn: 2 rdba: 0x00c003b6 (3/950)
Scn: 0x0000. 03be3c7d seq: 0x5a flg: 0x04 tail: 0x3c7d025a
Frmt: 0x02 chkval: 0x0868 type: 0x02 = KTU UNDO BLOCK
----------------------------------------
Error 607 in redo application callback
TYP: 0 CLS: 20 AFN: 3 DBA: 0x00c003b6 OBJ: 4294967295 SCN: 0x0000. 03be3c7d SEQ: 90 OP: 5.1
Ktudb redo: siz: 132 spc: 4462 flg: 0x0012Seq: 0x0de2Rec: 0x09

Undo blk:
Xid: 0x0002. 045.20.6c61Seq: 0xde0Cnt: 0x60 irb: 0x60 icl: 0x0 flg: 0x0000.

1.2.2 Cause

When we try toapply redo to an undo block (forward changes are made by the applicationof redo to a block) we check that the seq # in the undo record matches theseq # in the redo record.

-- When the database is started, a rollback operation is required. During the rollback, redo is applied to the undo block. During the operation, the seq # In the undorecord and the seq # In the redo record are checked #.

These seq # shocould be the same because when we apply a redo record we must apply itto the correct version of the block.

-- Under normal circumstances, the seq # Of the two should be consistent.

We can onlyapply a redo record to a block that contains the same seq # as in the redorecord.

-- When the redo record is consistent, we apply the redo record to the undo record.

If the seq # do not match then ORA-600 [4193] [a]. [B] is raised ..

Arg [a] Undorecord seq number --> seq: 0xde0 = 3552
Arg [B] Redo record seq number --> seq: 0x0de2 = 3554

-- If they are inconsistent, an error occurs for the ORA-600 [4193] [a] [B. Where a is the seq # record in undo, and B is the seq # value in redo. Here the values are all sixteen processes. We can use the to_number () function to convert them:

SYS @ anqing1 (rac1)> Select to_number ('de0', 'xxxx') from dual;

TO_NUMBER ('de0', 'xxxx ')

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

3552

This implies some kind of block upload uptionin either the redo or the undo block.

-- When the redo record and undo record are inconsistent, an error of ORA-600 [4193] will be thrown.

For related articles, refer:

Oracledatafile block format description
Roll back

1.2.3 Solution

1.2.3.1 If Database is opened:

-- In the db open state, the solution is as follows:

1) Find out the rollback segment, based onthe first part of the xid: 0x0002. 045.20.6c61

Usn = 2 is the segment_id

Selectsegment_name, status from dba_rollback_segs where segment_id = 2;

RS_DATA1 ONLINE

2) Dump the transaction table of the rollbacksegment to see if all TX are commited:

Alter system dump undoheader RS_DATA1;

Oracle dumpundo description

3) check the trace file created underuser_dump_dest

In the trace filesearch for the Keyword "trn tbl"

Trn tbl ::

Index state cflags wrap # uel scn dba
-----------------------------------------------------------------------------
0x00 9 0x00 0x21eb1 0x0023 0x0000. d28c43e9 0x00000000 ......

State = 9 means transaction is committed

4) offline the rollback segment:

Alter rollbacksegment rs_data1 offline;
Select status from dba_rollback_segs wheresegment_id = 2;

5) if STATUS = OFFLINE

Droprollback segment RS_DATA1;

1.2.3.2 If Database doesn' t open:

-- If the database is not open, the solution is as follows:

1. a) If using rollbacksegments, remove the rollback_segments line from init. ora, and open database

B) If usingundo segments set undo_management = manual in init. ora/spfile, and try to opendatabase.

2. If database opens means all transactionsare committed, and you can drop the rollback segment or the undo tablespace

1.3 bug caused by ORA-600 [4193]

MOS:

ORA-600 [4193] "seq # mismatch whileadding undo record" [ID 39282.1]

Bug 8240762-Undo events uptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] [ID 8240762.8]

Undo events uptionmay be caused after a shrink and the same undo block may be used for two different transactions causing several internal errors like:

ORA-600 [4193]/ORA-600 [4194] for newtransactions

ORA-600 [4137] for a transaction rollback

Undo segment shrink is internally done by Oracle.

-- Undo uptions caused by undo shrink

Workaround

Drop the undo segment.

Affects:

Product (Component)

Oracle Server (Rdbms)

Range of versions believed to be affected

Versions> = 10.2 but BELOW 11.2

Versions confirmed as being affected

Platforms affected

Generic (all/most platforms affected)

Fixed: