RMAN Single Datafile Recovery

Create New Tablespace With Single Data File

SQL> create tablespace custom datafile '/u01/app/oracle/oradata/proddb/custom01.dbf' size 10m;

Take Backup And Simulate Error

Connect to the target DB and catalog

Take DB full backup

RMAN> backup database plus archivelog format '/u02/rman_bkp/proddb/proddb_%U';

Once backup is completed, check backup tag via below command:

RMAN> list backup of database summary;

TAG20170116T111653

Create Test Table Inside New Tablespace

SQL> create table test(serial number(2),name varchar2(5)) tablespace custom;
SQL> insert into test values(1,'one');
SQL> insert into test values(2,'Two');
SQL> insert into test values(3,'Three');
SQL> insert into test values(4,'Four');

SQL> commit;

Simulate Failure

Delete only the data file associated with CUSTOM tablespace

rm -rf <Custom tablespace DF location>

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER;

Start Recovery

Connect to RMAN and issue below command:

RMAN> list failure;

Take the tablespace with the missing datafile offline

RMAN> SQL "ALTER TABLESPACE custom OFFLINE IMMEDIATE";
RMAN> RESTORE TABLESPACE custom;
RMAN> RECOVER TABLESPACE custom;
RMAN> SQL "ALTER TABLESPACE custom ONLINE";