• Arun Kumar

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';

195 views

Recent Posts

See All

Automate RMAN Backup using Shell Script

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au