I created a new database and (out of sloppiness) made the value of the SERVICE_NAMES parameter mixed case. Then, in all the tnsnames.ora files in the system (both local and remote), I specified an uppercase value for the SERVICE_NAME parameter. I then observed the following strange behavior:
When connecting to the database from a remote server (e.g. with sqlplus system@mydb), the connection would fail with the following error:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
I tested this from both Windows and Linux remote hosts; same result.
However, connecting from the local server (the same server that the database is on), using the same connection string (sqlplus system@mydb), I can connect.
Furthermore, all connection problems went away when I changed the database's SERVICE_NAMES to be all caps, matching the SERVICE_NAME parameter in the tnsnames.ora files.
I can find no reference to SERVICE_NAMES being case sensitive. Can anyone explain this behavior? Why would it work locally, but not remotely?
Database is 10gR2 running on RHEL4.
SERVICE_NAMES = MYDB.example.com
All tnsnames.ora files contain the following alias:
MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = EXAMPLE.COM)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB.EXAMPLE.COM)
)
)