Point-in-time recvory using SET UNTIL TIME in RMAN

Objective:

Upon success of my previous test, I'd like to see if below nicer format commands can work.

run {
startup nomount;
restore controlfile from '/u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812196029_8p495y2y_.bkp';
alter database mount;
set until time "to_date('2013-04-08:10:10:00','YYYY-MM-DD:HH24:MI:SS')";
restore database;
recover database;
}

The details

1. Clean-up

orarac2poc:TTS:/u02/oradata/TTS> rm *.log *.dbf *.bdf *.ctl
orarac2poc:TTS:/u02/oradata/TTS> ls -l
total 207212
drwxr-xr-x 2 oracle oinstall      4096 Apr  5 15:05 bak
-rw-r----- 1 oracle oinstall     59332 Apr  8 10:23 control01.ctl.gz.bak
drwxr-xr-x 3 oracle oinstall      4096 Mar 27 09:33 FRA
-rw-r--r-- 1 oracle oinstall        42 Apr  5 14:51 login.sql.gz
-rw-r--r-- 1 oracle oinstall       180 Apr  4 16:25 manual_copy.sh.gz
drwxr-xr-x 2 oracle oinstall      4096 Apr  8 10:39 redo
-rw-r----- 1 oracle oinstall  76447992 Apr  8 10:22 sysaux01.dbf.gz
-rw-r----- 1 oracle oinstall 127971383 Apr  8 10:22 system01.dbf.gz
-rw-r----- 1 oracle oinstall     22531 Apr  5 18:00 temp01.dbf.gz
-rw-r----- 1 oracle oinstall     24336 Apr  8 10:22 testtbs1.dbf.gz
-rw-r----- 1 oracle oinstall   1137520 Apr  8 10:22 tts1_01.bdf.gz
-rw-r----- 1 oracle oinstall   6234453 Apr  8 10:22 undotbs01.dbf.gz
-rw-r----- 1 oracle oinstall     13891 Apr  8 10:22 users01.dbf.gz


2. First attemp

run {
startup nomount;
restore controlfile from autobackup;
alter database mount;
set until time "to_date('2013-04-08:10:10:00','YYYY-MM-DD:HH24:MI:SS')";
restore database;
recover database;
}

connected to target database (not started)
Oracle instance started

Total System Global Area     663908352 bytes

Fixed Size                     2229440 bytes
Variable Size                281021248 bytes
Database Buffers             373293056 bytes
Redo Buffers                   7364608 bytes

Starting restore at 2013-04-08 13:53:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

recovery area destination: /u02/oradata/TTS/FRA
database name (or database unique name) used for search: TTS
channel ORA_DISK_1: AUTOBACKUP /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812201702_8p4gq70f_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812201702_8p4gq70f_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u02/oradata/TTS/control01.ctl
output file name=/u02/oradata/TTS/control02.ctl
Finished restore at 2013-04-08 13:53:27

database mounted
released channel: ORA_DISK_1

executing command: SET until clause

Starting restore at 2013-04-08 13:53:32
Starting implicit crosscheck backup at 2013-04-08 13:53:32
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/08/2013 13:53:33
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TTS      1769654909       PARENT  1          2013-03-20 16:41:33
2       2       TTS      1769654909       PARENT  721427     2013-03-21 14:12:46
3       3       TTS      1769654909       PARENT  1009055    2013-03-27 09:44:13
4       4       TTS      1769654909       PARENT  1019618    2013-03-27 15:11:48
5       5       TTS      1769654909       PARENT  1021343    2013-03-27 15:43:03
6       6       TTS      1769654909       PARENT  1147625    2013-03-28 17:35:21
9       9       TTS      1769654909       ORPHAN  1335171    2013-04-02 10:17:03
7       7       TTS      1769654909       PARENT  1368073    2013-04-04 12:47:03
8       8       TTS      1769654909       PARENT  1371062    2013-04-04 14:27:49
10      10      TTS      1769654909       PARENT  1372570    2013-04-04 16:41:22
11      11      TTS      1769654909       PARENT  1411967    2013-04-05 17:04:27
12      12      TTS      1769654909       CURRENT 1520977    2013-04-08 11:34:49

