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)


No comments: