"TNS could not resolve connect identifier specified", "...could not resolve service name"

I am trying to connect to the Data Warehouse or another database and am getting one of the following error messages:

ORA-12154: TNS:could not resolve the connect identifier specified

ORA-12154: TNS:could not resolve service name

Context

These error messages indicate that a connection to a database was requested using a connect identifier (such as "warehouse"), and the Oracle client could not resolve the specified connect identifier into a connect descriptor using one of the naming methods configured in sqlnet.ora. The connect descriptor will include server and port information.

Note: In previous versions of Oracle, the Oracle client relied on the TNSNAMES.ORA file to resolve the connect identifier. MIT currently uses the Oracle Internet Directory (OID) server, and all databases previously listed in TNSNAMES.ORA are now available on the OID server. Connection information for the OID server (LDAP server), is stored in ldap.ora.

Solution

One: Off campus users will need to first connect to the MIT VPN.

See: Prisma Access VPN Landing Page

If you're off campus, and connecting to the VPN is not a viable option, see:
"TNS could not resolve the connect identifier specified" error, user not on main MIT campus, VPN not a viable option

Two: Verify Oracle configuration files

If you're connecting to a 'well-known' MIT database, such as the Data Warehouse, you should be using MIT's Oracle Internet Directory (OID) server. This server acts as an online tnsnames.ora file. To verify your OID configuration:

1. If there is a chance that there is more than one Oracle installation on the machine, you'll need to look in the Path variable to ascertain which Oracle installation appears first in the Path. This is the Oracle installation which will need the MIT configuration files.
   • To find the Path variable, go to Start > Control Panel > System > Advanced > Environment Variables
   • Look for Path.
   • Copy and paste the path into a text editor such as Notepad. Look for the first time that Oracle appears. The entry will be for the Oracle bin directory.
2. Navigate, on your hard drive, to the Oracle installation. If there is more than one Oracle installation, be sure to choose the one that appeared first in the Windows Path variable (see Step 1).
3. Within the Oracle directory structure, navigate to network\Admin.
   • Make sure the file ldap.ora exists, and has these lines in it:
     DEFAULT_ADMIN_CONTEXT="dc=mit,dc=edu"
     DIRECTORY_SERVERS=(tnsnames.mit.edu:389:636,noid:4032:4031)
     DIRECTORY_SERVER_TYPE=OID
   • Make sure your sqlnet.ora file has this line:
     NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES)
   • For Oracle 11g on Windows 7, if MIT versions of sqlnet.ora and ldap.ora do not exist in Oracle's network\Admin directory, follow the instructions for installing the MIT Oracle configuration files
   • For Oracle 9, you can run the Repair option of the MIT Oracle 9i installer to re-create these files. Back up any file you've changed before you do this.
     • If you're trying to connect to a private database using Oracle 9, edit your local tnsnames.ora file (the default location is C:\Program Files\oracle\ora92\network\Admin\tnsnames.ora). Make sure a valid TNS entry exists for the database. You can use one of the entries as a template. Contact the database administrator for the correct parameters, if necessary.

Three: SQLPlus users, check for typos

If you are using SQLPlus, first check for typos. If you are using BrioQuery, this step is not applicable.

Oracle 11.1.0> sqlplus user/password@wareMouse

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 17 13:44:40 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Oracle 11.1.0> sqlplus user/password@warehouse

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 17 14:21:11 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>