ORA-12505 for Oracle XE 11g on Ubuntu

Question

I have some problems connecting to my local Oracle database.

I have done the installation based on this guide: installing-oracle-11g-r2-express

The tutorial worked out fine, but after the installation I am unable to connect to database using SQLDeveloper or 'sqlplus system/password@XE'

sqlplus system/password@XE
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 16 11:57:06 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

connecting without using XE works:

:~$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 16 11:54:54 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password: 
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> 

when I try to connect using SQLDeveloper I get the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

What am I missing?

---

listener.ora as requested by @Mihai: changed 20.11.2012 12:06

# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
     (SID_DESC =
       (SID_NAME = XE)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <name_of_my_computer>)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

---

$ lsnrctl status as requested by @BjarteBrandt:

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 18-NOV-2013 12:52:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                18-NOV-2013 12:48:06
Uptime                    0 days 0 hr. 4 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/<name_of_my_computer>/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<name_of_my_computer>)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Solution 1

Fist, please make sure the XE database did register to the listener.

output from my listener:

$ lsnrctl status

Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

If no services are in your list, please bounce the listener

$ lsnrctl stop
$ lsnrctl start

Login to the database over IPC as user sys. (does not contact the listener)

$ sqlplus / as sysdba
SQL>-- tell the listener to pick up this particular database service.
SQL>alter system register;

If your database service still is not listed, please configure you listener.ora file for a STATIC service.

Static service

Solution 2

Look at tnsnames.ora in your Oracle home.You should have something like this:

XE=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =somehost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XE)
    )
  )

Edit

In SQLDeveloper,that error means you are trying to connect on SID,change that to service_name.

OR, in tsanames change SERVICE_NAME = XE to SID = XE

Comments

[Mihai]

@Vegard Edited my post.

[Vegard]

I updated my original post, not sure what I should read from the status. the lsnrctl stop failed saying I am not authorized. running as root does not help.

[Bjarte Brandt]

your status indicates that no service named "XE" is registered with the listener. Please add a static entry to the SID_LIST.

[Vegard]

that did not work. But I am not sure I changed it correctly. I have edited my original post with what I tried. please take look.

[Vegard]

Did some more experimenting - and it worked! thanks a lot for the help. I am not quite sure if what I did to tnsnames.ora was a good idea, but your tip worked out. Could you take a quick look at the file despite it working now :)? updated my op

[Bjarte Brandt]

Don't confuse tnsnames.ora (lookup TNS-ALIAS) with listener.ora (listener config file). Your listener.ora looks fine. I am still not sure why we have to make a static entry. From 10g, the database is supposed to register dynamically. On my fedora box I did not have this issue. I am glad it worked finally!