RMAN recover with backup taken before RESETLOGS

This functionality has been available since the early 10 release, but I am embarassed to say I have never tested it.

Of course, you know where this is going. Last Friday evening, we took a full QA database backup. Over the weekend, some developer activity trashed a lot of the data. When I came in on Monday, we were asked to restore the database to its state as of early Saturday morning. “No problem”, we replied. This, we had done many times before, pretty standard stuff. We restored and recovered the database, and everyone was happy. I had this gnawing thought in the back of my head to take another backup, but then I thought, “you can always recover through a RESETLOGS with previous backups”, so I pushed the thought out and went on my way.

This afternoon I got a text saying, “hey, that problem we had over the weekend? we had it again last night. we need to restore to 9PM Wednesday evening.”

I thought, “Well, I hope this works”.

if you are looking for the short answer, all I did was the following:

#at the shell...
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

#...and in RMAN...
run {
  set until time '2012-08-22 21:00:00';
  allocate channel t1 device type 'sbt_tape';
  allocate channel t2 device type 'sbt_tape';
  allocate channel t3 device type 'sbt_tape';
  allocate channel t4 device type 'sbt_tape';
  allocate channel t5 device type 'sbt_tape';
  restore database;
  recover database delete archivelog maxsize 25g;
  release channel t1;
  release channel t2;
  release channel t3;
  release channel t4;
  release channel t5;
}

What I found was that it was “smart” enough to restore the first backup, apply all the logs until the first resetlogs operation, and then start applying the ones starting at sequence 1 after the resetlogs operation.

Good stuff!