Accessing an Oracle 10g instance through an SSH tunnel using Oracle 10g Connection Manager on Windows

Home -> Community -> Usenet -> c.d.o.server -> Accessing an Oracle 10g instance through an SSH tunnel using Oracle 10g Connection Manager on Windows

From: <pjbondi_at_SystemDatabase.com>
Date: 1 Sep 2005 11:24:08 -0700
Message-ID: <1125599048.431943.287300@f14g2000cwb.googlegroups.com>

Oracle 10g Connection Manager operates both a listener service (TNSLSNR.EXE) and Connection Manager Administration service (CMADMIN.EXE). The Connection Manager listener needs to be registered as a remote listener with your instance.

Oracle 10g Connection Manager requires an Enterprise Edition license, except when used under the appropriate terms specified by Oracle. Read your license agreement.

Parameters:

your_host_name      = Oracle host computer
your_host_name.ext  = Oracle host computer external host name
os_user             = operating system user on host your_host_name
                      with ssh access
client_computer     = the client computer from which you'll setup
                      the SSH tunnel to the Oracle host
your_instance_name  = Oracle instance name on the Oracle host
                      computer
your_service_name   = Oracle service name on the Oracle host
                      computer.  This is normally the same
                      as the instance name
listener_port       = 1521
cman_port           = 1610
ssh_tunnel_port     = 49201
Cygwin install
directory           = C:\cygwin
trace sub-dir       = C:\oracle\admin\listener

Install Oracle. Setup the first Oracle 10g Oracle Home, as appropriate.

Since we use Standard Edition, we need an Oracle Home dedicated for the Standard Edition database software.

As of this writing, Oracle 10g Release 1 patchset 2 is the latest available version for Windows.

2. If necessary, setup a second Oracle Home with only

Connection Manager. Connection Manager is an Enterprise Edition only component. Apply the patchset.

3. On Windows XP Service Pack 2 or Windows 2003 Service Pack 1

with the Windows Firewall, you'll need to punch holes through the firewall. Modify your paths as appropriate.

netsh firewall add allowedprogram ^
program="C:\cygwin\usr\sbin\sshd.exe" ^ name="Cygwin SSH daemon"

netsh firewall add allowedprogram ^
program="C:\cygwin\bin\cygrunsrv.exe" ^ name="Cygwin Service daemon"

netsh firewall add allowedprogram ^
program=C:\oracle\product\10.1.0\Db_1\BIN\TNSLSNR.EXE ^ name="Oracle 10g Listener"

netsh firewall add allowedprogram ^
program=C:\oracle\product\10.1.0\Db_1\BIN\oracle.exe ^ name="Oracle 10g Database Server"

netsh firewall add allowedprogram ^
program=C:\oracle\product\10.1.0\Db_1\BIN\emagent.exe ^ name="Oracle 10g Database Control"

netsh firewall add allowedprogram ^
program=C:\oracle\product\10.1.0\Db_1\jdk\bin\java.exe ^ name="Oracle 10g Java Virtual Machine"

NOTE: Oracle Connection Manager executables do not require

Windows Firewall exceptions (TAR 4630379.993)

4. Setup an Cygwin SSH server on the Oracle host computer

Install Cygwin from www.cygwin.com/setup.exe

The latest version of Cygwin that I downloaded, had specific support for Windows 2003 Server

Setup your SSH server using

ssh-host-config

Setup your os_user using

ssh-user-config

5. Setup your CMAN.ORA file as follows. In my testing, the

connection manager instance name was case sensitive and had to be upper case (don't worry, I don't understand either):

CMAN =
(CONFIGURATION=

        (ADDRESS=(PROTOCOL=TCP)(HOST=your_host_name)(PORT=1610))
        (RULE_LIST=


            (RULE=(SRC=*)(DST=your_host_name)(SRV=*)(ACT=accept))

            (rule=(src=*)(dst=127.0.0.1)(srv=cmon)(act=accept))

        )
        (PARAMETER_LIST=


            (MAX_GATEWAY_PROCESSES=8)

            (MIN_GATEWAY_PROCESSES=3)

            (IDLE_TIMEOUT=0)

            (MAX_CONNECTIONS=100)

            (REMOTE_ADMIN=NO)

            (LOG_DIRECTORY=C:\oracle\admin\listener)

            (TRACE_DIRECTORY=C:\oracle\admin\listener)

            (SESSION_TIMEOUT=0)

            (LOG_LEVEL=user)

        )

)

I have a dedicated trace and log sub-directory, which I like to specify manually, C:\oracle\admin\listener.

6. Connect to the console of the host computer. Do not use

Terminal Services connections. From a command prompt:

    cmctl
    admin CMAN
    startup

Do not exit from this prompt. The instructions require more commands to be executed later.

7. Run services.msc. Observe the Windows services

Ensure your default 10g Listener, your CMAdmin Connection Manager and your TNSListenerCMAN are all set to Automatic startup

I don't know if or when the default connection manager services created at install time might be used. Under Oracle TAR 4612674.993, I was instructed to use the services created by cmctl after the startup command is issued.

8. Setup an appropriate local net service name in your

TNSNAMES.ORA file on your client computer

SSHCMAN =

      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS =


            (PROTOCOL = TCP)

            (HOST = 127.0.0.1)

            (PORT = 49201))

        )
        (CONNECT_DATA =
          (SERVICE_NAME = your_service_name)
        )
      )

9. Register the Connection Manager listener

as a remote listener for your instance. Connect to your Oracle 10g instance as sysdba:

    ALTER SYSTEM SET
REMOTE_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=your_host_name)(PORT=1610))" SCOPE=BOTH;     Return to your cmctl prompt
    Type "show services"
    Observe that your instance has registered with the     Connection Manager listener

Type "show connections"
Observe that your connection count is 0

Do not exit from this prompt. The instructions require more commands to be executed later.

From a separate command line, connect to the Oracle host computer from your client computer

ssh os_user_at_your_host_name.ext -L 49201:your_host_name:1610

Please note that your_host_name must appear consistently throughout the ssh -L parameter, CMAN.ORA, TNSNAMES.ORA, and the ALTER REMOTE_LISTENER command

From a separate command line, connect using sqlplus from your client computer through the local net service name SSHCMAN

sqlplus "system/password_at_SSHCMAN"

Return to your cmctl prompt Type "show connections" Observe that your connection count has increased

Received on Thu Sep 01 2005 - 13:24:08 CDT