Search

Top 60 Oracle Blogs

Recent comments

RMAN old feature: Restore datafile without backup

Say I have created a new tablespace recently and did not yet take a backup of the datafile. Now I lose that datafile. Dilemma? No, because I can do an ALTER DATABASE CREATE DATAFILE. Sounds complex? Well even if I wouldn’t be aware of that possibility, a simple RMAN restore will work – as if there were a backup:

RMAN> create table adam.nu tablespace tbs1 as select * from adam.sales where rownum<=10000; 
Statement processed 
RMAN> alter system switch logfile;

Statement processed

RMAN> host 'echo kaputt > /u01/app/oracle/oradata/prima/tbs1.dbf';

host command complete

RMAN> select count(*) from adam.nu;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 10/20/2015 11:50:12
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 5: '/u01/app/oracle/oradata/prima/tbs1.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 131

RMAN> alter database datafile 5 offline;

Statement processed

RMAN> restore datafile 5;

Starting restore at 2015-10-20 11:50:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=187 device type=DISK

creating datafile file number=5 name=/u01/app/oracle/oradata/prima/tbs1.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 2015-10-20 11:50:45

RMAN> recover datafile 5;

Starting recover at 2015-10-20 11:50:52
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2015-10-20 11:50:53

RMAN> alter database datafile 5 online;

Statement processed

RMAN> select count(*) from adam.nu;

  COUNT(*)
----------
     10000

Cool isn’t it? Requires that you have all archived logs available since the creation of the tablespace. And besides the SQL commands inside the RMAN shell, it is not a 12c new feature. It works that way since forever, as far as I recall. Don’t believe it, test it!  Maybe not on a production system ;-)

Tagged: Backup & Recovery, RMAN