DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, April 17, 2014

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

ERROR:
======

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

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:

SOLUTION:
=========

startup mount

SQL>  select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3         ONLINE
/u03/app/oracle/oradata/star/redo03.log
NO

         2         ONLINE
/u03/app/oracle/oradata/star/redo02.log
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         1         ONLINE
/u03/app/oracle/oradata/star/redo01.log
NO

/u03/app/oracle/oradata/star/undotbs01.dbf


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

If We are using pfile then edit pfile and change the parameter :
=========================================

undo_management=MANUAL

If you are using spfile then
====================

alter system set undo_management = MANUAL scope=spfile;

alter database open;

Now drop the undo file
================

alter database datafile  '/u03/app/oracle/oradata/star/undotbs01.dbf' offline drop;

Drop the tablespace :
=============
drop tablespace UNDOTBS1;

Got the following error while droping the tablespace:
===================================

ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping tablespace

Inorder to ignore this error,
==================

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU1_3780397527$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU2_2232571081$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU3_2097677531$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU4_1152005954$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU5_1527469038$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU6_2443381498$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU7_3286610060$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU8_2012382730$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU9_1424341975$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU10_3550978943$          NEEDS RECOVERY   UNDOTBS1

10 rows selected.

Now shut down the database.

Add the lines in pfile inorder to ignore the segments that are used :
=================================================

_corrupted_rollback_segments =

('_SYSSMU1_3780397527$','_SYSSMU2_2232571081$','_SYSSMU3_2097677531$','_SYSSMU4_1152005954$','_SYSSMU5_1527469038$','_SYSSMU6_2443381498$','_SYSSMU7_32866100

60$','_SYSSMU8_2012382730$','_SYSSMU9_1424341975$','_SYSSMU10_3550978943$')


SQL> STARTUP MOUNT pfile='/u03/app/oracle/product/11.2.0/db_1/dbs/initstar.ora'
ORACLE instance started.

Total System Global Area  242208768 bytes
Fixed Size                  2212168 bytes
Variable Size             184553144 bytes
Database Buffers           50331648 bytes
Redo Buffers                5111808 bytes
Database mounted.

SQL> alter database open;

Database altered.

drop tablespace UNDOTBS1;

Now create the new undo tablespace with datafile.
====================================

create UNDO tablespace undotbs1 datafile '/u03/app/oracle/oradata/star/undotbs01_1.dbf' size 25m autoextend on next 1m maxsize 1024m;

Now start with spfile and make undo_management AUTO.

alter system set undo_management = AUTO scope=spfile;

REFERENCE :
========

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

8 comments:

Anonymous said...

This helped me tremendously. Thank you so much for your help on this.

Tomasz KieroĊ„ski said...

Hi,
i stack at this moment:
Now start with spfile and make undo_management AUTO.

what I do :

1)create spfile = 'SPFILEXE.ora' from pfile;


2)STARTUP MOUNT spfile='spfileexe.ora';

And I have:
SP2-0714: invalid combination of STARTUP options

Even when I try to do STARTUP FORCE or STARTUP MOUNT I always end with:
ORA-03113: end-of-file on communication channel

Could you help me with this problem?

Edgar Silva Guedes said...

Thank you very much. I´ve had the same problem. Very useful.

Unknown said...

Great job!
It is very helpfull

Anonymous said...

Thank you so much, this post resolved my issue!!!

max den said...

Amazingpost

Anonymous said...

Education plays important role in the development of the country as an essay writer does, education changes the thoughts of students, education is necessary for every student, education increases the income of students, education makes people healthier, education fosters peace.

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.