Datafile damaged and backup not available

A datafile can still be recovered if no backup exists for it, provided:

• all redolog files since the creation of the datafile are available

• the control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database)

Recovery steps

Assume that file /u01/ORADATA/data/data01.dbf of tablespace DATA is damaged and you don't have a backup of the datafile:

Start SQL*Plus:

SQL> connect SYS AS SYSDBA
connected

SQL> shutdown abort
Oracle Instance Aborted

SQL> startup mount
Oracle Instance Started

SQL> alter database create datafile '/u01/ORADATA/data/data01.dbf';
Statement Processed

SQL> recover datafile '/u01/ORADATA/data/data01.dbf';
Statement Processed

SQL> select * from v$datafile;

You will see a list of datafiles. If the status of the recovered file is not ONLINE, issue the following command:

SQL> alter database datafile '/u01/ORADATA/data/data01.dbf' online;
Statement Processed

SQL> alter database open;
Statement Processed

SQL> select * from dba_tablespaces;

If the status of the tablespace is not ONLINE, issue the following command.

SQL> alter tablespace DATA online;
Statement Processed