RMAN> exit


Recovery Manager complete.


-- The failure could relate to previous resetlogs in current controlfile , so I will use backup controlfile instead.

2. Second attempt by using backup control file.

orarac2poc:TTS:/u02/oradata/TTS> ls -l ./FRA/TTS/autobackup/2013_04_08/
total 39488
-rw-r----- 1 oracle asmadmin 10092544 Apr  8 10:00 o1_mf_s_812196029_8p495y2y_.bkp
-rw-r----- 1 oracle asmadmin 10092544 Apr  8 10:09 o1_mf_s_812196568_8p49pr7x_.bkp
-rw-r----- 1 oracle asmadmin 10092544 Apr  8 11:15 o1_mf_s_812200547_8p4fm3nx_.bkp
-rw-r----- 1 oracle asmadmin 10092544 Apr  8 11:35 o1_mf_s_812201702_8p4gq70f_.bkp


orarac2poc:TTS:/u02/oradata/TTS> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Apr 8 13:56:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

run {
startup nomount;
restore controlfile from '/u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812196029_8p495y2y_.bkp';
alter database mount;
set until time "to_date('2013-04-08:10:10:00','YYYY-MM-DD:HH24:MI:SS')";
restore database;
recover database;
}


Oracle instance started

Total System Global Area     663908352 bytes

Fixed Size                     2229440 bytes
Variable Size                281021248 bytes
Database Buffers             373293056 bytes
Redo Buffers                   7364608 bytes

Starting restore at 2013-04-08 13:56:53
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/oradata/TTS/control01.ctl
output file name=/u02/oradata/TTS/control02.ctl
Finished restore at 2013-04-08 13:56:55

database mounted
released channel: ORA_DISK_1

executing command: SET until clause

Starting restore at 2013-04-08 13:57:01
Starting implicit crosscheck backup at 2013-04-08 13:57:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 2013-04-08 13:57:02

Starting implicit crosscheck copy at 2013-04-08 13:57:02
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 2013-04-08 13:57:02

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812196568_8p49pr7x_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812200547_8p4fm3nx_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812201702_8p4gq70f_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812196029_8p495y2y_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_05/o1_mf_s_811961042_8ox3plm1_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_03_28/o1_mf_s_811272380_8o835wv5_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811868418_8ot982xv_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811868443_8ot98vgk_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811868428_8ot98djk_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811868201_8ot91b5p_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811868233_8ot929rp_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811868221_8ot91xwf_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811703243_8oo7ydpz_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811706806_8oocfp72_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811702379_8oo73cv1_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811702849_8oo7l23n_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811706780_8oocdwsh_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811691536_8onwjk4p_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811693094_8ony16vx_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811706022_8oobo6ws_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811706727_8oocc868_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_02/o1_mf_s_811703321_8oo80sjq_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_03/o1_mf_s_811769873_8oq90kjv_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_03/o1_mf_s_811769586_8oq8qlmd_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_03/o1_mf_s_811769883_8oq90vbw_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_03/o1_mf_s_811780266_8oqm5bsv_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_03/o1_mf_s_811769838_8oq8zh0h_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_03/o1_mf_s_811769291_8oq8gcoq_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/backupset/2013_04_08/o1_mf_annnn_TAG20130408T100926_8p49pps2_.bkp
File Name: /u02/oradata/TTS/FRA/TTS/archivelog/2013_04_08/o1_mf_1_8_8p4flknl_.arc
File Name: /u02/oradata/TTS/FRA/TTS/archivelog/2013_04_08/o1_mf_1_7_8p4b926y_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=144 STAMP=812196024 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_system_8owwc247_.dbf
destination for restore of datafile 00001: /u02/oradata/TTS/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/u02/oradata/TTS/system01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=143 STAMP=812196024 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_sysaux_8owwd7fd_.dbf
destination for restore of datafile 00002: /u02/oradata/TTS/sysaux01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=/u02/oradata/TTS/sysaux01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=142 STAMP=812196019 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_undotbs1_8owwfbql_.dbf
destination for restore of datafile 00003: /u02/oradata/TTS/undotbs01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=/u02/oradata/TTS/undotbs01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=139 STAMP=812196015 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_users_8owwfwm8_.dbf
destination for restore of datafile 00004: /u02/oradata/TTS/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/u02/oradata/TTS/users01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=141 STAMP=812196016 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_tts1_8owwft1v_.dbf
destination for restore of datafile 00005: /u02/oradata/TTS/tts1_01.bdf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/u02/oradata/TTS/tts1_01.bdf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=140 STAMP=812196015 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_testtbs_8owwfv98_.dbf
destination for restore of datafile 00006: /u02/oradata/TTS/testtbs1.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=/u02/oradata/TTS/testtbs1.dbf RECID=0 STAMP=0
Finished restore at 2013-04-08 13:58:34

