ORA-00001: unique constraint (RMAN.TF_U2) violated

Last night, we added a new tempfile to our TEMP tablespace and started seeing the following error while running the RESYNC operation during rman backups --

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 10/05/2009 10:00:33
ORA-00001: unique constraint (RMAN.TF_U2) violated

The Oracle database and RMAN version is 10.2.0.4 on Sun Solaris 10. It appears to be caused by the fact that a tempfile with the same file# existed in the past but was subsequently dropped (after a switchover activity). The situation is documented in Note 5932181.8.
The catalog entry never got cleaned and still had the old entry for that file#.
The bug seems to be patched in version 11.1.

The workaround fix is rather straight.

1) Find out the DBID of the database (which you are trying to sync up w/ catalog). Also find out the tempfile # for the newly created tempfile.
PRD > select dbid from v$database;
DBID
----------
505738321

PRD > select ts#,file#,rfile#,CREATION_TIME from v$tempfile;
TS# FILE# RFILE# CREATION_TIME
---------- ---------- ---------- ---------------
3 1 1 01Mar2008 20:44
22 2 1 01Mar2008 20:44
3 3 2 01Mar2008 20:46
3 4 3 04Sep2009 19:51

4 rows selected.

From above, we see that file# 4 is the new file that was created and likely causing the unique constraint error in the catalog db.


2) Sqlplus into the catalog database as the rman user and find out the DBINC_KEY.
RCAT > select dbid,name,resetlogs_time,dbinc_key from rc_database_incarnation where dbid=505738321;

DBID NAME RESETLOGS DBINC_KEY
---------- ------------------------- --------- ----------
505738321 PWPRD 02-OCT-06 83583
505738321 PWPRD 16-FEB-06 83585
2 rows selected.


3) For the most recent DBINC_KEY, locate the record that is creating the unique constraint error.
RCAT > select dbinc_key, ts#,file#, rfile#, create_scn, create_time from tf where dbinc_key=83583;

DBINC_KEY TS# FILE# RFILE# CREATE_SCN CREATE_TI
---------- ---------- ---------- ---------- ---------- ---------
83583 3 1 1 1.0103E+10 01-MAR-08
83583 22 2 1 1.0103E+10 01-MAR-08
83583 3 3 2 1.0103E+10 01-MAR-08
83583 3 4 3 1.5606E+10 13-NOV-08

4 rows selected.


4) From above, file# 4 seems to be an old tempfile entry from the past and should be deleted.
RCAT > delete from tf where DBINC_KEY=83583 and ts#=3 and FILE#=4 and rfile#=3;
1 row deleted.

RCAT > select dbinc_key, ts#,file#, rfile#, create_scn, create_time from tf where dbinc_key=83583;
DBINC_KEY TS# FILE# RFILE# CREATE_SCN CREATE_TI
---------- ---------- ---------- ---------- ---------- ---------
83583 3 1 1 1.0103E+10 01-MAR-08
83583 22 2 1 1.0103E+10 01-MAR-08
83583 3 3 2 1.0103E+10 01-MAR-08
3 rows selected.

RCAT > commit;
Commit complete.


5) Execute the RESYNC command now.
RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete


6) Query the TF table again in the catalog db and it should now create a new entry for file#4.
RCAT > select dbinc_key, ts#,file#, rfile#, create_scn, create_time from tf where dbinc_key=83583;

DBINC_KEY TS# FILE# RFILE# CREATE_SCN CREATE_TI
---------- ---------- ---------- ---------- ---------- ---------
83583 3 1 1 1.0103E+10 01-MAR-08
83583 22 2 1 1.0103E+10 01-MAR-08
83583 3 3 2 1.0103E+10 01-MAR-08
83583 3 4 3 1.7702E+10 04-SEP-09

4 rows selected.