记一次rac TNS-12541，ORA-12520，ORA-12521 错误处理

客户一rac数据库发生故障，1号节点的vip漂移至2号节点，由于部分业务没有采用负载均衡模式连接，即直接连接1号节点vip，当1号节点的vip漂移至2号节点后，业务连接出现异常。
主要表现为tnsping出现TNS-12541，sqlplus连接出现ORA-12520
[quote]$ tnsping CRMDB_1

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production on 16-APR-2011 13:56:35

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

Used parameter files:
/opt/oracle/product/10.2.0.4/rnd/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = crmdb)))
TNS-12541: TNS:no listener


$ sqlplus "drb/***@ractest"

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Apr 16 16:46:25 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
[/quote]
连接配置文件为
[quote]ractest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(service_name = crmdb)
(INSTANCE_NAME = crmdb1)
)
)[/quote]
~
检查监听状态似乎也正常：
[quote]Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=130.36.23.9)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=130.36.23.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "crmdb" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmdbXDB" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 1 handler(s) for this service...
Service "crmdb_XPT" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmsrv1" has 1 instance(s).
Instance "crmdb2", status READY, has 2 handler(s) for this service...
The command completed successfully[/quote]
于是检查2号节点监听文件，可以看到参数IP = FIRST，这个参数在单实例默认监听中很少看到，意思为监听只启动在ip为130.36.23.9，130.36.23.10，如果这台主机上有第3个ip，即本案例1号节点的vip，当业务程序采用此vip连接时，将报TNS-12541错误。
[quote]LISTENER_CRMDB02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.9)(PORT = 1521)([color=red]IP = FIRST)[/color])
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.10)(PORT = 1521)[color=red](IP =FIRST)[/color])
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)[/quote]
于是将监听文件修改如下，采用此配置，[color=red]监听将会启动在所有ip地址上[/color]。
[quote]LISTENER_CRMDB02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = crmdb02_vip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = crmdb02)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)[/quote]
重启监听，但应用连接报如下错误，此错误提示比较明显
[quote]$ sqlplus "drb/***@ractest"

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Apr 16 16:42:24 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

ERROR:
ORA-12521: TNS:listenr does not currently know of instance requested in
connect descriptor[/quote]
修改连接串如下：即将(INSTANCE_NAME = crmdb1)配置去掉，应用连接正常。
[quote]ractest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(service_name = crmdb)
)
)[/quote]
故障至此，已经解决。但我们看到实例crmdb1在监听状态显示中存在，且状态为READY
[quote]Service "crmdb" has 2 instance(s).
[color=red]Instance "crmdb1", status READY, has 1 handler(s) for this service.[/color]..
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmdbXDB" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 1 handler(s) for this service...
Service "crmdb_XPT" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmsrv1" has 1 instance(s).
Instance "crmdb2", status READY, has 2 handler(s) for this service...
The command completed successfully[/quote]
也就意味着这是由PMON进程注册的（监听动态注册，启用event可以跟踪pmon进程注册监听过程，如oradebug Event 10257 trace name context forever, level 16 ），在rac环境中，如启用remote_listener参数，那实例将在rac节点的监听中互相注册。其主要作用就是起到服务器端负载均衡作用，即用户进程连接至监听之后，监听根据pmon的Oracle繁忙程度的注册信息，自动将会话连接至相对空闲的实例，详见metalink 263599.1。