This discussion is archived
10 Replies Latest reply: Nov 30, 2012 5:39 AM by user522961 RSS

sysdate -8

user522961 Newbie
Currently Being Moderated
Hi,
on 10g R2 on AIX.
today (30th of Nov at 7PM) I want to restore my DB from a full backup done on 21 of Nov at 9AM.

Should I :
RMAN> Run {
2> shutdown immediate;
3> startup nomount;
4> SET UNTIL TIME "TRUNC(SYSDATE) - 8";
Is my "SET UNTIL TIME "TRUNC(SYSDATE) - 8" accurate ?

Or other value for SET UNTIL TIME ???

Thanks.
  • 1. Re: sysdate -8
    Fran Guru
    Currently Being Moderated
    SET UNTIL TIME 'SYSDATE-8';

    or

    SET UNTIL TIME 'Nov 21 2012 09:00:00';

    For more details:
    http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr006.htm

    Edited by: Fran on 30-nov-2012 3:27
  • 2. Re: sysdate -8
    user522961 Newbie
    Currently Being Moderated
    thank. You are true.
    But I should use/modify a standard script already using by our team and in it we can only use sysdate. That's the reason of my question other wise the SET UNTIL TIME 'Nov 21 2012 09:00:00'; is quite well known.
    Regards.
  • 3. Re: sysdate -8
    Girish Sharma Guru
    Currently Being Moderated
    run
     {
     sql 'alter session set NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"';
     set until time = '21-11-2012 09:00:00';
     restore database;
     recover database;
     sql 'alter database open resetlogs';
     }
    Regards
    Girish Sharma
  • 4. Re: sysdate -8
    JohnWatson Guru
    Currently Being Moderated
    RMAN will decide what backups to restore, but they will be at least as old as the time you specify. Then when you recover, the recovery will be up to the time specified. Because you are truncating the date, that will be up to midnight:
    orcl> select sysdate, TRUNC(SYSDATE) - 8 from dual;
    
    SYSDATE           TRUNC(SYSDATE)-8
    ----------------- -----------------
    30-11-12 11:32:02 22-11-12 00:00:00
    
    orcl>
    from what you say, I think that is in fact earlier than the time you want.
  • 5. Re: sysdate -8
    JohnWatson Guru
    Currently Being Moderated
    Girish Sharma wrote:
    run
    {
    sql 'alter session set NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"';
    set until time = '21-11-2012 09:00:00';
    restore database;
    recover database;
    sql 'alter database open resetlogs';
    }
    Regards
    Girish Sharma
    Are you sure of this, Girish? I think that will restore an earlier backup, and recover it up to when the backup OP asks for was started.
  • 6. Re: sysdate -8
    Fran Guru
    Currently Being Moderated
    TRUNC(SYSDATE) should be used when you want at exactly hour, in your case 9 am.
    I always use sysdate-X or 'day hour:sec', so I'm not 100% sure. I think it will be ok.
  • 7. Re: sysdate -8
    user522961 Newbie
    Currently Being Moderated
    thanks to all.
    I tried :
    SQL>  select to_char (sysdate -9  ,'DD-MON-RR HH:MI AM') from dual;
    
    TO_CHAR(SYSDATE-9,
    ------------------
    21-NOV-12 12:36 PM
    Then I think I should use "SET UNTIL TIME "TRUNC(SYSDATE) - 9".
  • 8. Re: sysdate -8
    Girish Sharma Guru
    Currently Being Moderated
    "sysdate" is always "current date-time"
    A "number" when applied to dates is "number of days" .

    Thus "sysdate-3" is "72 hours before the current time".

    Hemant K Chitale @ Re: set until 'sysdate - '

    Now, you conclude that using sysdate is good or set until time with given way is good ?

    Regards
    Girish Sharma
  • 9. Re: sysdate -8
    user296828 Expert
    Currently Being Moderated
    today (30th of Nov at 7PM) I want to restore my DB from a full backup done on 21 of Nov at 9AM.
    Is my "SET UNTIL TIME "TRUNC(SYSDATE) - 8" accurate ?
    That is not accurate. You are going back to 8 days in the past and the time you are running the restore command.
    Or other value for SET UNTIL TIME ???
    When you know the time you want to restore, you should always be using

    run {
    set until time *"to_date('30-11-2012 09:00:00','dd-mm-yyyy hh24:mi:ss')";*
    restore database;
    recover database;
    }
  • 10. Re: sysdate -8
    user522961 Newbie
    Currently Being Moderated
    Thanks to all.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points