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

DBRECOVER Recovery Options

For Oracle incidents, start with the DBRECOVER for Oracle trial to verify table visibility, row previews, and export readiness on copied datafiles. For MySQL and InnoDB incidents, DBRECOVER for MySQL is free software and can inspect.ibd files, ibdata1, and database directories locally.

When the case is urgent, preserve the original files first, work from copies, and contact paid emergency support with the database version, platform, error messages, file list, and recovery objective.