RMAN

How to Perform Database Point-In-Time Recovery

Sometimes, DBA may want the database back to a specific time just before a big mistake happened on the database. This is called database point-in-time recovery (DBPITR).

Here’s to demonstrate a DBPITR with a specific time.
  1. Note the current timestamp and SCN before shutdown.
  2. SQL> column systimestamp format a50;
    SQL> select systimestamp, current_scn from v$database;

    SYSTIMESTAMP                                       CURRENT_SCN
    -------------------------------------------------- -----------
    13-DEC-12 06.00.00.173272 PM +08:00                    2975639

  3. Restart the database to mount state.
  4. [oracle@primary01 ~]$ srvctl stop database -d compdb -o abort
    [oracle@primary01 ~]$ rman target /
    ...
    connected to target database (not started)

    RMAN> startup mount;

    Oracle instance started
    database mounted

    Total System Global Area     839282688 bytes

    Fixed Size                     2217992 bytes
    Variable Size                792725496 bytes
    Database Buffers              41943040 bytes
    Redo Buffers                   2396160 bytes

  5. List all backups we have now.
  6. RMAN> list backup;

    List of Backup Sets
    ===================

    ...
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    5       Full    1.14G      DISK        00:02:35     12-DEC-12
            BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T085811
            Piece Name: +DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293
      List of Datafiles in backup set 5
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      1       Full 2937050    12-DEC-12 +DATA/primdb/datafile/system.900.801828739
      2       Full 2937050    12-DEC-12 +DATA/primdb/datafile/sysaux.898.797943569
      3       Full 2937050    12-DEC-12 +DATA/primdb/datafile/undotbs1.899.797943687
      4       Full 2937050    12-DEC-12 +DATA/primdb/datafile/users.897.801828959
      5       Full 2937050    12-DEC-12 +DATA/primdb/datafile/example.902.801828959
      6       Full 2937050    12-DEC-12 +DATA/primdb/datafile/undotbs2.901.797943737

    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    6       Full    18.64M     DISK        00:00:09     12-DEC-12
            BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T085811
            Piece Name: +DATA/primdb/backupset/2012_12_12/ncsnf0_tag20121212t085811_0.1080.801824455
      SPFILE Included: Modification time: 12-DEC-12
      SPFILE db_unique_name: PRIMDB
      Control File Included: Ckp SCN: 2937050      Ckp time: 12-DEC-12
    ...

  7. Perform database point-in-time recovery in one run block.
  8. RMAN> run {
    2> set until time "to_date('2012-12-13 18:00:00','yyyy-mm-dd hh24:mi:ss')";
    3> restore database;
    4> recover database;
    5> alter database open resetlogs;
    6> }

    executing command: SET until clause

    Starting restore at 13-DEC-12
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=39 instance=primdb1 device type=DISK
    flashing back control file to SCN 2974272

    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to +DATA/primdb/datafile/system.900.801828739
    channel ORA_DISK_1: restoring datafile 00002 to +DATA/primdb/datafile/sysaux.898.797943569
    channel ORA_DISK_1: restoring datafile 00003 to +DATA/primdb/datafile/undotbs1.899.797943687
    channel ORA_DISK_1: restoring datafile 00004 to +DATA/primdb/datafile/users.897.801828959
    channel ORA_DISK_1: restoring datafile 00005 to +DATA/primdb/datafile/example.902.801828959
    channel ORA_DISK_1: restoring datafile 00006 to +DATA/primdb/datafile/undotbs2.901.797943737
    channel ORA_DISK_1: reading from backup piece +DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293
    channel ORA_DISK_1: piece handle=+DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293 tag=TAG20121212T085811
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:04:37
    Finished restore at 13-DEC-12

    Starting recover at 13-DEC-12
    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 71 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_71.1081.801824977
    archived log for thread 1 with sequence 72 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_72.1082.801824981
    ...
    archived log for thread 1 with sequence 89 is already on disk as file +DATA/primdb/archivelog/2012_12_13/thread_1_seq_89.1111.801942799
    archived log for thread 1 with sequence 90 is already on disk as file +DATA/primdb/onlinelog/group_2.904.797943777
    archived log for thread 2 with sequence 64 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_2_seq_64.1091.801846091
    archived log for thread 2 with sequence 65 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_2_seq_65.1092.801846095
    ...
    archived log for thread 2 with sequence 74 is already on disk as file +DATA/primdb/archivelog/2012_12_13/thread_2_seq_74.1110.801942797
    archived log for thread 2 with sequence 75 is already on disk as file +DATA/primdb/onlinelog/group_3.905.797943785
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_71.1081.801824977 thread=1 sequence=71
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_72.1082.801824981 thread=1 sequence=72
    ...
    archived log file name=+DATA/primdb/archivelog/2012_12_13/thread_1_seq_88.1106.801942681 thread=1 sequence=88
    archived log file name=+DATA/primdb/archivelog/2012_12_13/thread_2_seq_73.1109.801942789 thread=2 sequence=73
    media recovery complete, elapsed time: 00:00:46
    Finished recover at 13-DEC-12

    database opened

    You can see that I put all required steps in a run block for convenience.
If there is any file required by RMAN cannot be found, it will generate errors during database recovery.
  1. The error messages when required files are missing.
  2. ...
    Oracle Error:
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01669: standby database control file not consistent

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 12/13/2012 19:08:35
    RMAN-06053: unable to perform media recovery because of missing log
    RMAN-06025: no backup of archived log for thread 2 with sequence 73 and starting SCN of 2974309 found to restore
    ...

    RMAN>

  3. Use RECOVER DATABASE UNTIL … under SQL prompt to indicate the correct file location.
  4. SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

    Session altered.

    SQL> recover database until time '2012-12-13 19:40:00';
    ORA-00279: change 2948309 generated at 12/12/2012 15:01:34 needed for thread 2
    ORA-00289: suggestion :
    +DATA/primdb/archivelog/2012_12_13/thread_2_seq_66.1097.801918263
    ORA-00280: change 2948309 for thread 2 is in sequence #66


    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    +DATA/primdb/archivelog/2012_12_13/thread_2_seq_66.1097.801918263.arc
    ORA-00279: change 2948450 generated at 12/12/2012 15:02:03 needed for thread 1
    ORA-00289: suggestion :
    +DATA/primdb/archivelog/2012_12_12/thread_1_seq_82.1095.801846129
    ORA-00280: change 2948450 for thread 1 is in sequence #82


    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    AUTO
    ORA-00279: change 2949031 generated at 12/12/2012 15:02:08 needed for thread 1
    ORA-00289: suggestion :
    +DATA/primdb/archivelog/2012_12_13/thread_1_seq_83.1096.801918261
    ORA-00280: change 2949031 for thread 1 is in sequence #83
    ...


    Log applied.
    Media recovery complete.

  5. Open database with resetlogs.
  6. SQL> alter database open resetlogs;
More related posts that you may be interested in:

Leave a Reply

Your email address will not be published. Required fields are marked *