Friday, July 17, 2009

RMAN-08512: waiting for snapshot controlfile enqueue

Under normal circumstances, a job that must wait for the control file enqueue
waits for a brief interval and then successfully retrieves the enqueue.
Recovery Manager 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:

1. After you see the first RMAN-08512: waiting for snapshot controlfile enqueue
message, start a new SQL*Plus session on the target database:

% sqlplus sys/sys_pwd@prod1


2. 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: c1 0000210 STARTED
21-JUN-99


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

- Wait until the job creating the enqueue completes
- Cancel the current job and restart it once the job creating the enqueue completes
- Cancel the job creating the enqueue

Commonly, enqueue situations occur when a job is writing to a tape drive, but the tape
drive is waiting for a new cassette to be inserted. If you start a new job in
this
situation, you will probably receive the enqueue message because the first job cannot
complete until the new tape is loaded.

No comments:

Post a Comment

Followers

About Me

My photo
N.Delhi, Delhi, India
I am an Oracle Certified Professional, Oracle 9i/10G DBA, having 4+ years of core DBA experience.