ORA-12520错误

2009年10月5日
服务器上表现为
rac1-> sqlplus ctms/ctms@snowdb2
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 2 16:41:40 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
Enter user-name: ctms
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL>

客户端表现为
SQL> conn ctms/ctms@testrac
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
刚开始安装后，经常会有这种问题，就是一边能连上一边连不上，网络上有对于这个问题的起因有两种说法
1)数据库是专用服务器,但是在tnsname.ora配置中设置了连接方式为shared,这种情况下打开tnsname.ora,
把(server = shared) 改成 (server = dedicate)
2)是由于process不够引起的
后来查看到v$process一直涨到140多,而我的数据库设置的是150.据此大致能断定process不够,用以下语句修改数据库的processes值
alter system set processes=500 scope=spfile(由于processes是静态参数,scope只能为spfile,在v$sgastate条件为'procesees'中可以看到会每个process会点4个字节),重新启动数据库使新改的processes生效.
3)更改本地监听的配置。(好几天的问题，就在于此)

1、查看当前会话数、processes和sessions值，发现session数和2个参数的值已经非常逼近
SQL> select count(*) from v$session;
COUNT(*)
----------
30
SQL> show parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 170
shared_server_sessions integer
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
2、修改processes和sessions值
SQL> alter system set processes=300 scope=spfile;
System altered.
SQL> alter system set sessions=335 scope=spfile;
System altered.
3、查看processes和sessions参数，但更改并未生效
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 170
shared_server_sessions integer
4、重启数据库，使更改生效
SQL> conn / as sysdba
Connected.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 197132288 bytes
Fixed Size 1218484 bytes
Variable Size 113248332 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 300
SQL> show parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 335
shared_server_sessions integer

5.还有一个是本地监听要改变，起初是没有配的，可以的手工配置。
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
remote_listener string LISTENERS_SNOWDB

SQL> alter system set local_listener ='SNOWDB1' scope=both sid='snowdb1';
System altered.
SQL> alter system set local_listener ='SNOWDB2' scope=both sid='snowdb2';
System altered.
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string SNOWDB2
remote_listener string LISTENERS_SNOWDB

6.对于第5点，可以正常连接到数据库后，但又出现如下错误（有时连接正常有时错误提示）
在测试RAC时,发现连接DB时有大概50%的几率会出现无法连接,报如下错误:
ora-12545;因目标主机或对象不存在,连接失败
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Oct 5 14:42:27 2009
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> conn ctms/ctms@testrac
ERROR:
ORA-12545: Connect failed because target host or object does not exist
SQL> conn ctms/ctms@testrac
ERROR:
ORA-12545: Connect failed because target host or object does not exist
SQL> conn ctms/ctms@testrac
Connected.
SQL> conn ctms/ctms@testrac
ERROR:
ORA-12545: Connect failed because target host or object does not exist
Warning: You are no longer connected to ORACLE.
SQL> conn ctms/ctms@testrac
Connected.
经过网上查找后，连接到RAC数据库的时候经常会出现ORA-12545错误，在METALINK上查询了一下，是Oracle的一个小bug。
Oracle在文档Note:364855.1：RAC Connection Redirected To Wrong Host/IP ORA-12545中进行了相信的描述。
并给出了解决方法：修改数据库中的初始化参数LOCAL_LISTENER：
alter system set local_listener = '(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.99.205)(PORT = 1521))' sid='snowdb1';
alter system set local_listener = '(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.99.206)(PORT = 1521))' sid='snowdb2';
再测试后未发现错误
SQL> conn ctms/ctms@testrac
Connected.
SQL> conn ctms/ctms@testrac
Connected.
SQL> conn ctms/ctms@testrac
Connected.

[@more@]

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

转载于:http://blog.itpub.net/15242702/viewspace-1027689/