Solved

Accessing oracle from remote machine

Posted on 2004-04-30
13
1,710 Views
Last Modified: 2013-12-11
I am running a JSP application on Tomcat Server with oracle 8i as database.
Oracle server is maintained on a seperate machine and all the developers access the same Oracle server for developing while they have tomcat installed seperately on their respective machines.

If i install tomcat on the machine in which oracle is installed and try to access oracle using the following code in my JSP, it works perfectly fine.

 Class.forName("oracle.jdbc.driver.OracleDriver");
      return DriverManager.getConnection ("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");

 But if the same code(changing the ip to oracle server's id, so the code would be    
   return DriverManager.getConnection ("jdbc:oracle:thin:@192.168.27.32:1521:orcl", "scott", "tiger");
), is used by a developer in his JSP using his local tomcat server and trying to access the orace server, she gets the following error.

java.sql.SQLException:Io Exception: The Network adapter could not establish a connection.

What we want to acheive is that although every developer has a web server seperately on her machine, the oracle server should be installed on only one machine and each developer should access that for developing her JSP application.
Thanks
0
Comment
Question by:wilma22
  • 6
  • 5
13 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 10958833
You are attempting to connect to an Oracle instance using JDBC
and you are receiving the following error.
 
   java.sql.SQLException: Io exception:  
     The Network Adapter could not establish connection
 
   SQLException: SQLState (null) vendor code (17002)
 
Any or all of the following conditions may also apply:
 
1) You are able to establish a SQL*Plus connection from the same
   client to the same Oracle instance.
 
2) You are able to establish a JDBC OCI connection, but not a Thin
   connection from the same client to the same Oracle instance.
 
3) The same JDBC application is able to connect from a different
   client to the same Oracle instance.
 
4) The same behavior applies whether the initial JDBC connection
   string specifies a hostname or an IP address.
 
REDISCOVERY
-----------
 
To verify whether you are hitting this problem, verify whether the
Oracle instance is configured for Multithreaded Server (MTS).
 
If the Oracle instance is not configured for MTS, you are probably
encountering a different problem. Otherwise, continue.
 
Try forcing the JDBC connection to use a dedicated server instead
of a shared server. This can be accomplished in several ways.
 
For JDBC OCI or Thin, this can be done by reconfiguring the server
for dedicated connections only. This approach, however, may not be
feasible in many cases. In such cases, the following options apply:
 
For JDBC OCI:
 
1) Add the (SERVER=DEDICATED) property to the TNS connect string
   stored in the tnsnames.ora file on the client.
 
2) Set the user_dedicated_server=ON in sqlnet.ora on the client.
 
For JDBC Thin:
 
You must specify a full name-value pair connect string (the same
as it might appear in the tnsnames.ora file) instead of the short
JDBC Thin syntax. For example, instead of
 
   "jdbc:oracle:thin:@host:port:sid"
 
you would need to use a string of the form
 
   "jdbc:oracle:thin:@(DESCRIPTION="                   +
                         "(ADDRESS_LIST="              +
                             "(ADDRESS=(PROTOCOL=TCP)" +
                                      "(HOST=host)"    +
                                      "(PORT=port)"    +
                             ")"                       +
                         ")"                           +
                         "(CONNECT_DATA="              +
                             "(SERVICE_NAME=sid)"      +
                             "(SERVER=DEDICATED)"      +
                         ")"                           +
                       ")"
 
If the connection works fine after having made these changes, it
is very likely that this is the problem you are encountering. In
this case, one last test will help to verify this fact.
 
Log into the remote host on which the Oracle instance is running
and execute the appropriate command to determine what the server
'thinks' its hostname is (i.e. the name that was configured when
the server was installed and configured). For example, on a Unix
host the 'hostname' command can be used for this purpose.
 
Using the name displayed (e.g. by the hostname command), exactly
as it appeared (i.e. if the output from the hostname command had
the domain name included, then include it), return to the client
which was unable to connect and try pinging the server.
 
