This discussion is archived
13 Replies Latest reply: Jan 29, 2013 6:01 AM by oraman RSS

RMAN Point In Time Recovery - Possible for Standby DB?

oraman Newbie
Currently Being Moderated
Hi all,

as I've not found any info for it, I decided to aks it here.

I would like to know if it's possible without the flashback functionality but with the traditional rman Point In Time Recovery?

I testet it like this:

dgmgrl> disable database standby1;

sql+> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

rman>

run {
RESTORE DATABASE UNTIL TIME "to_date('DEZ 21 2012 12:00:00', 'MON DD YYYY HH24:MI:SS')";
RECOVER DATABASE UNTIL TIME "to_date('DEZ 21 2012 12:00:00', 'MON DD YYYY HH24:MI:SS')";
}

the process was successfull, but the data is not from 12:00. it's from 2 p.m. - the time when the database finished to get redo data.

version oracle 11g r2

thanks in advance!
  • 1. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    mseberg Guru
    Currently Being Moderated
    Yes it is possible.

    You can restore to a SCN ( which is what RMAN does behind the scene anyway with the other command )

    This example shows a roll forward :

    http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

    Please consider closing some of your old questions.

    Best Regards

    mseberg
  • 2. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    oraman Newbie
    Currently Being Moderated
    thank you for the answer, I marked my last questions as answered.

    I just noticed that scn on primary and standby is the same

    also scn on the primary db is the same from different timestamps:


    select current_scn from v$database;

    CURRENT_SCN
    -----------
    2,0078E+11

    alter system archive log current;


    select current_scn from v$database;

    CURRENT_SCN
    -----------
    2,0078E+11

    and here one can see that even during the last two days the scn haven't changed:

    select time_mp,to_char(time_dp, 'dd-mon-rrrr hh24:mi:ss'),SCN from smon_scn_time where rownum < 4;

    TIME_MP TIME_DP     SCN
    ---------- -------- ----------
    1355948534 19.12.12 2,0078E+11
    1356023801 20.12.12 2,0078E+11
    1355891155 19.12.12 2,0078E+11

    do I understand something wrong? I think this is strange.

    Theoretically scn should change after each commit!?
    thank you
  • 3. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    oraman Newbie
    Currently Being Moderated
    select to_char(current_scn) from v$database;
    this shows the correct scn number, i dont know why on some db's I need to_char and on some not.
    mseberg wrote:
    Yes it is possible.

    You can restore to a SCN ( which is what RMAN does behind the scene anyway with the other command )

    This example shows a roll forward :

    http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/
    I did the roll forward technique, however it's not what I meant. my question is "can I use rman point in time recovery instead of flashback database to roll back into the past a standby database?"

    like here
    http://docs.oracle.com/cd/E14072_01/server.112/e10700/scenarios.htm#i1049616
    it's suggested to flashback the standby database after a failover and resetlogs of the primary.

    I would like to know whether it's possible without the flashback feature but with the help of PITR?

    Does anybody have expirience on this?

    thank you
  • 4. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    mseberg Guru
    Currently Being Moderated
    Yes.

    It would be like a reinstate of an old Primary after a failover. You can use either flashback or RMAN to bring it back.

    This note may help :

    Reinstating a Physical Standby Using Backups Instead of Flashback [ID 416310.1]

    Best Regards

    mseberg
  • 5. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    oraman Newbie
    Currently Being Moderated
    please consider the following case:

    db A - primary
    db B - standby
    db C - standby

    failover to db B.

    Reinstate db A:

    select to_char(STANDBY_BECAME_PRIMARY_SCN) from v$database; (execute on B)

    RUN
    {
    SET UNTIL SCN 200786153823;
    RESTORE DATABASE;
    RECOVER DATABASE;
    }
    SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

    I tested it this way and it works. the note 416310.1 is also about reinstate db A - the former primary.

    ok it worked, however (sorry for beeing not clear) my question is still not answered "is it possible to reinstate db C without to recreate it and without flashback ?"

    db C is a standby which continue to be a standby. as I tested point in time recovery --> it doesnt work on a standby db to go to a point in time in the past. please correct me if I'm wrong.

    I think in order to reinstate db C, before to failover to db B, to stop redo apply from db A to db C in order to prevent from diverge of the db C from the new primary db B. then to try pitr if it works.

    what do you think?

    thank you indeed
  • 6. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    mseberg Guru
    Currently Being Moderated
    Yes as long as you have backup for it.

    It get's complex because all 3 databases have one "register" in an RMAN catalog.

    So you backup with RMAN, but its harder to maintain because of the additional pieces.

    Its about sync, so if you restore to the needed SCN you are good.

    I'm pretty much sold on Flashback in a Data Guard environment.

    Best Regards

    mseberg
  • 7. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    oraman Newbie
    Currently Being Moderated
    thank you.

    I dont use catalog, but if it's too complex I'll probably consider to recreate the db which is much simpler...
  • 8. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    oraman Newbie
    Currently Being Moderated
    I did RMAN Point In Time Recovery of the old primary after the failover. it worked.

    just a question of understanding:

    crosscheck backup;
    list backup;
    run
    {
    SET UNTIL SCN here scn of full backup before the failover happened;
    RESTORE DATABASE;
    REcovEr DATABASE;
    }

    outputs:

    RMAN-03002: failure of restore command at 01/22/2013 11:31:03
    RMAN-06026: some targets not found - aborting restore
    RMAN-06023: no backup or copy of datafile 3 found to restore
    RMAN-06023: no backup or copy of datafile 2 found to restore
    RMAN-06023: no backup or copy of datafile 1 found to restore

    but with

    run
    {
    SET UNTIL SCN standby_became_primary_scn;
    RESTORE DATABASE;
    REcovEr DATABASE;
    }

    everything goes well.

    Why in the first case rman does not find the backup although list backup shows that it's there?
    Incarnation of backup and of the database are the same.

    thank you indeed
  • 9. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    oraman Newbie
    Currently Being Moderated
    just for understanding
  • 10. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    mseberg Guru
    Currently Being Moderated
    Hello;

    Best guess is there's no backup for that SCN where in the other case there is.

    Would have a have every detail to know for sure.


    Best Regards

    mseberg
  • 11. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    oraman Newbie
    Currently Being Moderated
    Hello, please see the details:

    the backup I always make takes 15 mins. as we have too many archivelogs I dont backup all of them but just for the time of the backup run to make the backup consistent, so to be sure for the last 30 mins:

    RUN {
    sql "alter system archive log current";
    BACKUP DATABASE;
    sql "alter system archive log current";
    BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1/24/2' UNTIL TIME 'SYSDATE';
    sql "alter system archive log current";
    crosscheck backup;
    crosscheck archivelog all;
    DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';
    delete noprompt backup COMPLETED BEFORE 'SYSDATE-2';
    }

    after failover I try to recover the old primary from backup:

    startup mount;
    crosscheck backup;
    list backup;
    shutdown
    startup nomount;
    restore standby controlfile to 'path/control02.ctl' from 'path/autobackup/2013_01_25/o1_mf_s_805646627_8j530mm6_.bkp';
    restore standby controlfile to 'path/control01.ctl' from 'path/autobackup/2013_01_25/o1_mf_s_805646627_8j530mm6_.bkp';

    startup mount;
    run
    {
    SET UNTIL SCN n;
    RESTORE DATABASE;
    REcovEr DATABASE;
    }

    example of list backup:

    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    45 Full 7.34G DISK 00:01:05 25-JAN-13
    BP Key: 45 Status: AVAILABLE Compressed: NO Tag: TAG20130125T144245
    Piece Name: path/backupset/2013_01_25/o1_mf_nnndf_TAG20130125T144245_8j52yp1m_.bkp
    List of Datafiles in backup set 45
    File LV Type Ckp SCN Ckp Time Name
    ---- -- ---- ---------- --------- ----
    1 Full 200790935363 25-JAN-13 path/system01.dbf
    2 Full 200790935363 25-JAN-13 path/sysaux01.dbf
    3 Full 200790935363 25-JAN-13 path/undotbs01.dbf
    4 Full 200790935363 25-JAN-13 path/users.dbf

    BS Key Size Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    46 875.00K DISK 00:00:00 25-JAN-13
    BP Key: 46 Status: AVAILABLE Compressed: NO Tag: TAG20130125T144351
    Piece Name: path/backupset/2013_01_25/o1_mf_annnn_TAG20130125T144351_8j530qf3_.bkp

    List of Archived Logs in backup set 46
    Thrd Seq Low SCN Low Time Next SCN Next Time
    ---- ------- ---------- --------- ---------- ---------
    1 16 200790935363 25-JAN-13 200790935751 25-JAN-13
    1 17 200790935751 25-JAN-13 200790936657 25-JAN-13
    1 18 200790936657 25-JAN-13 200790936746 25-JAN-13
    1 19 200790936746 25-JAN-13 200790936769 25-JAN-13

    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    47 Full 12.23M DISK 00:00:00 25-JAN-13
    BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20130125T144352
    Piece Name: path/autobackup/2013_01_25/o1_mf_s_805646628_8j530rkc_.bkp
    SPFILE Included: Modification time: 25-JAN-13
    Standby Control File Included: Ckp SCN: 200790936746 Ckp time: 25-JAN-13

    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    48 Full 12.23M DISK 00:00:01 25-JAN-13
    BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20130125T150714
    Piece Name: path/autobackup/2013_01_25/o1_mf_s_805648034_8j54dlxw_.bkp
    SPFILE Included: Modification time: 25-JAN-13
    Control File Included: Ckp SCN: 200790938517 Ckp time: 25-JAN-13

    run recovery till the consistent backup-scn, so I choose the Next SCN of the next-to-last archivelog which were backuped during the "backup database" run:
    run
    {
    SET UNTIL SCN 200790936746;
    RESTORE DATABASE;
    REcovEr DATABASE;
    }

    which outputs:
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 01/18/2013 15:48:04
    RMAN-06026: some targets not found - aborting restore
    RMAN-06023: no backup or copy of datafile 3 found to restore

    thanks!
  • 12. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    mseberg Guru
    Currently Being Moderated
    I think this is your issue :


    Control File Included: Ckp SCN: 200790938517

    SET UNTIL SCN 200790936746;

    So the incarnation of database is different when you go back to 200790936746 from 200790938517.

    Best Regards

    mseberg
  • 13. Re: RMAN Point In Time Recovery - Possible for Standby DB?
    oraman Newbie
    Currently Being Moderated
    thank you, my fault

Legend

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