Advertisements
Skip to content
December 5, 2012 / Shivananda Rao P

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.

Advertisements

7 Comments

Leave a Comment
  1. Laura Michelle Dalton / Mar 27 2013 9:16 pm

    Thank you! Excellent instruction and solved my problem.

  2. Shivananda Rao P / Mar 28 2013 6:12 am

    Thank you Laura.

    Regards,
    Shivananda

  3. Dave / Jul 10 2013 9:08 am

    typo. Lifesaver. jajaja thanks again.

  4. shawn / Oct 23 2013 4:09 pm

    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.

  5. bigmoriver / Oct 23 2013 4:12 pm

    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.

  6. lhyousseu / Apr 30 2014 11:59 am

    great post, thanks!

  7. JD / May 17 2016 6:55 am

    Excellent!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: