Configure and Connect to Oracle Autonomous Database

Get the Oracle Autonomous Database configuration files and install them on the machine where CMAN-TDM is installed.

Configure the Oracle Autonomous Database Wallet Files Using the Mutual TLS Protocol

You must use a wallet to connect to an Oracle Autonomous Database (including Oracle Autonomous Data Warehouse, Oracle Autonomous Transaction Processing, and Oracle Autonomous JSON Database). This gives mutual Transport Layer Security (TLS), which provides enhanced security for authentication and encryption.

Log into the Oracle Cloud Infrastructure Console and navigate to the region where the autonomous database resides.
Navigate to the Autonomous Database page, then click the DB Connection tab.
The Autonomous Database Details display.
Click Download Wallet.
You will be prompted for a wallet password.
Provide a password, then download the wallet ZIP file to your local machine.
Transfer the downloaded ZIP file to your Linux machine using your preferred SSH file transfer application or method. It is important to save (or remember) the wallet password. You must use the password later in this solution.

Go to the next step after the wallet ZIP file is in your Linux machine.
Extract the contents to the $TNS_ADMIN directory.

Note:
This location might be the default $ORACLE_HOME/network/admin path of your CMAN-TDM machine installation.
The wallet doesn't contain database user name and password credentials, but you should still keep the wallet and files in a secure location for later use.

The following is an example of the extracted files:
```
-rw-r--r--. 1 oracle oinstall  7085 Sep  3 07:24 cwallet.sso
-rw-r--r--. 1 oracle oinstall  7040 Sep  3 07:24 ewallet.p12
-rw-r--r--. 1 oracle oinstall  3243 Aug 24 14:25 keystore.jks
-rw-r--r--. 1 oracle oinstall   691 Aug 24 14:25 ojdbc.properties
-rw-r--r--. 1 oracle oinstall  3387 Aug 24 14:25 README
-rw-r--r--. 1 oracle oinstall   235 Sep  3 08:00 sqlnet.ora
-rw-r--r--. 1 oracle oinstall  1832 Sep  3 08:41 tnsnames.ora
-rw-r--r--. 1 oracle oinstall  3336 Aug 24 14:25 truststore.jks
```
The main files required for the current use case are cwallet.sso, ewallet.p12, sqlnet.ora and tnsnames.ora. The other files are required only if the user plans to connect via a JDBC application.
Edit the sqlnet.ora file and change the wallet location DIRECTORY to the directory containing the cwallet.sso file:
```
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u01/app/oracle/network/admin")))
SSL_SERVER_DN_MATCH=yes
```
Now that the wallet is set, you can connect to your database using one of the "network service name" connection strings from tnsnames.ora (for example, exampledb_high).
Open the tnsnames.ora file and store the connection details of your autonomous database in another file, such as $HOME/dbdetails.txt:
You'll use the autonomous database's address, security, and service name parameters when you configure CMAN-TDM.
(Optional) Check the database connection using SQL*Plus.
- If SQL*Plus is on the same machine as CMAN-TDM, then you can view the tnsnames.ora file from the wallet directory $TNS_ADMIN.
- If SQL*Plus is installed on a different machine than the CMAN-TDM, then copy the Autonomous Database wallet files to the $TNS_ADMIN directory of the machine where SQL*Plus is installed. Then copy the database details from the previous section to the $TNS_ADMIN/tnsnames.ora file on the same machine.
The $TNS_ADMIN/tnsnames.ora file contains several network service names (to the left of the "=" signs). You can use any, depending on the level of performance and service concurrency you want from the database.

Run SQL*Plus as the ADMIN user and enter that user's password you set during database creation:
```
$ sqlplus -l admin@adb-name_high
```
When successful, you'll be connected to your database.

Configure CMAN to Connect to Oracle Autonomous Database

Create, configure, and add CMAN (cman.ora) configuration files with all of the specifications in the $TNS_ADMIN directory of the CMAN-TDM machine. The directory is often located in ORACLE_HOME/network/admin.

The cman.ora file is the main configuration file which is read by CMAN-TDM. It contains all the configuration details for enabling Traffic Director and CMAN to connect with Oracle Autonomous Database.

Log in as the oracle user.
Navigate to the $TNS_ADMIN directory and create the cman.ora file.
```
$ cd $TNS_ADMIN
$ vi cman.ora
```
Configure the cman.ora file.
In this example, the CMAN-TDM instance name is cman-test. The client application connects to the CMAN process through TCP protocol, and CMAN connects to the Oracle Autonomous Database through TCPS protocol.
The address parameter contains the host name, protocol of the CMAN-TDM server. The following example cman.ora file uses TCP and port number 1523. You can choose any available port number on your Linux machine.

