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.
- Note the current timestamp and SCN before shutdown.
- Restart the database to mount state.
- List all backups we have now.
- Perform database point-in-time recovery in one run block.
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
[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
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
...
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
Further Reading: Oracle TO_DATE Function Examples.
You can see that I put all required steps in a run block for convenience.
- The error messages when required files are missing.
- Use RECOVER DATABASE UNTIL ... under SQL prompt to indicate the correct file location.
- Open database with resetlogs.
...
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>
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.
SQL> alter database open resetlogs;
More related posts that you may be interested in:
- When the SPFILE is Missing
- When the Controlfile is Missing
- When Some of the Archived Logs are Missing
- When Several Datafiles Are Missing - Restore From Local Backups
- When Several Datafiles Are Missing - Restore From the Standby Database
- When Almost Everything of a Database is Missing
- How Will the Database React to Missing Tempfiles