To properly resolve this error and connect to the appropriate Oracle database, we’ll need to expound a bit on how Oracle behaves and, therefore, what is causing this issue in the first place.
SIDs vs SERVICE_NAMES
It is important to understand the (slight) difference between what Oracle defines as a SID compared to a SERVICE_NAME, as we’ll use this information to create a proper connection string later on.
In Oracle, the system identifier (or SID) is a local identifier of up to eight characters in length that is used to identify a particular database and differentiate it from other databases on the system.
Often the SID is the prefix word or DB_UNIQUE_NAME that precedes the DB_DOMAIN.
SERVICE_NAMES, on the other hand, represent the names by which database instances can be connected to. A SERVICE_NAME will typically follow the format of the SID followed by the database domain, like so: DB_UNIQUE_NAME.DB_DOMAIN
The TNS Listener
When a client is attempting to connect to an Oracle database, rather than connecting to the database directly, there is a broker service that intervenes and handles the connection request for the client.
This broker application is known as the listener and it performs the task of listening for incoming client requests. When a request is received, the listener processes and forwards that request onto the appropriate Oracle database server using a service handler, which just acts as the connection between the listener and the database server.
TNS Names and Configuration
When connecting to an Oracle database, typically your database server will have tnsnames.ora, which is a configuration file that informs the server about NET_SERVICE_NAMES which are valid database connections. By default, this file is located at ORACLE_HOME/network/admin.
For example, a NET_SERVICE_NAME descriptor in tnsnames.ora may be formatted like this:
myDatabaseNetService =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)(QUEUESIZE = 100))
(CONNECT_DATA =
(SERVICE_NAME = bookstore.company.com)
)
)This would define a NET_SERVICE_NAME using the SERVICE_NAME we discussed earlier and connecting to localhost through port 1521.
Connection String
With a bit more knowledge about how Oracle actually connects to databases, we can now look at how connection strings are formatted.
Connect via TNS NAME
When connecting through a NET_SERVICE_NAME as specified in your tnsnames.ora config file, you must use the username, password, and then append the NET_SERVICE_NAME with the @ symbol, like so:
username/password@NET_SERVICE_NAME
Connect via NET_SERVICE_NAME
Thus, for our previous NET_SERVICE_NAME descriptor above, the actual NET_SERVICE_NAME we defined was myTestDatabaseNetService, so our connection string might look something like this:
john/Pass123@myTestDatabaseNetService
Connect via SERVICE_NAME
When connecting through a SERVICE_NAME, you’ll also need to add the host and port, along with the / symbol preceding the SERVICE_NAME itself:
username/password@host:port/SERVICE_NAME
Connect via SID
Finally, if connecting without a configured NET_SERVICE_NAME or even SERVICE_NAME, you can do so directly through the SID by using the : symbol instead of the / symbol as with the SERVICE_NAME connection string:
username/password@host:port:SID
