RAC 没有配置remote_listener导致无法通过scan-ip登录数据库
1,911 total views, 1 views today
为现有RAC搭建了一套RAC stanby备库，在客户端用VIP可以正常连接，但是通过SCAN IP则无法连接。
客户端无法登录
|
1 2 3 4 5 6 7 |
$ 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提供服务。
|
1 2 3 4 5 |
SQL> show parameter remote_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_listener string |
查看scan ip的listener，发现没有提供服务。（注意RAC 中 db的listener配置文件在grid用户下）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
$ 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 |
解决办法
|
1 2 3 4 5 6 7 |
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的监听开始提供服务
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
$ 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连接到数据库
|
1 2 3 4 5 6 7 8 9 |
$ 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