restore database when datafile deleted but database has no backup and not in archivelog mode

Scenario :

DB is up and running

Deleted non system datafiles using rm -rf command

DB is still up and running. We have not stopped database.

-------------------------PROCEDURE-----------------------------------------

Find the PID of DBWRITER for the right database.

$ ps -ef | grep dbw0_TEMPDB

oracle   12272     1  0 10:55 ?        00:00:00 ora_dbw0_TEMPDB ===>

oracle   12941 11501  0 12:36 pts/0    00:00:00 grep dbw0_TEMPDB

It Lists the deleted file handles for that DBWRITER process.

ls -l /proc/12272/fd | grep deleted

l-wx------ 1 oracle dba 64 Jun  8 12:39 10 -> /u01/app/oracle/diag/rdbms/TEMPDB/TEMPDB/trace/TEMPDB_ora_8518.trc (deleted)

l-wx------ 1 oracle dba 64 Jun  8 12:39 11 -> /u01/app/oracle/diag/rdbms/TEMPDB/TEMPDB/trace/TEMPDB_ora_8518.trm (deleted)

lrwx------ 1 oracle dba 64 Jun  8 12:39 31 -> /u03/oradata/TEMPDB/TEST_DATA01.DBF (deleted)

lrwx------ 1 oracle dba 64 Jun  8 12:39 32 -> /u03/oradata/TEMPDB/TEST_LOBS01.DBF (deleted)

lrwx------ 1 oracle dba 64 Jun  8 12:39 33 -> /u03/oradata/TEMPDB/TEST_INDEX01.DBF (deleted)

lrwx------ 1 oracle dba 64 Jun  8 12:39 8 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkinstTEMPDB (deleted)

Create a symbolic link to your datafile with the original name.

ln -s /proc/8969/fd/31 /u03/oradata/TEMPDB/TEST_DATA01.DBF

ln -s /proc/8969/fd/32 /u03/oradata/TEMPDB/TEST_LOBS01.DBF

ln -s /proc/8969/fd/33 /u03/oradata/TEMPDB/TEST_INDEX01.DBF

The ORA errors will go!!

========================================================================

Make the tablespace with that datafile read only

SQL> select distinct tablespace_name from dba_data_files where file_name ='/u03/oradata/TEMPDB/TEST_DATA01.DBF';

TABLESPACE_NAME

------------------------------

TEST_DATA

SQL>  select distinct tablespace_name from dba_data_files where file_name ='/u03/oradata/TEMPDB/TEST_LOBS01.DBF';

TABLESPACE_NAME

------------------------------

TEST_LOBS

SQL> select distinct tablespace_name from dba_data_files where file_name ='/u03/oradata/TEMPDB/TEST_INDEX01.DBF';

TABLESPACE_NAME

------------------------------

TEST_INDEX

alter tablespace TEST_DATA read only;

alter tablespace TEST_LOBS read only;

alter tablespace TEST_INDEX read only;

Copy the file from the symlink to a new name

cp /u03/oradata/TEMPDB/TEST_DATA01.DBF /u03/oradata/TEMPDB/TEST_DATA01_NEW.DBF.dbf

cp /u03/oradata/TEMPDB/TEST_LOBS01.DBF /u02/oradata/TEMPDB/TEST_LOBS01_NEW.DBF.dbf

cp /u03/oradata/TEMPDB/TEST_INDEX01.DBF /u02/oradata/TEMPDB/TEST_INDEX01_NEW.DBF.dbf

(IMP !!!! Wait for COPY)

Shutdown the database:

shutdown abort;

Remove the now invalid symlink, and rename the datafile to its original name. Be careful not to remove the wrong file now!

rm -i /u03/oradata/TEMPDB/TEST_DATA01.DBF

rm -i /u03/oradata/TEMPDB/TEST_LOBS01.DBF

rm -i /u03/oradata/TEMPDB/TEST_INDEX01.DBF

mv /u03/oradata/TEMPDB/TEST_DATA01_NEW.DBF.dbf /u03/oradata/TEMPDB/TEST_DATA01.DBF

mv /u02/oradata/TEMPDB/TEST_LOBS01_NEW.DBF.dbf /u03/oradata/TEMPDB/TEST_LOBS01.DBF

mv /u02/oradata/TEMPDB/TEST_INDEX01_NEW.DBF.dbf /u03/oradata/TEMPDB/TEST_INDEX01.DBF

Startup your database normally and make the tablespace read/write:

startup

alter tablespace TEST_DATA read write;

alter tablespace TEST_LOBS read write;

alter tablespace TEST_INDEX read write;