This discussion is archived
12 Replies Latest reply: Feb 23, 2012 8:19 AM by 919043 RSS

ORA-12560 adapter error, connecting to remote database

919043 Newbie
Currently Being Moderated
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
    LaurenzAlbe Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    @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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    >
    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
    LaurenzAlbe Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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!

Legend

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