RMAN Recovery Session Fails with ORA-1861

RMAN Recovery Session Fails with ORA-1861

RMAN> recover database;

Starting recover at 03-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=526 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=533 devtype=DISK

starting media recovery

archive log thread 2 sequence 20034 is already on disk as file +FRA/sngprd/archivelog/2010_02_28/thread_2_seq_20034.4044.712175745
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/03/2010 11:52:19
ORA-01861: literal does not match format string

Cause:Bug 8513905: ORA-1861 DURING RMAN RECOVERY

Backuppieces in the controlfile have an invalid date.


From the provided information, it was indeed I was hitting the issue as reported in Note 852723.1.

Therefore, the proposed workaround to manually delete the reference to this backuppiece using package
dbms_backup_restore.deleteBackupPiece should be implemented.

After the reference has been deleted, you can re catalog the backuppiece. I have tried this and it did work.


To check if you are hitting this bug query the controlfile:

SQL>alter session set nls_date_format=’dd-mon-rr hh24:mi:ss;
SQL>select recid, status, device_type, handle, completion_time from v$backup_piece where completon_time > ‘<date>’;

Use ‘<date>’ to limit the output to those backuppieces being used during recovery and check for invalid dates.   In this case, completion_time for the backuppiece was 04/31/2009 00:57:33 (April only has 30 days).

If the SQL above raises ORA-1861, dump the controlfile and find the trace file in target udump directory :

SQL>alter session set events ‘trace name controlf level 9’;

Look for a the BACKUP PIECE RECORDS section eg

@ ***************************************************************************


1. Recreate the controlfile.

2. An alternative to recreating the catalog would be to first
identify all backup pieces with incorrect completion time, using a full
controlfile dump), then for each backup piece with incorrect completion time

SQL>select recid, stamp, handle, set_stamp, set_count, piece#
from v$backup_piece where recid = nnnnn;

WARNING: Before doing the next step, copy the backup piece to another
location on disk, otherwise it will be deleted, so for example:

$ cp <handle>  <handle_copy_2>

Call directly (as user SYS) dbms_backup_restore.deleteBackupPiece using the values obtained
from the select:

SQL>exec dbms_backup_restore.deleteBackupPiece(recid, stamp, ‘handle’,
set_stamp, set_count, piece#);

After all offending pieces have been deleted, then the copied pieces can be
recataloged (but not near the end of a month that does not have 31 days) and
the catalog resynced.


About mpoojari
Oracle Database/Applications Administrator

2 Responses to RMAN Recovery Session Fails with ORA-1861

  1. Dodson says:

    Mpoojari, I owe you a huge thank you for this post. Last night, our normal cloning process failed with this error for the first time. I was googling like mad and finally stumble across this post. I ran the query you supplied and got the error again. I ran it on my production database and it did not get the error. In the end, I restored the control file again and cataloged the backup pieces, ran the query and everything was fine.

    Thank you,

  2. Naseer Arif says:

    Mpoojari, thanks for your tip, it works for me 🙂

    Merci beaucoup

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: