14 Replies Latest reply: Sep 5, 2013 8:29 PM by user3050034 RSS

    VIRTUALBOX/Oracle Express 11g: ORA-12505

    user3050034

      I am configuring  Oracle Express 11g for the first time...

      Running it  as a guest in Win7/Virtualbox/Centos 6.4.

       

      The problem is, when 11g is initially installed and automatically configured, the listener does not know anything about the additional host address 192.168.56.101 which is necessary for guest-host communication. So, I am trying to resolve this issue.

       

      I can telnet from win7 to CentOS OK,

      sqlplus is running fine, I can connect sys/pass as sysdba.

      Running select instance from v$thread;

      gives XE

      tnsping XE in CentOS gives:

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))

      OK (0 msec)

       

      I changed listener.ora and tnsname.ora from what they were after install to put HOST = 192.168.56.101 instead of localhost.localdomain there:

       

      listener.ora is:

      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 = 192.168.56.101)(PORT = 1521))

          )

        )

       

      tnsname.ora is:

      XE =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(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)

          )

        )

      DEFAULT_SERVICE_LISTENER = (XE)

       

      /etc/hosts in CentOS is:

      127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

      ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

      192.168.56.101 loc

       

      Running SQL Developer is in Win7 where there is no Oracle installed.

      Connection parameters are:

       

      Username: sys

      Hostname: 192.168.56.101

      Port: 1521

      SID: XE

       

      Getting ORA-12505...

       

      ---------------------------------------------------------------

       

      The issue was resolved by disabling listener.ora and putting correct host address to tnsnames.ora

        • 1. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
          rp0428

          I know ORA-12505 is a common situation, but what about it in VIRTUALBOX?

          What about it?

           

          Since you didn't post what it is you are talking about how do you expect anyone to help? Why should anyone have to lookup the meaning of your exception?

           

          It is up to YOU to post ALL relevant information about your problem. At a minimum that includes:

           

          1. an EXACT copy of WHAT you did

          2. exactly HOW you did it

          3. a copy of the EXACT results that Oracle gave

          4. why you think those results are wrong

          5. the results that you expected to get

          • 2. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
            sb92075

            [oracle@localhost ~]$ oerr ora 12505

            12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"

            // *Cause:  The listener received a request to establish a connection to a

            // database or other service. The connect descriptor received by the listener

            // specified a SID for an instance (usually a database instance) that either

            // has not yet dynamically registered with the listener or has not been

            // statically configured for the listener. This may be a temporary condition

            // such as after the listener has started, but before the database instance

            // has registered with the listener.

            // *Action:

            //  - Wait a moment and try to connect a second time.

            //  - Check which instances are currently known by the listener by executing:

            //    lsnrctl services <listener name>

            //  - Check that the SID parameter in the connect descriptor specifies

            //    an instance known by the listener.

            //  - Check for an event in the listener.log file.

             

            issue the following OS command from the system where the database & listener are running

             

            lsnrctl status

            lsnrctl service

             

            COPY the results from above then PASTE all back here

             

            one of the lines will provide the complete pathname for the listener log flle.

             

            the listener log file will contain 1 or more lines with the status code of 12505

             

            post excerpt from the the listener log file which contains the line with the 12505 status code and a half dozen lines above & half dozen lines  below it.

            • 3. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
              user3050034

              sb92075 :  Thanks for the info. Please, have a look at the updated post.

              • 4. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                user3050034

                Look like you have not read the complete post...

                • 5. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                  sb92075

                  user3050034 wrote:

                   

                  sb92075 :  Thanks for the info. Please, have a look at the updated post.

                   

                  I can't tell what has changed or not.

                  • 6. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                    user3050034

                    My guess from the listener log is, host address change from the  initial configuration did not really work. Maybe you know how to do this without Oracle reinstall?

                    • 7. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                      sb92075

                      user3050034 wrote:

                       

                      My guess from the listener log is, host address change from the  initial configuration did not really work. Maybe you know how to do this without Oracle reinstall?

                       

                      my car did not work.

                      tell me how to make my car go.

                       

                      "did not work" is 100% devoid of any actionable detail.

                       

                      is  COPY & PASTE  broken for you?

                       

                       

                       

                      How do I ask a question on the forums?

                      https://forums.oracle.com/message/9362002#9362002

                      • 8. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                        EdStevens

                        user3050034 wrote:

                         

                        I am configuring  Oracle Express 11g for the first time...

                        Running it  as a guest in Win7/Virtualbox/Centos 6.4.

                        I can telnet from win7 to CentOS OK,

                        sqlplus is running fine, I can connect sys/pass as sysdba.

                        Running select instance from v$thread;

                        gives XE

                        tnsping XE in CentOS gives:

                        Used TNSNAMES adapter to resolve the alias

                        Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))

                        OK (0 msec)

                         

                        I changed listener.ora and tnsname.ora from what they were after install to put HOST = 192.168.56.101 instead of localhost.localdomain there:

                         

                        listener.ora is:

                        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 = 192.168.56.101)(PORT = 1521))

                            )

                          )

                         

                        tnsname.ora is:

                        XE =

                          (DESCRIPTION =

                            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(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)

                            )

                          )

                        DEFAULT_SERVICE_LISTENER = (XE)

                         

                        /etc/hosts in CentOS is:

                        127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

                        ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

                        192.168.56.101 loc

                        doesn't hurt anything, but it is a bit odd to want to resolve a host name of 'loc'.

                         

                         

                        Running SQL Developer is in Win7 where there is no Oracle installed.

                        You mean no Oracle database installed.  You obviously have an Oracle client installed ...

                         

                        Connection parameters are:

                         

                        Username: sys

                        Hostname: 192.168.56.101

                        Port: 1521

                        SID: XE

                         

                        Getting ORA-12505...

                         

                        tnsping XE:

                        Used TNSNAMES adapter to resolve the alias

                        Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))

                        OK (0 msec)

                        so far, so good.  Just be aware that tnsping only proves the message reaches a listener.  It says nothing about the database itself.

                         

                        lsnrctl service

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

                        Services Summary...

                        Service "PLSExtProc" has 1 instance(s).

                          Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

                            Handler(s):

                              "DEDICATED" established:0 refused:0

                                 LOCAL SERVER

                         

                        lsnrctl status

                        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                03-SEP-2013 19:36:41

                        Uptime                    0 days 21 hr. 27 min. 12 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/localhost/listener/alert/log.xml

                        Listening Endpoints Summary...

                          (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))

                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))

                        Services Summary...

                        Service "PLSExtProc" has 1 instance(s).

                          Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

                         

                        And sure enough, the listener does not know anything of the database, its sid or services.

                         

                        /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml:

                        <msg time='2013-09-04T11:57:59.605-04:00' org_id='oracle' comp_id='tnslsnr'

                        type='UNKNOWN' level='16' host_id='localhost.localdomain'

                        host_addr='::1'>

                        <txt>04-SEP-2013 11:57:59 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=michael))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=55426)) * establish * XE * 12505

                        </txt>

                        </msg>

                        <msg time='2013-09-04T11:57:59.606-04:00' org_id='oracle' comp_id='tnslsnr'

                        type='UNKNOWN' level='16' host_id='localhost.localdomain'

                        host_addr='::1'>

                        <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

                        </txt>

                        </msg>

                        Those xml logs are great for adrci, but the older straight text logs are much easier on human eyes.  Look in $ORACLE_BASE/diag/tnslsnr/<servername>/listener/trace

                         

                        Not sure how this relates with  connection parameters in SQL Developer above, but I see that listener's host_id in the log is still localhost.localdomain, not  192.168.56.101.

                        Also, PORT=55426 looks strange...

                        Maybe host address is not reconfigured properly? If so, what else needs to be done about it?

                         

                        Thanks!

                        you need to figure out why the database is not registered with the listener.

                        • 9. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                          user3050034

                          The point of this question is actually quite simple. The problem is, when 11g is initially installed and automatically configured, the listener does not know anything about additional host address 192.168.56.101 which is necessary for guest-host communication. So, I am trying to resolve this issue. Thanks

                          • 10. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                            sb92075

                            ><txt>04-SEP-2013 11:57:59 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=michael))) * (ADDRESS=(PROTOCOL=tcp)

                            >(HOST=192.168.56.1)(PORT=55426)) * establish * XE * 12505


                            NOTE the HOST IP# of 192.168.56.1 in line above.


                            You claim the XE database in running, but it has not registered itself with any listener.


                            You have done a fine job of confusing yourself, multiple OS & Oracle regarding who is named what & using which IP#!


                            For the record, no listener.ora file is required to start or use any listener.

                            Consider to remove, delete, eliminate, or rename listener.ora out of the way to exclude it from being part of the problem.

                            Needless to say the listener itself should be restarted after doing as above.

                            • 11. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                              rp0428

                              user3050034 wrote:

                               

                              Look like you have not read the complete post...

                              No - it looks like YOU have not read your own post.

                               

                              Nowhere in your post did you post a copy of the EXACT exception that includes both the error code and the error message.

                              The thread subject is this:

                              VIRTUALBOX/Oracle Express 11g: ORA-12505

                              And the ONLY other mention in your original post (go read it again right now if you want) of the actual problem is this:

                              Getting ORA-12505...

                              Many of us are not going to lookup ORA-12505 to see what it means.

                               

                              It is up to you to provide the information we need to help you. Oracle provided the information to you and then instead of providing it to us you provide only the error code.

                               

                              That is what I am talking about.

                              • 12. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                                user3050034

                                sb92075: This is needed to build Virtualbox VMs: http://www.oracle.com/technetwork/community/developer-vm/index.html#dbapp I could not make their VM work, so I installed 11G in CentOS VM. The idea to disable listener.ora turned out to be great. I used tnsnames.ora as following: XE =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 92.168.56.101)(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)     )   ) And, after the restart, the connection was OK. Thanks

                                • 13. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                                  user3050034

                                  The issue is resolved.

                                  • 14. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
                                    user3050034

                                    The issue is resolved.

                                    Thanks!