RMAN Particular Datafile Backup and Restore

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 ****