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

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

引用

$ 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 stener could not find available handler for requested type of     server

    连接配置文件为

引用

ractest =       (DESCRIPTION =         (ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))         (CONNECT_DATA =           (SERVER = DEDICATED)           (service_name = crmdb)           (INSTANCE_NAME = crmdb1)         )       )

    ~     检查监听状态似乎也正常：

引用

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

    于是检查2号节点监听文件，可以看到参数IP = FIRST，这个参数在单实例默认监听中很少看到，意思为监听只启动在ip为130.36.23.9，130.36.23.10，如果这台主机上有第3个 ip，即本案例1号节点的vip，当业务程序采用此vip连接时，将报TNS-12541错误。

引用

LISTENER_CRMDB02 =       (DESCRIPTION_LIST =         (DESCRIPTION =           (ADDRESS_LIST =             (ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.9)(PORT = 1521)( IP = FIRST))           )           (ADDRESS_LIST =             (ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.10)(PORT = 1521) (IP =FIRST))           )           (ADDRESS_LIST =             (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))           )         )       )

    于是将监听文件修改如下，采用此配置， 监听将会启动在所有ip地址上。

引用

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))           )         )       )

    重启监听，但应用连接报如下错误，此错误提示比较明显

引用

$ 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 stenr does not currently know of instance requested in     connect descriptor

    修改连接串如下：即将(INSTANCE_NAME = crmdb1)配置去掉，应用连接正常。

引用

ractest =       (DESCRIPTION =         (ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))         (CONNECT_DATA =           (SERVER = DEDICATED)           (service_name = crmdb)         )       )

    故障至此，已经解决。但我们看到实例crmdb1在监听状态显示中存在，且状态为READY

引用

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

    也就意味着这是由PMON进程注册的（监听动态注册，启用event可以跟踪pmon进程注册监听过程，如oradebug Event 10257 trace name context forever, level 16 ），在rac环境中，如启用remote_listener参数，那实例将在rac节点的监听中互相注册。其主要作用就是起到服务器端负载均衡作用，即用 户进程连接至监听之后，监听根据pmon的Oracle繁忙程度的注册信息，自动将会话连接至相对空闲的实例，详见metalink 263599.1。