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