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

[@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
(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

備份成功,而且備份完成後,自動刪除了歸檔日誌,問題解決.