ORA-01861: Using Date Formats in RMAN SET UNTIL TIME

During point-in-time recovery, RMAN can report ORA-01861: literal does not match format string when the value supplied to SET UNTIL TIME does not match the session’s date format.

The reliable fix

Use an explicit TO_DATE expression with a matching format mask. This makes the recovery script independent of the current NLS_DATE_FORMAT setting.

RUN {
  SET UNTIL TIME
    "TO_DATE('2002-02-20 15:33:00',
             'YYYY-MM-DD HH24:MI:SS')";

  RESTORE DATABASE;
  RECOVER DATABASE;
}

In RMAN, the outer double quotes contain the SQL date expression; the date value and format mask inside TO_DATE use single quotes. Use plain straight quotes when copying commands from a document or web page—typographic “smart quotes” are not valid command syntax.

Why the error occurs

A bare value such as the following relies on the session’s NLS_DATE_FORMAT:

SET UNTIL TIME '2002-02-20 15:33:00';

If the session expects a different order, separator, or time component, Oracle cannot convert the literal and raises ORA-01861. You can set NLS_DATE_FORMAT before starting RMAN, but an explicit TO_DATE expression is clearer and safer in a reusable recovery script.

Recovery notes

• Place SET UNTIL before both RESTORE and RECOVER so they use the same recovery boundary.
• RMAN also supports an SCN, log sequence, or named restore point when that is a more precise boundary.
• The specified time is an upper, noninclusive limit; RMAN recovers up to, but not including, that time.
• Confirm the database host’s time zone and the incident timeline before starting a destructive recovery operation.
• Validate the command and backup availability before affecting the production database.

Reference

See Oracle’s RMAN UNTIL clause reference for supported time, SCN, and log-sequence forms.