←
→

解决RAC环境下ORA-12545连接错误一例

Posted: May 3, 2012 | Author: | Filed under: Oracle | Leave a comment »

11gR2 RAC两节点的环境，我本机用SCAN VIP连的时候发现连不上了，不断尝试多次，始终报同样的错误ORA-12545：

C:\Documents and Settings\cuihua>sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 5月 3 10:53:00 2012

 

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

 

SQL> conn sys/oracle@nbsdev as sysdba;

ERROR:

ORA-12545: 因目标主机或对象不存在, 连接失败

 

nbsdev在我本机的tnsnames.ora中的配置为：

nbsdev =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.15.86)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = NBSDEV)

    )

  )

 

虽然连不上，但tnsping可以ping通，因为tnsping只检查IP地址和端口是否能连通，至于数据库实例状态，监听注册了哪些服务这些，它是不检查的：

C:\Documents and Settings\cuihua>tnsping nbsdev

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 03-5月 -

2012 10:43:00

 

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

 

已使用的参数文件:

C:\app\cuihua\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

 

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.15.86)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NBSDEV)))

OK (30 毫秒)

 

如下是NBSDEV在database server端的相关设置：

grid:/nbsdu01/app/11.2/network/admin>cat /etc/hosts

# @(#)47        1.2  src/bos/usr/sbin/netstart/hosts, cmdnet, bos61D, d2007_49A2 10/1/07 13:57:52

省略显示相关内容……

#RAC

10.1.15.64  P550-05-LA

10.1.15.84  P550-05-LA-vip

9.2.1.64    P550-05-LA-priv

10.1.15.65  P550-05-LB

10.1.15.85  P550-05-LB-vip

9.2.1.65    P550-05-LB-priv

10.1.15.86  nbsdev-scan

 

grid:/nbsdu01/app/11.2/network/admin>lsnrctl status LISTENER_SCAN1

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 11:28:16

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))

STATUS of the LISTENER

————————

Alias                     LISTENER_SCAN1

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                03-MAY-2012 11:27:07

Uptime                    0 days 0 hr. 1 min. 9 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /nbsdu01/app/11.2/network/admin/listener.ora

Listener Log File         /nbsdu01/app/grid/diag/tnslsnr/P550-05-LA/listener_scan1/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.15.86)(PORT=1522)))

Services Summary…

Service “NBSDEV” has 2 instance(s).

  Instance “NBSDEV1″, status READY, has 1 handler(s) for this service…

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

Service “NBSDEVXDB” has 2 instance(s).

  Instance “NBSDEV1″, status READY, has 1 handler(s) for this service…

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

The command completed successfully

 

现在的状况是看起来一切正常，但就是连不上。

我们来仔细检查一下SCAN Listener的状况：

grid:/nbsdu01/app/11.2/network/admin>lsnrctl service LISTENER_SCAN1

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 11:28:28

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))

Services Summary…

Service “NBSDEV” has 2 instance(s).

  Instance “NBSDEV1″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “DEDICATED” established:0 refused:0 state:ready

         REMOTE SERVER

         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.84)(PORT=1522)))

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “DEDICATED” established:0 refused:0 state:blocked

         REMOTE SERVER

         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.84)(PORT=1522)))

Service “NBSDEVXDB” has 2 instance(s).

  Instance “NBSDEV1″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “D000″ established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: P550-05-LA, pid: 17104980>

         (ADDRESS=(PROTOCOL=tcp)(HOST=P550-05-LA)(PORT=64296))

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “D000″ established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: P550-05-LB, pid: 10616892>

         (ADDRESS=(PROTOCOL=tcp)(HOST=P550-05-LB)(PORT=43555))

The command completed successfully

从上述结果里我们可以看到，现在节点2上面的NBSDEV2的Handler的状态为blocked且HOST地址错了――这里HOST地址明显应该是10.1.15.85。

 

我们登陆一下节点2，看一下local listener的状态：

grid:/home/grid>lsnrctl status

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 11:31:40

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                28-APR-2012 17:34:03

Uptime                    4 days 17 hr. 57 min. 37 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /nbsdu01/app/11.2/network/admin/listener.ora

Listener Log File         /nbsdu01/app/grid/diag/tnslsnr/P550-05-LB/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.15.65)(PORT=1522)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.15.85)(PORT=1522)))

Services Summary…

Service “+ASM” has 1 instance(s).

  Instance “+ASM2″, status READY, has 1 handler(s) for this service…

The command completed successfully

从结果里可以看到，实例NBSDEV2的动态注册并没有成功，只有ASM实例，这个不对。

 

我们重启一下节点2上的local listener：

grid:/home/grid>lsnrctl stop

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 11:32:50

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

The command completed successfully

 

grid:/home/grid>lsnrctl start

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 11:32:54

 

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

 

Starting /nbsdu01/app/11.2/bin/tnslsnr: please wait…

 

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

System parameter file is /nbsdu01/app/11.2/network/admin/listener.ora

