ITPub博客

首页 > Linux操作系统 > Linux操作系统 > RAC节点出现ORA-12520错误

RAC节点出现ORA-12520错误

原创 Linux操作系统 作者:yangtingkun 时间:2011-01-25 11:03:20 0 删除 编辑

客户的RAC数据库频繁出现ORA-12520错误。

 

 

OracleORA-12520错误信息为:

ORA-12520: TNS:listener could not find available handler for requested type of server
Cause: None of the known and available service handlers for requested type of server (dedicated or shared) are appropriate for the client connection.
Action: Run "lsnrctl services" to ensure that the instance(s) have registered with the listener and that the appropriate handlers are accepting connections.

无论是错误信息还是Oracle提供的解决方法,都与监听的配置有关。

而且以前碰到过一次ORA-12520错误,那次问题是和LOCAL_LISTENER以及REMOTE_LISTENER的配置异常有关。

因此首先想到的就是检查监听信息以及各个节点上的LISTENER参数配置:

SQL> show parameter listener

NAME             TYPE    VALUE
---------------- ------- ------------------------------
local_listener   string  (ADDRESS = (PROTOCOL=TCP)(HOST=10.0.5.62)(PORT = 1521))
remote_listener  string  (ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.62) (PORT = 1521))

[portaldb1]@portal_db01[/backup]$lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production on 14-JAN-2011 15:28:27

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_PORTAL_DB01
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production
Start Date                06-JAN-2011 01:08:52
Uptime                    8 days 14 hr. 19 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_2/network/log/listener_portal_db01.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.5.62)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.5.60)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "portaldb" has 1 instance(s).
  Instance "portaldb1", status READY, has 2 handler(s) for this service...
Service "portaldbXDB" has 1 instance(s).
  Instance "portaldb1", status READY, has 1 handler(s) for this service...
Service "portaldb_XPT" has 1 instance(s).
  Instance "portaldb1", status READY, has 2 handler(s) for this service...
The command completed successfully

节点2的监听信息和监听相关的初始化参数配置:

SQL> show parameter listener

NAME             TYPE    VALUE
---------------- ------- ------------------------------
local_listener   string  (ADDRESS = (PROTOCOL=TCP)(HOST=10.0.5.63)(PORT = 1521))
remote_listener  string  (ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.63) (PORT = 1521))

[portaldb2]@portal_db02[/home/oracle]$lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production on 14-JAN-2011 15:27:18

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_PORTAL_DB02
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production
Start Date                06-JAN-2011 01:08:06
Uptime                    8 days 14 hr. 19 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_2/network/log/listener_portal_db02.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.5.63)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.5.61)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "portaldb" has 1 instance(s).
  Instance "portaldb2", status READY, has 2 handler(s) for this service...
Service "portaldbXDB" has 1 instance(s).
  Instance "portaldb2", status READY, has 1 handler(s) for this service...
Service "portaldb_XPT" has 1 instance(s).
  Instance "portaldb2", status READY, has 2 handler(s) for this service...
The command completed successfully

可以看到两个节点的监听状态没有什么问题,不过REMOTE_LISTENER就存在问题了,可以看到各个节点上设置的LOCAL_LISTENERREMOTE_LISTENER设置是相同的,也就是说远端监听设置的仍然是本地的监听地址。

指导客户修改了各个节点上的REMOTE_LISTENER参数后,问题依旧产生。

于是检查各个节点上的alert文件:

Thu Jan 13 20:51:21 BEIST 2011
Process J000 died, see its trace file
Thu Jan 13 20:51:21 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 20:51:21 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

Thu Jan 13 20:51:24 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/udump/portaldb1_ora_4919666.trc:
ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []
Thu Jan 13 20:52:11 BEIST 2011
Trace dumping is performing id=[cdmp_20110113205211]
Thu Jan 13 20:53:20 BEIST 2011
Process J000 died, see its trace file
Thu Jan 13 20:53:20 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 20:53:20 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

Thu Jan 13 20:55:19 BEIST 2011
Process J000 died, see its trace file
Thu Jan 13 20:55:19 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 20:55:19 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

