Recovering from SYSTEM Tablespace Missing

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

Since 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, shut it down with this SQLPLUS command:

   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 tools BRBACKUP and BRARCHIVE to find the volumes 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 or lost files and the backed up offline redo log files of all instances, using BRRESTORE.

5. Mount the database with this SQLPLUS command:

   connect / as sysdba

   startup mount

6. If necessary, change the names and paths of the files in the control file. See Updating the Control File.

7. If required, automate the recovery using the autorecovery option. See Automating the Recovery and read the corresponding Oracle documentation.

8. Start the recovery with the following SQLPLUS command:

   recover database;

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

10. When the message recovery complete is displayed, open the database again with this SQLPLUS command :

    alter database open;

    Note:

    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.