9 Replies Latest reply: Dec 1, 2012 10:17 PM by 977377 RSS

    Oracle 11g XE in Ubuntu 12.04 - ORA 12505 error

    904947
      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
          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
            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
              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
                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
                  *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
                    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
                      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
                        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
                          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.