Real case: Oracle database recovery ORA-01861 Problems

Oracle Database WEBDB performs a remote recovery test of backup in a new server environment, and an error of "ORA-01861: literal does not match format string" occurs when a data file is restored.

This error is not caused by incorrect setting of the NLS_DATE_FORMAT parameter, but by an Oracle bug. In the process of solving this error, we did not use the officially recommended solution for recreating the control file. Instead, we used our own solution to completely delete the old backup set and then register it into the new backup set.

The production database is 10.2.0.3, RAC cluster, and ASM storage. The test database is stored in a single instance and a file system.

This bug does not affect the normal backup and recovery operations of the production database.

1. Problem

A ORA-01861 error occurred while restoring the data file, and the file could not be restored, causing the Backup Recovery to stop.

The entire restoration process is as follows:

1. Start the instance to nomount in sqlplus;

2. Restore the control file in rman;

Restore controlfile from '/backup/ctl. XXX ';

3. Start the instance to the mounted status

SQL 'alter database mount ';

4. Register a backup set

Catalog start with '/backup /';

Because the backup Set Directory of the production database is different from the backup Set Directory of the test database, use catalog to register the new location into the control file.

5. Restore all data files and rename them.

Here, we will test the restoration of the first data file, and modify the file system directory from the file location in ASM.

Run
{
Allocate channel ch01 type disk;
Set until time "to_date ('2017-06-25 23:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')";

Set newname for datafile 1 to '/stor2T/app/oracle/oradata/WEBDB/system01.dbf ';
Restore datafile 1;
Switch datafile all;
Release channel ch01;
}

In this step, the following error occurs:

RMAN-03002: failure of restore command at 16:09:51

ORA-01861: literal does not match format string

 

The backup recovery test is terminated.

According to the error message, this should beNLS_DATE_FORMATIncorrect format,Set until timeThe format is incorrect.

So we replaceSet until scnThe steps for restoring data files are as follows.

Run
{
Allocate channel ch01 type disk;
Set until scn 3054150330;

Set newname for datafile 1 to '/stor2T/app/oracle/oradata/WEBDB/system01.dbf ';
Restore datafile 1;
Switch datafile all;
Release channel ch01;
}

 

HoweverORA-01861Error.

This error even occurs when listing all backup sets.

RMAN> list backupset summary;

 

Using target database control file instead of recovery catalog

RMAN-00571: ========================================================== ==============================

RMAN-00569: ==================== error message stack follows ==========================

RMAN-00571: ========================================================== ==============================

RMAN-03002: failure of list command at 06/27/2012 16:29:18

ORA-01861: literal does not match format string

From this point of view, this problem is no longer as simple as incorrect parameter settings.

1 2 3 4 5 Next Page