Can you restore from a full online backup ?

The question is not HOW TO DO IT but WHETHER YOU CAN DO IT !

A typical backup script would contains something like BACKUP DATABASE PLUS ARCHIVELOG:

backup database format
'/u99/backup/DB01/20150518/full_0_%d_s%s_p%p'
plus archivelog format
'/u99/backup/DB01/20150518/arc_%d_s%s_p%p';

Starting backup at 2015-05-18_18:27:55
current log archived
input archived log thread=1 sequence=469
...
piece handle=
/u99/backup/DB01/20150518/arc_DB01_s86_p1
Finished backup at 2015-05-18_18:27:58

Starting backup at 2015-05-18_18:27:58
input datafile file number=00002 name=
/u02/oradata/DB01/undots01.dbf
...
including current control file in backup set
including current SPFILE in backup set
piece handle=
/u99/backup/DB01/20150518/full_0_DB01_s88_p1
Finished backup at 2015-05-18_18:28:16

Starting backup at 2015-05-18_18:28:16
current log archived
input archived log thread=1 sequence=20
piece handle=
/u99/backup/DB01/20150518/arc_DB01_s89_p1
Finished backup at 2015-05-18_18:28:17

This sounds pretty safe, but what happened if you keep this full backup for ever ? do you have all what you need in it to restore ?

It depends. Chance exists that you can restore. To annoy the paranoiacs, here is a counter example.

  1. you start your full backup at 6pm.
    backuping sequence 21,22,23,24
    backup datafiles…

  2. at 7 pm one cronjob issue backup archivelog all delete input;
    backuping and deleting sequence 21,22,23,24,25,26,27,28,29,30

  3. at 8pm your backup is about to finish
    backuping sequence 31

Well, where is my sequence 27 ?

Let’s try

rm /tmp/arch_DB01*
startup force mount;
crosscheck backup;restore database;


Starting restore at 2015-05-18_18:47:45
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 2015-05-18_18:49:51

Ok, the restore was fine. Now what?

RMAN> recover database;

Starting recover at 2015-05-18_18:50:35
using channel ORA_DISK_1

starting media recovery

RMAN-00571: =================================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =================================================
RMAN-03002: failure of recover command at 05/18/2015 18:50:36
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 30
RMAN-06025: no backup of archived log for thread 1 with sequence 29
RMAN-06025: no backup of archived log for thread 1 with sequence 27

RMAN> alter database open;

RMAN-00571: =================================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =================================================
RMAN-03002: failure of alter db command at 05/18/2015 18:51:29
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oradata/DB01/system01.dbf'

You have lost your database. Even if you did a full online backup plus archivelog, it did not contain all the archivelogs and RMAN did not tell you.

There is more than one way to reduce this annoyance :

1) don’t backup archivelogs during full backup
2) don’t delete archivelog backups done during a full backup
3) report any archivelog backups that run during a full. Either by looking in the RMAN dictionary or in your house logfiles
4) do offline backup for long term archives
5) don’t trust RMAN

And as well :
Test your backups 😀

7 thoughts on “Can you restore from a full online backup ?

  1. Franck Pachot

    Hi Laurent,
    I was discussing that with a colleague recently. The solution (1) was implemented at customer site but I don’t like it because it means that when backup database is long then archived logs are not backed up and RPO is not fulfilled.
    I proposed something like your solution (3) but using ‘RESTORE PREVIEW’ after the backup in order to list the required files. I always prefer to rely on RMAN to find the required backup pieces rather than guess from file name or date.
    Regards,
    Franck.

  2. Mike

    Hi Laurent,

    I did not understand. Why RMAN would lost the sequence 27? By the way, It was backed up by RMAN, right? Why recover would fail?

    Thanks

    Mike

  3. Laurent Schneider Post author

    @Mike : it was saved in another folder, /tmp, which I removed with rm /tmp/arch_DB01*. As long as you have both your full backups and your archivelog backups you are fine

    @Frank: (1) could be done without DELETE INPUT. (3) RESTORE DATABASE PREVIEW works nicely šŸ™‚

    no backup of archived log for thread 1 with sequence 27 found to restore
    no backup of archived log for thread 1 with sequence 29 found to restore
    no backup of archived log for thread 1 with sequence 30 found to restore

  4. Pingback: Irrecoverable full backup part II : reporting | Laurent Schneider

  5. Pingback: Irrecoverable part III : a fix | Laurent Schneider

  6. Jakub Wartak

    6) fix ‘delete input’ arch job, with
    a) backup archivelog all not backed up 1 times;
    b) delete noprompt archivelog all backed up 1 times completed before time ‘sysdate-Nā€² [with retention and properly size your FRA (it can still run parallel to full/incr/cumulative one).
    c) proper retention policy

    This works awesomely good as you have N*24h redo logs for any potential DataGuard gap plus it’s turbo mode for any potential +DATA/datafiles restore (as there is no need to restore archived logs as they are already in place). Happy to listen if you have bad experiences with such implementation.

    BTW: Pythian did nice presentation: http://www.slideshare.net/yvelikanov/10-problems-with-your-rman-backup-script worth reading.

Comments are closed.