RMAN: operation disallowed: snapshot controlfile enqueue unavailable

When this error message is shown by RMAN Log some process still trys to create a backup of the snapshot file and is locking this file. In order to stop that proccess carry out following steps:

SQL> SELECT s.sid, username AS “User”, logon_time “Logon”, l.*
  2  FROM v$session s, v$enqueue_lock l
  3  WHERE l.sid = s.sid and l.type = ‘CF’ AND l.id1 = 0 and l.id2 = 2;

       SID User                           Logon     ADDR
———- —————————— ——— —————-
KADDR                   SID TY        ID1        ID2      LMODE    REQUEST
—————- ———- — ———- ———- ———- ———-
     CTIME      BLOCK
———- ———-
        29 SYS                            04-MAY-07 070000000B3BF738
070000000B3BF758         29 CF          0          2          4          0
    565733          0

The useful number here is the Session ID or SID, in the above example it is 29,

Then carry out the following:

column “ORACLE USER” format a11
column SERIAL# format 9999999
column “OS USER” format a8

  1 select distinct substr(s.username,1,11) “ORACLE USER”, p.pid “PROCESS ID”,
  2  s.sid “SESSION ID”, s.serial#, osuser “OS USER”, p.spid “PROC SPID”,
  3  s.process “SESS SPID”, s.lockwait “LOCK WAIT”
  4  from v$process p, v$session s, v$access a
  5  where a.sid=s.sid and
  6  p.addr=s.paddr and
  7  s.username = ‘SYS’;

———– ———- ———- ——– ——– ———— ————
SYS                 27         16    43406 ar1dba   98466        90176

SYS                 30         29    14605 ar1dba   131674       73510

SYS                 33         32      536 ar1dba   16850        55484

Select the process with the Session ID matching the session ID from the previous step, in this example it is the middle process, the OS process is the number in the PROC SPID column, in this example 131674, check this is the correct process by carrying out a PS -ef |grep 131674, the procedure should return something simlar to :

ar1dba 131674      1   0   May 04      –  5:04 oracleAR1 (LOCAL=NO)

The process has been running since the 04th of May, this corresponds to the result obtained in the first SQL query, and the fact that the backup has not been possible since the 4th of May, therfore we can kill the process.

Carry out a kill -9 on the process.

Check this worked by carrying out the following select again, no rows should be returned:

SQL> SELECT s.sid, username AS “User”, program, module, action, logon_time “Logon”, l.*
  2  FROM v$session s, v$enqueue_lock l
  3  WHERE l.sid = s.sid and l.type = ‘CF’ AND l.id1 = 0 and l.id2 = 2;

Thats it.

This entry was posted in Oracle, RMAN and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.