The following rescue scenario is the most powerful action to resolve for example complete loss of SYSAUX tablespace. For the example we rescue the SYSAUX tablespace.

The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

If you get an ORA-00600 or the following error message on your Oracle database:

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/home/oracle/data1/sysaux.dbf'

Steps to rescue the SYSAUX Tablespace

1.  Shutdown the Database if it is still running and start it in MOUNT mode

   sqlplus "/ as sysdba"
   shutdown abort;
   startup mount;

2.  Backup the current Controlfile to Userdump Directory

   alter database backup controlfile to trace;
   shutdown immediate;
   startup nomount;

Edit the generated Text Controlfile Tracefile, remove the Line with the SYSAUX Tablespace. The following is an example of such a Tracefile. Apply the CREATE CONTROLFILE statement.

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 4
    MAXDATAFILES 250
    MAXINSTANCES 4
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/home/oracle/data1/redo-01a.rdo'
    '/home/oracle/data2/redo-01b.rdo'
  ) SIZE 30M,
  GROUP 2 (
    '/home/oracle/data1/redo-02a.rdo',
    '/home/oracle/data2/redo-02b.rdo'
  ) SIZE 30M,
  GROUP 3 (
    '/

home/oracle/data1/redo-03a.rdo',
    '/home/oracle/data2/redo-03b.rdo'
  ) SIZE 30M
DATAFILE
  '/home/oracle/data1/system-01.dbf',
  '/home/oracle/data1/undo-01.dbf',

  -- Please note that SYSAUX datafile has been removed

  '/home/oracle/data1/users-01.dbf',
  '/home/oracle/data1/data-01.dbf',
  '/home/oracle/data1/index-01.dbf',
CHARACTER SET AL32UTF8;

Control file created.

2.  Recover the Database using the created Controlfile - enter cancel to stop the «Dummy» Recovery
 
    RECOVER DATABASE USING BACKUP CONTROLFILE;

    ORA-00279: change 190712 generated at 02/24/2009 11:54:49 needed for thread 1
    ORA-00289: suggestion :
    /home/oracle/data1/archive/TEST-s24-t1-r679661814.arc
    ORA-00280: change 190712 for thread 1 is in sequence #24
    Specify log: {=suggested | filename | AUTO | CANCEL}
 
    Media recovery cancelled.

    shutdown immediate;

3.  Edit the Oracle Parameter File PFILE and insert the following two Lines

    _allow_resetlogs_corruption = true
    _allow_read_only_corruption = true

4.  Open the Database with OPEN RESETLOGS
 
    startup mount;
    alter database open resetlogs;

5. Immediately Export the desired Tablespace and Recreate the Database

    exp userid=system/manager TABLESPACES=(test_data,test_index)