If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline: +86 13764045638 E-mail: service@parnassusdata.com

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.2 and later

Information in this document applies to any platform.

***Checked for relevance on 16-Apr-2014***

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