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

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

 

如下是NBSDEVdatabase 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上面的NBSDEV2Handler的状态为blockedHOST地址错了――这里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

从结果里我们可以看到,现在NBSDEV2Handler的状态已经变成了readyHOST地址也变成了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上,又因为节点2SCAN 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停掉后,节点1SCAN Listener中节点2对应的Handler的状态已经变成了blocked,但此时HOST地址是对的。

 

从如下结果我们可以看到——虽然节点1SCAN 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

 


One Comment on “解决RAC环境下ORA-12545连接错误一例”

  1. L says:

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

    崔大,这个HOST地址出现错误是人为导致的还是说节点紊乱导致的?


Leave a Reply

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