Problems with hosts file and "TNS:could not resolve the connect identifier specified"

If you update your hosts file as I described in a recent post to disassociate 127.0.0.1 with your host name you may get an error when you try to spin up anything using the database "TNS:could not resolve the connect identifier specified".

This is not technically a middleware problem - it's a problem with the database. But if you're like me you're probably not a database expert and won't know how to fix it.

So here's how to fix it... First the symptom:
You can use sqlplus to connect to the database with a valid username and password (e.g. system/ABcd1234) works, but if you add the at sign and the SID (e.g. system/ABcd1234@orcl) things go sideways.

Like so:

[oracle@idm11g ~]$ . /usr/local/bin/oraenv 
ORACLE_SID = [oracle] ? orcl
The Oracle base for ORACLE_HOME=/home/oracle/database/product/11.2.0/dbhome_1 is /home/oracle/database
[oracle@idm11g ~]$ sqlplus system/ABcd1234

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 1 14:52:14 2011

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


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

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@idm11g ~]$ 
[oracle@idm11g ~]$ sqlplus system/ABcd1234@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 1 14:52:17 2011

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

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

to fix it: 1. Find your tnsnames.ora (mine's in /home/oracle/database/product/11.2.0/dbhome_1/network/admin/tnsnames.ora) and cat/more/less it. It will look something like this:

# tnsnames.ora Network Configuration File: /home/oracle/database/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

2. Copy the part starting with "(ADDRESS =" through the closing paren. In my case that's "(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))" 3. run "sqlplus / as sysdba"

4. type in

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))' scope=both;

make sure you update that line to reflect the contents of your tnsnames.ora file.

You may want to reboot to make sure everything works the next time you start the VM, but that's not actually necessary.

Hope this helps someone!