How to troubleshoot Oracle remote database connection

Many Oracle DBAs, developers and just end-users often encounter an issue when they can not connect remotely to an Oracle database. There can be different reasons of the connection problems. I give below a short cookbook on resolving those database connection issues. But first let’s explore a bit Oracle connectivity concepts and terminology.

Oracle NET Client Listener tnsnames

Oracle NET Client Server configuration

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. The service name is included in the connect data part of the connect descriptor. To connect to a database service, clients use a connect descriptor that provides the location of the database and the name of the database service. The address portion of the connect descriptor is actually the protocol address of the listener. To connect to a database service, clients first contact a listener process that typically resides on the database server. The listener receives incoming client connection requests and hands these requests to the database server. Once the connection is established, the client and database server communicate directly. Read more »

How to fix ORA-12547 TNS lost contact when try to connect to Oracle

Issue / Oracle error

sqlplus scott/tiger
SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 09:32:35 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
ERROR:
ORA-12547: TNS :lost contact when try to connect to Oracle.

Issue description

I saw that TNS connection issue along with ORA-12547 Oracle error several times, usually when trying to connect to Oracle database server on Unix / Linux host with an OS user that does not belong to oinstall group (Oracle binaries owner group).  In this case, interesting enough that local TNS connection to database (when using tnsnames alias) works fine: Read more »

Oracle Listener refused connection ORA-12519 troubleshooting

Platform

Oracle database 11gR2 (11.2.0.2.2)

Problem

New database connection attempts to Oracle database 11gR2 fail with following Oracle error:

Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found   [SQLCode: 12519], [SQLState: 66000] None None

Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email