12 Replies Latest reply: Feb 23, 2012 10:19 AM by 919043 RSS

    ORA-12560 adapter error, connecting to remote database

    919043
      I've only just started learning about Oracle for the last week or so and, admittedly, my setup process has been a bit ad hoc. Ultimately, I'm trying to connect JBoss to the database, but for now I'd just like to try a simpler setup.

      I'm using Instant Client on Windows 7 with Oracle 11.2 running on a RedHat 5 virtual machine. When I connect with the following...

      sqlplus sys@domain:1521/sid

      it hangs, eventually timing out. When I use...

      sqlplus sys as sysdba @domain:1521/sid

      it asks for my password then gives me the adapter error after I enter it.

      My listener status is:

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-FEB-2012 16:27:16

      Copyright (c) 1991, 2009, Oracle. All rights reserved.

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
      STATUS of the LISTENER

      Alias LISTENER
      Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
      Start Date 20-FEB-2012 15:28:54
      Uptime 1 days 0 hr. 58 min. 21 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File /var/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
      Listener Log File /var/app/oracle/diag/tnslsnr/ssc-redhat64/listener/alert/log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domain)(PORT=1521)))
      Services Summary...
      Service "sid" has 1 instance(s).
      Instance "sid", status UNKNOWN, has 1 handler(s) for this service...
      Service "sid.domain" has 1 instance(s).
      Instance "sid", status READY, has 1 handler(s) for this service...
      Service "sidXDB.domain" has 1 instance(s).
      Instance "sid", status READY, has 1 handler(s) for this service...
      The command completed successfully


      My main reason for using Instant Client is simply to test connecting to the database remotely. What other information should I post? Any help would be very appreciated.
        • 1. Re: ORA-12560 adapter error, connecting to remote database
          Laurenz Albe
          There is a syntax error that would explain the error message: "as sysdba" should be after the connection string.

          But don't try to connect remotely as sysdba. That will only work if you have a password file on the server, and it is an unsafe practice.

          Create a normal user and then try
          sqlplus user@hostname:1521/sid

          Yours,
          Laurenz Albe
          • 2. Re: ORA-12560 adapter error, connecting to remote database
            orafad
            916040 wrote:
            Ultimately, I'm trying to connect JBoss to the database, but for now I'd just like to try a simpler setup.
            Seem like a sound approach.
            I'm using Instant Client on Windows 7 with Oracle 11.2 running on a RedHat 5 virtual machine. When I connect with the following...

            sqlplus sys@domain:1521/sid

            it hangs, eventually timing out.
            Did you verify basic network connectivity first?

            E.g. can Windows 7 client access virtual machine host?

            Does virtual terminal (putty) connection from windows to RH vm work? Is the firewall up, and ports opened in RH? (port 1521, at least)
            • 3. Re: ORA-12560 adapter error, connecting to remote database
              919043
              I can connect with Putty from Windows. And I have this entry in /etc/sysconfig/iptables:

              -A RH-Firewall-1-INPUT -m state state NEW -m tcp -p tcp dport 1521 -j ACCEPT

              Is that enough to get that port open?
              • 4. Re: ORA-12560 adapter error, connecting to remote database
                919043
                @Laurenz: I tried those two different ways since it was just timing out with what you say is the correct way. I guess that just means there's a connection issue. I'll do it that way from now on.

                Also, here's the output from the command "nmap -sT -O localhost":

                PORT STATE SERVICE
                22/tcp open ssh
                25/tcp open smtp
                111/tcp open rpcbind
                631/tcp open ipp
                1521/tcp open oracle
                Device type: general purpose
                Running: Linux 2.6.X
                OS details: Linux 2.6.17 - 2.6.30
                Network Distance: 0 hops

                So 1521 should be good to go, right?
                • 5. Re: ORA-12560 adapter error, connecting to remote database
                  919043
                  Here's part of the output from netstat -anp.

                  Proto Recv-Q Send-Q Local Address Foreign Address Status PID/Program name
                  tcp 0 0 IP ADDRESS:12976 IP ADDRESS:1521 ESTABLISHED 6103/ora_pmon_adept
                  tcp 0 0 :::1521 :::* LISTEN 5863/tnslsnr

                  I'll need to research this a bit more but if this is relevant to my issue, I'd like to know if anyone knows.
                  • 6. Re: ORA-12560 adapter error, connecting to remote database
                    orafad
                    Try connecting, from windows:
                    sqlplus nouser@hostname/service_name
                    (supply bogus password at prompt)

                    Try one more time, but this time from linux.

                    Post last few lines from listener.log that, possibly, matches above connection attempts.
                    • 7. Re: ORA-12560 adapter error, connecting to remote database
                      919043
                      The log file you're talking about... I found one at /var/app/oracle/diag/tnslsnr/domain/listener/alert/log.xml. Is that it?

                      There are a lot of entries in there, like the following, showing up once every thirty seconds. There doesn't seem to be anything corresponding to my Windows connection attempt.

                      <msg time='2012-02-22T16:17:35.672-06:00' org_id='oracle' comp_id='tnslsnr'
                      type='UNKNOWN' level='16' host_id='HOST NAME'
                      host_addr='HOST IP'>
                      <txt>22-FEB-2012 16:17:35 * service_update * adept2 * 0
                      </txt>
                      </msg>


                      When I connect from Redhat, I get this.

                      <msg time='2012-02-22T16:03:40.375-06:00' org_id='oracle' comp_id='tnslsnr'
                      type='UNKNOWN' level='16' host_id='HOST NAME'
                      host_addr='HOST IP'>
                      <txt>22-FEB-2012 16:03:40 * (CONNECT_DATA=(SERVICE_NAME=)(CID=(PROGRAM=sqlplus)(HOST=HOST NAME)(USER=oracle))) * establish * 12504
                      </txt>
                      </msg>

                      Edited by: strauss on Feb 22, 2012 2:21 PM
                      • 8. Re: ORA-12560 adapter error, connecting to remote database
                        919043
                        Alright, so maybe my port wasn't open. I restarted iptables, and now I'm getting a different error. Should I make a new thread for it, or can I leave it in here? This is the error.

                        ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                        • 9. Re: ORA-12560 adapter error, connecting to remote database
                          orafad
                          >
                          ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                          Try with quotes around the connect id part, e.g.

                          C:\>sqlplus user@'hostname/test'
                          • 10. Re: ORA-12560 adapter error, connecting to remote database
                            Laurenz Albe
                            strauss wrote:
                            I can connect with Putty from Windows. And I have this entry in /etc/sysconfig/iptables:

                            -A RH-Firewall-1-INPUT -m state state NEW -m tcp -p tcp dport 1521 -j ACCEPT

                            Is that enough to get that port open?
                            Even if port 1521 is open, that may not be enough.
                            Read this coarse description of the connection process:
                            http://docs.oracle.com/cd/E11882_01/network.112/e10836/net_arch.htm#CEGBBEHH
                            Step 3, where the client connects to the server, is either a "resend" of the connection packet to port 1521 or a "redirect" to some other port where the actual server process is listening. It depends on the OS involved and other configuration settings which of the two takes place.
                            For a "redirect", the client must be able to open a TCP connection to an arbitrary port on the server machine.
                            I restarted iptables, and now I'm getting a different error. Should I make a new thread for it, or can I leave it in here? This is the error.

                            ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                            Mysterious.
                            Try an Oracle Net Trace on the client, that should give enough information to resolve the problem.
                            In "sqlnet.ora" on the client, add

                            TRACE_LEVEL_CLIENT = SUPPORT
                            TRACE_DIRECTORY_CLIENT = c:\TEMP (or some other directory where you are allowed to write)
                            SQLNET.ENCRYPTION_CLIENT = REJECTED
                            DIAG_ADR_ENABLED = OFF

                            Then the failed connection attempt will produce a largish cli_*.trc file.
                            Look for errors, post relevant parts if you are not sure.

                            Yours,
                            Laurenz Albe
                            • 11. Re: ORA-12560 adapter error, connecting to remote database
                              orafad
                              Laurenz Albe wrote:
                              Mysterious.
                              Not very mysterious, if you had read my post.


                              Different command syntax results in "unexpected" connect descriptor being used (one without service_name value, as shown by one of above posts with listener log output).

                              Whether pw prompt is presented is a hint.
                              C:\>sqlplus blah@localhost/test
                              
                              SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 23 13:56:44 2012
                              
                              Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                              
                              ERROR:
                              ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                              
                              
                              Enter user-name:
                              C:\>sqlplus blah@'localhost/test'
                              
                              SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 23 13:56:59 2012
                              
                              Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                              
                              Enter password:
                              Edited by: orafad on Feb 23, 2012 2:02 PM
                              • 12. Re: ORA-12560 adapter error, connecting to remote database
                                919043
                                Putting single quotes around the connection definitely helped. The final step was to log in as "system", since using sys kept giving the "should be as sysdba" error.

                                Once I got Instant Client working, it was a simple matter to make the right changes to my application's configuration file and connect using JBoss. From here, I should be good. Thanks, everyone!