This discussion is archived
9 Replies Latest reply: Dec 1, 2012 8:17 PM by 977377 RSS

Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error

904947 Newbie
Currently Being Moderated
I installed oracle 11g XE on ubuntu 12.04 using the help from Oracle 11gR2 Express Edition on Linux Ubuntu 11.10 howto
The installation went through successfully.
I am able to login to the database as sys using the SQL prompt.

But when I try to connect apex - it is not connecting
When I try to establish a new connection from SQLDeveloper it gives m ORA - 12505 error

Message in SQLDeveloper

Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

I am able to get success with the lsnrctl command - output appended below
bold lsnrctl - output bold

neerav@pavilion7011tx:~$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 23-JUL-2012 18:08:12

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 23-JUL-2012 17:27:40
Uptime 0 days 0 hr. 40 min. 32 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/pavilion7011tx/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pavilion7011tx)(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

The contents of the listener.ora and tnsnames.ora are as under:
bold # listener.ora Network Configuration File: bold

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

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = pavilion7011tx)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)


bold # tnsnames.ora Network Configuration File: bold

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pavilion7011tx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

bold neerav@pavilion7011tx:~$ cat /etc/hosts bold
127.0.0.1     localhost
127.0.1.1     pavilion7011tx

# The following lines are desirable for IPv6 capable hosts
#::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

First of all the link above is a great - marvellous post from Dude. Thanks a ton. It is because of such help that novice like me are daring to explore.

I am not able to understand what is wrong with my installation. I got the success messages after the installation. But still am un able to connect to apex or connect through SQLDeveloper

Note: Ignoring the warning regarding compatibility, I am using jdk 1.7 u5 instead of 1.6uxx.

Dude: I searched for an update as mentioned in your post regarding install instructions for precise but could not find one.

Thanks again and awaiting help
  • 1. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    Udo Guru
    Currently Being Moderated
    Hi,

    the status report of your listener doesn't show that the XE instance has registered. Are you sure your database is up and running at all? Please post the output of
    sqlplus / as sysdba
    while being logged as oracle user.
    Another hint could be in the listeners log file in +/u01/app/oracle/diag/tnslsnr/pavilion7011tx/listener/alert/log.xml+ . Could you check it for errors and post any you see?

    -Udo
  • 2. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    904947 Newbie
    Currently Being Moderated
    Hi Udo

    Thanks for replying and taking interest.

    Below are the outputs

    *1. Output of plsql / as sysdba logged as oracle user*

    neerav@pavilion7011tx:~$ su - oracle
    Password:
    oracle@pavilion7011tx:~$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 24 01:50:29 2012

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


    Connected to:
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

    SQL>


    *2. Output of the listener log file*

    Ouptut of listeners log file in /u01/app/oracle/diag/tnslsnr/pavilion7011tx/listener/alert/log.xml

    XML Parsing Error: junk after document element
    Location: file:///u01/app/oracle/diag/tnslsnr/pavilion7011tx/listener/alert/log.xml
    Line Number 8, Column 1:<msg time='2012-07-23T17:13:15.117+05:30' org_id='oracle' comp_id='tnslsnr'
    ^
  • 3. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    orafad Oracle ACE
    Currently Being Moderated
    Neerav Pandya wrote:
    bold neerav@pavilion7011tx:~$ cat /etc/hosts bold
    127.0.0.1     localhost
    127.0.1.1     pavilion7011tx
    The second line would seem like a potential problem.

    Put the actual ip interface adress instead or remove the line.

    The a simple troubleshooting step is to rename listener.ora file - it's not needed in most cases (defaults will do fine).
  • 4. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    904947 Newbie
    Currently Being Moderated
    Hi
    As per your suggestion I tried commenting out the 2nd line but it was giving problems so I commented out the 1st line i.e.

    # 127.0.0.1 local host in /etc/hosts

    Then I made a copy of listener.ora by the name of listener1.ora and then moved the listener.ora to a backup folder.

    Now when I am trying to issue lsnrctl status and start commands I get the following errors
    neerav@pavilion7011tx:~$ lsnrctl status
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-JUL-2012 17:53:47
    
    Copyright (c) 1991, 2011, Oracle.  All rights reserved.
    
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       Linux Error: 111: Connection refused
    neerav@pavilion7011tx:~$ lsnrctl start
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-JUL-2012 17:54:21
    
    Copyright (c) 1991, 2011, Oracle.  All rights reserved.
    
    Starting /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr: please wait...
    
    TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    Log messages written to /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/pavilion7011tx/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pavilion7011tx)(PORT=1521)))
    
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    Start Date                24-JUL-2012 17:54:21
    Uptime                    0 days 0 hr. 0 min. 0 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/pavilion7011tx/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pavilion7011tx)(PORT=1521)))
    The listener supports no services
    The command completed successfully
    What should I do now?
  • 5. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    Udo Guru
    Currently Being Moderated
    *1. Output of plsql / as sysdba logged as oracle user*
    neerav@pavilion7011tx:~$ su - oracle
    Password:
    oracle@pavilion7011tx:~$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 24 01:50:29 2012
    
    Copyright (c) 1982, 2011, Oracle. All rights reserved.
    
    Connected to:
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
    So your database is actually up and running, it's "just" a listener problem.
    Have you checked your firewall settings?
    XML Parsing Error: junk after document element
    Location: file:///u01/app/oracle/diag/tnslsnr/pavilion7011tx/listener/alert/log.xml
    That looks awkward. I'm not sure where this parsing error comes from...
    Could you check whether you have messages like that (or other errors) in +/u01/app/oracle/diag/tnslsnr/pavilion7011tx/listener/trace/listener.log+ as well? This is the plain text version of that alert log...

    -Udo
  • 6. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    orafad Oracle ACE
    Currently Being Moderated
    Neerav Pandya wrote:
    Uptime 0 days 0 hr. 0 min. 0 sec
    After waiting at least 1 minute, what does lsnrctl stat command reveal?
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pavilion7011tx)(PORT=1521)))
    The listener supports no services
  • 7. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    952713 Newbie
    Currently Being Moderated
    Do the following:



    *#tnsnames.ora Network Configuration File:*



    XE =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = pavilion7011tx)(PORT = 1521))

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = XE)

    )

    )



    *#for local access*

    XE.LOCAL =

    +(DESCRIPTION =+

    +(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))+

    +(CONNECT_DATA =+

    +(SERVER = DEDICATED)+

    +(SERVICE_NAME = XE)+

    +)+

    +)+



    and use XE.LOCAL (from your laptop where client = server) to connect and only from your laptop. This is all you need. No LISTENER.ORA, no SQLNET.ORA.

    Just try <tnsping XE.LOCAL>;. If it works, you're done.



    You are running oracle on a laptop, don't you?

    On a Laptop, using a hostname is required unless you can use a fixed IP-address. But this makes you dependent from name resolution. DHCP may assign hostnames of its own. Even using an IP-address may not work, if you are not connected to a network because your physical interface may be down.



    Oracle expects to run on a server! Servers usually have fixed IP-addresses, even if these are assigned by DHCP.

    IP-addressing must be consistent across all configuration files.

    If you are using the IP-address in one place you must use it everywhere, also on clients.

    If you are using hostname, use the same hostname everywhere and make sure that name resolution works with exactly that hostname given in TNSNAMES.ORA.

    And on a laptop use the loopback interface to connect from within.
  • 8. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    977377 Newbie
    Currently Being Moderated
    Hi Udo,

    I think i need your help I am using Ubuntu 12.10 and installed Oracle recently. Now, i trying to connect it from SQL developer. However, i am getting the following error message:
    The network adapter could not establish connection. After searching for a while i found out that my listener service is not up and running. So, i tried many times with failed attempts to "lsnrctl start" for which the error message was :

    Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    TNS-12555: TNS:permission denied
    TNS-12560: TNS:protocol adapter error
    TNS-00525: Insufficient privilege for operation
    Linux Error: 1: Operation not permitted

    So, i tried to login to SQL with "sqlplus / as sysdba". But again it says insufficient privilege. I am not sure what is happening. I also noticed that there is a "oracle" OS user
    created after i installed Oracle 11g express edition, in my ubuntu login screen. I am not even sure what its password is. Could you please help?

    thanks,
    gokul.
  • 9. Re: Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error
    977377 Newbie
    Currently Being Moderated
    Hi Udo,

    I think i need your help I am using Ubuntu 12.10 and installed Oracle recently. Now, i trying to connect it from SQL developer. However, i am getting the following error message:
    The network adapter could not establish connection. After searching for a while i found out that my listener service is not up and running. So, i tried many times with failed attempts to "lsnrctl start" for which the error message was :

    Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    TNS-12555: TNS:permission denied
    TNS-12560: TNS:protocol adapter error
    TNS-00525: Insufficient privilege for operation
    Linux Error: 1: Operation not permitted

    So, i tried to login to SQL with "sqlplus / as sysdba". But again it says insufficient privilege. I am not sure what is happening. I also noticed that there is a "oracle" OS user
    created after i installed Oracle 11g express edition, in my ubuntu login screen. I am not even sure what its password is. Could you please help?

    thanks,
    gokul.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points