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.