ORA-00230: operation disallowed: snapshot control file enqueue unavailable

一、RMAN备份故障

RMAN备份日志中报错：ORA-00230: operation disallowed: snapshot control file enqueue unavailable

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 05/30/2025 00:41:42
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

二、查看错误代码

大概意思是RMAN备份时候有其它的进程将控制文件放入队列

[oracle@gdzqdb01 ~]$ oerr ora 00230
00230, 00000, "operation disallowed: snapshot control file enqueue unavailable"
// *Cause:  The attempted operation cannot be executed at this time because
//          another process currently holds the snapshot control file enqueue.
// *Action: Retry the operation after the concurrent operation that is holding
//          the snapshot control file enqueue terminates.

三、查看占有控制文件的进程

set line 300 pagesize 300
col user for a20 
col module for a30
col action for a10
select s.sid,
       username   as "User",
       program,
       module,
       action,
       logon_time "Logon"
  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;

---根据SID 查看操作系统进程SPID。
SQL> select spid from v$process where addr in(select paddr from v$session where sid=&sid);

[oracle@db02 ~]$ ps -ef|grep 4097
oracle    4097     1  0 Apr29 ?        00:00:03 oracledb2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   15984 15453  0 11:05 pts/37   00:00:00 grep 4097

结束掉LOCAL=YES进程，重新备份数据库即可:
kill -9 4097


或者使用以下命令查看持有控制文件进程:
set linesize 300 pagesize 300 
col sid for 99999
col id1 for 999
col id2 for 999
col lmode for 9999
col request for 9999
col block for 99999
col username for a10
col  program for a28
col module for a30
col action for a10
select s.sid, username, program, module, action, logon_time, l.*
  from v$session s, v$enqueue_lock l
 where l.sid = s.sid
   and l.type = 'CF';
   
  SID USERNAME   PROGRAM		       MODULE			      ACTION	 LOGON_TIM ADDR 	    KADDR		SID TY	ID1  ID2 LMODE REQUEST	    CTIME  BLOCK
------ ---------- ---------------------------- ------------------------------ ---------- --------- ---------------- ---------------- ------ -- ---- ---- ----- ------- ---------- ------
  9920 SYS	  sqlplus@gdzqdb02 (TNS V1-V3) sqlplus@gdzqdb02 (TNS V1-V3)		 29-APR-25 00000419201B8370 00000419201B8390   9920 CF	  0    2     4	     0	  2675625      2
 10974		  oracle@gdzqdb02 (CKPT)						 30-APR-22 00000419201AE698 00000419201AE6B8  10974 CF	  0    0     2	     0	 97244242      2

kill进程结束:
select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);

Enter value for sid: 9920
old   1: select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid)
new   1: select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=9920)

'KILL-9'||SPID
--------------------
kill -9 4097