RAC 没有配置remote_listener导致无法通过scan-ip登录数据库

为现有RAC搭建了一套RAC stanby备库，在客户端用VIP可以正常连接，但是通过SCAN IP则无法连接。

客户端无法登录

$ sqlplus  TRADE/xxxxx@10.0.2.110/trade34

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 13:56:41 2016

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

 

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

问题原因

服务端是RAC，没有设置remote_listener。
11gR2 数据库，默认设置local_listener 参数为local LISTENER（本地监听器,ip地址为vip），为VIP listener提供服务；
remote_listener参数设置为SCAN listener，为SCAN IP提供服务。

SQL> show parameter remote_listener

 

NAME                                 TYPE        VALUE

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

remote_listener                      string

查看scan ip的listener，发现没有提供服务。（注意RAC 中 db的listener配置文件在grid用户下）

$ lsnrctl status LISTENER_SCAN1

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-MAR-2016 14:43:05

 

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

 

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

STATUS of the LISTENER

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

Alias                     LISTENER_SCAN1

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                26-FEB-2016 17:14:35

Uptime                    25 days 21 hr. 28 min. 29 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/grid/network/admin/listener.ora

Listener Log File         /u01/grid/log/diag/tnslsnr/SL010A-IVDB04/listener_scan1/alert/log.xml

Listening Endpoints Summary...

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

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

The listener supports no services

The command completed successfully

解决办法

SQL> alter system set remote_listener='trade34-scan:1521' scope=both sid='*';

SQL> alter system register;

SQL> show parameter remote_listener

NAME                                 TYPE        VALUE

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

remote_listener                      string      trade34-scan:1521

scan ip的监听开始提供服务

$ lsnrctl status LISTENER_SCAN1

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-MAR-2016 14:45:48

 

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

 

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

STATUS of the LISTENER

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

Alias                     LISTENER_SCAN1

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                26-FEB-2016 17:14:35

Uptime                    25 days 21 hr. 31 min. 13 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/grid/network/admin/listener.ora

Listener Log File         /u01/grid/log/diag/tnslsnr/SL010A-IVDB04/listener_scan1/alert/log.xml

Listening Endpoints Summary...

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

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

Services Summary...

Service "trade34" has 2 instance(s).

  Instance "trade3", status READY, has 1 handler(s) for this service...

  Instance "trade4", status READY, has 1 handler(s) for this service...

The command completed successfully

再次测试，成功通过SCAN IP连接到数据库

$ sqlplus  TRADE/xxxxxx@10.0.2.110/trade34

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 14:16:39 2016

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

参考：

MOS TNS-12514 Unable to connect to RAC database using SCAN name (文档 ID 1459768.1) 

见 http://blog.chinaunix.net/uid-23284114-id-5680804.html

转载请注明：
十字螺丝钉
http://blog.chinaunix.net/uid/23284114.html
QQ:463725310
E-MAIL:houora#gmail.com(#请自行替换为@)