Re: Sqlplus not work with VPN, but tnsping work

aaa wrote:
> I tried to access our ORACLE server through VPN.
> The problem I have is
> "sqlplus" not work, but tnsping work.
> Network engineer told me he did open port
> 1521 for me.
>
> if I use ORACLE "tnsping" on PC side, it will response back.
> Server side have
> message "15-DEC-2004 20:36:38 * ping * 0" on listener.ora file.
>
> If I use "sqlplus" on PC, there is message pop-up:
> ORA-12535: TNS: Operation time out
>
> there is NO message on ORACLE server side listener.ora file.
>
> Anyone know why?
>
> Thanks.

In general, the more information that you supply in your posting regarding Oracle version (4 digits is sufficient (e.g. 7.3.4.5, 8.0.6.3, 8.1.7.4, 9.2.0.6, 10.1.0.3) and Server Operating System (e.g. RHEL 3.0 ES Update 3) the more specific an answer could be. If you are running 8i Release 3, the term would be MTS, whereas if you are running 9i Release 2, the term would be shared servers.

Jonathan Gennick posted an article regarding this topic some time ago:

http://gennick.com/lock_the_door.html

which leveraged connection manager (CMAN). Depending upon the edition of the Oracle Database Server software, this may or may not be a licensed option for you - check with your sales rep and v$option.

In general, opening a well known port to the internet is to be avoided. In your case, opening a specific port inside of a VPN tunnel to satisfy a business need sounds acceptable provided that access is logged and the logs verified. If you can get away with allowing access to an application server and not allow direct access to the Oracle server, so much the better.

In 9i Release 2 one can specify a fixed port for a dispatcher in the init.ora/spfile.ora which will accept incoming shared server connections.
Depending upon the number of sessions and their usage patterns, more than one dispatcher may be required.

It has been my experience that if a dedicated server connection is not explicitly requested, that all requests will be shared server connections, which will be different than the previous behavior. If you want to support a mixed environment of dedicated and shared server sessions, you might have to alter the already deployed Oracle client configuration files (sqlnet.ora, tnsnames.ora) or Oracle Names server entries, or LDAP server (OID).

You do not want your power users, batch jobs running shared server sessions.

A perusal of the documentation might also be a good idea: http://download-west.oracle.com/docs/cd/B14117_01/network.101/b10775.pdf - chapter 12

but that would require knowing what version of the software you are working with.

Others have posted solutions of tunneling dedicated server sessions via ssh, but running oracle traffic thru ssh thru a VPN might be overkill. Having users logon to the Oracle server via ssh and run sqlplus from there may be another option for you, depending upon the security policy in place.

-bdbafh Received on Thu Dec 16 2004 - 07:55:20 CST