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:
Post a Comment