This discussion is archived
14 Replies Latest reply: Sep 5, 2013 6:29 PM by user3050034 RSS

VIRTUALBOX/Oracle Express 11g: ORA-12505

user3050034 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    [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 Newbie
    Currently Being Moderated

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

  • 4. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
    user3050034 Newbie
    Currently Being Moderated

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

  • 5. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
    sb92075 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    ><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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    The issue is resolved.

  • 14. Re: VIRTUALBOX/Oracle Express 11g: ORA-12505
    user3050034 Newbie
    Currently Being Moderated

    The issue is resolved.

    Thanks!

Legend

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