connecting to oracle database over internet timing out

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
LVL 34
Big MontySenior Web Developer / CEO of ExchangeTree.org Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

seazodiacCommented:
can you use "TNSPING" to get an estimate of packet round-trip?

for example:

tnsping <oracle_database_tnsname>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bigfam5Commented:

What, if any is the CONNECT_TIMEOUT set to?

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
also, there is no firewall setup
0
seazodiacCommented:
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
seazodiacCommented:
but i think CONNECT_TIMEOUT should be changed on the Server machine though
0
Bigfam5Commented:
Yes you have to ask the DBA of the database you are trying to hit if the parameter is set

0
seazodiacCommented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
Bigfam5Commented:
You should find it in the oracle home directory under network\admin
0
seazodiacCommented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
seazodiacCommented:
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
seazodiacCommented:
after modifying your listener.ora file as I posted above,
restart the listener process by

lsnrctl stop
lsnrctl start
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
seazodiacCommented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
seazodiacCommented:
can you post your listener.ora and tnsnames.ora file from New Zealand office?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
seazodiacCommented:
sorry, I meant for sqlnet.ora file in the client machine in NZ office.
0
seazodiacCommented:
can you post the content of the sqlnet.ora file in the NZ office?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
seazodiacCommented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
i did that and got "unable to resolve service name"
0
seazodiacCommented:
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
schwertnerCommented:
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
Vinay_dbaCommented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.