Thu Jan 13 20:56:24 BEIST 2011
Process J000 died, see its trace file
Thu Jan 13 20:56:24 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 20:56:24 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

Thu Jan 13 20:57:25 BEIST 2011
Process J000 died, see its trace file
Thu Jan 13 20:57:25 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 20:57:25 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

Thu Jan 13 20:59:25 BEIST 2011
Process J001 died, see its trace file
Thu Jan 13 20:59:25 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 20:59:25 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

Thu Jan 13 21:00:04 BEIST 2011
Process m000 died, see its trace file
Thu Jan 13 21:00:04 BEIST 2011
ksvcreate: Process(m000) creation failed
Thu Jan 13 21:00:24 BEIST 2011
Process J001 died, see its trace file
Thu Jan 13 21:00:24 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 21:00:24 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

Thu Jan 13 21:00:26 BEIST 2011
Process m000 died, see its trace file
Thu Jan 13 21:00:26 BEIST 2011
ksvcreate: Process(m000) creation failed
Process m000 died, see its trace file
Thu Jan 13 21:00:27 BEIST 2011
ksvcreate: Process(m000) creation failed
Thu Jan 13 21:01:30 BEIST 2011
Process J000 died, see its trace file
Thu Jan 13 21:01:30 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 21:01:30 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

Thu Jan 13 21:02:30 BEIST 2011
Process J001 died, see its trace file
Thu Jan 13 21:02:30 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Thu Jan 13 21:02:30 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

.
.
.

Fri Jan 14 11:56:36 BEIST 2011
Process J002 died, see its trace file
Fri Jan 14 11:56:36 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Fri Jan 14 11:56:36 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb1_cjq0_2547832.trc:

节点2上的错误信息:

Process J000 died, see its trace file
Mon Jan 17 10:03:33 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Mon Jan 17 10:03:33 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb2_cjq0_3051724.trc:

Mon Jan 17 10:03:44 BEIST 2011
Process J000 died, see its trace file
Mon Jan 17 10:03:44 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Mon Jan 17 10:03:44 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb2_cjq0_3051724.trc:

Mon Jan 17 10:03:45 BEIST 2011
Process m000 died, see its trace file
Mon Jan 17 10:03:45 BEIST 2011
ksvcreate: Process(m000) creation failed
Mon Jan 17 10:03:46 BEIST 2011
Process J000 died, see its trace file
Mon Jan 17 10:03:46 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Mon Jan 17 10:03:46 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb2_cjq0_3051724.trc:

Mon Jan 17 10:03:47 BEIST 2011
Process m000 died, see its trace file
Mon Jan 17 10:03:47 BEIST 2011
ksvcreate: Process(m000) creation failed
Mon Jan 17 10:03:49 BEIST 2011
Process J000 died, see its trace file
Mon Jan 17 10:03:49 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Mon Jan 17 10:03:49 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb2_cjq0_3051724.trc:

Process J000 died, see its trace file
Mon Jan 17 10:03:51 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Mon Jan 17 10:03:51 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb2_cjq0_3051724.trc:

Process J000 died, see its trace file
Mon Jan 17 10:03:53 BEIST 2011
kkjcre1p: unable to spawn jobq slave process
Mon Jan 17 10:03:53 BEIST 2011
Errors in file /u01/app/oracle/admin/portaldb/bdump/portaldb2_cjq0_3051724.trc:

虽然两个节点上都出现了ORA-600(kcblasm_1)的错误,但这个错误并不是导致这个问题的真正原因,因为这个错误贯穿整个数据库的错误日志,而这个ORA-12520是最近才产生的。从大量的kkjcre1p: unable to spawn jobq slave process信息不难判断,显然导致问题的原因是系统资源不足。

继续检查操作系统配置后发现,问题可能是由于配置了LARGE PAGE,占用了过多的物理内存,导致系统上内存不足。

建议客户去掉LARGE PAGE设置后,问题没有每次出现。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-684634/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1954
  • 访问量
    10633402