This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Oct 12, 2011 3:17 AM by misterimran RSS

Unable to access RAC from SQL Developer!

misterimran Newbie
Currently Being Moderated
Dear All,

I have just configured 2 Node RAC 11gR2 on Redhat Linux.
I can connect to SQLPlus from both RAC nodes and can access it through enterprise manager.

However I am unable to connect it from any client machines, even from SQL Developer on my machine, i have been doing the RAC installation from it. I have check from systems and there is no restriction on accessibility of these IPs.

These are my IPs

Public IP : 192.168.20.31, 192.168.20.33
Virtual IP: 192.168.20.32, 192.168.20.34
Single client access IP : 10.168.20.29 name: rac-scan

These are the contents of tnsadmin file from node 1.

BSSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bssdb)
)
)


Should ichange the host from rac-scan to IP of the machine and then restart the listener?

Kindly help on this.

Regards,
Imran
  • 1. Re: Unable to access RAC from SQL Developer!
    585179 Expert
    Currently Being Moderated
    misterimran wrote:
    Dear All,

    I have just configured 2 Node RAC 11gR2 on Redhat Linux.
    I can connect to SQLPlus from both RAC nodes and can access it through enterprise manager.

    However I am unable to connect it from any client machines, even from SQL Developer on my machine, i have been doing the RAC installation from it. I have check from systems and there is no restriction on accessibility of these IPs.

    These are my IPs

    Public IP : 192.168.20.31, 192.168.20.33
    Virtual IP: 192.168.20.32, 192.168.20.34
    Single client access IP : 10.168.20.29 name: rac-scan

    These are the contents of tnsadmin file from node 1.

    BSSDB =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = bssdb)
    )
    )


    Should ichange the host from rac-scan to IP of the machine and then restart the listener?

    Kindly help on this.

    Regards,
    Imran
    What error did you get?

    what happen if you try connect through VIP?

    Also the scan address configuration is not correct, it must be in the same subnet with public IP


    Cheers
  • 2. Re: Unable to access RAC from SQL Developer!
    misterimran Newbie
    Currently Being Moderated
    Sorry, Single client access IP : 192.168.20.29 name: rac-scan it is on the same network where the public IP is.
    The error I get is: Listener refuse the connection with the following error: ORA - 12505, The listener does not know of SID given in connect descriptor.

    The error is same for public and virtual IPs.

    Regards, Imran
  • 3. Re: Unable to access RAC from SQL Developer!
    misterimran Newbie
    Currently Being Moderated
    When i see the status of listener from node 1 it shows:

    I have restart the listener and register the database after which i can access using using public and virtual IPs but still not with rac-scan (i.e. 192.168.20.29)

    The applications that have to connect this RAC should use rac-scan as per my understanding

    LSNRCTL> status
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 05-OCT-2011 14:41:09
    Uptime 0 days 21 hr. 12 min. 45 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
    Listener Log File /u01/app/grid/diag/tnslsnr/racnode1/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.31)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.32)(PORT=1521)))
    Services Summary...
    Service "+ASM" has 1 instance(s).
    Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Service "bssdb" has 1 instance(s).
    Instance "bssdb1", status READY, has 1 handler(s) for this service...
    Service "racdbXDB" has 1 instance(s).
    Instance "bssdb1", status READY, has 1 handler(s) for this service...
    The command completed successfully

    Regards, Imran
  • 4. Re: Unable to access RAC from SQL Developer!
    orafad Oracle ACE
    Currently Being Moderated
    misterimran wrote:
    ORA - 12505, The listener does not know of SID given in connect descriptor.
    Here it says SID, not service name.

    Does it make a difference if you change connection properties in SQL Developer to use Service name instead of SID?
  • 5. Re: Unable to access RAC from SQL Developer!
    orafad Oracle ACE
    Currently Being Moderated
    Oh, and its forum is here: {forum:id=260}
  • 6. Re: Unable to access RAC from SQL Developer!
    misterimran Newbie
    Currently Being Moderated
    No difference if we change from SID to service.
    Error is the same.
  • 7. Re: Unable to access RAC from SQL Developer!
    misterimran Newbie
    Currently Being Moderated
    I am not just talking about SQL Developer, from any other tool also, after editing tnsnames.ora adding connection to RAC and then accessing database using SQL Plus error remains the same.

    Regards, Imran
  • 8. Re: Unable to access RAC from SQL Developer!
    orafad Oracle ACE
    Currently Being Moderated
    Exact same message? Then things definitely don't add upp.

    In SQL Developer, what is the Connection type and what fields are filled?
  • 9. Re: Unable to access RAC from SQL Developer!
    P.Forstmann Guru
    Currently Being Moderated
    Please post full output:
    sqlplus <user>/xxx@<Oracle Net service name>
    tnsping <Oracle Net service name>
  • 10. Re: Unable to access RAC from SQL Developer!
    misterimran Newbie
    Currently Being Moderated
    SQL> connect imran/imran@bssdb
    ERROR:
    ORA-12505: TNS:listener does not currently know of SID given in connect
    descriptor


    Warning: You are no longer connected to ORACLE.

    tnsping bssdb

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.29)(PORT = 1521)) (CONNECT_DATA = (SID = bssdb)))
    OK (0 msec)
  • 11. Re: Unable to access RAC from SQL Developer!
    misterimran Newbie
    Currently Being Moderated
    I added

    192.168.20.29 in host file of the machine where i am trying to access this RAC

    Earlier (be4 adding entry in host file), it was giving error of unknown host name, but after this entry now the error is ORA-12505: TNS:listener does not currently know of SID

    Maybe it helps
  • 12. Re: Unable to access RAC from SQL Developer!
    P.Forstmann Guru
    Currently Being Moderated
    In general and especially for RAC database you should use SERVICE_NAME to connect and not SID (because only SERVICE_NAME can use load balancing and failover features):

    So try to put in your tnsnames.ora:
    ... (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.29)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = bssdb)))
    Edited by: P. Forstmann on 6 oct. 2011 14:05
  • 13. Re: Unable to access RAC from SQL Developer!
    misterimran Newbie
    Currently Being Moderated
    Thanks for the tip. I did it.

    Any how the result is still the same.
  • 14. Re: Unable to access RAC from SQL Developer!
    orafad Oracle ACE
    Currently Being Moderated
    If you changed to service_name parameter and error still is 12505, then you don't have the right tnsnames.ora / not using the right entry.

    Also verify that connection is possible:

    sqlplus user/pass@'host:port/service_name'

    Edited by: orafad on Oct 6, 2011 3:03 PM
1 2 Previous Next

Legend

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