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 ] 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
username/password@NET_SERVICE_NAME
0. For example, the SID
of our
username/password@NET_SERVICE_NAME
2 database, as seen in in the full
username/password@NET_SERVICE_NAME
3 of
username/password@NET_SERVICE_NAME
4.
username/password@NET_SERVICE_NAME
5, 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:
username/password@NET_SERVICE_NAME
8
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 and it performs the task of listening for incoming client requests. When a request is received, the
username/password@NET_SERVICE_NAME
9 processes and forwards that request onto the appropriate Oracle database server using a , which just acts as the connection between the
username/password@NET_SERVICE_NAME
9 and the database server.
TNS Names and Configuration
When connecting to an Oracle database, typically your database server will have
john/Hunter2@myDatabaseNetService
3, which is a configuration file that informs the server about
john/Hunter2@myDatabaseNetService
4 which are valid database connections. By default, this file is located at
john/Hunter2@myDatabaseNetService
5.
For example, a
john/Hunter2@myDatabaseNetService
6 descriptor in
john/Hunter2@myDatabaseNetService
3 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
john/Hunter2@myDatabaseNetService
6 using the SERVICE_NAME
we discussed earlier [
username/password@NET_SERVICE_NAME
- and connecting to
username/password@host:port/SERVICE_NAME
1 through port
username/password@host:port/SERVICE_NAME
2.
Connection String
With a bit more knowledge about how Oracle actually connects to databases, we can now look at how
username/password@host:port/SERVICE_NAME
3 are formatted.
Connect via TNS Name/NET_SERVICE_NAME
When connecting through a
john/Hunter2@myDatabaseNetService
6 as specified in your
john/Hunter2@myDatabaseNetService
3 config file, you must use the
username/password@host:port/SERVICE_NAME
6,
username/password@host:port/SERVICE_NAME
7, and then append the
john/Hunter2@myDatabaseNetService
6 with the
username/password@host:port/SERVICE_NAME
9 symbol, like so:
username/password@NET_SERVICE_NAME
Thus, for our previous
john/Hunter2@myDatabaseNetService
6 descriptor above, the actual
john/Hunter2@myDatabaseNetService
6 we defined was
username/password@host:port:SID
2, so our connection string might look something like this:
john/Hunter2@myDatabaseNetService
Connect via SERVICE_NAME
When connecting through a SERVICE_NAME
, you’ll also need to add the
username/password@host:port:SID
4 and
username/password@host:port:SID
5, along with the
username/password@host:port:SID
6 symbol preceding the SERVICE_NAME
itself:
username/password@host:port/SERVICE_NAME
Connect via SID
Finally, if connecting without a configured
john/Hunter2@myDatabaseNetService
6 or even SERVICE_NAME
, you can do so directly through the SID
by using the `SID`1 symbol instead of the
username/password@host:port:SID
6 symbol as with the SERVICE_NAME
connection string: