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.
you start your full backup at 6pm.
backuping sequence 21,22,23,24
backup datafiles…
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
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 😀
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.
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
@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
Here is the script I use to get list of files fom a ‘restore preview’: http://www.dbi-services.com/index.php/blog/entry/list-all-rman-backups-that-are-needed-to-recover
Pingback: Irrecoverable full backup part II : reporting | Laurent Schneider
Pingback: Irrecoverable part III : a fix | Laurent Schneider
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.