ORA-12154 when connecting using tns alias

Question

I installed Oracle 11.2 on a Win7x64 laptop and almost everything works fine. One thing I don't understand, however. I can connect to the sample HR schema with:

sqlplus hr/password

but not with:

sqlplus hr/password@orcl

I get an ORA-12154: TNS:could not resolve the connect identifier specified.

I found this out by accident, as I was expecting the second option to be correct. Why can I leave out the TNS alias? I'm an Oracle beginner but when I connect to other environments, the TNS alias is required or I can't make a connection.

I have the following in my tnsnames.ora:

LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA = (DESCRIPTION =
  (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))
  (CONNECT_DATA = (SID = CLRExtProc)(PRESENTATION = RO)))

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

I know for sure it's being used because both tnsping orcl and tnsping dummy are ok. Can someone explain this to me?

UPDATE 1: The output of lsnrctl status is the following:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                11-NOV-2012 00:43:22
Uptime                    0 days 0 hr. 15 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\Ronald\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\app\ronald\diag\tnslsnr\Ronald-PDC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

The result of SELECT name FROM v$database is one database: ORCL.

UPDATE 2: Output of tnsping orcl (formatted for readability).

Used parameter files:
C:\app\Ronald\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

Answer 1

Did you actually create a database instance named ORCL?

Check out the actual name of your database with the command

lsnrctl status

This should tell you what services the listener knows about, which is what you're talking to when connecting with the @ syntax. The reason you can connect without the @ORCL is that in that case you're using interprocess communication (IPC) and bypassing the listener, because the database is on the same computer as your SQL*Plus client session.

You can also find out the database name when you connect in the first example with this:

SELECT name FROM v$database;

EDIT:

Try the EZConnect syntax and see if that works:

sqlplus user/pw@localhost/ORCL

Keep in mind that TNSPING doesn't do anything besides contact the listener - it doesn't verify the database can be accessed.

One more thing: It looks like maybe TCP isn't a configured protocol for the listener? check your listener.ora file (in the same directory as your tnsnames.ora file). The entry for LISTENER should look something like this:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

My TNSPING output looks a bit different in one line near the start of output:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))