The Oracle (tm) Users' Co-Operative FAQ

How to Recover From a Lost Datafile That Belongs to the SYSTEM Tablespace

Author's name: Allan W. Tham

Author's Email:

Date written: Dec 15 2001

Oracle version(s):

SYSTEM tablespace is essential for Oracle to start and operate properly. What would you do if you lost a datafile that belongs to SYSTEM tablespace? Well, you need Media Recovery

Back to index of questions

1. Shutdown abort the database if the database is still up

2. Copy the corrupted or lost datafile from backup to the original location

3. Startup mount

4. select, member, sequence#, first_change#
     from v$log v1, v$logfile v2

5. Recover the database by Recover datafile '/path/filename.dbf'

6. Logs will be prompted. Confirm it until you see "Media Recovery Complete".
     If you are asked to enter a non-existence archived log, enter the full path of
     a member of the redo group where the sequence number matches the one being
     prompted (from step 4) until you see "Media Recovery Complete"

7. Alter database open

Note: this recovery method is good for Archivelog mode ON.

If you need to rename or relocate the datafile, refer to Metalink Article 115424.1

Further reading: Oracle Backup and Recovery

Back to top

Back to index of questions