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 |
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
