Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008

    Unanswered: Any magic formula for the set until time clause?


    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')";
    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?

    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.

    --------------------- --------------------- ---------- --- --- --- - -------------------- --------------------
            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
    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.

    --------------------- --------------------- ---------- --- --- --- - -------------------- --------------------
            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
    From the sample information above, 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.

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    For 10278 & 10278 NEXT_TIME is before COMPLETION_TIME.
    Very curious.

    When I do RMAN hot backups, I switch log file before starting & note that SEQ#

    Upon completion of backups I force another log file switch & note that SEQ#.

    I ensure backup of all archived redo log file between these 2 values inclusive.

    With regards to restoration, any answers depends upon what you have to restore from.

    If you have very recent actual REDO log files intact, a restore closer in time to failure than when you have only most recent archived redo log file.

    IMO, there is no single solution that handles all possibilities.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Since log archiving happens asynchronously, I think there is a good chance that some changes prior to the archiving completion time may be in the next log file. I would think that USING SEQUENCE is a more reliable method of specifying the restore point.
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jun 2003
    West Palm Beach, FL

    Wink Copy them over...

    When RMAN fails with missing archive logs, just copy them over and perform a regular restore database until cancel in sqlplus.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Sep 2008
    Thanks for all your responses. They are all very helpful.

    Are you eagle-eyed or what ... didn't notice that NEXT_TIME COMPLETION_TIME thingy ... ... very curious indeed ... before your start wondering, I didn't manually change it. that is what it is on the database, so I'll keep an eye on that ...

    BTW, anyone here from NZ ... ???

  6. #6
    Join Date
    Sep 2008

    BTW, forgot to mention ... since the backup is triggered by the media management layer (MML) software, in this case, Commvault, I do not have the access to force a log switch either before or after the backup.

  7. #7
    Join Date
    Jul 2003
    then keep applying archivelogs until you can open db resetlogs.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts