ORA-00230

Backup Fails Because of Control File Enqueue: ORA-00230

 

In this scenario, a backup job fails because RMAN cannot make a snapshot control file. The message stack is as follows:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/30/2001 22:48:44
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

Cause:

When RMAN needs to back up or resynchronize from the control file, it first creates a snapshot or consistent image of the control file. If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, then you may see the following message:

Waiting for snapshot control file enqueue

Under normal circumstances, a job that must wait for the control file enqueue waits for a brief interval and then successfully obtains the enqueue. RMAN makes up to five attempts to get the enqueue and then fails the job. The conflict is usually caused when two jobs are both backing up the control file, and the job that first starts backing up the control file waits for service from the media manager.

 

Solution:

To determine which job is holding the conflicting enqueue:

 

1.      Start a new SQL*Plus session and login with the sysdba:

2.      Execute the query to check the waiting cause:

SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE, 
ACTION, LOGON_TIME "Logon", l.* 
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;
The output is look like as: (if there is lock)
SID User Program              Module                    Action           Logon
--- ---- -------------------- ------------------- ---------------- ---------
4 SYS rman@rman (TNS V1-V3) backup full datafile: c10000210 STARTED 16-NOV-12

This situation generally came across when a job is writing to a tape drive, but the tape drive is waiting for new tape to be inserted. In the mean time if you start new job then you will probably receive the enqueue message because the first job cannot complete until the new tape is loaded.

After you have determined which job is creating the enqueue, you can do one of the following:

§         Wait until the job holding the enqueue completes

§         Cancel the current job and restart it after the job holding the enqueue completes

§         Cancel the job creating the enqueue