Log messages written to /nbsdu01/app/grid/diag/tnslsnr/P550-05-LB/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                03-MAY-2012 11:32:55

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /nbsdu01/app/11.2/network/admin/listener.ora

Listener Log File         /nbsdu01/app/grid/diag/tnslsnr/P550-05-LB/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

The listener supports no services

The command completed successfully

 

重启完后现在的状态就对了：

grid:/home/grid>lsnrctl status

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 11:34:10

                              

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                03-MAY-2012 11:32:55

Uptime                    0 days 0 hr. 1 min. 15 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /nbsdu01/app/11.2/network/admin/listener.ora

Listener Log File         /nbsdu01/app/grid/diag/tnslsnr/P550-05-LB/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.15.65)(PORT=1522)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.15.85)(PORT=1522)))

Services Summary…

Service “+ASM” has 1 instance(s).

  Instance “+ASM2″, status READY, has 1 handler(s) for this service…

Service “NBSDEV” has 1 instance(s).

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

Service “NBSDEVXDB” has 1 instance(s).

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

The command completed successfully

 

再次回到节点1，现在我们再来看一下SCAN Listener的状态：

grid:/nbsdu01/app/11.2/network/admin>lsnrctl service LISTENER_SCAN1

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 11:34:52

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))

Services Summary…

Service “NBSDEV” has 2 instance(s).

  Instance “NBSDEV1″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “DEDICATED” established:0 refused:0 state:ready

         REMOTE SERVER

         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.84)(PORT=1522)))

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “DEDICATED” established:0 refused:0 state:ready

         REMOTE SERVER

         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.85)(PORT=1522)))

Service “NBSDEVXDB” has 2 instance(s).

  Instance “NBSDEV1″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “D000″ established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: P550-05-LA, pid: 17104980>

         (ADDRESS=(PROTOCOL=tcp)(HOST=P550-05-LA)(PORT=64296))

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “D000″ established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: P550-05-LB, pid: 10616892>

         (ADDRESS=(PROTOCOL=tcp)(HOST=P550-05-LB)(PORT=43555))

The command completed successfully

从结果里我们可以看到，现在NBSDEV2的Handler的状态已经变成了ready且HOST地址也变成了10.1.15.85。

 

再次在我本机上尝试连接NBSDEV，刚才的报错ORA-12545已不复存在，我们又可以连上了：

SQL> conn sys/oracle@nbsdev as sysdba;

已连接。

 

SQL> show parameter instance_name;

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

instance_name                        string      NBSDEV1

 

从整个诊断过程来看，在用SCAN VIP连接的时候，Oracle似乎是在我反复尝试的情况下，始终都将我的连接redirect到节点2上，又因为节点2在SCAN Listener上注册的HOST地址不对，所以Oracle这里报错“ORA-12545: 因目标主机或对象不存在, 连接失败”，这个跟我心目中SCAN VIP的原理不太一样。节点2有问题，你就让我连节点1好了，干嘛要在我反复尝试的情况下始终报错ORA-12545呢？

 

现在我们手工把节点2上的local listener停掉：

grid:/home/grid>lsnrctl stop

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 14:08:45

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

The command completed successfully

 

再次回到节点1，查看一下SCAN Listener的状态：

grid:/home/grid>lsnrctl service LISTENER_SCAN1

 

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 03-MAY-2012 14:10:26

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))

Services Summary…

Service “NBSDEV” has 2 instance(s).

  Instance “NBSDEV1″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “DEDICATED” established:23 refused:0 state:ready

         REMOTE SERVER

         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.84)(PORT=1522)))

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “DEDICATED” established:3 refused:0 state:blocked

         REMOTE SERVER

         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.85)(PORT=1522)))

Service “NBSDEVXDB” has 2 instance(s).

  Instance “NBSDEV1″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “D000″ established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: P550-05-LA, pid: 17104980>

         (ADDRESS=(PROTOCOL=tcp)(HOST=P550-05-LA)(PORT=64296))

  Instance “NBSDEV2″, status READY, has 1 handler(s) for this service…

    Handler(s):

      “D000″ established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: P550-05-LB, pid: 10616892>

         (ADDRESS=(PROTOCOL=tcp)(HOST=P550-05-LB)(PORT=43555))

The command completed successfully

从上述结果里我们可以看到，当我把节点2上的local listener停掉后，节点1上SCAN Listener中节点2对应的Handler的状态已经变成了blocked，但此时HOST地址是对的。

 

从如下结果我们可以看到——虽然节点1上SCAN Listener中节点2对应的Handler的状态已经变成了blocked，但因为HOST地址是对的，所以当我在本机以SCAN VIP连接的时候还是可以连上，Oracle这里正确的将我的连接redirect到了节点1上：

SQL> conn sys/oracle@nbsdev as sysdba;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as SYS

 

SQL> show parameter instance_name;

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

instance_name                        string      NBSDEV1

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

←
→