Oracle 11g listener fails with ORA-12514 and ORA-12505 errors

I run an instance of Oracle 11g locally on my development machine and can connect to the local instance directly via SqlPlus:

c:\>sqlplus ace

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 11:50:20 2013

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

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

SQL> select count(*) from my_table ;

  COUNT(*)
----------
      5297

But I cannot connect to it via the listener:

c:\>sqlplus -L "user/pw@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))"

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 11:52:40 2013

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

Similarly, if I connect via SqlDeveloper I get an error (albeit ORA-12505, TNS:listener does not currently know of SID given in connect descriptor).

This instance has been stable and working fine for a year or more until today, a Monday morning. Our corporate IT do sometimes push new policies and updates over the weekend, so I'm assuming that something has changed, but I've not been able to work out what.

I've restarted the service and the listener several times, the listener log doesn't give any clues.

The listener seems fine:

```
c:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Beta on 11-MAR-2013 11:55:33

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Beta
Start Date 11-MAR-2013 11:17:30
Uptime 0 days 0 hr. 38 min. 3 sec
Trace Level

So, with thanks to @YasirArsanukaev for the time he put in, I have found a solution which works, but which I can't really explain.

Riffing on the LOCAL_LISTENER thought, I was reading this other answer where it said:


The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent to hostname:1521.

So I tried to ping my own hostname and couldn't - it looks like some IPv6 problem, receiving a general failure message.

So I took the advice from that answer

SQL> alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
SQL> alter system register;

and it now works, presumably because it can resolve the localhost reference, where it was failing the resolve the actual hostname.

StackExchange Database Administrators Q#36370, answer score: 15