Solved

connecting to oracle database over internet timing out

Posted on 2003-10-28
29
1,082 Views
Last Modified: 2008-02-01
Heya Expersts,

I'm trying to connect to a database located on a computer on the other side of the world. I have access to the computer (obviously it has a valid ip address) and I can ping the computer successfully. After running net8Config, I time out trying to connect to the database. Any ideas? I'm using Oracle 8.1.7 and terminal services when trying to do the connect.

Josh
0
Comment
Question by:Big Monty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
  • 3
  • +2
29 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 250 total points
ID: 9635155
can you use "TNSPING" to get an estimate of packet round-trip?

for example:

tnsping <oracle_database_tnsname>
0
 
LVL 7

Assisted Solution

by:Bigfam5
Bigfam5 earned 250 total points
ID: 9635161

What, if any is the CONNECT_TIMEOUT set to?

0
 
LVL 33

Author Comment

by:Big Monty
ID: 9635200
seazodiac:
i get a return of 550ms on the database

bigfam5:
where would i set this to? my knowledge of oracle administration is somewhat limited
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Author Comment

by:Big Monty
ID: 9635212
also, there is no firewall setup
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9635238
CONNECT_TIMEOUT is in the listener.ora
try to set CONNECT_TIMEOUT = 0  

this will let client wait infinitely....but I don't think this is the problem though
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9635250
but i think CONNECT_TIMEOUT should be changed on the Server machine though
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 9635265
Yes you have to ask the DBA of the database you are trying to hit if the parameter is set

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9635279
but just out of curiosity, are you accessing your database inside the terminal service? why?

If you have oracle client configured on your machine, why don't you try SQLPLUS on your shell window?
you can connect directly using SQLPLUS client.
0
 
LVL 33

Author Comment

by:Big Monty
ID: 9635281
on the client machine there is no listener.ora, and it looks to be running oracle 9

on the server machine, where would i make that setting? (i am the dba in this case)
0
 
LVL 33

Author Comment

by:Big Monty
ID: 9635306
the way its setup is that my company has an office in new zealand, and i am located in our boston offices...i TS to our nz server, then try to connect to the database on our boston server using sql plus. hope thats clear enough :)
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 9635586
You should find it in the oracle home directory under network\admin
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9635671
In your listener file on Boston Server,
try to add this line:
CONNECT_TIMEOUT = 0  

and then restart the listener process, try it again.
0
 
LVL 33

Author Comment

by:Big Monty
ID: 9636475
didnt work, same error...heres what my listener.ora file looks like:

# LISTENER.ORA Network Configuration File: C:\oracle\ora81\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = WTCQA)(PORT = 1521)
      (CONNECT_TIMEOUT = 0)
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\ora81)
      (PROGRAM = extproc)
    )
  )

did i set it up correctly?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9636636
given the above listener file, you did not have listener configured correctly.
by the way, get rid of connect_timeout line.

you should have a section pointing to your database , for example you have a database named "test",
you should have:


LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = WTCQA)(PORT = 1521)
     )
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
     )
   )
 )

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
     (SID_NAME = TEST)
     (ORACLE_HOME = C:\oracle\ora81)
    )

   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = C:\oracle\ora81)
     (PROGRAM = extproc)
   )
 )
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9636646
after modifying your listener.ora file as I posted above,
restart the listener process by

lsnrctl stop
lsnrctl start
0
 
LVL 33

Author Comment

by:Big Monty
ID: 9637032
still cant connect...times out...heres my listener file:

# LISTENER.ORA Network Configuration File: C:\oracle\ora81\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 66.107.74.197)(PORT = 1521)
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
  )

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
     (SID_NAME = workqa)
     (ORACLE_HOME = C:\oracle\ora81)
    )

  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\ora81)
      (PROGRAM = extproc)
    )
  )
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9637184
You know what will be a good test?

since you are in Boston office with the database server, you should first try to connect to this database from one of your local client machine.
you just need to have oracle client installed on the client machine and then try to connect this database.
see if you can connect to this.

I know that you are trying to solve this quickly, but these are steps you have to go through to diagnose.
0
 
