Recovering from SYSTEM Tablespace Missing

Use

One or more files of the SYSTEM tablespace has been damaged or lost due to a media error.

Backups of the affected files exist. All offline redo log files that have been written since the last backup are available uninterrupted. The control files and all online redo log files are undamaged.

Prerequisites

Because the SYSTEM tablespace is affected, the recovery must take place with the database closed. If a backup of the missing files and all redo log entries are available, a complete recovery is possible.

Procedure

...

       1.      If the database system is still active, use the following SQLPLUS command to shut it down:

shutdown abort

ABORT is required because the loss of individual files from the tablespace means that the changes from SGA can no longer be recorded, and the database cannot be closed properly as a result.

       2.      Examine the ALERT and trace files to determine the cause of the problem.

       3.      Use the log files created by the SAP utilities BRBACKUP and BRARCHIVE to find the volume(s) containing the following files:

Ў        Last backup of the SYSTEM tablespace

Ў        Offline redo log files of all instances backed up since the last backup

       4.      Restore the backups of the damaged/lost files and the backed up offline redo log files of all instances.

Use BRRESTORE for this.

       5.      Mount the database. Enter the following SQLPLUS commands:

connect / as sysdba

startup mount

       6.      You may have to change the names and paths of the files in the control file. See Updating the Control File.

       7.      The database administrator can automate the recovery using the autorecovery option. See Automating the Recoveryand read the corresponding Oracle documentation.

       8.      Use the following SQLPLUS command to start the recovery:

recover database;

       9.      Depending on the recovery mode (autorecovery on/off), the required offline redo log files are either processed automatically or you will have to enter their paths and names.  The system applies the online redo log files automatically.

   10.      When the message recovery complete is displayed, use the following SQLPLUS command to open the database again:

alter database open;

In most cases, BRRECOVER can be used to correct media errors affecting the data files of a user tablespace. For more information, see Complete Database Recovery with BR*Tools.