Starting recover at 2013-04-08 13:58:35
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u02/oradata/TTS/FRA/TTS/archivelog/2013_04_08/o1_mf_1_7_8p4b926y_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: reading from backup piece /u02/oradata/TTS/FRA/TTS/backupset/2013_04_08/o1_mf_annnn_TAG20130408T095918_8p493q99_.bkp
channel ORA_DISK_1: piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_04_08/o1_mf_annnn_TAG20130408T095918_8p493q99_.bkp tag=TAG20130408T095918
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u02/oradata/TTS/FRA/TTS/archivelog/2013_04_08/o1_mf_1_5_8p4q4kn6_.arc thread=1 sequence=5
channel default: deleting archived log(s)
archived log file name=/u02/oradata/TTS/FRA/TTS/archivelog/2013_04_08/o1_mf_1_5_8p4q4kn6_.arc RECID=80 STAMP=812210322
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /u02/oradata/TTS/FRA/TTS/backupset/2013_04_08/o1_mf_annnn_TAG20130408T100926_8p49pps2_.bkp
channel ORA_DISK_1: piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_04_08/o1_mf_annnn_TAG20130408T100926_8p49pps2_.bkp tag=TAG20130408T100926
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oradata/TTS/FRA/TTS/archivelog/2013_04_08/o1_mf_1_6_8p4q4o7b_.arc thread=1 sequence=6
channel default: deleting archived log(s)
archived log file name=/u02/oradata/TTS/FRA/TTS/archivelog/2013_04_08/o1_mf_1_6_8p4q4o7b_.arc RECID=81 STAMP=812210325
archived log file name=/u02/oradata/TTS/FRA/TTS/archivelog/2013_04_08/o1_mf_1_7_8p4b926y_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:03
Finished recover at 2013-04-08 13:58:49

RMAN> exit


Recovery Manager complete.


3. Can open database in read only mode now, because SCN in datafile and controlfile are in sync.

orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 8 13:59:25 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> select file#, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1520976
         2            1520976
         3            1520976
         4            1520976
         5            1520976
         6            1520976

6 rows selected.

SYS@TTS> set time on
14:00:02 SYS@TTS> select controlfile_change# from v$database;

CONTROLFILE_CHANGE#
-------------------
            1520976

14:00:14 SYS@TTS> alter database open read only;

Database altered.

14:00:42 SYS@TTS> select * from  liqy.incr_changes;

        C1 CHANGES
---------- --------------------
         3 some changes
         4 some more changes
         5 change no apply
         6 last change
         9 using bakctrlfile2
        10 change before drop
        12 bf delete
         1 init backup copy
         2 change before crash
         7 after PITR
         8 using bakctrlfile
         7 no online redo
        11 change2 before drop

13 rows selected.

14:00:46 SYS@TTS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
14:01:03 SYS@TTS>