RMAN old feature: Restore datafile without backup

helps

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 😉

,

  1. #1 by jaffardba on October 20, 2015 - 12:21

    Cool. This was one of my favorite RMAN questions which I usually ask the candidates during interview.

  2. #2 by Narendra on October 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. #3 by Uwe Hesse on October 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. #4 by saikat on October 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. #5 by Uwe Hesse on October 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. #6 by John Boyle on October 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. #7 by Uwe Hesse on October 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. #8 by oraclebase on December 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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: