This content has been marked as final. Show 12 replies
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
916040 wrote:Seem like a sound approach.
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...Did you verify basic network connectivity first?
it hangs, eventually timing out.
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)
@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?
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.
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'
<txt>22-FEB-2012 16:17:35 * service_update * adept2 * 0
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'
<txt>22-FEB-2012 16:03:40 * (CONNECT_DATA=(SERVICE_NAME=)(CID=(PROGRAM=sqlplus)(HOST=HOST NAME)(USER=oracle))) * establish * 12504
Edited by: strauss on Feb 22, 2012 2:21 PM
strauss wrote:Even if port 1521 is open, that may not be enough.
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?
Read this coarse description of the connection process:
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.Mysterious.
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
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.
Laurenz Albe wrote: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.
Edited by: orafad on Feb 23, 2012 2:02 PM
C:\>sqlplus blah@localhost/test SQL*Plus: Release 18.104.22.168.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 22.214.171.124.0 Production on Thu Feb 23 13:56:59 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter password:
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!