RMAN Catalog Deadlock errors

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6
This problem can occur on any platform.

Symptoms

RMAN backups that use catalog database fails with following deadlock errors.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/26/2008 22:00:43
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of partial resync command on default channel at 11/26/2008 22:00:43
ORA-00060: deadlock detected while waiting for resource


Dead lock trace file contains the following statement

DELETE FROM CKP WHERE DBINC_KEY = :B1 AND CKP_KEY IN (SELECT CKP_KEY1 FROM
(SELECT CKP_KEY CKP_KEY1 FROM CKP WHERE DBINC_KEY = :B1 ) CKP1, (SELECT :B2
CKP_KEY2 FROM DUAL UNION SELECT :B3 FROM DUAL UNION SELECT NVL(MAX(CKP_KEY),0) F
ROM CKP WHERE DBINC_KEY=:B1 UNION SELECT START_CKP_KEY FROM TSATT WHERE DBINC_KE
Y = :B1 UNION SELECT NVL(END_CKP_KEY,0) FROM TSATT WHERE DBINC_KEY = :B1 UNION S
ELECT START_CKP_KEY FROM DFATT WHERE DBINC_KEY = :B1 UNION SELECT NVL(END_CKP_KE
Y,0) FROM DFATT WHERE DBINC_KEY = :B1 UNION SELECT START_CKP_KEY FROM TFATT WHER
E DBINC_KEY = :B1 UNION SELECT NVL(END_CKP_KEY,0) FROM TFATT WHERE DBINC_KEY = :
B1 ) CKP2 WHERE CKP_KEY1 = CKP_KEY2(+) AND CKP_KEY2 IS NULL)

Cause

This issue is due to Bug 6830296
Abstract: RMAN-3014, ORA-60 RUNNING BACKUPS FOR 2 DATABASES USING SAME CATALOG

Fixed version :- 11.1.0.6

Solution

Workaround :-
In Rman catalog database create the following indexes :-

CREATE INDEX tfatt_i_sck on tfatt(start_ckp_key)
CREATE INDEX tfatt_i_eck on tfatt(end_ckp_key)
Or
Check for availability of one off patch using the following link Patch 6830296