DB: Connection manager 18c and 19c setup for Autonomous databases

This note is about how to configure Connection manager 18c or 19c to be used as a front end proxy for both the oracle autonomous database, ATP or ADW.

This may be particularly convenient for older jdbc stack, not supporting tcps, such ojdbc6.

Here are the steps to configure cman on a new VM on OCI:

#1 First provision a VM running a basic shape, running OL7

#2 Login to the connection manager VM and configure the oracle user as detailed in this note

#3 Configure the XDisplay as detailed in this note

#4 Add the following prereq for the database client runinstaller not to fail because of missing prerequisites

yum install compat-libcap1 libstdc++-devel ksh glibc-devel libaio-devel psmisc

#5 Open the port tcp/1950 for the connection manager as detailed in this note

#6 Transfer the 18c or 19c Database client on the VM and unzip it as the oracle user.

#7 Login with the oracle user and run the installer. Ignore the warning about the swap size. Install the connection manager component. Install the ‘Connection manager’ and sqlplus component, to start with.

#8 Create the directory /u01/app/oracle/admin/wallet/atp_wallet and transfer into this location the ATP or ADP wallet files.

This directory should list the following files:

ls -l /u01/app/oracle/admin/wallet/atp_wallet
total 44
-rw-r--r--. 1 oracle oinstall 10613 Dec 16 13:10 cwallet.sso
-rw-------. 1 oracle oinstall     0 Dec 16 13:10 cwallet.sso.lck
-rw-r--r--. 1 oracle oinstall 10568 Dec 16 13:10 ewallet.p12
-rw-------. 1 oracle oinstall     0 Dec 16 13:10 ewallet.p12.lck

#9 Configure the cman.ora file in $ORACLE_HOME/network/admin

Specify the private IP and a port. Other parameters are only relevant for more complex configurations.

Add the wallet_location parameter

cman_[vm].[sub].[vcn].oraclevcn.com=   
  (configuration=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.14)(PORT=1950))
    )
    (RULE_LIST=
      (RULE=(SRC=*)(DST=*)(SRV=*)(ACT=accept))
    )
    (parameter_list=
      (log_level=user)
      (max_connections=256)
      (idle_timeout=0)
      (inbound_connect_timeout=0)
      (session_timeout=0)
      (outbound_connect_timeout=0)
      (max_gateway_processes=8)
      (min_gateway_processes=3)
      (remote_admin=on)
      (trace_directory=/tmp)
      (trace_level=user)
      (trace_timestamp=on)
      (trace_filelen=1000)
      (trace_fileno=1)
      (max_cmctl_sessions=4)
      (event_group=init_and_term,memory_ops)
    )
  )

wallet_location =
  (SOURCE=
    (METHOD=File)
    (METHOD_DATA=
      (DIRECTORY=/u01/app/oracle/admin/wallet/atp_wallet)
    )
  )

#10 Start the connection manager

$ cmctl startup 
CMCTL for Linux: Version 18.0.0.0.0 - Production on 16-Dec-2018 13:35:42
Copyright (c) 1996, 2018, Oracle. All rights reserved.
Current instance ... is not yet started
Connecting to (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.14)(PORT=1950)))
Starting Oracle Connection Manager instance ...
Please wait...
CMAN for Linux: Version 18.0.0.0.0 - Production
Status of the Instance
----------------------
Instance name cman...oraclevcn.com
Version CMAN for Linux: Version 18.0.0.0.0 - Production
Start date 16-Dec-2018 13:35:42
Uptime 0 days 0 hr. 0 min. 9 sec
Num of gateways started 3
Average Load level 0
Log Level USER
Trace Level USER
Instance Config file /u01/app/oracle/product/18.0.0/client_1/network/admin/cman.ora
Instance Log directory /u01/app/oracle/diag/netcman/.../alert
Instance Trace directory /u01/app/oracle/diag/netcman/.../trace
The command completed successfully.
$

#11 Test the connection to the ATP

Revise the TNS aliases to reference first the connection manager host and port:

atp_cman=
  (DESCRIPTION =
    (SOURCE_ROUTE=YES)
    (ADDRESS =
       (PROTOCOL = TCP)
       (HOST = 10.0.0.14)
       (PORT = 1950))
    (ADDRESS =
       (protocol=tcps)
       (port=1522)
       (host=[atp_host].adb.eu-frankfurt-1.oraclecloud.com))
    )
    (CONNECT_DATA =
      (service_name=[atp service].atp.oraclecloud.com)
    )
    (security=(ssl_server_cert_dn=
        "CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))   )
    )
  )

then connect from the ATP database using the new alias:

$ sqlplus user/pwd@atp_cman

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Dec 16 13:47:30 2019 Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Last Successful login time: Fri Dec 16 2018 13:46:49 +00:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0
SQL>



Not that it also may be possible to create a route defined in such a way that the connection to the connection manager are redirected automatically to the Autonomous database. This will be done via the NEXT_HOP primitive in the cman.ora configuration file, as below:

(NEXT_HOP=
(ADDRESS=
(protocol=tcps)
(port=1522)
(host=[atp_host].adb.eu-frankfurt-1.oraclecloud.com)
)
)

 

Then the TNS alias would only reference the connection manager entry point, as defined below:

atp_cman=
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 10.0.0.14)
(PORT = 1950)
)
(CONNECT_DATA =
(service_name=[atp service].atp.oraclecloud.com)
)
(security=(ssl_server_cert_dn=
"CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
)
)