An application I'm working on connects to a database using the service name. The expected format is <SID>.<domain>. However, the application can't connect to the database using this as the service name.
There are 9 databases running on this server. I can connect to all of the other databases using that format for the service name (through the application or SQL developer). For some reason I can connect to this one using just the SID as the service name -- but not with the domain.
As you can see from the tns listener status below, it's using the SID alone for this database as the service name. I should also say that the pfiles for both of the databases I looked at for comparison had the same values.
listener.ora:
<...>
LISTENER_<this sid> =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>.<domain>)(PORT=1521)))
SID_LIST_LISTENER_KS=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=<this sid>.<domain>)
(SID_NAME=<this sid>)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)
LISTENER_<other sid> =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>.<domain>)(PORT=1521)))
SID_LIST_LISTENER_KS=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=<other sid>.<domain>)
(SID_NAME=<other sid>)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)
<...>
tnsnames.ora:
<...>
<this sid> =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>.<domain>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <this sid>.<domain>)))
<other sid> =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>.<domain>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <other sid>.<domain>)))
<...>
tns listener status:
$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-MAR-2019 10:24:48
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 07-MAR-2019 08:46:13
Uptime 0 days 1 hr. 38 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/<hostname>/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>.<domain>)(PORT=1521)))
Services Summary...
<...>
Service "<this sid>" has 1 instance(s).
Instance "<this sid>", status READY, has 1 handler(s) for this service...
Service "<this sid>XDB" has 1 instance(s).
Instance "<this sid>", status READY, has 1 handler(s) for this service...
Service "<other sid>.<domain>" has 1 instance(s).
Instance "<other sid>", status READY, has 1 handler(s) for this service...
Service "<other sid>XDB.<domain>" has 1 instance(s).
Instance "<other sid>", status READY, has 1 handler(s) for this service...
<...>
The command completed successfully
Error message when I use the <sid>.<domain> as service name:
Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
check db_name,db_domain and select global_name from global_name;