Sunday, May 26, 2013

ORA-00607/ORA-00600


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

As you see the above error ORA-00600 usually Indicate for Bug, A mismatch has been detected between Redo records and Rollback (Undo)  records. and to solve this issue you need to do the below :

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS

SQL> alter system set undo_management='MANUAL' scope=spfile;

SQL > Shutdown immediate ;
SQL > Startup ; 
SQL > create undo tablespace NEW_UNDOTBS datafile '/u01/app/oracle/oradata/SID/NEW_UNDOTBS01.DBF' size 1G ;

SQL> alter system set undo_tablespace='NEW_UNDOTBS' scope=spfile;
System altered.
SQL> alter system set undo_management='AUTO' scope=spfile;
System altered.
 After Shutdown The new Effect Should be Taken And If you want to Drop Old Undo you can do This another option you can use is Take if Offline. Sometimes when you want to drop Old Undo you receive new error : 

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

So What Can i do Take Offline Or The Below Option :

SQL> select * from v$rollname ;
USN NAME
---------- -----------------
0   SYSTEM
1  _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
 SQL > Create Pfile='/u01/New.init' from spfile
 File Created
SQL > Shutdown immediate ;
Edit pfile 

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

  • Startup database from new pfile without create spfile ;
  • try to Drop Tablespace Now, this way should work.
  • After drop Undo Tablespace, Remove _offline Parameter and undo_management to AUTO 

Thank you 
Osama Mustafa

3 comments:

  1. Thanks a lot..!! Worked great for me.

    ReplyDelete
  2. This is amazing!! Thank you very much. But the addition of *._offline_rollback_segments didn't work for me.

    ReplyDelete
    Replies
    1. I remember this case happened to me, and had to add this parameter to solve it it will not work for everyone

      Delete