How To Force Open A Database

WARNING!

  • The following procedure is not supported by Oracle and might lead to logical corruption to the database. It should only be performed as a last resort.
  • Ensure a full database export is performed after the database startup and PRIOR to users logging back on.

SYMPTOMS

  • On attempting to start the database, the following error, or similar, is produced:
         SQL*Plus: Release 9.2.0.8.0 - Production on Sat Mar 1 09:28:26 2008

         Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

         Connected to an idle instance.

         SQL> ORACLE instance started.

         Total System Global Area 1.6896E+10 bytes
         Fixed Size                   752608 bytes
         Variable Size            1593835520 bytes
         Database Buffers         1.5032E+10 bytes
         Redo Buffers              268574720 bytes
         Database mounted.
         ORA-01092: ORACLE instance terminated. Disconnection forced
  • The alert.log shows entries similar to:
         Errors in file /u01/app/oracle/admin/<SID>/udump/jsdclo01_ora_27243.trc:
         ORA-00604: error occurred at recursive SQL level 1
         ORA-01552: cannot use system rollback segment for non-system tablespace '<tablespace_name>'

         Error 604 happened during db open, shutting down database
         USER: terminating instance due to error 604
         Instance terminated by USER, pid = 27243
         ORA-1092 signalled during: ALTER DATABASE OPEN...

STEP-BY-STEP GUIDE

  • Identify the SYSTEM rollback segments as follows:
    • For a SYSTEM tablespace datafile that is under 2 Gb in size:
      • strings <system tablespace datafile> | grep _SYSSMU | cut -d $ -f 1 | sort -u > <output file name>
    • For a SYSTEM tablespace datafile that is over 2 Gb in size:
      • dd if=<system tablespace datafile> of=<temp holding file> bs=<datafile blocksize> count=50000
      • strings <temp holding file> | grep _SYSSMU | cut -d $ -f 1 | sort -u > <output file name>
  • Amend the UNDO_MANAGEMENT section of the init.ora file, as well as using the the contents of <output file name> to construct an _OFFLINE_ROLLBACK_SEGMENTS entry (Note, the additional $ sign):
    • UNDO_MANAGEMENT=MANUAL
    • _OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, … etc)
  • Startup database in RESTRICT mode:
    • SQL> STARTUP RESTRICT
  • List the current SYSTEM rollback segments in use:
    • SQL> SELECT segment_name, status FROM dba_rollback_segs WHERE segment_name != 'SYSTEM';
  • Drop the current SYSTEM rollbacks segments in use:
    • SQL> DROP ROLLBACK SEGMENT "_SYSSMU&no.$";
    • Enter the appropriate SYSTEM rollback segment number.
    • Repeat for all listed SYSTEM rollback segments.
  • Drop the UNDO tablespace, including associated datafiles:
    • SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;
  • Recreate the UNDO tablespace:
    • SQL> CREATE UNDO TABLESPACE <tablespace_name> DATAFILE '<file name>' SIZE <size> REUSE;
  • Shutdown the database:
    • SQL> SHUTDOWN IMMEDIATE
    • NOTE: The shutdown may cause ORA-600 errors and require a SHUTDOWN ABORT - this is no cause for concern.
  • Remove the _OFFLINE_ROLLBACK_SEGMENTS entry from the init.ora file and reset the UNDO_MANAGEMENT parameter:
    • UNDO_MANAGEMENT=AUTO
  • Restart the database:
    • SQL> STARTUP
  • Perform a full database export.
© copyright 2001-2014 ABCdba.com | all rights reserved