NOTE: It is critical that you attempt to ping the server using
      EXACTLY the same hostname you got from the server.
 
If you are unable to ping the server via this hostname, then you
almost certainly hitting this problem. If not, this may be a new
issue, but at least you will have found a workaround (i.e. use a
dedicated connection).
 
EXPLANATION
-----------
 
To understand why this problem occurs, one must first understand
the differences in how the listener handles connections to shared
servers versus dedicated servers.
 
When connecting to a dedicated server, the client connects to the
listener (via hostname or IP address). The listener then spawns a
dedicated server process and hands off the socket used to accept
the client connection to that server. The client and server then
start communicating via the endpoints established by the initial
connection. NOTE: There is only one connection in this case.
 
When connecting to a shared server, the initial client connection
to the listener is the same. However, with MTS, there is no need
to spawn a new server process; a pool of shared processes already
exists. Also, clients do not communicate directly with the server
processes in MTS; rather, they communicate with a dispatcher.
 
For this reason, when setting up an MTS connection, the listener
sends a redirect message back to the client asking the client to
close the connection to the listener and connect to a dispatcher.
The information in this message includes the hostname and a port
number for the appropriate dispatcher. The redirect message will
ALWAYS specify a hostname, even if the client initially provided
an IP address.
 
If, for any reason, the hostname provided to the listener (e.g. by
the 'hostname' or another command) doesn't agree with the hostname  
by which the server is known on the client, the connection fails.
 
On the other hand, if "(SERVER=DEDICATED)" already appears in the
TNS connect string in tnsnames.ora or if "use_dedicated_server=ON"
already appears in the sqlnet.ora file, you may find that SQL*Plus
and/or JDBC OCI work fine, while JDBC Thin fails.
 
SOLUTION
--------
 
Obviously, one solution is to use dedicated servers. However, this
may not always be feasible.
 
The key is to make sure the hostname on both the client and server
agree. This can be accomplished by reconfiguring either the client
or the server, but there are things to be aware of in both cases.
 
If the server is configured to return a different hostname, then it
is possible that other clients which used to work will now fail.
 
In some cases, it may not be feasible to reconfigure the client. For
example, if the server version of the hostname does not include the
domain, you would need to remove the domain portion of the hostname
on the client; but, if the client needs to connect to more than one
server with the same base name in different domains, this may not be
possible, as the hostname may be ambiguous.

0
 

Author Comment

by:wilma22
ID: 10975066
I tried using

"jdbc:oracle:thin:@(DESCRIPTION="                   +
                         "(ADDRESS_LIST="              +
                             "(ADDRESS=(PROTOCOL=TCP)" +
                                      "(HOST=host)"    +
                                      "(PORT=port)"    +
                             ")"                       +
                         ")"                           +
                         "(CONNECT_DATA="              +
                             "(SERVICE_NAME=sid)"      +
                             "(SERVER=DEDICATED)"      +
                         ")"                           +
                       ")"
 

Now i am getting the following error:

java.sql.SQLException: invalid arguments in call
0
 
LVL 47

Expert Comment

by:schwertner
ID: 10975271
You are using the sample data and of course they will not work.
Investigate the name of the Oracle server machine, the port, the name of the service, etc.

"jdbc:oracle:thin:@(DESCRIPTION="                   +
                         "(ADDRESS_LIST="              +
                             "(ADDRESS=(PROTOCOL=TCP)" +
                                      "(HOST=Investigate name of host_machine)"    +
                                      "(PORT=1521)"    +
                             ")"                       +
                         ")"                           +
                         "(CONNECT_DATA="              +
                             "(SERVICE_NAME=-investigate_the sid)"      +
                             "(SERVER=DEDICATED)"      +
                         ")"                           +
                       ")"
 


0
 

Author Comment

