1 2 Previous Next 18 Replies Latest reply: Oct 12, 2011 5:17 AM by misterimran RSS

    Unable to access RAC from SQL Developer!

    misterimran
      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
          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
            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
              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
                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
                  Oh, and its forum is here: {forum:id=260}
                  • 6. Re: Unable to access RAC from SQL Developer!
                    misterimran
                    No difference if we change from SID to service.
                    Error is the same.
                    • 7. Re: Unable to access RAC from SQL Developer!
                      misterimran
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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