• Arun Kumar

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


Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Perform Manual Switchover on Physical Standby

Connect to proddb database via client: Take a new putty session and connect to proddb database via client machine. Keep querying below sqlplus sys/sys@proddb as sysdba SQL> select name, open_mode, db

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback