RMAN-03009,ORA-00230错误恢复

ORA-00230 错误解决案例

一. 问题描述

在日常的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

二. 问题分析

[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.itpub.net/post/42280/527193

http://surachartopun.com/2007/07/oraclermanora-00230-operation.html

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