OCI: Connection manager 18c setup for ATP/ADW

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

 

#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 12c 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 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

 

#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=adb.eu-frankfurt-1.oraclecloud.com)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (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>

OCI: Setting up the firewall on iaas VMs

Examples of command to manage the firewall on OCI VMs.

OL6

sudo iptables -I INPUT -p tcp -m tcp --dport 3876 -j ACCEPT

sudo service iptables save

sudo service iptables restart

 

OL7

https://docs.oracle.com/cd/E52668_01/E54669/html/ol7-ctrlfwsvc-sec.html

sudo firewall-cmd --zone=public --add-port=8000/tcp --permanent

sudo firewall-cmd --zone=public --add-port=8443/tcp --permanent

sudo firewall-cmd --add-port=5555/tcp

sudo firewall-cmd --add-port=1521/tcp --permanent

sudo firewall-cmd --add-port=1950/tcp --permanent 
sudo systemctl restart firewalld.service

 

 

EMCC: Redirection http->https

To have the unsecured login page redirected to the secured https page, proceed as follow:

#1 On each OMS server, navigate into the htdocs directory, for example:

cd /u01/app/oracle/gc/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/instances/ohs*/htdocs

 

#2 Create there the redirection page welcome-index.html (12c) or index.html (13c) with the following content:

<html>
<head>
<meta HTTP-EQUIV="REFRESH" content="1; url=https://xxxxx.xxx.com/em">
</head>
<body>
<center>
<img src="/images/company.png"/>
<p>
<p><font size="14"><font color="##FF0000"><font face="Calibri"><i>Redirecting</i></font></font></font></p>
</center>
</body>
</html>

Optionally include an image as above.