How to restore a lost datafile while the database is open

How to restore a lost datafile while the database is open

firstly conncet to the db
sqlplus "/as sysdba"

SQL> select file_name from dba_data_files where TABLESPACE_NAME = 'TMP';

FILE_NAME
--------------------------------------------------------------------------------
/shared/sync/data/tmp.dbf

Of course to restore a tablespace you need to have a valid backup so I'm going to execute a backup tablespace
command for the TMP tablespace using RMAN:

RMAN> backup tablespace TMP;

Starting backup at 18-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=87 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=/shared/sync/data/tmp.dbf
channel ORA_DISK_1: starting piece 1 at 18-JUL-14
channel ORA_DISK_1: finished piece 1 at 18-JUL-14
piece handle=/oracle/product/10.2.0/db_1/dbs/0qpdmjg3_1_1 tag=TAG20140718T091235 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-JUL-14

--A media failure happened and I've lost all datafiles belonging to TMP tablespace:
rm tmp.dbf
then
rman target/
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    325      SYSTEM               ***     /shared/sync/data/system01.dbf
2    200      UNDOTBS              ***     /shared/sync/data/undo01.dbf
3    400      SYSAUX               ***     /shared/sync/data/sysaux01.dbf
4    200      TBS_1                ***     /shared/sync/data/users.dbf
5    4096     USERS                ***     /shared/sync/data/users01.dbf
6    4096     USERS                ***     /shared/sync/data/users02.dbf
7    500      SYSTEM               ***     /shared/sync/data/system04.dbf
8    0        TMP                  ***     /shared/sync/data/tmp.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMPTS1              20          /shared/sync/data/temp_tbs.dbf
2    1024     TEMP                 1024        /shared/sync/data/temp01.dbf

Above we can see tmp tablespace size is 0.

Next step is to put those datafiles offline
RMAN> sql 'alter database datafile 8 offline';
Restore  your datafile

RMAN> restore datafile 8;

Starting restore at 18-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=88 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00008 to /shared/sync/data/tmp.dbf
channel ORA_DISK_1: reading from backup piece /oracle/product/10.2.0/db_1/dbs/0qpdmjg3_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/product/10.2.0/db_1/dbs/0qpdmjg3_1_1 tag=TAG20140718T091235
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 18-JUL-14

now recover your datafile
RMAN> recover datafile 8;

Starting recover at 18-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 18-JUL-14

Let datafiles be available to all the users, putting them online.
RMAN> sql 'alter database datafile 8 online';

report schema command displays your datafile
RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    325      SYSTEM               ***     /shared/sync/data/system01.dbf
2    200      UNDOTBS              ***     /shared/sync/data/undo01.dbf
3    400      SYSAUX               ***     /shared/sync/data/sysaux01.dbf
4    200      TBS_1                ***     /shared/sync/data/users.dbf
5    4096     USERS                ***     /shared/sync/data/users01.dbf
6    4096     USERS                ***     /shared/sync/data/users02.dbf
7    500      SYSTEM               ***     /shared/sync/data/system04.dbf
8    2048     TMP                  ***     /shared/sync/data/tmp.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMPTS1              20          /shared/sync/data/temp_tbs.dbf
2    1024     TEMP                 1024        /shared/sync/data/temp01.dbf