by:wilma22
ID: 10983595
Thanks  schwertner
Did that already. This is what my code looked like:

return DriverManager.getConnection ("jdbc:oracle:thin:@(DESCRIPTION="                   +
                         "(ADDRESS_LIST="              +
                             "(ADDRESS=(PROTOCOL=TCP)" +
                                      "(HOST=192.128.116.34)"    +
                                      "(PORT=1521)"    +
                             ")"                       +
                         ")"                           +
                         "(CONNECT_DATA="              +
                             "(SERVICE_NAME=orcl)"      +
                             "(SERVER=DEDICATED)"      +
                         ")"                           +
                       ")"
);

Even tried machine name instead of IP.
0
 

Author Comment

by:wilma22
ID: 10983604
we are not given any user name and password in this code, like we did in previous code

Class.forName("oracle.jdbc.driver.OracleDriver");
      return DriverManager.getConnection ("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");


Don't we need to mention scott/tiger anywhere?
0
 
LVL 47

Expert Comment

by:schwertner
ID: 10983648
"scott" is the user name
"tiger" is the password for this user.

Mr. Scott was the first employee of Oracle, and Tiger is the name of his cat!

In an Oracle instance  there are many user names with appropriate passwords. Each contains a set of tables.


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:wilma22
ID: 10984350
>>"scott" is the user name
"tiger" is the password for this user.

What I wanted to ask was don't we need to specify the user name and password for the scheam we are trying to log into.
The code which was posted by you doesn't have any fields for user name and password


("jdbc:oracle:thin:@(DESCRIPTION="                   +
                         "(ADDRESS_LIST="              +
                             "(ADDRESS=(PROTOCOL=TCP)" +
                                      "(HOST=192.128.116.34)"    +
                                      "(PORT=1521)"    +
                             ")"                       +
                         ")"                           +
                         "(CONNECT_DATA="              +
                             "(SERVICE_NAME=orcl)"      +
                             "(SERVER=DEDICATED)"      +
                         ")"                           +
                       ")"

It doesn't specify user name and password anywhere.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 11003604
based on

("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");

You have to

("jdbc:oracle:thin:@(DESCRIPTION="                   +
                         "(ADDRESS_LIST="              +
                             "(ADDRESS=(PROTOCOL=TCP)" +
                                      "(HOST=192.128.116.34)"    +
                                      "(PORT=1521)"    +
                             ")"                       +
                         ")"                           +
                         "(CONNECT_DATA="              +
                             "(SERVICE_NAME=orcl)"      +
                             "(SERVER=DEDICATED)"      +
                         ")"                           +
                         ",scott,tiger"          +
                       ")"

Details could be found in your JDBC book.

BUT I THINK the first is good enough.

127.0.0.1 as I remember is LOOPBACK IP address, i.e. the same machine.

You have to make sure the IP of the machine on which Oracle server resides.
You have to check whether you can access the machine:
C:>ping 110.30.17.124
Only after this you will be able to establish the needed connection.
If you have installed Oracle client on the server machine check if you can connect Oracle.
Using Easy Config or NET Config. Assistant make an local name connection string to the database and test it. If you succeeed to do this you will not have problems.
After you make the connection string it is easy to check the connection:
C:>tnsping string
Of course make sure that the Oracle listener and the Oracle instance are runing on the Oracle server side.




   

 
0
 

Author Comment

by:wilma22
ID: 11011938
>>If you have installed Oracle client on the server machine check if you can connect Oracle

Do i need to install oracle machine in the server machine or the client machine.


0
 
LVL 47

Expert Comment

by:schwertner
ID: 11015006
For the thin client - NO.
But it seems you have no access to the Oracle Server machine.
You have to check if you could see it as remote machie - use PINF command as explained above.
0
 

Author Comment

by:wilma22
ID: 11046737
I used the ping command and and the oracle server machine pings fine.


0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 11291646
PAQed, with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now