Instructions to kill the Oracle process holding on the controlfile snapshot (80291)

If the controlfile is included in an Oracle backup, a snapshot of the controlfile is created to ensure consistency. After the backup completes the snapshot is released. However, if for some reason the controlfile backup hangs the snapshot will not release causing subsequent controlfile jobs to fail.

This solution provides instructions to kill the Oracle process holding on the controlfile snapshot.

1) Oracle Bug 7450366 - "RMAN Autobackup fails with unable to obtain snapshot enqueue (ORA-230)".
See Oracle Article ID 7450366.8 for more information.
https://support.oracle.com

2) There is a lock on the control file and a backup job hangs at log output: "Log Message: Starting Control File and SPFILE Autobackup at ".

3) A job is aborted and a subsequent backup job completes with an error message in the log of the job: "ORA-00230: operation disallowed: snapshot control file enqueue unavailable".

,

1. Abort the hung backup job.

2. Remove the lock on the Control File.

a. Start SQL*Plus and connect with administrator privileges to the local instance.

SQLPLUS SYS AS SYSDBA

b. Verify there is a lock on the Control File and obtain the offending SID with the following query:

SELECT s.SID, USERNAME AS "User"
FROM V$SESSION s,V$ENQUEUE_LOCK l WHERE l.SID = s.SID
AND l.TYPE = 'CF' AND l.ID1 = 0 AND l.ID2 = 2;

c. Obtain the Process ID (SPID) that matches the SID with the following query:

SELECT s.sid,p.spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr order by s.sid;

d. Exit SQL*Plus and stop the Process in the OS shell:

Kill -9

3. Ensure that Post-installation Requirements for RMAN as defined in Post-installation Requirements for RMAN Method of Chapter 3 on page 39 have been executed. After executing these commands, ensure that the NetVault: Server and Do Restore from NetVault Server are defined in the Default Attributes as detailed in Configuring Default Attributes of Chapter 4 on page 46.

BakBone SFDC Solution Number: 00003454