RMAN Single Datafile Recovery

In this article we will be looking at how to recover a single datafile attached to a tablespace using RMAN. We will be creating a test tablespace and then simulate the failure.

Create New Tablespace With Single Data File

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

Take Backup

Connect to the target DB and catalog and take DB full backup

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 DATAFILE '<df location>;
RMAN> RECOVER DATAFILE '<df location>;
RMAN> SQL 'ALTER TABLESPACE custom ONLINE';