LVL 33

Author Comment

by:Big Monty
ID: 9637211
i've done that already and have verified that i can connect through my network here. Sorry I should have mentioned that first. I'm really stumped on this one!
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9637283
can you post your listener.ora and tnsnames.ora file from New Zealand office?
0
 
LVL 33

Author Comment

by:Big Monty
ID: 9637318
tnsnames.ora from NZ:
heres the entry for the database:

WORKQA.WELLINGTON.WTNZ.WORKTECHNOLOGY.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 66.107.74.197)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = workqa)
    )
  )

there is no listener.ora file as no databases are not installed on that machine
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9637386
sorry, I meant for sqlnet.ora file in the client machine in NZ office.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9637392
can you post the content of the sqlnet.ora file in the NZ office?
0
 
LVL 33

Author Comment

by:Big Monty
ID: 9637523
here you go:

# SQLNET.ORA Network Configuration File: C:\Progra~1\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = Wellington.WTNZ.worktechnology.com

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9637556
Ok, can you try to change your tnsnames.ora to this:

WORKQA=
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 66.107.74.197)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = workqa)
   )
 )
0
 
LVL 33

Author Comment

by:Big Monty
ID: 9637584
i did that and got "unable to resolve service name"
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9637738
ok, go back to your original tnsnames.ora settings.

Now, in the Boston Server, turn on the listener tracing.
you can enable listener logging and tracing in net 8 assistant--->profile --->general

hopefully, this will give you why the connection failed.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 9639980
First at all delete
SQLNET.AUTHENTICATION_SERVICES= (NTS)
or set it to
SQLNET.AUTHENTICATION_SERVICES= (NONE)

If this not help use the utility tnsping to see if the Listener on the server is alive and you van reach it.

If this is OK then try to figure out the parameters of the server like host address, port, dbname etc and use Net 8 Assistant or Easy Config to set the right service name in tnsnames.ora on the client. The test connection (of course with right user/password) will show if you can connect or not.

0
 
LVL 4

Expert Comment

by:Vinay_dba
ID: 9641445
Try setting tracing on NZ machine.
If you are unsure how to obtain a trace, place the following entries in the SQLNet.ORA file. Make sure before you start your application that the C:\Trace directory exist.
TRACE_FILE_CLIENT = net817
TRACE_DIRECTORY_CLIENT = C:\trace
TRACE_UNIQUE_CLIENT = ON
TRACE_LEVEL_CLIENT = 16
TRACE_TIMESTAMP_CLIENT = ON

When finished obtaining both traces, set the TRACE_LEVEL_CLIENT = 0 from 16 to turn off tracing.

This will give you some additional clues.
Check listner.log on server in boston for any entries from NZ server.
If this is not working - try  setting SDU parameter in tnsnames.ora  in NZ machine.

Use the parameter SDU to instruct Oracle Net to optimize the transfer rate of data packets being sent across the network with the session data unit (SDU) size you specify.

Embed this parameter under the DESCRIPTION parameter.

Default
2 KB

Usage
The SDU size can range from 512 bytes to 32 KB. The default SDU for the client and the database is 2 KB.

Optimal SDU size depends on the maximum segment size (MSS) and message fragmentation. For Two-Task Common (TTC) connections, configuring an SDU size larger than the 2 KB default requires configuring the SDU on both the client and server computers. When the configured values do not match, the lower of the two values will be used.

To minimize packet header overhead and message fragmentation, set the SDU size as a multiple of the MSS. When Oracle Advanced Security encryption is not used, increase the SDU size by one (1). For example, the TCP/IP version 4 MSS on Ethernet is 1460 bytes. Use a multiple of 1460 for the SDU size if encryption is used. If encryption is not used, increase the SDU size to 1461.

See Also:
Oracle9i Net Services Administrator's Guide for complete configuration information
0
 
LVL 33

Author Comment

by:Big Monty
ID: 10776753
doing a little cleanup here...

i was taken off this project and it was eventually canned due to the fact that we got a new firewall in place and other various reasons.

I split the points between you two for your time and effort.

Cheers
B.D.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

738 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