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


Comments