User Managed Recover of Datafile when Backup is not Available

If a datafile is damaged and you don't have any backup of the datafile then still you can recover your data file if the following conditions met.

1)You have all archived redo logs available since the data file creation.

2)The control file contains the name of the lost file. That means either the control file is current or it is taken after the data file creation.

3)The datafile does not belong to SYSTEM tablespace.

To illustrate the scenario I will demonstrate the whole procedure with an example.

A)Add a datafile to a tablespace.

SQL> ALTER TABLESPACE USER_TBS ADD DATAFILE '/oradata2/data1/dbase/datafile03.dbf' SIZE 1M;
Tablespace altered.

B)Drop the datafile.

SQL> !rm /oradata2/data1/dbase/datafile03.dbf

C)Make the affect data file offline if the database is open.

SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 7 - file is in use or recovery
ORA-01110: data file 7: '/oradata2/data1/dbase/datafile03.dbf'

It can't rename as database currently using this file. So, make it offline.

SQL> alter database datafile '/oradata2/data1/dbase/datafile03.dbf' offline;
Database altered.

D) a new, empty datafile to replace a damaged datafile that has no corresponding backup. Here I craeted the damaged file 7 as to new location '/oradata1/arju/created_new.dbf'.

SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
Database altered.

E)Perform media recovery on the empty datafile.
SQL> RECOVER DATAFILE '/oradata1/arju/created_new.dbf';
Media recovery complete.

F)Make the datafile online.

SQL> alter database datafile '/oradata1/arju/created_new.dbf' ONLINE;
Database altered.

Now we will look at the scenario if while starting database it can't find datafile.

SQL> !rm /oradata1/arju/created_new.dbf

Shutdown immediate will fail as it can't find one datafile.

SQL> shutdown immediate;
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 88080424 bytes
Database Buffers 71303168 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'


SQL> ALTER DATABASE CREATE DATAFILE '/oradata1/arju/created_new.dbf' AS '/oradata2/data1/dbase/datafile03.dbf';

Database altered.

SQL> RECOVER DATAFILE 7;
Media recovery complete.

SQL> ALTER DATABASE OPEN;
Database altered.