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”
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
[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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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.
Reference:
No comments:
Post a Comment