环境hp 11.23 Oracle10203
RAC + ASM
由于业务需要在备用系统上部署了新的统计程序，由于某个bug导致产生了几倍的归档日志，导致归档空间很快被消耗光
于是出现错误如下：
ORA-00257: archiver error. Connect internal only, until freed.
出现该错误时，在节点1使用普通用户无法登陆，只能使用sys用户登陆，在节点2使用普通用户登陆正常
检查磁盘组的剩余空间发现只有几个G剩余
select name,
state,
(total_mb / 1024) "TOTAL_G",
round(free_mb / 1024) "FREE_G",
round((total_mb - free_mb) * 100 / total_mb, 2) || '%' "USE_RATIO"
from v$asm_diskgroup;
通常这个错误很好解决
1、删除过期的归档日志
crosscheck archivelog all;
DELETE NOPROMPT ARCHIVELOG all UNTIL TIME 'SYSDATE-1';
2、将归档路径指定到其它目录
alter system set log_archive_dest_1='LOCATION=+DG_DATA_02/billdb/' scope=both sid='*';
alter system set db_recovery_file_dest='+DG_DATA_02' scope=both sid='*';
在我执行1后仍然不能登陆，于是执行2结果一样不能登陆。
此时感觉莫名奇妙于是想使用asmcmd登陆asm查看磁盘组信息
结果错误如下
oracle@CHNFSCC1[+ASM1]:/home/oracle$ asmcmd
ORA-00020: maximum number of processes (%s) exceeded (DBD ERROR: OCISessionBegin)
oracle@CHNFSCC1[+ASM1]:/home/oracle
查看报警日志发现有很多归档报警
Errors in file /u01/app/oracle/admin/billdb/bdump/billdb1_arc1_26068.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DG_DATA_01/billdb/onlinelog/group_1.262.633805015'
ORA-17503: ksfdopn:2 Failed to open file +DG_DATA_01/billdb/onlinelog/group_1.262.633805015
ORA-00020: maximum number of processes () exceeded
oracle@CHNFSCC1[billdb1]:/u01/app/oracle/admin/billdb/bdump$ more /u01/app/oracle/admin/billdb/bdump/billdb1_arc1_26068.trc
/u01/app/oracle/admin/billdb/bdump/billdb1_arc1_26068.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/rac10g
System name: HP-UX
Node name: CHNFSCC1
Release: B.11.23
Version: U
Machine: ia64
Instance name: billdb1
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 26068, image: oracle@CHNFSCC1 (ARC1)
*** 2008-09-07 20:05:18.553
*** SERVICE NAME:(SYS$BACKGROUND) 2008-09-07 20:05:18.540
*** SESSION ID:(1078.1) 2008-09-07 20:05:18.540
Control file resized from 1436 to 1798 blocks
kccrsd_append: rectype = 11, lbn = 718, recs = 5055
*** 2008-09-08 01:43:54.207
Failed to create file '+DG_DATA_01/billdb/1_67503_633805015.dbf' (file not accessible?)
ORA-19504: failed to create file "+DG_DATA_01/billdb/1_67503_633805015.dbf"
ORA-17502: ksfdcre:4 Failed to create file +DG_DATA_01/billdb/1_67503_633805015.dbf
ORA-15041: diskgroup space exhausted
*** 2008-09-08 01:43:54.256 61287 kcrr.c
ARC1: Error 19504 Creating archive log file to '+DG_DATA_01/billdb/1_67503_633805015.dbf'
*** 2008-09-08 01:43:54.272 59530 kcrr.c
kcrrfail: dest:1 err:19504 force:0 blast:1
ARCH: Connecting to console port...
ARCH: Connecting to console port...
*** 2008-09-08 01:43:54.378 20257 kcrr.c
ORA-16038: log 6 sequence# 67503 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 6 thread 1: '+DG_DATA_01/billdb/onlinelog/group_6.263.640005941'
*** 2008-09-08 01:44:07.456
ARCH: Connecting to console port...
ARCH: Connecting to console port...
*** 2008-09-08 01:44:07.456 20257 kcrr.c
ORA-16014: log 6 sequence# 67503 not archived, no available destinations
ORA-00312: online log 6 thread 1: '+DG_DATA_01/billdb/onlinelog/group_6.263.640005941'
*** 2008-09-08 01:44:24.422
*** 2008-09-08 01:44:24.422 20257 kcrr.c
*** 2008-09-08 01:44:43.523
*** 2008-09-08 01:44:43.523 20257 kcrr.c
*** 2008-09-08 01:44:45.548 20257 kcrr.c
*** 2008-09-08 01:44:47.473 20257 kcrr.c
*** 2008-09-08 01:44:48.482 20257 kcrr.c
*** 2008-09-08 01:44:50.479 20257 kcrr.c
*** 2008-09-08 01:44:51.476 20257 kcrr.c
*** 2008-09-08 01:44:53.477 20257 kcrr.c
*** 2008-09-08 01:44:54.475
*** 2008-09-08 01:44:54.475 20257 kcrr.c
*** 2008-09-08 01:44:56.477 20257 kcrr.c
*** 2008-09-08 01:44:57.477 20257 kcrr.c
*** 2008-09-08 01:44:59.479 20257 kcrr.c
*** 2008-09-08 01:45:00.479 20257 kcrr.c
*** 2008-09-08 01:45:01.977 20257 kcrr.c
*** 2008-09-08 01:45:03.305 20257 kcrr.c
*** 2008-09-08 01:45:04.976
*** 2008-09-08 01:45:04.976 20257 kcrr.c
*** 2008-09-08 01:45:06.102 20257 kcrr.c
*** 2008-09-08 01:45:06.977 20257 kcrr.c
*** 2008-09-08 01:45:07.976 20257 kcrr.c
*** 2008-09-08 01:45:08.974 20257 kcrr.c
*** 2008-09-08 01:45:09.303 20257 kcrr.c
billdb1_arc1_26068.trc (0%)
后来赶紧上metalink查找ORA-00020，发现这是在Oracle10203版本下的一个bug
解决方法：
1、在清除过期归档后，执行如下命令
alter system set log_archive_max_processes=1 scope=memory sid='*';
此命令的主要作用是关闭归档进程，只保留一个归档进程
2、重启数据库
BS Oracle 在解决方法时将 Restart the DB instance放在第一位，由于问题紧急
我们很容易只看到一个就以为找到唯一解决办法了。呵呵
Subject: Bug 6139547 - Shadow process leak on ASM instance when diskspace exhausted (ORA-20)
Doc ID: Note:6139547.8 Type: PATCH
Last Revision Date: 03-APR-2008 Status: PUBLISHED
Click here for details of sections in this note.
Bug 6139547 Shadow process leak on ASM instance when diskspace exhausted (ORA-20)
This note gives a brief overview of bug 6139547.
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 10.2 but < 11.2
Versions confirmed as being affected 10.2.0.3
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in 10.2.0.4 (Server Patch Set)
11.1.0.7 (Server Patch Set)
11.2 (Future Release)
Symptoms: Related To:
Error May Occur
Leak (Resources Eg: File Handles)
ORA-20
Automatic Storage Management (ASM)
Description
When ASM diskgroup space is exhausted the ARC process in the database
can generate multiple processes in the ASM instance, causing an ORA-20.
Workaround:
Restart the DB instance
OR
Use RMAN or asmcmd to backup and delete archive logs
in the diskgroup to free up space, then to issue the command:
alter system set log_archive_max_processes=1 scope=memory sid='*';
This should free up ASM connections from ARC processes