RAC节点出现ORA-12520错误

最新推荐文章于 2022-08-09 17:01:01 发布
最新推荐文章于 2022-08-09 17:01:01 发布
阅读量269 收藏
点赞数
文章标签： 网络 数据库 操作系统

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

 

 

Oracle的ORA-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_LISTENER和REMOTE_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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-684634/

ckawt40802
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle12cRAC数据库 出现ora-12520, ora-12516
08-17
在使用Oracle12cRAC（Real Application Clusters）数据库的过程中，可能会遇到客户端连接时出现ora-12520或ora-12516错误的问题。这两种错误代码分别表示“无法为服务找到可用实例”和“TNS：没有监听器”，通常与...
ORA-12520: TNS:listener could not find available handler for requested type of server
aoxi0033的博客
12-15 1261
当你碰到ORA-12520错误时，如下所示： 英文错误提示： ORA-12520: TNS:listener could not find available handler for requested type of server 中文错误提示： ORA-12520: TNS: 监听程序无法为请求的服务器类型找到可用的处理程序 一般你应该从下面两个方面去检查出错原因并解决问题...
Oracle数据库ORA-12520问题处理
Marshall_Ma的博客
08-09 5483
ORA-12520: TNS: 监听程序无法为请求的服务器类型找到可用的处理程序
ORA-12520: TNS: 监听程序无法为请求的服务器类型找到可用的处理程序
Data & Analysis
07-12 3380
当你碰到ORA-12520错误时，如下所示： 英文错误提示： ORA-12520: TNS:listener could not find available handler for requested type of server 中文错误提示： ORA-12520: TNS: 监听程序无法为请求的服务器类型找到可用的处理程序 一般你应该从下面两个方面去检查出错原因并解决问题： 1：...
ORA-12520 TNS:Listener count not find available handler for requested type of server错误解决办法
波波诸葛伟
05-12 1457
专有服务器进程是与客户端进程是一对一映射，即时生成，即时消失。用户会话数据和游标状态放在PGA里 共享服务器进程是已经存在的，分为2部分，一种是dispatch进程，一种是共享服务器进程。 dispatch进程与user进程建立连接，把用户请求放入SGA的请求队列里，共享进程取出请求进行处理，处理完后放入SGA响应队列，dispatch进程取出处理结果给用户进程。一个dispatch可以和25
rac-ora-03113.docx
09-08
然而，在使用 Oracle RAC 时，可能会遇到一些故障，例如 ORA-03113 错误。该错误通常是由于控制文件和数据文件头不匹配所引起的。在本文中，我们将详细介绍 ORA-03113 故障的处理步骤。 一、故障现象描述 在某个 ...
Oracle连接出现ora-12154无法解析指定的连接标识符
12-15
配置好rac后,两个节点用tnsping都可以ping通,但用sqlplus连接都出现以下错误,不知道哪里出了问题 [oracle@rac2 admin]$ lsnrctl status LSNRCTL for Linux: Version 9.2.0.4.0 – Production on 04-MAR-2008 08:32:...
RAC节点宕机故障分析
06-27
RAC节点宕机故障分析 RAC 节点宕机故障分析是指在 Oracle Real Application Clusters（RAC）环境中，节点宕机故障的诊断和解决方法。在这个主题中，我们将重点介绍 RAC 节点宕机故障的分析和解决方法，涵盖 ALERT ...
BLOG_【故障处理】Oracle_lhr_ORA-01565 告警日志报“ORA-01565 Unable To open Spfile”.pdf
08-08
故障主要表现在两个不同的时间点上：其中一个RAC节点在每天凌晨3点左右出现该错误，而另一个节点则是在凌晨1点左右。这一现象提示我们可能存在某种定时任务或者操作触发了这个问题。 告警日志中的具体错误信息为： ...
ORA-12520 TNS:Listener count not find available handler for requested type of server错误解决办法...
01-07 85
专有服务器进程是与客户端进程是一对一映射，即时生成，即时消失。用户会话数据和游标状态放在PGA里 共享服务器进程是已经存在的，分为2部分，一种是dispatch进程，一种是共享服务器进程。 dispatch进程与user进程建立连接，把用户请求放入SGA的请求队列里，共享进程取出请求进行处理，处理完后放入SGA响应队列，dispatch进程取出处理结果给用户进程。一个disp...
ORACLE ORA-12520 : TNS:listener could not find available handler for requested type of server
weixin_34117522的博客
11-17 331
我们在客户端通过监听连接连接数据库的时候常常会遇到下面两个错误，特别在新搭建的环境中 ORA-12520:TNS:listenercouldnotfindavailablehandlerforrequestedtypeofserverorORA-12514:TNS:listenerdoesnotcurrentlyknow...
由ORA-12519、ORA-12520错误进而修改oracle的最大连接数
MingYuan_Zero的博客
08-20 577
最近Oracle服务经常性的无法连接，当时也没看日志，就凭经验处理查看来数据文件，发件文件空间满了，就新增了一个数据文件。但是没用，没用，没用，一段时间后又登录不上了。然后就去查日志，结果发现英文错误提示： ORA-12519：TNS:no appropriate service handler found ORA-12520：TNS:listener could not find available handler for requested type of server 中文错误提示： ORA-1251
Oracel 错误： 12520
努力工作 认真玩
12-09 2872
错误内容
ORA-12520错误
crfb86846的博客
10-10 136
2009年10月5日服务器上表现为rac1-> sqlplus ctms/ctms@snowdb2SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 2 16:41:4...
ORA-12520: TNS:监听程序无法为请求的服务器类型找到可用的处理程序解
weixin_30591551的博客
10-18 733
问题描述 用 PLSQL 登录 oracle 数据库报错，提示：ORA-12520: TNS:监听程序无法为请求的服务器类型找到可用的处理程序。 解决方法：1、登录sysDBA查看最大连接数 select value from v$parameter where name = 'processes'; 2、修改最大连接数 alter system set process...
ORA-12520: TNS:listener could not find available handler for requested type of
langzixin1986的专栏
05-06 626
表现症状：   刚开始安装后，经常会有这种问题，就是一边能连上一边连不上   解决方案：   1)数据库是专用服务器,但是在tnsname.ora配置中设置了连接方式为shared,这种情况下打开tnsname.ora, 把(server = shared) 改成 (server = dedicate) 2)是由于process不够引起的 后来查看到v$process一直涨到140多,...
监听的instance status blocked分析
weixin_33958366的博客
12-27 2565
对于处于NOMOUNT状态的数据库，PMON还没有将服务注册到监听上，这个时候服务的状态是BLOCKED的，对于来自远程的任何连接都会报ORA-12528错误。如下： [oracle@dbtest ~]$ export ORACLE_SID=orcl[oracle@dbtest ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Pro...
ora-39097,ora-39065,ora-31644,ora-19502,ora-29701
最新发布
12-19
ORA-29701：这个错误代码表示在使用Oracle Real Application Clusters（RAC）时，没有足够的可用资源。需要检查集群配置和资源分配是否正确，确保每个节点都能够正常运行。 以上这些错误代码都是在数据库操作中可能...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值