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


147 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir