Oracle 11g hangs on first connection

structuredweb
structuredweb used Ask the Experts™
on
Dell PowerEdge 2950
Oracle RHEL 5
Oracle DB 11g

After installing the DB, when testing we noticed that the first connection to the DB takes a long time, and whats worst, while it waits no other connection can go in!. Trying this on 2 windows locally,
window A: tnsping to the DB, immediate (0 ms)
window B: sqlplus to the same database, taking 10-20 seconds.
Window A: try the tnsping again, stuck until the sqlplus from window B connects.

The sqlnet definition for the listener is very simple, TNSNAMES only, everything is local, not even NFS volumes.

This is happening on 2 identical machines with same software and DB.

I have replaced window B with a remote client trying a JDBC connection test, and I get the same results. If I keep trying the same test connection then apparently is reuses the same connection because there is no delay. The moment I force a new connection then I get stuck again.

Anyone has seen such behavior?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
What state is the database in? mount or open?

SQL> select open_mode from v$database;

How did you start it?

How are you connecting (as a dba or a normal user?)

Have you checked the alert log?
Top Expert 2008

Commented:
Wrong network parameters (domain server, default gateway, port) in the network parameters of Linux.
DHCP address of the Server

Author

Commented:
thanks for the 2 comments so far:

 - the Database is opened normally. (startup open)
 - connecting as normal users

as to network parameters, i will check again but we are not using DHCP. the IP is static, the gateway is fine. anyway, if the listener is using TNSNAMES only whty would it matter as this point? remember i am testing this from 2 local wondows, already looged in to the machine, and it's the same behavior as of remote connections.
SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES)

HINT: another phenomena which might be related or not. on the 2 machines, when i open a new SSH window, and enter the password, the program is stuck for 5-10 seconds until i  get the prompt. if i switch to another user in the same window (su -) it's instantaneous. even if i SSH from one server to the other in the same window there's no delay


Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2013
Top Expert 2013
Commented:
Did you check your Domain Name Server (DNS)?
Your problem looks quite as if there were an unresponsive DNS at the first position of resolv.conf, and taht the delay was due to waiting for timeout.
wmp
Top Expert 2008

Commented:
This are bad kernel parametters or firewall or VPN thing.
Oracle only initiates the connection via 1521. Aftr that it redirectes the connection to other port.


Enter hostname: stctestbox01.us.oracle.com
Under "Miscellaneous Settings"
Gateway: 192.168.203.2
Primary DNS: 144.20.190.70
Secondary DNS: 138.2.202.15


Is the RHEL firewall disabled by the installation?

As root, open a terminal window and perform these steps:
Edit /etc/hosts. The file should contain text similar to the following:
127.0.0.1      localhost.localdomain    localhost
192.168.203.11 stctestbox01.us.oracle.com stctestbox01


Edit /etc/sysctl.conf.
Add the following to the file:
# Kernel paramaters required by Oracle 11gR1
fs.file-max = 6553600
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144


Linux installation are complex things!

Author

Commented:
wmp, you are a genius! yes, resolv.conf first entry was an NAT address that is not reachable from the trust zone.
BTW, should i/can i change listener.ora so Oracle is not dependent on DNS? use something like "localhost"?

Author

Commented:
schwertner,

as you can see the problem was a bad /etc/resolv.conf
/etc/sysctl.conf is configured correctly.

i am curious, what is the purpose of your excersize? making sure defining the local machine as a well-known host, gateway and DNS would work with Oracle?

Commented:
enable listener and sqlnet tracing to see if you have anytime out and the cause for that;
it looks like a network problem ;
Oracle always can adapt to bad config and it will till what do you have ;
you may also use  an app like wireshark to analyze your traffic.
Most Valuable Expert 2013
Top Expert 2013

Commented:
Yes, localhost should work in tnsnames.ora (if it's really the local host!) And you should have "hosts: files dns" in /etc/nsswitch.conf.
 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial