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:

SQL>
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’;

ORACLE USER PROCESS ID SESSION ID  SERIAL# OS USER  PROC SPID    SESS SPID
———– ———- ———- ——– ——– ———— ————
LOCK WAIT
—————-
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.