Recreating a missing Oracle datafile with no backups

Posted by PDSERVICE on Apr 06, 2020 In

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

You are here

Recreating a missing Oracle datafile with no backups

Recreating a missing Oracle datafile with no backups