How can I allow an Oracle database to be accessed remotely?

Oracle

Oracle is an enterprise database server so you shouldnt need any strange setup to get it working, just the correct configuration on the DB server or client will be enough.

When you get connection errors, i suggest first ensuring that the client machine can ping the database server where is the name or ip address – try both as it’s possible the name may not be configured in the hosts file of the client machine. (Note that ping may be disabled due to security but if you get a reply back, you know the connection is good.A non reply may not mean the network is missing but you can try to ssh or telnet to the dbhost from the client machine as an alternative)

unix> ping
Once you have confirmed the client machine can see the db server, you can check the configuration on the DB server.

On the DB server, if you connect with the following, you are connecting directly to the database without using TNS.

unix> sqlplus user/pass
If you connect instead with

unix> sqlplus user/[email protected]_SID
where ORACLE_SID is the identifier of the database, you will be using TNS, the same as your client machine. If this doesnt work locally, then it’s also not going to work on the remote machine.

Check the listener is running

unix> lsnrctl status
and if it’s not, start it as the oracle user

unix> lsnrctl start
and try again to connect using user/[email protected]_SID

If this connects OK, then check your JDBC configuration to ensure you have specified the connection string correctly.

see http://javaeesupportpatterns.blogspot.de/2011/08/network-adapter-could-not-establish.html for assistance with the correct jdbc connection string.

=======================================================================================================

Help! I canít connect to my Oracle database!

(This is a revised version of an article I originally published at edstevensdba.wordpress.com)

Some of the most frequently asked questions on the Oracle Technology Network (OTN) forums deal with problems trying to connect to the database. Tracing the problem isnít rocket science, but I often see people not paying attention to (or not trusting) the very specific error messages and riding off in all directions at once. So let me try to explain a little about how Oracle handles a request to ìconnect me to my databaseî and actually locates a database running on a machine on the other side of the planet (or even on the very machine from which the request originated!)

Before digging in, letís talk about a very simple concept that an amazing number of people struggle with. For purposes of the current discussion there are two ìentitiesî, or processes, involved. First there is the server process. Depending on oneís semantic precision and the context in which the term is used, the ìserverî could refer to the database server process, or the computer on which that process executes. The second entity is the client process. That is the process that is requesting the connection to (and services from) the database. Again, depending on oneís semantic precision and context, the term ìclientî could refer to a process or a computer on which the process executes. For our purposes both ìclientî and ìserverî mean the process. These processes could be running on any two separate computers, or (and understand this) they could be running on the very same computer. The important thing is the distinction between the two processes.

So letís take a quick walk down the path from the client to the server. We will dig deeper in future posts. For our purposes, we will use the most common Oracle client program of all: sqlplus. At a command line you issue this statement to start it and connect to your database:

1
C:> sqlplus scott/[email protected]
Of course, the first thing that will happen really has nothing to do with Oracle. First, the OS must locate an executable called ësqlplusí, load it, and pass it the rest of the command line (scott/[email protected]) do with as it sees fit. And what sqlplus sees fit is to pass a request to Oralceís network layer (TNS, Transparent Network Substrate) to make a connection to ìlarryî, using the userid ìscottî and the password ìtigerî as its authentication credentials. So TNS has to figure out what is meant by ìlarryî. By default it will do this by looking in a file called tnsnames.ora. Since we are still at the client making the request, this file must be found on the client machine. By default it will be found in $ORACLE_HOME/network/admin.

Letís make it easy and suppose our tnsnames file has this entry:

larry =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = curley)
)
)
TNS will look in tnsnames.ora for an entry called ëlarryí. Finding it, a request is sent through the normal OS network stack to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley). Notice where it got this information from the entry in the tnsnames file. (This entry is known as the ìconnect identifierî.) Also notice that what is going on here is the resolution of an alias (ìlarryî) to an actual destination. In this respect the tnsnames.ora file serves the same purpose for sqlnet as the OSís ìhostsî file serves for the standard network stack. Or for a less technical analogy, it serves the same purpose as your telephone directory, where the the name of ìlarryî would be associated with the routing information (telephone number) needed by the telephone network.

All network routing is done by IP address, but all we have provided here is a host name of ìmyhostî Where is ìmyhostî) on the network? When the request gets passed from TNS to the standard network stack, the name ëmyhostí will get resolved to an IP address, either via a local ëhostsí file or a DNS server. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora but for ease of maintenance this is not recommended.

Once the ip address is determined, the standard networking process delivers the message to the designated port (PORT = 1521) at the specified ip address. Hopefully, there is an Oracle database listener process at that address and configured to listen on the specified port, and that listener knows about SERVICE_NAME=curley. If so, the listener will spawn a server process to act as the intermediary between your client and the database instance. Communication to that server process will be on a different port, selected by the listener and communicated back to the client. At that point the listener is out of the process and continues to await other connection requests coming in on its configured port.

What can go wrong?

First, there may not be an entry for ëlarryí in your tnsnames. In that case you get ìORA-12154: TNS:could not resolve the connect identifier specifiedî. Iíll expand on the various reasons ìlarryî may not have been found at a later date, but make no mistake, if you receive a ORA-12154, it is an absolute certainty your request never got past this point. You are wasting your time trying to solve this by looking at your listener. If you canít place a telephone call because you donít know the number (canít find your telephone directory ñ aka ìtnsnames.oraî ñ or canít find the party you are looking for listed in it ñ no entry for larry) you donít look for problems at the telephone switchboard.

Maybe the entry for ìlarryî was found, but ìmyhostî couldnít be resolved to an IP address (neither the local hosts file nor the DNS server had an entry for ìmyhostî). This will result in ìORA-12545: Connect failed because target host or object does not existî.

Maybe there was an entry for ìmyserverî in the local hosts file or the DNS server, but it specified an IP address that does not exist on the network or is otherwise unreachable. This will result in ìORA-12170: TNS:Connect timeout occurredî.

Maybe the IP that is reachable on the network, but there is no listener running on that machine. ìORA-12541: TNS:no listenerî

Maybe the IP was good, there is a listener at that address, but it is listening on a different port. Again, ìORA-12541: TNS:no listenerî

Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesnít know about SERVICE_NAME=curley. ìORA-12514: TNS:listener does not currently know of service requested in connect descriptorî

Ok, that is how we get from the client connection request to the listener. What about the listenerís part of all this?

The listener is very simple. It runs on the server machine and itís job is to listen for connection requests and make the connection (server process) between the client and the database instance. Once that connection is made, the listener is out of the picture. If you were to kill the listener, all existing connections would continue.

The listener is configured with the listener.ora file, but if that file doesnít exist, the listener is quite capable of starting up with all default values. One common mistake with the listener configuration is to specify ìHOST=localhostî or ìHOST=127.0.01î. This is a very special ip address, known as the ìlocal loopbackî address. LOCALHOST and ip address 127.0.0.1 always mean ìthis machine on which I am sittingî. So, all computers are known as ìlocalhostî or ì127.0.0.1î. If you specify this address in your listener configuration, the listener will only be capable of receiving requests from the machine on which it is running. If you specified that address in your tnsnames file, the request would be routed to the machine on which the requesting client resides. Probably not what you want.