SQL> select file_id,tablespace_name,file_name from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
----------------------------------------------------------------------------------------
4 USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF
3 SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSAUX01.DBF
2 UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\UNDOTBS01.DBF
1 SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSTEM01.DBF
6 CHEC C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\CHECK01.DBF
5 TEST C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST02.DBF
6 rows selected.
SQL> create table test(name varchar(100)) tablespace users;
Table created.
SQL> insert into test values('**** INSERT BEFORE BACKUP ****');
1 row created.
SQL> commit;
Commit complete.
RMAN> backup datafile 4;
SQL> insert into test values('**** INSERT AFTER BACKUP ****');
1 row created.
SQL> commit;
Commit complete.
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
Delete the datafile USERS01.DBF to show how we can restore and recover it.
SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 83887484 bytes
Database Buffers 75497472 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF'
RMAN> restore datafile 4;
RMAN> recover datafile 4;
SQL> alter database open;
Database altered.
SQL>Select * from test;
name
---------------------------------------------------------------------
**** INSERT BEFORE BACKUP ****
**** INSERT AFTER BACKUP ****