记一次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:listener 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:listenr 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。