Pages

Thursday, December 3, 2015

RMAN : ORA-01861: literal does not match format string

The issue occurred for some of the databases around the month end.
The backup started failing with same message and even connecting to database or catalog, no rman command was working.

$rman target / catalog=<catalog details>

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Dec 1 07:40:36 2015 


Copyright (c) 1982, 2007, Oracle. All rights reserved. 


connected to target database: ********

connected to recovery catalog database 

RMAN> resync catalog; 


starting full resync of recovery catalog 

RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03009: failure of resync command on default channel at 12/01/2015 07:40:55 
ORA-01861: literal does not match format string 

RMAN> 


Issue:

Taken the dump of controlfile with following commands.

SQL> Alter session set tracefile_identifier='dump'; 

SQL> alter session set events 'immediate trace name controlf level 9'; 


The dump showed something like following.

RECID #49661 Recno 1277 Record timestamp 11/30/15 12:45:55 piece #1 copy #1 pool 0 
Backup set key: stamp=897133568, count=46318 
V$RMAN_STATUS: recid=140735545929200, stamp=140735545929192 
Flags: 
Device: DISK 
Handle: *******
Media-Handle: <Handle Name>
Comment: 
Tag: ********
Completion time 11/31/15 01:37:53 


The point to notice here was the date i.e. 31 Nov. This is a bug for database versions 10.2.0.4 and 11.0.2.1 and oracle has patches for them.
But the problem here is existing malicious backup which are not allowing to proceed.

To resolve those following were the step taken.

Check the target database for following.

SQL> select recid, stamp, handle, set_stamp, set_count, piece# 
from v$backup_piece 
where recid in (<recId>);

This can be recid for handle name or anything  from above list to identify the backups taken on a malicious date.

Once we have the details. Take backup of controlfile before proceding futher.

SQL> alter database backup controlfile to '/tmp/backupcontrol.ct' 

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


Note:- Need to run above for all backup pieces, and it will delete the entries from v$rman_status as well as backup piece from disk.

Now the rman can work without connecting catalog, but the catalog still throws the same error. Even resync catalog too fails with same error.

Later following commands were  run on target database.

NOTE:- you loose all earlier backup information after following activities.

SQL> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(19); #delete piece

PL/SQL procedure successfully completed.


SQL> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13); #active piece


PL/SQL procedure successfully completed.


SQL>


Post the activity, there are two options.(you may try both in some cases).

1. Unregister database and register database in catalog database.

2. Connect catalog schema using sqlplus and run following.

SQL> update dbinc set high_do_recid =0 where db_name = '<your target's DB_NAME here>'; 
SQL> commit; 


Now connect to rman using target and catalog, and do a resync catalog.

$rman target / catalog=<catalog details>

RMAN> resync catalog;


Finally this came back to a state where backups can work with catalog using existing scripts.