In this Document
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later
Information in this document applies to any platform.
SYMPTOMS
Configed 3 node RAC and all instances are up and running, user can not connect using SCAN name, get ORA-12514:
node1:racr1:/home/oracle> sqlplus
/@db_oltp
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 9 15:09:55 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Both default LISTENER and SCAN LISTENER are running. All database instances are running.
In tnsnames.ora, service db_oltp is defined as:
DB_OLTP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-rac.sample.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db_oltp)
)
)
The database remote_listener is set correctly as:
remote_listener=scan-rac:1521
lsnrctl status listener_scan shows "The listener supports no services".
$ lsnrctl status listener_scan1
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 9-MAY-2012 15:19:55
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.2.0 - Production
Start Date 9-MAY-2012 15:19:55
Uptime 0 days 4 hr. 0 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/112/grid/network/admin/listener.ora
Listener Log File /u01/112/grid/log/diag/tnslsnr/rachost1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.x)(PORT=1521)))
The listener supports no services
The command completed successfully
NAMES.DIRECTORY_PATH is either not set in sqlnet.ora or set with EZCONNECT included.
CAUSE
The problem is that : is wrongly defined in RDBMS ORACLE_HOME/network/admin/tnsnames.ora or $TNS_ADMIN/network/admin/tnsnames.ora with the following content:
SCAN-RAC:1521 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3vip)(PORT = 1521))
)
This causes scan-rac:1521 to be translated to host vips and their port, instead of scan listener. All instances are registered on the default listener which listens on host VIP as remote server rather than on the scan listener. Hence SCAN listeners have no service registered and client connection using the SCAN name and port will fail with ORA-12514 / TNS-12514.
This can happen for EBS environment if AutoConfig has been run.
Per Note 823587.1 Using Oracle 11g Release 2 Real Application Clusters with Oracle E-Business Suite Release 12
"Known Issues:
2. Remove the ":" tns alias from the tnsnames.ora files manually after running AutoConfig."
SOLUTION
1. Remove : definition (eg: scan-rac:1521 in this example) from the tnsnames.ora under RDBMS ORACLE_HOME/network/admin or $TNS_ADMIN/network/admin directory
2. On any one of the RAC instance:
sqlplus / as sysdba
alter system set remote_listener='' scope=both sid='*';
alter system set remote_listener='scan-rac:1521' scope=both sid='*';
alter system register;
3. To verify remote service registration:
lsnrctl service Listener_SCAN
Services should now be registered with scan listeners and client connections should be successfully connecting to the SCAN name and port.
