Cry about...
Oracle Troubleshooting

ORA-12203: TNS: unable to connect to destination

Symptom:

When trying to connect to Oracle the following error is generated:

ORA-12203: unable to connect to destination

Possible Causes and Remedies:

• Invalid TNS address supplied.
  • Verify that the service name is correct.
  • Verify that the name of the ‘host’ computer (defined as part of the TNS address) is valid and correct.
• Destination not listening.
  • Ensure that the listener is running at the remove node. This can be verified on the server by using the command ‘lsnrctl80 status’ and also by looking at the services and checking that the service ‘OracleService<Sid>’ is running.
  • If the listener service is not running, does not exist and the database has just been created anew on the machine by restoring it from a backup, then the listener service will need to be created:

    Oradim80 –new –sid <SID> -intpwd Oracle –startmode AUTO –pfile <full-path-of-init-file>

    This should only be done in the circumstances outlined above.

• Possibly because of underlying network transport problems.
  • Check that the remote node is visible on the network.
  • To gain more information in diagnosing the problem, enable tracking on the client, try making the connection again and then look at the trace file SQLNET.LOG.
• There are multiple databases on the server and the database alias was not supplied as part of the connection string. This will only arise if connecting to the database on the same machine, since normally in this instance the database alias is optional if there is only one instance. Try:
  • Setting the environment variable ‘ORACLE_SID’ to the SID of the desired instance. (Consider setting ORACLE_SID in the registry.)
  • Explicitly specifying a database alias when connecting to the database.
  • Setting the environment variable ‘LOCAL’ or ‘REMOTE’ to the SID of the desired database. The service name does not then need to be specified.

Note: Using the utility ‘TNSPING’ may help in identifying any problems. As well as identifying whether a successful connection can be made, it also indicates whether the TNS name supplied is valid and if so then the host computer that it is trying to connect to. Syntax:

TNSPING <tns-name>

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.