Configure the following parameters:
- tdm=true: Important! You must set the tdm parameter to true to enable Traffic Director Mode.
- tdm_threading_mode=dedicated: This example is running CMAN-TDM in dedicated mode, which is the default mode for Traffic Director Mode. You can run Traffic Director Mode in shared mode.
- log_level: Set to enable the required level of logging for testing and debugging purposes.
- max_connections: set according to your performance requirements.
- idle_timeout=0
- registration_invited_nodes = *
- inbound_connect_timeout=0
- session_timeout=0
- outbound_connect_timeout=0
- max_gateway_processes: Set according to your performance requirements.
- min_gateway_processes: Set according to your performance requirements.
- trace_level: Set to enable the required level of tracing for testing and debugging purposes.
- max_cmctl_sessions: Set according to your performance requirements.
- event_group: init_and_term, memory_ops
- next_hop: The parameter points to the address details of the Oracle Autonomous Database along with the SSL details. These details are taken from the dbdetails.txt file, which you created in the previous section.
- WALLET_LOCATION: The parameter points to the location of the Traffic Director Mode application user wallet, which you'll create later in this section.
- SQLNET.WALLET_OVERRIDE = TRUE
The following is an example cman.ora file:
```
exampledb_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g5cadf7d7e4c6c8_exampledb_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

cman-test = (configuration=
  (address=(protocol=tcp)(host=oracle-cloud-linuxvm)(port=1523))
  (parameter_list =
    (tdm=true)
    (tdm_threading_mode=dedicated)
    (log_level=off)
    (max_connections=50)
    (idle_timeout=0)
    (registration_invited_nodes = *)
    (inbound_connect_timeout=0)
    (session_timeout=0)
    (outbound_connect_timeout=0)
    (max_gateway_processes=8)
    (min_gateway_processes=3)
    (trace_level=support)
    (max_cmctl_sessions=4)
    (event_group=init_and_term,memory_ops)
  )
  (next_hop=(description=(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(security=(ssl_server_dn_match=on)(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US"))))
  (rule_list=
    (rule=
       (src=*)(dst=*)(srv=*)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
  ) )
)

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u01/app/oracle/network/admin")))
SQLNET.WALLET_OVERRIDE = TRUE
```

Create a database proxy user (tdm) to get all of the permissions needed to enable Traffic Director Mode (TDM).

Log into the Oracle Autonomous Database through SQL*Plus as the admin user.

$ sqlplus admin@exampledb_high

SQL*Plus: Release 21.0.0.0.0 - Production on Tue May 6 15:34:43 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Tue May 06 2025 15:33:09 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.1.0

Create a database proxy user.

This example uses tdm as the database proxy user.

SQL> create user tdm identified by {password} -- replace {password} with your actual password

User created.

SQL> grant create session to tdm;

Grant succeeded.

Exit SQL*Plus.

Create the database proxy user wallet.

Add the TDM User Credentials to your existing Autonomous Database wallet.
1. Navigate to the $TNS_ADMIN directory where the Autonomous Database wallet is located.
  The directory is often $ORACLE_HOME/network/admin.
```
$ cd $TNS_ADMIN
```
2. Add the tdm user details to the Autonomous Database wallet using the mkstore utility present in the $ORACLE_HOME/bin directory.
  Note:
  - For the service_name parameter, enter the service name that's in the dbdetails.txt file that you created earlier.
  - For the username and password parameters, enter your tdm user credentials. Repeat this step if you want to have the other services (such as exampledb_low) available through tdm.
  Use the following syntax: $ORACLE_HOME/bin/mkstore -wrl . -createCredential service_name username password. For example:
```
$ $ORACLE_HOME/bin/mkstore -wrl . -createCredential g5cadf7d7e4c6c8_exampledb_high.adb.oraclecloud.com tdm {password}
```
3. Verify that the wallet is created properly by using the orapki utility.
```
$ $ORACLE_HOME/bin/orapki wallet display -wallet .
Oracle PKI Tool Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=AAE9D2E1AE8646298FED591FC4A097EA,DNQ=V1
Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
Trusted Certificates:
Subject:        CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Subject:        C=US,ST=California,L=Redwood Shores,O=Oracle Corporation Autonomous Data Warehouse Cloud Self-signed CA,CN=Autonomous Data Warehouse Cloud CA
Subject:        CN=DigiCert SHA2 Secure Server CA,O=DigiCert Inc,C=US
```
  The output should contain Oracle Secret Store entries, which indicates that the tdm user credentials are stored and are Trusted Certificates and User Certificates (Autonomous Database SSL certificates).
