RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
When you are performing a PITR (Point In Time Recovery), you might have undergone the error “RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time”. The reason that RMAN throws this error is when you are trying to perform a PITR of the database to a time before the database was last opened with RESETLOGS. In other words, the time you speicify in the “until time” clause of RMAN is the time that the database was in its previous incarnation and not the CURRENT incarnation.
Here is an example, that I would like to demonstrate with.
Database Name: srprim Database Version: 11.2.0.3
Step1 : I restore the SPFILE from the backup that was taken on 1st Dec location “/u01/backup/1_dec”
[oracle@ora1-1 1_dec]$ ls -lrth total 1.1G -rw-r----- 1 oracle oinstall 1.1G Dec 1 11:28 SRPRIM_inc0_20121201_05nrp04l_1_1.bak -rw-r----- 1 oracle oinstall 9.7M Dec 1 11:28 SRPRIM_inc0_20121201_06nrp06c_1_1.bak [oracle@ora1-1 1_dec]$ pwd /u01/backup/1_dec [oracle@ora1-1 dbs]$ rman target sys/oracle@srprim Recovery Manager: Release 11.2.0.3.0 - Production on Sat Dec 1 20:22:12 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup force nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.3/db1/dbs/initsrprim.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 158662656 bytes Fixed Size 2226456 bytes Variable Size 92276456 bytes Database Buffers 58720256 bytes Redo Buffers 5439488 bytes RMAN> restore spfile from '/u01/backup/1_dec/SRPRIM_inc0_20121201_06nrp06c_1_1.bak'; Starting restore at 01-DEC-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/1_dec/SRPRIM_inc0_20121201_06nrp06c_1_1.bak channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 01-DEC-12
Step 2: Let me start my database in nomount stage with the SPFILE that I restored in the previous step. Later I would restore the controlfile from the same above mentioned backup piece and mount the database.
RMAN> shutdown immediate Oracle instance shut down RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 771752176 bytes Database Buffers 486539264 bytes Redo Buffers 8847360 bytes RMAN> restore controlfile from '/u01/backup/1_dec/SRPRIM_inc0_20121201_06nrp06c_1_1.bak'; Starting restore at 01-DEC-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=25 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 output file name=+DATA/srprim/controlfile/current.264.800915029 output file name=+FRA/srprim/controlfile/current.256.800915035 Finished restore at 01-DEC-12 RMAN> alter database mount; database mounted released channel: ORA_DISK_1
Step 3: The next step is to restore and recover my database until time “1st Dec 2012 – 9:00 AM” as per my requirement. I try to perform it as below:
RMAN> run
2> {
3> set until time "to_date('01-12-2012:09:00:00','DD-MM-YYYY:HH24:MI:SS')";
4> restore database;
5> recover database;
6> }
When I ran the above commands, RMAN threw an error saying “RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time”.
executing command: SET until clause Starting restore at 01-DEC-12 Starting implicit crosscheck backup at 01-DEC-12 RMAN-00571: =================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======= RMAN-00571: =================================================== RMAN-03002: failure of restore command at 12/01/2012 20:26:41 RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
Ok, RMAN is fair enough and I’ve to accept what it is saying. Now, let me check the incarnations of the database and determine why RMAN threw the above error. With my database mounted, I run the “list incarnation” command at RMAN prompt.
[oracle@ora1-1 dbs]$ export NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS' [oracle@ora1-1 dbs]$ rman target sys/oracle@srprim Recovery Manager: Release 11.2.0.3.0 - Production on Sat Dec 1 20:27:37 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: SRPRIM (DBID=216679430, not open) RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 SRPRIM 216679430 PARENT 1 17-09-2011 09:46:04 2 2 SRPRIM 216679430 PARENT 995548 30-11-2012 19:13:17 3 3 SRPRIM 216679430 CURRENT 1040914 01-12-2012 11:19:53
From the above outcome, I can draw a conclusion that the current incarnation of the database has started from the time “1st Dec 2012 11:19 AM” and the time until which I tried to perform the PITR was “1st Dec 2012 9:00 AM”. It is obvious that RMAN has reported the error and could not perform the restoration.
So, to fulfill my requirement, I need to reset the database incarnation, in other words I need to set it to the incarnation where in the Point of time I’am looking out for recovery fits in. To do so, I need to have the backup of the database of the incarnation to which I would be setting. In my case, I had to reset the database incarnation# 2 as this incarnation began at “30th Nov 2012 19:13” which would fit in the time I was looking out (1st Dec 2012 9:00 AM) for recovery. To do this, I would follow
RMAN> shutdown immediate database dismounted Oracle instance shut down RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 771752176 bytes Database Buffers 486539264 bytes Redo Buffers 8847360 bytes [oracle@ora1-1 30_nov]$ pwd /u01/backup/30_nov [oracle@ora1-1 30_nov]$ ls -lrth total 1.2G -rw-r----- 1 oracle oinstall 27M Nov 30 19:26 SRPRIM_inc0_20121130_01nrkjeu_1_1.bak -rw-r----- 1 oracle oinstall 1.1G Nov 30 19:27 SRPRIM_inc0_20121130_02nrkjf1_1_1.bak -rw-r----- 1 oracle oinstall 9.4M Nov 30 19:27 SRPRIM_inc0_20121130_03nrkjgo_1_1.bak -rw-r----- 1 oracle oinstall 3.0K Nov 30 19:27 SRPRIM_inc0_20121130_04nrkjgu_1_1.bak -rw-r----- 1 oracle oinstall 27M Nov 30 19:27 SRPRIM_inc0_20121130_05nrkjh4_1_1.bak -rw-r----- 1 oracle oinstall 9.4M Nov 30 19:27 SRPRIM_inc0_20121130_06nrkjhb_1_1.bak -rw-r----- 1 oracle oinstall 96K Nov 30 19:27 SRPRIM_inc0_20121130_07nrkjhe_1_1.bak
Restore the controlfile from the backup that belongs to the incarnation that I would be changing to (incarnation #2)
RMAN> restore controlfile from '/u01/backup/30_nov/SRPRIM_inc0_20121130_06nrkjhb_1_1.bak'; Starting restore at 01-12-2012 20:32:21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=+DATA/srprim/controlfile/current.264.800915029 output file name=+FRA/srprim/controlfile/current.256.800915035 Finished restore at 01-12-2012 20:32:24 RMAN> alter database mount; database mounted released channel: ORA_DISK_1
Once the database is mounted, I would set the incarnation of the database to #2.
RMAN> reset database to incarnation 2; database reset to incarnation 2
Now, upon checking the the list of incarnations that my contorlfile is aware of, it would show as below
RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 SRPRIM 216679430 PARENT 1 17-09-2011 09:46:04 2 2 SRPRIM 216679430 CURRENT 995548 30-11-2012 19:13:17
We can see that the database CURRENT incarnation is set to incarnation# 2. Now, I need to catalog the backups that I had taken up with the backup of this controlfile. Note that I cannot use the backup that I had taken on 1st Dec. I’ll have to look out for the backups that fall under the incarnation that was newly set above.
RMAN> catalog start with '/u01/backup/30_nov/'; Starting implicit crosscheck backup at 01-12-2012 20:33:15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK Crosschecked 5 objects Finished implicit crosscheck backup at 01-12-2012 20:33:16 Starting implicit crosscheck copy at 01-12-2012 20:33:16 using channel ORA_DISK_1 Finished implicit crosscheck copy at 01-12-2012 20:33:16 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /u01/backup/30_nov/ List of Files Unknown to the Database ===================================== File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_02nrkjf1_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_04nrkjgu_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_07nrkjhe_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_01nrkjeu_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_05nrkjh4_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_06nrkjhb_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_03nrkjgo_1_1.bak Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_02nrkjf1_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_04nrkjgu_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_07nrkjhe_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_01nrkjeu_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_05nrkjh4_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_06nrkjhb_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_03nrkjgo_1_1.bak
Once I catalog the backups, let me try to perform the Point in time recovery until time “1st Dec 2012 9:00 AM”.
RMAN> run
2> {
3> set until time "to_date('01-12-2012:09:00:00','DD-MM-YYYY:HH24:MI:SS')";
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 01-12-2012 20:34:37
using channel ORA_DISK_1
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/srprim/datafile/system.265.800737881
channel ORA_DISK_1: restoring datafile 00002 to +DATA/srprim/datafile/sysaux.259.800737883
channel ORA_DISK_1: restoring datafile 00003 to +DATA/srprim/datafile/undotbs1.258.800737885
channel ORA_DISK_1: restoring datafile 00004 to +DATA/srprim/datafile/users.257.800737887
channel ORA_DISK_1: restoring datafile 00005 to +DATA/srprim/datafile/example.264.800738043
channel ORA_DISK_1: reading from backup piece /u01/backup/30_nov/SRPRIM_inc0_20121130_02nrkjf1_1_1.bak
channel ORA_DISK_1: piece handle=/u01/backup/30_nov/SRPRIM_inc0_20121130_02nrkjf1_1_1.bak tag=TAG20121130T192625
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 01-12-2012 20:36:22
Starting recover at 01-12-2012 20:36:23
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: reading from backup piece /u01/backup/30_nov/SRPRIM_inc0_20121130_05nrkjh4_1_1.bak
channel ORA_DISK_1: piece handle=/u01/backup/30_nov/SRPRIM_inc0_20121130_05nrkjh4_1_1.bak tag=TAG20121130T192731
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=+FRA/srprim/archivelog/2012_12_01/thread_1_seq_4.259.800915785 thread=1 sequence=4
channel default: deleting archived log(s)
archived log file name=+FRA/srprim/archivelog/2012_12_01/thread_1_seq_4.259.800915785 RECID=4 STAMP=800915788
archived log file name=+FRA/srprim/archivelog/2012_12_01/thread_1_seq_5.258.800915789 thread=1 sequence=5
channel default: deleting archived log(s)
archived log file name=+FRA/srprim/archivelog/2012_12_01/thread_1_seq_5.258.800915789 RECID=5 STAMP=800915791
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =======
RMAN-00571: ===================================================
RMAN-03002: failure of recover command at 12/01/2012 20:36:39
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 1040949
Ok, so RMAN has recovered until the last sequence that was generated till time “1st Dec 2012 9:00 AM”. Now, let me open the database with RESETLOGS.
RMAN> alter database open resetlogs; database opened
Here you go !! We were successfully able to perform the Point in Time Recovery to the time that was beyond the CURRENT incarnation of the database.
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Shivananda Rao 
Thank you! Excellent instruction and solved my problem.
Thank you Laura.
Regards,
Shivananda
typo. Lifesaver. jajaja thanks again.
This saved me. Interesting thing is that I pointed it to an incarnation, that had a reset date 2 years ago, of course I had no backup that far back, but I was still able to reset it, restore my controlfile to the backup that I wanted, set the until time, restore and recover with no issues.
This saved me, Interesting the incarnation that I wanted had a reset date 2 years old. I did not have a backup that far back, but went ahead and reset it and was able to restore controlfile I wanted, set the until time, restore and recover no issue. So it must just be a marker of some kind.
Very well laid out Shiva.
great post, thanks!
Excellent!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!