Any magic formula for the set until time clause?
Follow up by mail Click here

Subject: Any magic formula for the set until time clause?
Author: BenBart BartBen, Philippines
Date: Sep 26, 2009, 342 days ago
Os info: AIX+LINUX+Windows
Oracle info: Oracle 9/10/11
Error info: Here are some sample information

For 20090918, we used set until time "to_date('18/09/2009 14:42:00', 'dd/mm/yyyy hh24:mi:ss')"; and RMAN complete
everything with no issues, i.e, recover, generate DBID etc.

[code]

NEXT_CHANGE# FIRST_CHANGE# SEQUENCE# ARC APP DEL S COMPLETION_TIME NEXT_TIME
--------------------- --------------------- ---------- --- --- --- - -------------------- --------------------
1673665720701 1673665501303 10272 YES NO YES D 18-SEP-2009 14:26:09 18-SEP-2009 14:26:09
1673665720715 1673665720701 10273 YES NO YES D 18-SEP-2009 14:26:11 18-SEP-2009 14:26:11
1673665794694 1673665720715 10274 YES NO YES D 18-SEP-2009 14:41:29 18-SEP-2009 14:41:29
1673665794708 1673665794694 10275 YES NO YES D 18-SEP-2009 14:41:30 18-SEP-2009 14:41:30
1673666174843 1673665794708 10276 YES NO YES D 18-SEP-2009 16:00:14 18-SEP-2009 16:00:14
1673666174857 1673666174843 10277 YES NO YES D 18-SEP-2009 16:00:17 18-SEP-2009 16:00:17
1673669901331 1673666174857 10278 YES NO YES D 18-SEP-2009 18:12:36 18-SEP-2009 18:12:31
1673669901347 1673669901331 10279 YES NO YES D 18-SEP-2009 18:12:42 18-SEP-2009 18:12:41

[/code]

For 20090925, we used set until time "to_date('25/09/2009 14:46:23', 'dd/mm/yyyy hh24:mi:ss')"; and RMAN did not do
all its task as it failed to restore an archivelogs that has not been archived yet.

[code]

NEXT_CHANGE# FIRST_CHANGE# SEQUENCE# ARC APP DEL S COMPLETION_TIME NEXT_TIME
--------------------- --------------------- ---------- --- --- --- - -------------------- --------------------
1673840228129 1673840228115 10501 YES NO YES D 25-SEP-2009 07:59:48 25-SEP-2009 07:59:48
1673840235101 1673840228129 10502 YES NO YES D 25-SEP-2009 10:00:16 25-SEP-2009 10:00:16
1673840240941 1673840235101 10503 YES NO YES D 25-SEP-2009 12:00:42 25-SEP-2009 12:00:42
1673840240955 1673840240941 10504 YES NO YES D 25-SEP-2009 12:00:44 25-SEP-2009 12:00:44
1673840255585 1673840240955 10505 YES NO YES D 25-SEP-2009 14:45:48 25-SEP-2009 14:45:48
1673840255599 1673840255585 10506 YES NO YES D 25-SEP-2009 14:45:50 25-SEP-2009 14:45:50
1673840261168 1673840255599 10507 YES NO YES D 25-SEP-2009 16:02:15 25-SEP-2009 16:02:15
1673840261182 1673840261168 10508 YES NO YES D 25-SEP-2009 16:02:18 25-SEP-2009 16:02:18

[/code]
Message: Hi,

Here is another long story ... :?

Is there a magic formula on what should be the date+timestamp for doing a point-in-time restore?

We are using a Media Management Layer and at the moment, am using the completion time of the backup as the
timestamp. Sometimes it work and RMAN does all the work, restores datafiles and archivelogs, do the recovery,
generate a new DBID and restart the database.

However in some cases, the timestamp from the completion time of the backup does not work which is what happened
over the weekend that causes my RMAN catalog to become out of whack. RMAN do its usual stuff of restoring the datafiles
and archivelogs, but unfortunately it cannot restore some required additional archivelogs since it is still in the redologs
and had not been archived, so it stopped doing the recovery, DBA startup and alter database open resetlogs, database opened
but the DBID was not changed, the scheduled backup for the duplicated db ran with no failures but the original source
database began having RMAN-20004 errors because it still has the same DBID with the dupdb.


run {
set until time "to_date('25/09/2009 14:46:23', 'dd/mm/yyyy hh24:mi:ss')";
ALLOCATE AUXILIARY CHANNEL ch1 TYPE = 'sbt_tape' PARMS="BLKSIZE=262144";
duplicate target database to 'dupdb';
}


Does using the completion_time below a good start towards determining the most reliable point-in-time recovery
period to use?


select next_change#, first_change#, sequence#, archived, applied, deleted, status, completion_time, next_time
from v$archived_log

From Google, some suggest deducting 5-10secs off the completion time of the backup. Not sure if that is reliable though.
I would assume that if there is a place for the most reliable until time to use, that information should be in the
database, but where or which?

From the sample information, none of the COMPLETION_TIME satisfies the SET UNTIL TIME to use so I conclude
that v$archived_log is not the best place to check for what timestamp to use. If that is the case, where should
that information come from, via trial and error?

Any suggestion will be very much appreciated.

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: Any magic formula for the set until time clause?
Author: LKBrwn DBA>, United States
Date: Sep 28, 2009, 340 days ago
Message: Instead of using "UNTIL TIME", use:

SET UNTIL SEQUENCE={sequence#} THREAD={thread#};

And you get the info with this query: 

SELECT   MAX (sequence#), thread#

    FROM v$archived_log

   WHERE archived = 'YES' AND status = 'A'

GROUP BY thread#;

:p
.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here