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 😉
RMAN old feature: Restore datafile without backup https://t.co/SIZpDigUH4
— Uwe Hesse (@UweHesse) October 20, 2015
Pickleball spielen 
#1 von jaffardba am Oktober 20, 2015 - 12:21
Cool. This was one of my favorite RMAN questions which I usually ask the candidates during interview.
#2 von Narendra am Oktober 20, 2015 - 15:02
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 think it works before 12c. Following is from 11.2.0.4 DB
RMAN> select sysdate from dual ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found „identifier“: expecting one of: „advise, allocate, alter, backup, @, catalog, change, configure, connect, convert, copy, create, crosscheck, delete, drop, duplicate, exit, flashback, grant, host, import, list, mount, open, print, quit, recover, register, release, repair, replace, report, reset, restore, resync, revoke, run, send, set, show, shutdown, spool, sql, startup, switch, transport, unregister, upgrade, validate, {, “
RMAN-01008: the bad identifier was: select
RMAN-01007: at line 1 column 1 file: standard input
#3 von Uwe Hesse am Oktober 20, 2015 - 15:10
I said ‚besides the SQL commands inside the RMAN shell‘ – that is indeed a new feature but not the point of the posting 🙂
#4 von saikat am Oktober 20, 2015 - 17:44
Hi,
What you did by „echo kaputt > /u01/app/oracle/oradata/prima/tbs1.dbf‘;?? especially „kaputt“?…did you just write that to the datafile and corrupted it?
#5 von Uwe Hesse am Oktober 22, 2015 - 13:08
saikat, yes, that command destroys the datafile so I have a reason to restore and recover it. Don’t do that at home 😉
#6 von John Boyle am Oktober 24, 2015 - 02:43
Uwe – not “cool”
1) You have probably been negligent in not having a backup of the datafile – it would in a production environment have infringed your SLA.
2) if not outside the SLA then the tables within this tablespace perhaps should have been Global Temporary tables.
3) If you have “lost” the datafile the most common reason would be that the disk (assuming now that you haven’t deemed mirroring ASM or O/S appropriate) is not available – you have not demonstrated how to recover on a new disk path.
A typical example of a good instructor having a lack of knowledge of what happens in the real world.
#7 von Uwe Hesse am Oktober 24, 2015 - 15:27
John, thank you for your contribution! As a former colleague, you should know that teaching often involves using a simplified model of the more complex reality. Of course I could have mentioned many more distracting details, but the point of this article is to make the audience aware of this feature at all. With that knowledge, they will later on be able to apply it if (for whatever reason) a datafile is lost without having a backup.
#8 von oraclebase am Dezember 1, 2016 - 09:57
John: Judging by the number of „real world“ DBAs that write to me and say things like, „I’ve lost ??? and I don’t have a backup! What do I do?“, I would suggest the assumption that „real world“ scenarios always include such SLAs is a pretty big assumption. 🙂
I talk to many DBAs that believe they are good at backup/recovery, but when I’m speaking to them it becomes obvious they have little-to-no idea about the subject, let alone recent experience of practising common scenarios. I find that particularly distressing as backup/recovery/DR has got to be one of the most important things for a DBA to know IMHO.
I thought your comments were quite harsh. I think it’s pretty clear what the intent of this blog post was. Not every blog post has to be a full scenario. 🙂
Cheers
Tim…