Success cases of Until time reovery using RMAN

Finally, I succeeded to finish point-in-time recovery using RMAN until clauses.

commands are:

1. Using backup control file earlier than point-in-time.

orarac2poc:TTS:/u02/oradata/TTS> ls -l /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812196029_8p495y2y_.bkp
-rw-r----- 1 oracle asmadmin 10092544 Apr  8 10:00 /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08/o1_mf_s_812196029_8p495y2y_.bkp
orarac2poc:TTS:/u02/oradata/TTS> ls -lrt /u02/oradata/TTS/FRA/TTS/autobackup/2013_04_08
total 59232
-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
-rw-r----- 1 oracle asmadmin 10092544 Apr  8 14:14 o1_mf_s_812211264_8p4r20n9_.bkp
-rw-r----- 1 oracle asmadmin 10092544 Apr  8 14:44 o1_mf_s_812213062_8p4st6r1_.bkp


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;
}

2. Environment variable NLS_DATE_FORMAT doesn't affect to_date format. They can be in different format.
e.g.  in above test, the NLS_DATE_FORMAT=YYYY-MON-DD HH24:MI:SS , but I use purposely use "to_date('2013-04-08 10:10:00','YYYY-MM-DD HH24:MI:SS')"

  Oracle document of untilClause also explains this.

When specifying dates in RMAN commands, the date string must be either:

    A literal string whose format matches the NLS_DATE_FORMAT setting.

    A SQL expression of type DATE, for example, 'SYSDATE-10' or "TO_DATE('01/30/2007', 'MM/DD/YYYY')". Note that the second example includes its own date format mask and so is independent of the current NLS_DATE_FORMAT setting.


    and someone concludes that :

    "With RMAN NLS_DATE_FORMAT is used ONLY to display dates.
    In the commands you must use the default format or explicitly use TO_DATE."

3.  Format in to_date function is not limit  to format of 'YYYY-MM-DD:HI24:MI:SS', which is fixed in SQL*PLUS command RECOVER DATABASE (refer to [ID 249164.1]).  The following formats work for me, colon or space between DD and HH24.

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;
}


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;
}

  Specifying UNTIL TIME in RESTORE DATABASE and RECOVER DATA also works. i.e.
RMAN> restore database until time "to_date('2013-04-08:10:10:00','YYYY-MM-DD:HH24:MI:SS')";

Starting restore at 2013-04-08 11:32:01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
...
Finished restore at 2013-04-08 11:33:23

RMAN> recover database until time "to_date('2013-04-08:10:10:00','YYYY-MM-DD:HH24:MI:SS')";

Starting recover at 2013-04-08 11:33:46
using channel ORA_DISK_1

starting media recovery

...
media recovery complete, elapsed time: 00:00:02
Finished recover at 2013-04-08 11:33:53

4. I do encountered some funny error relate to to_date format, similar to NLS_DATE_FORMAT and RMAN . Fortunately, above format work with me, hope they are not bugs like Doc ID: Note:249164.1  ID 852723.1