SAPDBA Recovery: 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.

Recovery Procedure

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.

1. If the database system is still active, use the following SVRMGR 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 archived since the last backup

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

Use the SAP utility BRRESTORE (see Command Options for BRRESTORE).

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

connect internal

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 Recovery. Also read the corresponding ORACLE documentation.
8. Use the following SVRMGR 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 reads the online redo log files automatically.
10. 1When the message
recovery complete is displayed, use the following SVRMGR command to open the database again:

alter database open;

Please note: in most cases, SAPDBA can be used to correct media errors affecting the data files of a user tablespace. Therefore, we recommend using the SAPDBA recovery functions (see SAPDBA Database Recovery).