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:

Description of parameters of ORA-600

Http://blog.csdn.net/tianlesoftware/article/details/6645809

 

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/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: 0x0012 seq: 0x0de2 REC: 0x09

 

Undo BLK:
Xid: 0x0002. 045.20.6c61 seq: 0xde0 CNT: 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

Http://blog.csdn.net/tianlesoftware/article/details/6654786

 

Roll back

Http://blog.csdn.net/tianlesoftware/article/details/6286330

 

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

Http://blog.csdn.net/tianlesoftware/article/details/6672417

 

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 versionsBelievedTo be affected	Versions> = 10.2 but below 11.2
VersionsConfirmedAs being affected	10.2.0.4 10.2.0.3
Platforms Affected	Generic (all/Most Platforms Affected)

Fixed:

This issue is fixed in

11.2.0.1 (Base Release) 11.1.0.7.10 Patch Set Update 10.2.0.5 (server patch set) 11.1.0.7 patch 42 on Windows Platforms 10.2.0.4 patch 40 on Windows Platforms

 

DB from Oracle 10.2 to 11.2 may be affected by bug 8240762, resulting in undo crash uption. This bug has been fixed in 10.2.0.5. If this problem occurs, drop the corresponding undo segment.

 

 

Ii. Summary

The first part lists some official explanations, which are summarized here.

 

When the database is started, a forward operation will be performed first. At this time, some redo records will be applied to the Undo block. Before applying, The seq # values in the two blocks will be checked first, if they are consistent, OK. Otherwise, the ORA-600 [4193] error will occur.

 

The format of ORA-600 [4193] is: ORA-600 [4193] [a]. [B]:

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

 

Parameter A is the seq # In the UNDO, and parameter B is the seq # value in the redo. This value can be viewed from the trace, or you can directly dump the corresponding block:

 

Oracle dumpredo Log File description

Http://blog.csdn.net/tianlesoftware/article/details/6670962

 

Oracle dumpundo description

Http://blog.csdn.net/tianlesoftware/article/details/6672417

 

Oracledatafile block format description

Http://blog.csdn.net/tianlesoftware/article/details/6654786

 

The seq value dumped by dump is in hexadecimal format and can be converted using the to_number function:

Sys @ anqing1 (Rac1)> select to_number ('de0', 'xxxx') from dual;

To_number ('de0', 'xxxx ')

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

3552

 

Cause of error that caused ORA-600 [4193], MOS mentioned 2:

1. The online redo log is copied in the DB open state.

In the open state, the onlien redo log cannot be copied, but the archive should be copied, and then open resetlogs should be used, so that the onlineredo log will be automatically rebuilt.

2. Bug 8240762 -- shrink undo

ORA-600 [4193]/ORA-600 [4194] for new transactions

ORA-600 [4137] for a transaction rollback

This bug exists in Oracle versions 10.2.0.3, 10.2.0.4, and 11gr1.

 

The solution to this problem is to re-create the Undo tablespace. Now the system that uses rollbacksegment under system is basically gone.

 

Two undo reconstruction methods are listed in the following blog:

How to deal with currentonline redo and undo corruption

Http://blog.csdn.net/tianlesoftware/article/details/6261475

 

(1) Use spfile to create a pfile and modify the parameters:

# *. Undo_tablespace = 'undotbs1'

# *. Undo_management = 'auto'

# *. Undo_tablespace

# *. Undo_retention

Undo_management = 'manual'

Rollback_segments = 'system'

 

(2) restart the database with the modified pfile.

SQL> startup mountpfile = 'f:/initorcl. ora ';

 

(3) Delete the original tablespace and create a new undo tablespace.

SQL> drop tablespace undotbs;

SQL> Create undo tablespace undotbs1datafile '/u01/oradata/undotbs1.dbf' size 10 m;

 

(4) Close the database, modify the pfile parameter, and then create a spfile with the new pfile to start the database normally.

*. Undo_tablespace = 'undotbs1'

*. Undo_management = 'auto'

# Undo_management = 'manual'

# Rollback_segments = 'system'

 

 

For more information about the Undo tablespace, see:

Oracle undo Management

Http://blog.csdn.net/tianlesoftware/article/details/4901666

 

Oracle undo tablespace Management

Http://blog.csdn.net/tianlesoftware/article/details/5689558

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940