connecting to oracle database over internet timing out

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

also, there is no firewall setup

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

but i think CONNECT_TIMEOUT should be changed on the Server machine though

Yes you have to ask the DBA of the database you are trying to hit if the parameter is set

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.

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)

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 :)

You should find it in the oracle home directory under network\admin

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

and then restart the listener process, try it again.

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?

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

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

lsnrctl stop
lsnrctl start

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

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.

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!

can you post your listener.ora and tnsnames.ora file from New Zealand office?

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

sorry, I meant for sqlnet.ora file in the client machine in NZ office.

can you post the content of the sqlnet.ora file in the NZ office?

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)

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

i did that and got "unable to resolve service name"

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.

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.

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

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.