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


wallet_location =


#10 Start the connection manager

$ cmctl startup 
CMCTL for Linux: Version - Production on 16-Dec-2018 13:35:42
Copyright (c) 1996, 2018, Oracle. All rights reserved.
Current instance ... is not yet started
Starting Oracle Connection Manager instance ...
Please wait...
CMAN for Linux: Version - Production
Status of the Instance
Instance name cman...oraclevcn.com
Version CMAN for Linux: Version - 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:

    (ADDRESS =
       (PROTOCOL = TCP)
       (HOST =
       (PORT = 1950))
    (ADDRESS =
      (service_name=[atp service].atp.oraclecloud.com)
        "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 - Production on Sun Dec 16 13:47:30 2019 Version
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 - Production Version

ADW: dbms_cloud.copy_data and record delimiter

To import via dbms_cloud.copy_data a windows file that include window CR+LF at the end of each record, specify the record delimiter as follow:

(table_name      => '<table>'
,credential_name => '<credential_name>'
,file_uri_list   => 'https://<restpoint>/<file>'
,schema_name     => '<schema>'
,format           => json_object('recorddelimiter'      value '''\\r\\n''')


An invalid specification of this delimiter would return the error below:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 801
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 782
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "xxx": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01007: at line 2 column 2
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 757
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 819
ORA-06512: at line 2