Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\FBC305BB46560\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=machine.domain.com)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Port 1521 seems ok:

c:\>netstat -an -O | find /i "1521"
  TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       4368
  TCP    169.243.90.109:55307   159.185.207.100:1521   ESTABLISHED     12416
  TCP    [::]:1521              [::]:0                 LISTENING       4368

(PID 4368 is TNSLSNR.exe process.)

Also, I can tnsping to the XE service:

c:\>tnsping xe

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Beta on 11-MAR-2013 12:27:47

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

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = machine.domain.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (210 msec)

The listenerr.ora file:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = machine.domain.com)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

Additionally, and I've no idea if it is related, I can't seem to access apex on https://127.0.0.1:8080/apex (even though the permissions for that seem fine).

So where else should I be looking?

Update with requested information:

SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      XE
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string

Update2: as @miracle173 correctly points out, the listener was not fine. With the updated 'local_listener' parameter now shows extra information:

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=machine.domain.com)(PORT=1521)))
  (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 "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
share|improve this question
    
What are the values of your INSTANCE_NAME initialization parameter and ORACLE_SID environment variable? Your database doesn't register with the default listener for some reason. Try to issue alter system register; and then re-connect to to the database. –  Yasir Arsanukaev Mar 11 '13 at 14:03
    
@YasirArsanukaev instance_name is 'xe' (lower case if that is relevant) and I don't have an ORACLE_SID environment variable. –  Unsliced Mar 11 '13 at 14:07
1  
two notes: "the listener seems fine": I don't think so because it does not display a service named "XE" "I can ping the XE service": tnsping connects to the listener but it does not bother about services. so you can only show if the listener is up and running but you cannot use ist to check if the listener has registered a service. So tnsping "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))" will succeed if a listener is running on port 1521 of localhost even if the service does not exist. –  miracle173 Mar 12 '13 at 8:14
    
I ran into the same problem today, and as @miracle173 described, there is a registration in the alert.log. Oracle is running on a VM. It turns out on the VM, both hostname, and hostname.domain all point to a ip address different than 127.0.0.1 (localhost). After modifying the hosts file (Windows\system32\drivers\etc) to force the mapping, all is fine now. –  stepheng88 Jun 13 at 19:05

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
you actually eyplained the solution: the automatic regristration of the database to the default LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))' (where hostname is the network name of the machine) did not work because there is a problem to reach the machine under this name. Maybe there is an entry in the alert.log or in some sqnlnet.log (%ORACLE_HOME%/network/log/) about the registration problem of the database –  miracle173 Mar 12 '13 at 9:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.