4. Verify that the tdm user credentials are in the wallet by using the mkstore utility. When prompted for the wallet password, enter the Autonomous Database wallet password that you created when you downloaded the wallet zip file from the Oracle Cloud Infrastructure Console.
```
$ $ORACLE_HOME/bin/mkstore -wrl . -listCredential
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: g5cadf7d7e4c6c8_exampledb_high.adb.oraclecloud.com tdm
```
Connect using an CMAN-TDM application user on the Oracle Autonomous Database.
You'll need to create a 'normal' database user for general application access instead of the privileged ADMIN user for connecting to the Oracle Autonomous Database through CMAN-TDM using the database proxy user (tdm) permissions.

Note:
If you already have one or more existing application users in the Autonomous Database, then skip this step.
1. Create a database user.
  
  You can copy and customize the following createuser.sql script to create the user.
```
define USERNAME = &1

-- Uncomment if you want to clean up a previous user
-- begin execute immediate 'drop user &USERNAME cascade'; exception when others then if sqlcode <> -1918 then raise; end if; end;
-- /

create user &USERNAME;

alter user &USERNAME
      default tablespace data temporary tablespace temp
      account unlock quota unlimited on data;

grant connect, resource to &USERNAME;

grant create session, create table, create procedure,
      create sequence, create trigger, create view,
      create synonym, alter  session, create type,
      soda_app to &USERNAME;

password &USERNAME
```
2. Start SQL*Plus as the ADMIN user:
```
$ sqlplus -l admin@exampledb_high
```
3. Run the createuser.sql script.
  
  Pass the name of your user as the script argument. This example uses example.
  
  The script will prompt you to set the password for this new user. The password must meet the Oracle Autonomous Database User Password Guidelines. You won't be able to create a user if you don't meet the guidelines.
```
SQL> @createuser.sql example
```
  The new user is created as soon as you enter the new password.
Add the database proxy user tdm privileges to all the application users requiring access to the Autonomous Database through CMAN-TDM, then exit SQL*Plus.
```
SQL> alter user example grant connect through tdm;
User altered.
SQL> exit
```
Verify that the connection succeeds for the new user (you will be prompted to enter the password):
```
$ sqlplus -l example@exampledb_high

[ . . .]

SQL> show user
USER is "example"

SQL> quit
```

You'll use this user to connect to Oracle Autonomous Database through CMAN-TDM.

Connect to Oracle Autonomous Database Through CMAN

Add the CMAN-TDM address for the server in the $TNS_ADMIN/tnsnames.ora file in the machine running the client application to add the connection.

Open the CMAN-TDM port (1523 in this case) first as the root user on your CMAN-TDM's Linux VM if the VM is behind a firewall.
```
$ sudo su root
$ firewall-cmd --permanent --add-port=1523/tcp
$ firewall-cmd --reload
```
You can view the $TNS_ADMIN/tnsnames.ora file of the machine where the client application is located to confirm the address parameters. You can add the CMAN-TDM Connect Alias in the file.

In this example, the client app SQL*Plus is on the same machine as the CMAN-TDM.

The address parameter includes the CMAN-TDM address. The connect_data parameter must contain the service_name parameter for the Autonomous Database.
```
exampledb_high_cman_tdm = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcp)(port=1523)(host=oracle-cloud-linuxvm))(connect_data=(service_name=g5cadf7d7e4c6c8_exampledb_high.adb.oraclecloud.com)))
```
Run the cmctl utility to start CMAN-TDM.
```
$ $ORACLE_HOME/bin/cmctl startup -c cman-test
```
The utility will connect to and start your CMAN instance. The status of the instance and details are displayed.
Run SQL*Plus using the CMAN-TDM Connect Alias and application user example created earlier to connect to the Autonomous Database.
```
$ sqlplus example@exampledb_high_cman_tdm
```
The SQL query will display the database proxy user TDM used by CMAN-TDM to connect to your Oracle Autonomous Database. This query will return a blank value, when we connect directly to the Oracle Database.

You are now connected through CMAN-TDM to an Oracle Autonomous Database.