oracle vip连接串,记：仅配置单vip连接串，当vip对应的节点down机情况下程序无法连接上正常节点的故障...

问题摘要：

昨天，生产库RAC的二号节点倒了，但是部分程序无法连接上正常的一号节点，发现程序的连接串仅配置了二号节点的vip.

在测试环境模拟错误发生的情况，并找到在二号节点短时间内无法正常启动和程序无法修改的情况下的临时解决方法．

具体实验情况如下：

一．测试环境如下

RAC 环境：

172.16.89.229   node1

172.16.90.132   node2

172.16.90.167   node1-vip

172.16.90.168   node2-vip

192.168.126.101 node1-priv

192.168.126.102 node2-priv

节点一listener.ora文件：

LISTENER_NODE1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521)(IP = FIRST))

)

)

客户端tnsnames配置如下：

NODE2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.90.168)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb)

)

)

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

二．模拟错误

把节点二机器down机，节点二的vip转移到节点一,如下

[oracle@node1 ~]$ crs_stat -t

Name           Type           Target    State     Host

------------------------------------------------------------

ora....B1.inst application    ONLINE    ONLINE    node1

ora....B2.inst application    OFFLINE   OFFLINE

ora.RACDB.db   application    ONLINE    ONLINE    node1

ora....SM1.asm application    ONLINE    ONLINE    node1

ora....E1.lsnr application    ONLINE    ONLINE    node1

ora.node1.gsd  application    ONLINE    ONLINE    node1

ora.node1.ons  application    ONLINE    ONLINE    node1

ora.node1.vip  application    ONLINE    ONLINE    node1

ora....SM2.asm application    ONLINE    OFFLINE

ora....E2.lsnr application    OFFLINE   OFFLINE

ora.node2.gsd  application    ONLINE    OFFLINE

ora.node2.ons  application    ONLINE    OFFLINE

ora.node2.vip  application    ONLINE    ONLINE    node1　　　－－转到节点二了．

SQL> CONN

ERROR:

ORA-12541: TNS:no listener

Warning: You are no longer connected to ORACLE.

查看：节点一的监听状态

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 09-OCT-2012 17:44:37

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER_NODE1

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                09-OCT-2012 17:39:28

Uptime                    0 days 0 hr. 5 min. 8 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File         /opt/ora10g/product/10.2.0/db_1/network/log/listener_node1.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.90.167)(PORT=1521)))  --注意：这儿的host直接是ip

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.89.229)(PORT=1521)))

Services Summary...

．．．

The command completed successfully

并没有监听node2-priv，所以无法通过node2-priv连接到节点一的实例．

三．问题可以通过下面的临时方法解决

方法一：通过lsnrctl命令重启LISTENER_NODE1(一定不能用crs_stop ,crs_start )：

[oracle@node1 admin]$ lsnrctl stop

[oracle@node1 admin]$ lsnrctl start

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 09-OCT-2012 17:51:40

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

...

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))) --注意：这儿的host为node1  Services Summary...

....

The command completed successfully

测试连接：

SQL> CONN ;

Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

RACDB1

SQL>

成功连接到节点一的实例

方法二：修改节点一listener.ora文件，增加对node2-priv的监听．

LISTENER_NODE1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521)(IP = FIRST))  注：新增加内容    )

)

重启监听：

[oracle@node1 admin]$ crs_stop ora.node1.LISTENER_NODE1.lsnr

[oracle@node1 admin]$ crs_start ora.node1.LISTENER_NODE1.lsnr

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 09-OCT-2012 17:44:37

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER_NODE1

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                09-OCT-2012 17:39:28

Uptime                    0 days 0 hr. 5 min. 8 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File         /opt/ora10g/product/10.2.0/db_1/network/log/listener_node1.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.90.167)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.89.229)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.90.168)(PORT=1521)))  --注意：这儿出现我们需要的ip了

Services Summary...

．．．

The command completed successfully

测试连接：

SQL> CONN ;

Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

RACDB1

SQL>

成功连接到节点一的实例

最后：上面的仅是临时的解决方法，最好的方法还是把所有的vip都配置在连接串中．