ORA-01830: date format picture ends before converting entire input string

I just needed to do a point in time recovery of the tablespace TESTTB, so that I can recover objects which were dropped, or undo recent logical corruptions. But RMAN didn't seem to be happy with the date format I entered.

RMAN> recover tablespace testtb until time '09-JAN-2012 12:21:15' auxiliary destination '/x01/oracle/auxiliary';

Starting recover at 09-JAN-2012 14:14:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/09/2012 14:14:20
ORA-01830: date format picture ends before converting entire input string

I exported NLS_DATE_FORMAT and tried again, but it didn't work.

[oracle@rhel auxiliary]$ export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
RMAN> recover tablespace testtb until time '09-JAN-2012 12:21:15' auxiliary destination '/x01/oracle/auxiliary';

Starting recover at 09-JAN-2012 14:03:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/09/2012 14:03:30
ORA-01830: date format picture ends before converting entire input string

Solution

Use explicit date conversion.

RMAN> recover tablespace testtb until time "to_date('09-jan-2012 12:21:15','dd-mon-rrrr hh24:mi:ss')" auxiliary destination '/x01/oracle/auxiliary';

Note: You can't do TSPITR to a tablespace which contains objects owned by SYS. Objects owned by SYS are not considered as self contained. If you do so, you would get the following error.

starting up automatic instance ORA11G

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes
Variable Size                100666596 bytes
Database Buffers             184549376 bytes
Redo Buffers                   6381568 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
The following errors need to be fixed before peforming this command
Violation: ORA-39917: SYS owned object TESTTB in tablespace TESTTB not allowed in pluggable set