Monday, July 16, 2007

[Oracle:Rman]ORA-00230: operation disallowed: snapshot control file enqueue unavailable

We found below error, when we run rman command to backup Database.

waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
cannot make a snapshot control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql command at 07/14/2007 04:02:36
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 07/14/2007 04:02:36
ORA-00230: operation disallowed: snapshot control file enqueue unavailable



And We searched error on Internet.

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtroub004.htm

Backup Fails Because of Control File Enqueue: Scenario

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
Backup Fails Because of Control File Enqueue: Diagnosis
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.

To determine which job is holding the conflicting enqueue:

After you see the first message stating "RMAN-08512: waiting for snapshot control file enqueue", start a new SQL*Plus session on the target database:

% sqlplus 'SYS/oracle@trgt AS SYSDBA'

Execute the following query to determine which job is causing the wait:

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;

You should see output similar to the following (the output in this example has been truncated):

SID User Program Module Action Logon
--- ---- -------------------- ------------------- ---------------- ---------
9 SYS rman@h13 (TNS V1-V3) backup full datafile: c10000210 STARTED 21-JUN-01


Backup Fails Because of Control File Enqueue: Solution

Commonly, enqueue situations occur when a job is writing to a tape drive, but the tape drive is waiting for new tape to be inserted. If you start a new job in this situation, 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

3 comments:

Anonymous said...

Good article... a few suggestions.
(From Marcel DeMaria) I just hit this today... good info about the retries and locks.

One can also connect as system instead of / as sysdba or a user that has select any dictionary access. (/ as sysdba is audited by default)

Also to RAC'ify the query since the current query will only provide results form the curren tinstance and RAC with RMAn can run on ANY instance (least loaded)

SELECT s.INST_ID, s.SID, s.USERNAME AS "User", PROGRAM, MODULE,
ACTION, LOGON_TIME "Logon", l.*
FROM GV$SESSION s, GV$ENQUEUE_LOCK l
WHERE l.SID = s.SID
AND l.TYPE = 'CF'
AND l.ID1 = 0
AND l.ID2 = 2;

Anonymous said...

When this query return no rows for a case as you mentioned?

Surachart said...

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;

>When this query return no rows for a case as you mentioned?

perhaps you can check at not peak time on your database.