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

Date - 02/2008
Affected Product & Version - NetVault: Backup 7.4.5, 8.0
Affected Module & Version - APM for Oracle (Version 5.0, 5.1)
OS Version - All Supported Unix/Linux OS
Application Information - Oracle 9.2.x, 10.1.x, 10.2.x


Description:

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


Symptoms:

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 ". Or this 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.