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: allanwtham@hotmail.com

Date written: Dec 15 2001

Oracle version(s): 8.1.7.0.0

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 v1.group#, member, sequence#, first_change#
     from v$log v1, v$logfile v2
     where v1.group#=v2.group#;

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