RMAN-03009,ORA-00230

测试备份oracle的job中，始终无法备份成功，检查后台log, 发现如下错误信息：
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03009: failure of Control File and SPFILE Autobackup command on cspfile channel at 10/17/2008 1...
 ORA-00230: operation disallowed: snapshot controlfile enqueue unavailable
 Recovery Manager complete.
 
       google 得到此是一个enqueue lock存在导致备份失败，原来多次运行备份作业，然后中途又取消，导致残留一个rman进程在后台。
       开始执行：
[root@testdb ~]# ps -efw | grep rman
       找到一进程ID，kill 掉，但错误依旧。
 
       用如下语句检查：
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'
   and l.id1 = 0
   and l.id2 = 2
 
  发觉还残留一进程，通过SID查找其OS对应SID:
select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);

 在后台继续 kill 掉该 process, 备份任务终于恢复正常。

 ORA-00230 错误解决案例 2012-04-26 16:04:11

一. 问题描述
在日常的rman备份工作中,发现rman在备份归档日志的时候,不成功,不能自动删除归档日志,而且controlfile的备份也没有成功.
检查rman备份的日志文件,发现在备份过程中报ora-00230的错误.如下:
Starting Control File and SPFILE Autobackup at 26-4鏈?-12
released channel: ch00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on ch00 channel at 04/26/2012 11:50:55
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
[@more@]
二. 问题分析
[oracle@dmis ~]$ oerr ora 230
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.
这个错误可能是因为有进行hold住了control file的备份(可能是因为介质的故障,导致备份进程一直无法完成)
通过下面的语句找出是哪个进程hold住controlfile
SQL>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 User
---------- ------------------------------------------------------------------------------------------
PROGRAM
------------------------------------------------------------------------------------------------------------------------------------------------
MODULE
------------------------------------------------------------------------------------------------------------------------------------------------
ACTION Logon
------------------------------------------------------------------------------------------------ ------------
522 SYS
rman@dmis (TNS V1-V3)
backup incr datafile
0000498 STARTED111
三. 问题解决 
首先手工删除导致control file备份不成功的进程.
SQL> select s.sid,s.serial#,p.spid,p.pid from v$session s ,v$process p where s.paddr=p.addr and s.sid=522;
SID SERIAL# SPID PID
---------- ---------- ------------------------------------ ----------
522 2703 22312 24
SQL> alter system kill session '522,2703' immediate;
alter system kill session '522,2703' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> exit
[oracle@dmis ~]$ ps -ef|grep 22312;
oracle 22312 22304 0 Apr20 ? 00:10:22 oracleDMIS (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26500 14190 0 15:44 pts/2 00:00:00 grep 22312
[oracle@dmis ~]$ kill -9 22312
[oracle@dmis ~]$ ps -ef|grep 22312
oracle 26675 14190 0 15:44 pts/2 00:00:00 grep 22312
重新进行归档日志的备份
[oracle@dmis ~]$ rman target /
RMAN> run{
2> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
3> BACKUP
4> filesperset 20
5> FORMAT 'al_%s_%p_%t'
6> ARCHIVELOG ALL DELETE INPUT;
7> RELEASE CHANNEL ch00;}
using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: sid=510 devtype=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
Starting backup at 26-4月 -12
current log archived
channel ch00: starting archive log backupset
channel ch00: specifying archive log(s) in backup set
input archive log thread=1 sequence=138126 recid=75788 stamp=781622149
input archive log thread=1 sequence=138127 recid=75789 stamp=781622448
input archive log thread=1 sequence=138128 recid=75790 stamp=781622848
......
archive log filename=/archivelog/1_138152_668681208.dbf recid=75814 stamp=781630850
archive log filename=/archivelog/1_138153_668681208.dbf recid=75815 stamp=781631116
Finished backup at 26-4月 -12
Starting Control File and SPFILE Autobackup at 26-4月 -12
piece handle=c-510725048-20120426-00 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 26-4月 -12
released channel: ch00
备份成功,而且备份完成后,自动删除了归档日志,问题解决.

转载至

http://blog.sina.com.cn/s/blog_538285a70100nvzi.html

http://blog.itpub.net/32980/viewspace-1058029/