TNSPING works, SQLPLUS times out

Implemented a new Oracle client server which must pass through a firewall to connect to the Oracle9i database.  We had the listener port 1521 opened in the firewall between the systems and that appears to be working.  We can successfully tnsping from the client system to the database system, but any attempt to use SQLPLUS results in a TNS timeout.  I've double-checked the tnsnames.ora and it uses only port 1521.  Any ideas?
TechGuyIIAsked:
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.

Oracle-DBACommented:
I am speculating that you are running on Windows architecture.  Oracle networking on Windows utilizes "port redirection".  In a nutshell, here is how it works:
1)  Client sends connection request to listener to the IP address and port specified in tnsnames.ora (port 1521 in your case).
2)  If the firewall has port 1521 opened, then the connection request will continue.
3)  The listener will accept the request on port 1521, ask the database to start a thread (a new server thread) for your connection.
4)  The listener will return to the client a NEW port in which to use to communicate with your server thread.  This new port is returned in the form of a "port redirection".
5)  The client will then attempt to communicate with the database thread on the new port.

So...  Two ports are involved, one the well known port (1521) and the second a randomly selected port.  Your timeout is due to the fact that the firewall is rejecting the communication request on the 2nd port.

Most major firewall manufacturers have a set of rules or a firewall object specially made to handle Oracle port redirections.  In our case, we use Checkpoint firewalls, so we make sure to request the "SQLNET2" firewall object is also included in addition to opening port 1521.  I don't know the specific name for other types of firewalls, but all the majors will handle Oracle networking on Windows.

Lastly, why does tnsping work and sqlplus timeout?  That is because tnsping does not perform a port redirection.  It simply goes into the specified port (1521 in your case) and back through 1521.  No 2nd port is needed.  A connection to the database, whether through SQLPLUS or any other tool WILL cause the port redirection to occur and will therefore time out.

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
TechGuyIIAuthor Commented:
Yes, it is Windows.  I'll give it a try and let you know.  We can likely test it later today.
docramCommented:
How can I tackle this when I am using EC2 Windows machine on Amazon Cloud? I have opened port 1521 in the security group.

tnsping works but sqlplus is timing out.
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.