Received error
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
in alert log of newly created primary after the switchover from DR and tracefile has following content
*** 2016-10-18 08:58:53.795
*** SESSION ID:(1998.27) 2016-10-18 08:58:53.795
*** CLIENT ID:() 2016-10-18 08:58:53.795
*** SERVICE NAME:(SYS$BACKGROUND) 2016-10-18 08:58:53.795
*** MODULE NAME:(MMON_SLAVE) 2016-10-18 08:58:53.795
*** ACTION NAME:(Autobackup Control File) 2016-10-18 08:58:53.795
Starting control autobackup
Got error: 230
******************** WARNING ***************************
The errors during Server autobackup are not fatal, as it
is attempted after sucessful completion of the command.
However, it is recomended to take an RMAN control file
backup as soon as possible because the Autobackup failed
with the following error:
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
******************** END OF WARNING *******************
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
Solution:
It looks like starting control file autobackup had some issues and resulted in creating
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
Log in on the database and run the command below:
col CLIENT_INFO for a20
col EVENT for a35
Select sysdate, inst_id, sid, client_info,
event, seq#,p1,p2,p3, wait_time, seconds_in_wait,
wait_time_micro, time_since_last_wait_micro, state
from gv$session
where program like '%rman%'
and client_info like '%channel%';
or
SELECT s.sid, username AS "user" , program, module, action, logon_time as "Logon" , l.* 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;
If you are getting some output with state waiting that means there is already and operation going on causing new operation to fail and showing the error in alert log.
Sometimes the process could be sitting there for ages, which means it is hung. In order to kill the process we need to find its UNIX process id to kill it.
COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw,
V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 'sbt%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR;
once you have the SPID which is server process id or ID of UNIX process
then simply issue
kill -9 <SPID>
It should fix the issue.
