Solved

connecting to oracle database over internet timing out

Posted on 2003-10-28
29
1,077 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
  • 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 32

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
 
LVL 32

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 32

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 32

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 32

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
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.

 
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 32

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 32

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 32

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 32

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 32

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 47

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 32

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

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

707 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

20 Experts available now in Live!

Get 1:1 Help Now