Oracle Database Constantly Shuts Down with ORA-00600 Errors

Feb 6, 2013

After either a database, hardware, or power failure, there are some cases where the database is unable to perform normal functions due to errors such as ORA-01033 (See https://pitss.com/us/2013/01/11/ora-01333-error-occurs-in-oracle-database-after-pc-crash/ for more information). If the steps applied in the mentioned KB article as well as the traditional “shutdown immediate” and “startup” DB commands fail and you receive the following error below when trying to log into your database:

clip_image002

Also, if the database instance constantly shuts down randomly with errors such as:

image

It is possibly that the UNDO tablespace may be corrupted. To fix the problem, you can follow the following steps:

  1. Start up the database instance if it is not up (Run “startup;” in SQLPLUS).
  2. Run in SQLPLUS:
    • create pfile from spfile;
  3. Run in SQLPLUS:
    • shutdown immediate; (if you get an error, exit SQLPLUS and reconnect. Then run “shutdown abort;”)
  4. Go to the location where your pfile is located. If using the default location, it should be in %ORACLE_HOME%\product\11.2.0\dbhome_1\database. The file is called INIT<SID>.ORA (Example: INITorcl.ORA).
  5. Make a backup of this .ORA file.
  6. Open INIT<SID>.ORA in a text editor.
  7. Make the following changes in the file:
    • *.undo_management = manual
    • *.event = ‘10513 trace name context forever, level 2’
  8. Save and close the file
  9. Log into SQLPLUS and run:
    • startup restrict pfile=’%PATH_TO_PFILE%\INIT<SID>.ORA’;
  10. Run in SQLPLUS:
    • select tablespace_name, status, segment_name from dba_rollback_segs where status !=’OFFLINE’;
  11. When running the command in step 10, make sure that only SYSTEM appears to be online. If any segments are “PARTLY AVAILABLE” or “NEEDS RECOVERY”, you will need to create an SR at Oracle Support (Metalink) for this. Otherwise, skip this step.
  12. Create a new tablespace in SQLPLUS:
    • create undo tablespace <name of new undo tablespace> datafile <name of new datafile> size <size of tablespace>
    • Example: create undo tablespace UNDOTBS02 datafile UNDOTBS02.DBF size 2000M;
  13. (Optional): You may delete the old corrupted tablespace:
    • drop tablespace <old undo tablespace> including contents and datafiles;
  14. Shutdown the DB:
    • shutdown immediate;
  15. Startup the DB in mount mode:
    • startup mount;
  16. Return to the directory in step 4 and update the INIT<SID>.ORA file and specify the new undo tablespace created in step 12 in the *.undo_tablespace parameter. Alternatively, you can also run:
    • alter system set undo_tablespace='<new undo tablespace>’ scope=pfile;
  17. Shut down the DB:
    • shutdown immediate;
  18. Start up the DB in open mode:
    • startup;

After the above steps are applied, the database should return to normal operations.

Reference: Oracle Support Note 1428786.1