RMAN restore from a particular Days TAPE

Hi,

We got a scenario where we were required to restore database from a particular tape. Client asked us to find out the tape which should be used for that restore. We faced lots of issues in getting those tapes in place.

1) We were trying point in time recovery till 1 PM for restoring(time was not a constrain)
2)Restore command was not able to find the required backup and as it was 10g it failed after trying to access all the backups taken before that time.
3)Customer complained that he did not find any RMAN request was not coming to TAPE device.
4)We wasted about 8 hours.

Below are the couple of things we learnt during this activity.

1) RMAN always tries to restore from the backup taken prior to the time until which we try to restore

For Example:

suppose Until time is set till 13:00:00
Daily backup happens at 5 PM
And we are required to restore say till 29:07:07

In this scenario rman will restore from backup taken on 28th and apply all the archives generated after the backup till 29:07:07 13:00:00.
So the we must mount the tape of 28th .

Client wanted us to restore from tape containing the 29th backup. In this case you cannot use until time before 5:30Pm (Backup completion time)

Also one more thing we need to keep in mind is RMAN Always restores from the prevoius backup, it first tries to restore from 28th backup , if it is not there it sarches for 27th backup (and apply all the archive log till 29) so on..

The above feature is only present in 10G and later releases.

This is how we need to find out the backup and time for till which we can restore so that we remain within the available options.

Assuming that we have a CATALOG. Use the below select statements and extract the time from the catalog.

select to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS') from RC_BACKUP_DATAFILE where trunc(completion_time) >= trunc(sysdate-9);

.
. TO_CHAR(COMPLETION_
-------------------
06/29/2007 16:47:50
06/29/2007 16:47:50
06/29/2007 16:47:50
06/29/2007 16:45:09
06/29/2007 16:28:10
06/29/2007 15:18:42
06/29/2007 14:29:03
06/29/2007 14:19:37
06/29/2007 14:45:31
06/29/2007 14:48:36
06/29/2007 15:02:08

TO_CHAR(COMPLETION_
-------------------
06/29/2007 15:04:45
06/29/2007 15:17:04
06/29/2007 15:19:37
06/29/2007 15:21:37
06/29/2007 15:20:57
06/29/2007 15:20:19
06/29/2007 15:20:51
06/29/2007 15:21:20
06/29/2007 15:21:27
06/29/2007 15:21:35
06/29/2007 15:21:28


This Confirms the backup completion time. We can select any time after 15:21:28 which will confirm that the rman will restore from 29th backup.

Now till what time to recover , Since it is a Tape , if we give the restore time as 15:21:28 ,Then Rman will restore from the proper backup but DB open will fail saying some DBf's need more recovery. In such case you need to restore all the archive logs and apply them (or recover using RMAN) which will take plenty of time.

Its better we give a proper time for restore.

Use the below query.
select DB_NAME,to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS'),THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from rc_backup_redolog where trunc(completion_time) >= trunc(sysdate-9) and DB_NAME='PROD' order by 3,4,2;

.
.
PROD 06/29/2007 17:05:02 1 22239 2445236995 2445476389

DB_NAME TO_CHAR(COMPLETION_ THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------- ------------------- ---------- ---------- ------------- ------------
PROD 06/29/2007 17:05:02 1 22240 2445476389 2445739485
PROD 06/29/2007 17:05:02 1 22241 2445739485 2446147163
PROD 06/29/2007 17:05:02 1 22242 2446147163 2446554642
PROD 06/29/2007 17:05:02 1 22243 2446554642 2446899303
PROD 06/29/2007 17:05:02 1 22244 2446899303 2446936172
PROD 06/29/2007 17:05:02 1 22245 2446936172 2447040692
PROD 06/29/2007 17:05:02 1 22246 2447040692 2447308086
PROD 06/29/2007 17:05:02 1 22247 2447308086 2447610092
PROD 06/29/2007 17:05:02 1 22248 2447610092 2447611059
PROD 06/30/2007 16:56:09 1 22249 2447611059 2447876345
PROD 06/30/2007 16:56:09 1 22250 2447876345 2448128725


This is the archive log which is in the TAPE "PROD 06/30/2007 16:56:09 1 22250 2447876345 2448128725"

Now we can use the time "06/30/2007 16:56:09 " as the candidate for until time.
But good practice is to use until scn 2448128725 which is very accurate OR time when archive with sequence 22250 was created.

Now if we use "06/30/2007 16:56:09 " as until time then RMAN thinks that it must recover till "06/30/2007 16:56:09 " but this time is actually the archivelog backup completion time.
This archive must be generated before '06/30/2007 16:56:09 ".

The RMAN recovery will fail saying that it could not find archivelog 22251 .

In the above case do the below steps.

on the DB where rman restore failed

Connect as RMAN

rman target /

recover database until cancel

.
.

Just enter Cancel.

then open the database using restelogs (if required use "alter database open resetlogs using backup controlfile"


The above scenario will not occur if we use the until SCN or "Archive log ceration time"

Also in 10G as told earlier the RMAN will will fail over to previous backup if it does not find the exact backup.
eg :

it must restore from 29Th backup. If that tape is not mounted then it will try to restore from 28th backup . If 28th backup is available then it will restore the DBF's but recover will fail . It will now search for archive logs generated on 28'th after backup till "'06/30/2007 16:56:09" .
so some archives are in 28th tape and rest in 29th tape.

To avoid this make sure the RMAN restores from proper backup set.
We will use the above time for restore. Also list backup of database gives the information about the backup set which should be used for restore

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136394 Incr 0 191G SBT_TAPE 03:18:06 29-JUN-07
BP Key: 136397 Status: AVAILABLE Compressed: NO Tag: TAG20070629T133039
Handle: bk_u6lilgak0_s2261_p1_t626535040 Media:
List of Datafiles in backup set 136394
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/system01.dbf
2 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/undotbs01.dbf
3 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/sysaux01.dbf

Rman must restore from bk_u6lilgak0_s2261_p1_t626535040 which is the first backupset created on 29th . This is present in the restore logfile. Make sure that RMAN does not fail over to previous days backup if so our untilTime should be the Culprit Or TAPE is not the correct one ot it May not be mounted properly.

Thanks,
Sandarsh.