ORA-600 4194

最新推荐文章于 2024-02-26 08:00:41 发布

ctfmxd8196

最新推荐文章于 2024-02-26 08:00:41 发布

阅读量121

点赞数

Step by step to resolve ORA-600 4194 4193 4197 on database crash (文档 ID 1428786.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 04-Dec-2013***

SYMPTOMS

The following error is occurring in the alert.log right before the database crashes.

ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []

This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

ARGUMENTS:

Arg [a] - Maximum Undo record number in Undo block
Arg [b] - Undo record number from Redo block

Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then an ORA-600 [4194] will be triggered.

CHANGES

This issue generally occurs when there is a power outage or hardware failure that initially crashes the database. On startup, the database does the normal roll forward (redo) and then rollback (undo), this is where the error is generated on the rollback.

CAUSE

This also can be cause by the following defect

Bug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINK

Details:
Undo corruption may 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 new transactions
ORA-600 [4137] for a transaction rollback

SOLUTION

Best practice to create a new undo tablespace.
This method includes segment check.

Create pfile from spfile to edit
>create pfile from spfile;

1. Shutdown the instance

2. set the following parameters in the pfile
undo_management = manual
event = '10513 trace name context forever, level 2'

3. >startup restrict pfile=

4. >select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

This is critical - we are looking for all undo segments to be offline - System will always be online.

If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.

If all offline then continue to the next step

5. Create new undo tablespace - example
>create undo tablespace datafile size 2000M;

6. Drop old undo tablespace
>drop tablespace including contents and datafiles;

7. >shutdown immediate;

8 >startup nomount; --> Using your Original spfile

9 modify the spfile with the new undo tablespace name

Alter system set undo_tablespace = '' scope=spfile;

10. >shutdown immediate;

11. >startup; --> Using spfile

The reason we create a new undo tablespace first is to use new undo segment numbers that are higher then the current segments being used. This way when a transaction goes to do block clean-out the reference to that undo segment does not exist and continues with the block clean-out

在解决数据库异常宕机的时候，会遇到undo段无法使用的情况，为了正常的让数据库拉起来，需要经undo 段drop 掉然后重新建立新的undo ，这样会导致一部分数据的丢失，并且会造成坏块的现象，因此使用完上述的方法正常启动之后需要使用dbv 工具或是是使用rman 进行检查数据文件中的坏块，如果是索引需要重建，如果是表的话也要重建