Saturday, September 12, 2009

Rehash of Oracle Networking

It's that time again. A new crop of DBAs is trying to connect their app to their database. A quick overview that might help one or two of them:

You got your Database SERVER. It's isolated. The only way to get to it across the network is via a LISTENER. The listener listens on the network based on the rules in the LISTENER.ORA but those rules may be modified by the SQLNET.ORA

On the flip side

You got your application (Informatica, SQLPlus, TOAD, whatever). It wants to talk to the database. To do that it talks to the Oracle Networking (aka SQLNet) CLIENT. That is governed by the rules in the TNSNAMES.ORA (but those rules may be modified by the SQLNET.ORA)

If not Java and assuming TCP/IP, your CLIENT is told (by the app) to connect to "user/password@alias". It looks up 'alias' in the TNSNAMES.ORA to get 'host, port, database-service'. (Java/JDBC needs the same info, but in typical Java fashion, it's syntax is 'different'.)

- The host is the one where your database should be.
- The port is the TCP/IP port the listener is told to listen on (using LISTENER.ORA)
- The service represents the database the listener should know about, to which you want to connect

Possible Failures:

- CLIENT does not understand the alias (ORA-12154) ... bad entry in TNSNAMES.ORA - or DEFAULT_DOMAIN in SQLNET.ORA
- CLIENT can't find host (DNS issue?)
- CLIENT can't get to port (Firewall)
- CLIENT can't find LISTENER on port (ORA-12541) (listner not up; listener on a different port; listener.ora vs tnsnames.ora mismatch)
- LISTENER doesn't know what client is talking about (ORA-12514)
- - database is not up or has not been registered with Listener (check 'lsnrctl status')
- - Listener on an unexpected port (check DB init parameter 'LOCAL_LISTENER' - default=localhost:1521, compare to listener.ora)

Oh - and ORA-12560 means that the piece (client OR listener) can not verify it is on the network. Often means that the tnsnames.ora (client) or listener.ora (server) has a hostname or port problem. That may be due to DHCP (hostname) or firewall (port).

There are other reasons, of course. The Oracle Network Administration manual has a fantastic set of chapters for troubleshooting.


Aman.... said...

Very nice Hans :-).


Gary Myers said...

12154, 12541 and 12514. Sadists ! Something a little more distinct would have been easier to remember.