Archive

Posts Tagged ‘ORA-00600’

A mismatch has been detected between Redo records and Rollback (Undo) records ORA-00600/ORA-00607.

Last night I got the following error on one of my database (Oracle 9i):

ORA-00600: internal error code, arguments: [4193], [4271], [4056], ...

The Oracle Support gives more information about it:

VERSIONS:           
versions 6.0 to 10.1
DESCRIPTION:        
We are validating the Undo block sequence number in the undo block against
the Redo block sequence number relating to the change being applied.
This error is reported when this validation fails.
IMPACT:
process failure
possible rollback segment corruption
SUGGESTIONS:
This error may indicate a rollback segment corruption.
This may require a recovery from a database backup depending on the situation.

Here is the possible solution without recovery from a database backup. In SQLPLUS type the following command:

show parameter undo;

The output should be similar to this:

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

If the parameter UNDO_MANAGEMENT is set to AUTO, then change it to MANUAL (in my case I have the database on Windows Server and use the spfile):

alter system set undo_management='MANUAL' scope=spfile;
shutdown immediate;
startup;
create undo tablespace NEW_UNDOTBS datafile 'e:\oracle\oradata\SID\NEW_UNDOTBS01.DBF' size 1G;
alter system set undo_tablespace='NEW_UNDOTBS' scope=spfile;
alter system set undo_management='AUTO' scope=spfile;

After another shutdown the new parameters should be taken and if you want you can drop old UNDO tablespace. Sometimes when you try to do it, you can get the error similar to that:

drop tablespace UNDOTBS01 including contents;
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

How to handle that situation?
In SQLPLUS type the following command:

select * from v$rollname;

The output should be similar to this:

USN        NAME
---------- -----------------
0           SYSTEM
1          _SYSSMU1$
2          _SYSSMU2$
3          _SYSSMU3$
4          _SYSSMU4$
5          _SYSSMU5$
6          _SYSSMU6$

You need to create PFILE from SPFILE and shutdown the database:

create pfile='c:\new.init' from spfile;
File Created
shutdown immediate;

Then edit pfile and made the following changes:

*.undo_management='MANUAL'
*.undo_retention=1800
*.undo_tablespace='NEW_UNDOTBS'
*._offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$)

Then startup database from new pfile, then try to drop old UNDO tablespace, this way should work now. After drop old UNDO tablespace shutdown the database and start it up with the spfile.

Advertisements