Phone: +86 13764045638 Email: service@parnassusdata.com 7 x 24 online support!

Recreating a missing Oracle datafile with no backups

Recreating a missing Oracle datafile with no backups

GOAL
How to recreate a datafile that is missing at the operating system level. Missing/inaccessible files may be reported with one or more of these errors:
 
ORA-01116: error in opening database file %s
ORA-27041: unable to open file
ORA-01157: cannot identify/lock data file %s - see DBWR trace file
ORA-01119: error in creating database file '%s'
 
 
No backup or copy of the datafile is required. We only need the redo logs starting from the time of the datafile creation to the current point in time.
 
Note: plugged-in datafiles do not apply in this scenario and needs to be plugged-in again from its source.
 
SOLUTION
When a datafile goes missing at the operating system level, you would normally need to restore and recover it from a backup. If you do not have backups of this datafile, but do have redo logs you can still create and recover the datafile. You only need the redo logs starting from the datafile creation time to now.
 
Prior to 10g, you would use the following SQL command:
 
SQL> alter database create datafile 'missing name' as 'misisng name';
SQL> recover datafile 'missing name';
SQL> alter database datafile '<missing name>' online;
As of 10g, you can also do this in RMAN.
 
1) RMAN will create the datafile if there is no backups or copies of this datafile:
 
 
 
RMAN> restore datafile <missing file id>;
 
 
2) Recover the newly created datafile:
 
RMAN> recover datafile <missing file id>;
 
 
3) Bring it online:
 
RMAN> sql 'alter database datafile <missing file id> online';
 
 
 
Example:
 
RMAN> list copy of datafile 6;
 
specification does not match any datafile copy in the repository
 
RMAN> list backup of datafile 6;
 
specification does not match any backup in the repository
 
RMAN> restore datafile 6;
 
Starting restore at 14 JUL 10 10:20:02
using channel ORA_DISK_1
 
creating datafile file number=6 name=/opt/app/oracle/oradata/ORA112/datafile/o1_mf_leng_ts_63t08t64_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 14 JUL 10 10:20:05
 
RMAN> recover datafile 6;
 
Starting recover at 14 JUL 10 10:21:02
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:00
 
Finished recover at 14 JUL 10 10:21:02
 
RMAN> sql 'alter database datafile 6 online';
 
sql statement: alter database datafile 6 online