Wednesday, March 19, 2008

RMAN: waiting for snapshot control file enqueue

RMAN> run {
sql 'alter system archive log current';
backup......
.
.
.
}


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 03/19/2008 02:02:35
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 03/19/2008 02:02:35
ORA-00230: operation disallowed: snapshot control file enqueue unavailable


When we need to back up or resynchronize from the Control file by RMAN, that 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 a new snapshot control file, then may see error:

RMAN-08512: waiting for snapshot controlfile enqueue

A job that must wait for the control file enqueue waits for a brief interval and the successfully retrieves the enqueue.
RMAN makes up to five attempts to get the enqueue and then fails the job.

waiting for snapshot control file enqueue <- 1
waiting for snapshot control file enqueue <- 2
waiting for snapshot control file enqueue <- 3
waiting for snapshot control file enqueue <- 4
waiting for snapshot control file enqueue <- 5
cannot make a snapshot control file <- Failed

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/passwd@target_db as sysdba

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 lWHERE l.SID = s.SIDAND l.TYPE = 'CF'AND l.ID1 = 0AND 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@db2(TNS V1-V3) backup full datafile: ch1 0000210 STARTED 19-Mar-08

After have determined, We could do:

- 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


Matalink:
Note:145619.1

1 comment:

valiantvimal said...

Good share dear.. Thanks for the same :)