Configuring and using Oracle Connection Manager on Amazon EC2 for Amazon RDS for Oracle

This post describes how to configure Oracle Connection Manager on Amazon Elastic Compute Cloud (Amazon EC2) in an Amazon Relational Database Service (Amazon RDS) for Oracle environment, and introduces some best practice use cases when using Oracle Connection Manager on Amazon EC2.

Some customers want to have a database proxy server that forwards database connection traffic to Amazon RDS for Oracle and gives some additional controls over database sessions. Specifically, they want to have more control over specific database connections between on-premises servers and Amazon RDS for Oracle. Many on-premises firewalls allow communication between on-premises servers and cloud databases only through static IP addresses that were registered in the firewall before communication. You can also address the requirement by implementing Oracle Connection Manager. The following diagram shows a conceptual architecture regarding these requirements.

The following diagram shows a conceptual architecture regarding these requirements.

Introduction of Oracle Connection Manager

Oracle Connection Manager is a database proxy server that forwards connection requests to Oracle databases or other proxy servers. If you configure Oracle Connection Manager on Amazon EC2 for Amazon RDS for Oracle, you can utilize useful features such as access control and session timeout. In addition, Oracle Connection Manager on Amazon EC2 can enable some clients to connect to Amazon RDS for Oracle with non-changing static IP addresses while IP addresses of Amazon RDS for Oracle change. Also, Oracle Connection Manager can support a different database service port other than the original service port of an Amazon RDS for Oracle service port in a situation where you need to hide the Oracle Database service port toward certain programs for security reasons.

However, not all features of Oracle Connection Manager are supported in Amazon RDS for Oracle. In the Amazon RDS environment, you aren’t allowed to log in to the operation system of Amazon RDS, so some of Oracle parameters can’t be modified. Some features of Oracle Connection Manager are supported only by modifying the REMOTE_LISTENER initialization parameter or only by modifying Oracle Database Server configuration files such as listener.ora or tnsnames.ora. These features of Oracle Connection Manager that require logging in to the operation system aren’t supported in Amazon RDS for Oracle. In addition, because Oracle Connection Manager is only supported in Oracle Enterprise Edition, the features of Oracle Connection Manager mentioned in this post are only available in Amazon RDS for Oracle Enterprise Edition.

Overview of testing architecture

In this post, I demonstrate how to use Oracle Connection Manager by implementing the common requirements based on a simple architecture of Amazon RDS for Oracle. The following diagram illustrates this architecture.

The following diagram illustrates this architecture.

Amazon RDS for Oracle is configured with Multi-AZ. Amazon RDS has a DNS endpoint with which Oracle Connection Manager installed on Amazon EC2 connects to Amazon RDS for Oracle. Oracle Connection Manager is installed on Amazon EC2 and redirects incoming Oracle Client traffic to Amazon RDS for Oracle. Oracle Instant Client is installed on Amazon EC2 and is used for connection testing on Amazon RDS for Oracle and Oracle Connection Manager.

Prerequisites

If you want to verify the features of Oracle Connection Manager by using the preceding architecture, you must complete the following prerequisites:

Prepare an Amazon Virtual Private Cloud (Amazon VPC) for this demo architecture with following requirement:
- IPv4 CIDR block of the VPC set as 10.0.0.0/16.
Create subnets on the VPC with following requirements:
- A public subnet.
- Two private subnets, where each private subnet resides on each Availability Zone.
Create Amazon RDS for Oracle on the created private subnets with following requirements:
- Database should be selected as Oracle 19c Enterprise Edition because Oracle Connection Manager is only supported on Oracle Enterprise Edition. Also, you should have an Oracle Enterprise Edition license because Amazon RDS for Oracle Enterprise Edition only supports the bring your own license (BYOL) model.
- Amazon EC2 type can be selected as db.t3.small because db.t3.small is the smallest type that Amazon RDS for Oracle supports.
- For the security group of Amazon RDS for Oracle, port 1521 should be open toward 10.0.0.0/16 CIDR for accepting Oracle Client connection.
Prepare Amazon EC2 with Oracle Linux 7 on the public subnet with following requirements. This EC2 instance is used for Oracle Connection Manager EC2.
- You can use community Amazon Machine Images (AMIs) for Oracle Linux 7.
- Use the AWS Free Tier-eligible t2.micro as the instance type.
- Amazon Elastic Block Store (Amazon EBS) size should be 10 GB or more for Oracle Client installation.
- For the security group, port 22 should be open for terminal access from the internet.
Prepare Amazon EC2 with Amazon Linux AMI on the public subnet with following requirements. This is used for Oracle Instant Client EC2.
- Use the AWS Free Tier-eligible t2.micro as the instance type.
- For security group, port 22 should be open for terminal access from the internet.
- The rest of values for Amazon EC2 can be set as default values.
Prepare an Oracle login account, which is required when downloading Oracle installation files and a JDBC jar file.

Cost might be incurred because all resources described here are not under the AWS Free Tier. For more information about cost, see AWS Pricing.

Configuring Oracle Connection Manager on an EC2 instance

In this section, I show you how to configure Oracle Connection Manager on the prepared Oracle Linux EC2 instance. To do that, I explain how to install Connection Manager by using Oracle Client installation procedures. Then I demonstrate how to configure cman.ora. Also, I introduce the required steps to open a firewall and security group.

Installing CMAN

As a first step, you need to install Oracle Connection Manager on Amazon EC2. You use the previously prepared Oracle Linux EC2 for the Oracle Connection Manager. Complete the following steps:

Download the Oracle Client installation binary to your local computer (requires an Oracle login account).

Download the Oracle Client installation binary to your local computer (requires an Oracle login account).

Upload the downloaded binaries to the /tmp directory of Oracle Linux 7 EC2 using the following style scp command:

scp -i path/to/key LINUX.X64_193000_client.zip ec2-user@ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com:/tmp

Log in as the root user to Oracle Connection Manager EC2 and install the related packages and settings for Oracle Client installation:
```
yum install oracle-database-preinstall-19c.x86_64
```
As the root user, make a directory for installation and grant its ownership to the Oracle user:
```
mkdir /u01
chown oracle:oinstall /u01
```
Log in as the oracle user.

Install Oracle Client including the CMAN module (network.cman:19.0.0.0.0) by running following commands sequentially:

# prepare Oracle install directory
export INSTALL_HOME=/u01
mkdir -p /u01/app/oracle/product/client19300

# unzip the install binaries for Oracle Client
mkdir -p $INSTALL_HOME/stage
cd $INSTALL_HOME/stage
cp /tmp/LINUX.X64_193000_client.zip .
unzip LINUX.X64_193000_client.zip

# prepare the client install response file as follows
cat <<EOF > $INSTALL_HOME/stage/clientinstall.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_clientinstall_response_schema_v19.0.0
ORACLE_HOSTNAME=$(hostname)
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/client19300
ORACLE_BASE=/u01/app/oracle
oracle.install.client.installType=Custom
oracle.install.client.customComponents="oracle.sqlplus:19.0.0.0.0","oracle.network.client:19.0.0.0.0","oracle.network.cman:19.0.0.0.0","oracle.network.listener:19.0.0.0.0"
EOF

# install client with silent mode
$INSTALL_HOME/stage/client/runInstaller -silent -responseFile $INSTALL_HOME/stage/clientinstall.rsp ORACLE_HOME_NAME=client19300

Run the finalizing scripts of Oracle Client installation:

# finish the install
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/client19300/root.sh

Log in as oracle and configure .bash_profile as in the following code. It is required to run Oracle utilities such as sqlplus on an Oracle user terminal session.
```
## for Oracle Client
export ORACLE_HOME=/u01/app/oracle/product/client19300
export PATH=$PATH:$ORACLE_HOME/bin
```

Test cmctl by running following command:

$ cmctl

CMCTL for Linux: Version 19.0.0.0.0 - Production on 06-OCT-2020 00:34:01

Copyright (c) 1996, 2019, Oracle.  All rights reserved.

Welcome to CMCTL, type "help" for information.

CMCTL>
CMCTL> exit

If you encounter any errors, you can find some reasons for these errors by looking into the CMAN log files, which are located in the /u01/app/oracle/diag/netcman/ip-xx-xx-xx-xx/cman_1/trace directory.

Configuring cman.ora

You now configure cman.ora by using the previously installed Connection Manager. The easiest way to configure cman.ora is to use the sample cman.ora file that is provided in the sample directory of $ORACLE_HOME/network/admin.

Log in as oracle to Oracle Connection Manager EC2.
Copy the $ORACLE_HOME/network/admin/samples/cman.ora sample file to the directory of $ORACLE_HOME/network/admin/, which is an Oracle network home directory:
```
cp $ORACLE_HOME/network/admin/samples/cman.ora $ORACLE_HOME/network/admin/
```

Identify the IP address of Oracle Connection Manager EC2. You can use the ifconfig OS command as follows:

$ ifconfig
ens5: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 9001
        inet 10.0.0.89  netmask 255.255.255.0  broadcast 10.0.0.255
        inet6 fe80::66:7fff:fe15:4d  prefixlen 64  scopeid 0x20<link>
        ether 02:66:7f:15:00:4d  txqueuelen 1000  (Ethernet)
        RX packets 227122  bytes 1254800996 (1.1 GiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 66076  bytes 5849839 (5.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

Configure the CMAN alias, host IP, and port in the cman.ora file by the identified IP address.
1. Open the cman.ora file in the $ORACLE_HOME/network/admin directory by using the vi command.
2. Configure CMAN alias as cman_1 like the first line of the # CMAN Alias section in the file in the following code.
3. Configure the host IP address as the identified EC2 IP address in the previous step like the following code. The host IP configuration exists in the first line of the # Listening address of the CMAN section of the file.
4. Configure the port as 1521 like the following code. The port configuration also exists in the first line of the # Listening address of the CMAN section of the file.
```
.. skip ..
# CMAN Alias
cman_1 =
(configuration=

  # Listening address of the CMAN
  (address=(protocol=tcp)(host=10.0.0.89)(port=1521))
.. skip ..
```

Test the cman.ora configuration by using following code. You can find the configured information from the output of the cmctl command:

$ cmctl startup -c cman_1

CMCTL for Linux: Version 19.0.0.0.0 - Production on 06-OCT-2020 00:45:48

Copyright (c) 1996, 2019, Oracle.  All rights reserved.

Current instance cman_1 is not yet started
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=10.0.0.89)(port=1521)))
Starting Oracle Connection Manager instance cman_1. Please wait...
CMAN for Linux: Version 19.0.0.0.0 - Production
Status of the Instance
----------------------
Instance name             cman_1
Version                   CMAN for Linux: Version 19.0.0.0.0 - Production
Start date                06-OCT-2020 00:45:48
Uptime                    0 days 0 hr. 0 min. 9 sec
Num of gateways started   2
Average Load level        0
Log Level                 OFF
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/client19300/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/ip-10-0-0-89/cman_1/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/ip-10-0-0-89/cman_1/trace
The command completed successfully.

If you encounter any errors, refer to the CMAN log files, located in the /u01/app/oracle/diag/netcman/ip-xx-xx-xx-xx/cman_1/trace directory. Optionally, when you need to stop this CMAN service, you can run following command by using the CMAN alias name, which was configured in cman.ora:

cmctl shutdown -c cman_1

Opening a firewall for Oracle Linux 7

For CMAN to receive incoming traffic, you need to open the service port by using a firewall command because Oracle Linux 7 doesn’t open user-defined ports by default.

# farewalld package install
yum install firewalld firewall-config

# Make sure the service is started and will auto-start on reboot.
systemctl start firewalld.service
systemctl enable firewalld.service

# open database service port
firewall-cmd --permanent --add-port=1521/tcp
firewall-cmd --reload

Opening a security group

Oracle Connect Manager EC2 should be able to receive traffic from Oracle Clients. Therefore, you also need to open the 1521/tcp port from the security group of Oracle Connection Manager EC2. Complete the following steps:

On the Amazon EC2 console, in the navigation pane, choose Instances.
Choose Oracle Connection Manager EC2.
On the Security tab, choose the security group for Oracle Connection Manager EC2.
Choose Edit inbound rules.
Choose Add rule.
For Type, choose Oracle-RDS.
For Source, enter 10.0.0.0/16.

For Source, enter 0.0.0/16.

Choose Save rules.

Configuring Oracle Instant Client on EC2

To test the features of Oracle Connection Manager, you need to have an Oracle Client environment. The easiest way to configure Oracle Client is to use Oracle Instant Client. In this section, we configure Oracle Instant Client on the prepared Amazon Linux EC2 and set up the tnsnames.ora configuration file to connect to Amazon RDS for Oracle.

Configuring Oracle Instant Client

First, you download Oracle Instant Client files and configure these files on the EC2 instance for the EC2 instance to act as Oracle Client. Complete the following steps:

Download the Oracle Instant Client .zip files (requires an Oracle login account).

You should download the following three .zip files:

•	instantclient-basic-linux.x64-19.3.0.0.0dbru.zip
•	instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip
•	instantclient-tools-linux.x64-19.3.0.0.0dbru.zip

Log in as ec2-user to the prepared Amazon Linux EC2 and make an oracle directory where Oracle Instant Client is configured:
```
mkdir oracle
```

Upload the three .zip files into the oracle directory of Oracle Instant Client EC2. You can upload the downloaded files from your local computer to the EC2 instance by using the following scp commands:

scp -i path/to/key instantclient-basic-linux.x64-19.3.0.0.0dbru.zip ec2-user@ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com:/home/ec2-user/oracle
scp -i path/to/key instantclient-tools-linux.x64-19.3.0.0.0dbru.zip ec2-user@ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com:/home/ec2-user/oracle
scp -i path/to/key instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip ec2-user@ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com:/home/ec2-user/oracle

Go the /home/ec2-user/oracle directory and unzip the three files:

unzip instantclient-basic-linux.x64-19.3.0.0.0dbru.zip
unzip instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip
unzip instantclient-tools-linux.x64-19.3.0.0.0dbru.zip

When the unzip commands are finished, you can see a newly created directory where Oracle Instant Client is installed. You can see this by submitting the ls command on the terminal. The newly created directory became the Oracle Instant Client home directory. In the following code, /home/ec2-user/oracle/instantclient_19_3 is the Oracle Instant Client home directory:

$ ls
instantclient-basic-linux.x64-19.3.0.0.0dbru.zip
instantclient-tools-linux.x64-19.3.0.0.0dbru.zip
instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip
instantclient_19_3

For ec2-user to use Oracle Instant Client, you need to configure .bash_profile in the ec2-user home directory.

Open .bash_profile by using the vi command

Enter the following environment variables at the end of the file. INSTANT_CLIENT_PATH should match the Oracle Instant Client home directory.

# Oracle Instant Client Path
export INSTANT_CLIENT_PATH=/home/ec2-user/oracle/instantclient_19_3
# Oracle Configuration Path
export PATH=$PATH:$INSTANT_CLIENT_PATH
export LD_LIBRARY_PATH=$INSTANT_CLIENT_PATH
export TNS_ADMIN=$INSTANT_CLIENT_PATH

Apply the .bash_profile configuration to the connected SSH session:

. ./.bash_profile

Test if sqlplus is working:

$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 6 01:03:20 2020
Version 19.3.0.0.0

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

Enter user-name:

If you encounter any error from the sqlplus command, the error usually is related to the wrong path setting of environment variables. In that case, you need to check again whether the path of the Oracle Instant Client is correctly applied to environment variables.

Configuring tnsnames.ora

Before testing Oracle Connection Manager, you need to verify that Oracle Instant Client connects to Amazon RDS for Oracle successfully by using the tnsnames.ora file. This section describes how to configure tnsnames.ora on Oracle Instant Client and verify connection to Amazon RDS for Oracle.

You first locate the Amazon RDS for Oracle hose DNS and service name, which you use to configure the tnsnames.ora file.

On the Amazon RDS console, choose the Amazon RDS for Oracle database that you want to connect to.
On the Connectivity & security tab, locate the endpoint URL for the database.

This endpoint is the Amazon RDS for Oracle host DNS.

On the Configuration tab, locate the value under DB name (for this post, ORCL).

This is the service name that you can use in the tnsnames.ora file.

This is the service name that you can use in the tnsnames.ora file.

Go to network administration folder on Oracle Instant Client EC2. Use the following command:

cd $INSTANT_CLIENT_PATH/network/admin

You configure the tnsnames.ora file by using the identified Amazon RDS for Oracle host DNS and service name in previous step.

Open the tnsnames.ora file by using the vi command.

Copy the following code and enter it into the tnsnames.ora file. In the HOST section, enter the identified Amazon RDS for Oracle host DNS. In the SERVICE_NAME section, enter the identified service name.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx.xxxxx.xxxxxxx.rds.amazonaws.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

You can now connect to the Amazon RDS for Oracle database by using the connection entry that you made in the tnsnames.ora file.

Enter the following command by using your user name, password, and DB name:

$ sqlplus admin/*********@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 6 01:24:44 2020
Version 19.3.0.0.0

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

Last Successful login time: Mon Sep 14 2020 09:57:11 +00:00

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

SQL>

If you encounter an error from this command, check whether the 1521/tcp port of Amazon RDS for Oracle is open by using the security group of Amazon RDS for Oracle. Also, verify that the DNS name and service name in the tnsnames.ora file are correctly configured.

Best practice use cases of Connection Manager

So far, I have explained how to configure Oracle Connection Manager. In this section, I explain the best practice use cases of Oracle Connection Manager based on typical customer requirements. These features include database proxy, session control, and support for additional service ports.

Proxy configuration when using Oracle Client

To configure database traffic to pass through a designated server for control or management purposes, you can use a database proxy. In this section, I explain how to configure an Oracle Database proxy, which is the most common use case for Oracle Connection Manager. I use the SQLPLUS utility of Oracle Instant Client as a testing agent.

Log in as ec2-user to Oracle Instant Client.
Go to the network administration directory where tnsnames.ora file exists:

cd $INSTANT_CLIENT_PATH/network/admin

You now configure tnsnames.ora to use Oracle Connection Manager EC2 as database proxy.

Open tnsnames.ora file by using the vi command.

Copy the following text, which is the connection route configuration example:

(SOURCE_ROUTE=YES)
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=<proxy client ip>)(PORT=1521)
    )

Enter the copied text into the tnsnames.ora file, under the DESCRIPTION section. Provide the IP address of Oracle Connection Manager EC2. The final output should look similar to the following code:

ORCL =
  (DESCRIPTION =
    (SOURCE_ROUTE=YES)
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=10.0.0.89)(PORT=1521)
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx.xxxxx.xxxxxxx.rds.amazonaws.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

Test if sqlplus connects to Amazon RDS for Oracle by using Oracle Connection Manager EC2:

$ sqlplus admin/*********@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 6 01:48:37 2020
Version 19.3.0.0.0

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

Last Successful login time: Tue Oct 06 2020 01:24:44 +00:00

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

SQL>

From the output from the command, you need to verify that sqlplus is using Oracle Connection Manager EC2.

Without exiting this sqlplus session, open another SSH terminal and connect to Oracle Connection Manager EC2.

Enter the following code:

[oracle@ip-10-0-0-89 ~]$ netstat -an | grep 1521
tcp        0      0 10.0.0.89:1521          0.0.0.0:*               LISTEN
tcp        0      0 10.0.0.89:59202         10.0.0.89:1521          ESTABLISHED
tcp        0      0 10.0.0.89:1521          10.0.0.89:59202         ESTABLISHED
tcp        0      0 10.0.0.89:1521          10.0.0.100:52726        ESTABLISHED
tcp        0      0 10.0.0.89:60028         10.0.1.24:1521          ESTABLISHED
[oracle@ip-10-0-0-89 ~]$

As you can see from the result, sqlplus Oracle Instant Client [10.0.0.100:52726] is connecting to Oracle Connection Manager EC2 [10.0.0.89:1521]. Also, Oracle Connection Manager EC2 [10.0.0.89:60028] is connecting to the Amazon RDS for Oracle server [10.0.1.24:1521]. The connected sqlplus session is using Oracle Connection Manager EC2 as an Oracle Database proxy client.

Proxy configuration for JDBC thin driver

Many applications run based on Java. If you use a thin JDBC driver when connecting to an Oracle database in Java, you can’t make database traffic passes through Oracle Connection Manager by configuring Oracle Client. In this section, I show you how to use Oracle Connection Manager EC2 as a database proxy client even when you use a JDBC thin driver.

Because the Oracle JDBC thin driver doesn’t depend on the tnsnames.ora setting, you need to find another way to use the database proxy. Fortunately, because the Oracle JDBC thin driver supports a description-based URL path, you can use this feature to utilize Oracle Connection Manager. Instead of the normal JDBC connection string, use the following style JDBC connection string:

jdbc:oracle:thin:@(description=(SOURCE_ROUTE=YES)(ADDRESS=(PROTOCOL=TCP)(HOST=<IP_of_Oracle_Connection_Manager>)(PORT=1521))(address=(HOST=<DNS_of_RDS_Oracle>)(protocol=tcp)(port=1521))(connect_data=(SERVICE_NAME=<Service_name_of_RDS_Oracle>)))

When you use this description as database proxy configuration, you need to modify the following parts by using your connection information:

Your IP address of Oracle Connection Manager EC2
Your DNS of Amazon RDS for Oracle
The service name of Amazon RDS for Oracle

To use Oracle Connection Manager by using a Java program, complete the following steps:

Download the Oracle JDBC driver file (requires an Oracle login account).
Upload the ojdbc8.jar file into the Oracle Instant Client EC2 home directory:

scp -i path/to/key ojdbc8.jar ec2-user@ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com:/home/ec2-user

Log in as ec2-user to Oracle Instant Client EC2.
In the home directory of ec2-user, create the java file by using the vi command.

Enter the following Java script. In the conn section, change the connection information with previous description-based URL path.

import java.sql.*;
import java.io.*;
import java.util.*;

class DbConnect 
{
    Connection  conn    = null ; 
        
    public static void main(String[] args) 
    {
        DbConnect a = new DbConnect();
        a.connect();
    }
    
    public void connect()
    {
        try
        {
            dbConnect();
            
            Statement stmt = conn.createStatement();
            ResultSet rs  = null;
            String sql = "select dbid, name from V$database" ;
            Console cnsl = System.console();

            rs = stmt.executeQuery(sql);
            
            while(rs.next())
            {
                System.out.println( "-- Result : [COL1] => " + rs.getString(1) + ", [COL2] => " + rs.getString(2) );
                String readStr = cnsl.readLine( "Enter string : "); 
            }   
            
            stmt.close();
            rs.close();
            
            dbDisconnect();
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
    }
    
    public void dbConnect()
    {
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@(description=(SOURCE_ROUTE=YES)(ADDRESS=(PROTOCOL=TCP)(HOST=<IP_of_Oracle_Connection_Manager>)(PORT=1521))(address=(HOST=<URL_of_RDS_Oracle>)(protocol=tcp)(port=1521))(connect_data=(SERVICE_NAME=<Service_name_of_RDS_Oracle>)))","admin","**********");
        }
        catch( Exception e )
        {
            System.out.println( "Connect Exception : " + e );
            System.exit(0);
        }
    }

    public void dbDisconnect()
    {
        try
        {
            conn.close();
        }
        catch( Exception e )
        {
            System.out.println( "Disconnect Exception : " + e );
        }
    }
}

Configure CLASSPATH and compile and run the Java program:

export CLASSPATH=.:/home/ec2-user/ojdbc8.jar
javac DbConnect.java
java DbConnect

The Java program returns a record and is waiting for user input from command line. This is because the readLine() function of Java was used for the purpose of not exiting the database connection. See the following code:

$ java DbConnect
-- Result : [COL1] => 1576657990, [COL2] => ORCL
Enter string :

Log in to Oracle Connection Manager EC2 using another SSH terminal to verify that the connection is using Oracle Connection Manager.

Enter the following netstat The output shows that the JDBC program [10.0.0.100] is connecting to Oracle Connection Manager EC2 [10.0.0.89] and Oracle Connection Manager EC2 [10.0.0.89] is connecting to Amazon RDS for Oracle [10.0.1.24].

[oracle@ip-10-0-0-89 ~]$ netstat -an | grep 1521
tcp        0      0 10.0.0.89:1521          0.0.0.0:*               LISTEN
tcp        0      0 10.0.0.89:59202         10.0.0.89:1521          ESTABLISHED
tcp        0      0 10.0.0.89:1521          10.0.0.89:59202         ESTABLISHED
tcp        0      0 10.0.0.89:60426         10.0.1.24:1521          ESTABLISHED
tcp        0      0 10.0.0.89:1521          10.0.0.100:52732        ESTABLISHED
[oracle@ip-10-0-0-89 ~]$

This demonstrates that JDBC traffic can be rerouted by using Oracle Connection Manager EC2.

Access control for source IP address

You can use Oracle Connection Manager for database traffic management or control purposes. Because Oracle Connection Manager can perform IP-based access control, administrators can use Oracle Connection Manager to allow database access only from specific IP addresses. In this section, we enable Oracle Connection Manager to allow traffic from a specific IP address.

Log in as oracle to Oracle Connection Manager EC2.
Go to the Oracle network administration directory:

cd $ORACLE_HOME/network/admin

Open the cman.ora file by using the vi command.
Delete the existing rule_list section and enter the following configuration text. In the text, you should change the first src= part with IP address that should be allowed. The act= part should remain as accept, which means allowing the traffic. In the following code, only 10.0.0.100/32 IP address are allowed to use Oracle Connection Manager:
```
  (rule_list=
    (rule=
       (src=10.0.0.100/32)(dst=*)(srv=*)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
    (rule=(src=ip-10-0-0-89)(dst=127.0.0.1)(srv=cmon)(act=accept))
  )
```
Restart cmctl to make cman.ora reflect the configuration:
```
cmctl shutdown -c cman_1
cmctl startup -c cman_1
```

To check whether it has been applied, log in as ec2-user to Oracle Instant Client EC2, which has 10.0.0.100/32 IP address.

Enter the following sqlplus command:

[ec2-user@ip-10-0-0-100 admin]$ sqlplus admin/********@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 16 01:13:18 2020
Version 19.3.0.0.0

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

Last Successful login time: Fri Oct 16 2020 01:11:49 +00:00

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

SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[ec2-user@ip-10-0-0-100 admin]$

The result confirms that the client with 10.0.0.100/32 is allowed to connect to Amazon RDS for Oracle by using Oracle Connection Manager EC2.

You can also test whether the specific IP address can be rejected with Oracle Connection Manager EC2. Repeat the previous steps but use the following rejecting rule. In the act= section, change allow to reject:

(rule_list=
    (rule=
       (src=10.0.0.100/32)(dst=*)(srv=*)(act=reject)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
    (rule=(src=ip-10-0-0-89)(dst=127.0.0.1)(srv=cmon)(act=accept))
  )

After applying this configuration, you can try to connect to Amazon RDS for Oracle with the following command:

[ec2-user@ip-10-0-0-100 admin]$ sqlplus admin/*******@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 16 01:40:16 2020
Version 19.3.0.0.0

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

ERROR:
ORA-12529: TNS:connect request rejected based on current filtering rules

Enter user-name:

The output shows that the connection was rejected.

Access control for idle timeout

Idle database connections occupy database resources unnecessarily. You can use Oracle Connection Manager to control these idle database connections. Oracle Connection Manager can close the database connection if the session idles for more than a certain period of time. You can use the feature by using the mit value of action_list in the Oracle Connection Manager configuration.

To do so, repeat the previously described steps, but use the following rule_list, which applies mit=10. This means that the session automatically stops when the session idles for more than 10 seconds.

(rule_list=
    (rule=
       (src=10.0.0.100/32)(dst=*)(srv=*)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=10)(conn_stats=on))
    )
    (rule=(src=ip-10-0-0-89)(dst=127.0.0.1)(srv=cmon)(act=accept))
  )

Let’s test how it works after applying the setting. First, connect to Amazon RDS for Oracle with the sqlplus command. If you try to run a simple SQL more than 10 seconds after login, you encounter an Oracle connection error. See the following code:

[ec2-user@ip-10-0-0-100 admin]$ sqlplus admin/******@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 16 01:47:37 2020
Version 19.3.0.0.0

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

Last Successful login time: Fri Oct 16 2020 01:44:06 +00:00

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

SQL>
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 15708
Session ID: 639 Serial number: 2460

As shown in this example, Oracle Connection Manager EC2 can be useful when you want to control idle time of database sessions.

Support for additional service ports for Amazon RDS for Oracle

When using Amazon RDS for Oracle, you only have one service port. However, you may have to provide another port instead of the original service port for a specific application for security reasons, such as hiding the original database service port. You can use Oracle Connection Manager to provide an additional database service port other than the original port.

To do so, you provide an additional 1630/tcp port service through Oracle Connection Manager instead of the 1521/tcp port.

You first change the service port of Oracle Connection Manager EC2.

Log in as oracle user to Oracle Connection Manager EC2.
Go to the network administration directory.
Open cman.ora by using the vi command.

Change the cman service port from 1521 to 1630. You can do this by changing the port= part of the cman.ora file:

# CMAN Alias
cman_1 =
(configuration=

  # Listening address of the CMAN
  (address=(protocol=tcp)(host=10.0.0.89)(port=1630))

  # Configuration parameters of this CMAN
  (parameter_list =

    # Need authentication for connection?
    # Valid values: boolean values for on/off

Restart cmctl with the following code:

cmctl shutdown -c cman_1
cmctl startup -c cman_1

The CMAN is listening to 1630 from now on.

Because Oracle Linux 7 doesn’t open the user-defined port by default, you need to open the 1630/tcp port on Oracle Connection Manager EC2.

Enter the following commands:

firewall-cmd --permanent --add-port=1630/tcp
firewall-cmd --reload

Next, you modify a security group rule of Oracle Connection Manager EC2 to serve the 1630/tcp port.

On the Amazon EC2 console, choose Instances.
Choose Oracle Connection Manager EC2.
On the Security tab of the selected instance, choose the security group of the EC2 instance.
Choose Edit inbound rules.
Add 1630/tcp port entry for 10.0.0.0/16 CIDR.

Add 1630/tcp port entry for 10.0.0.0/16 CIDR.

You now modify the TNS entry in the tnsnames.ora file.

Log in as ec2-user to Oracle Instant Client EC2.
In the network administration directory, open the tnsnams.ora file with the vi command.

Modify the database proxy service port from 1521 to 1630 in the database proxy section, which is two lines below the SOURCE_ROUTE=YES description text:

ORCL =
  (DESCRIPTION =
    (SOURCE_ROUTE=YES)
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=10.0.0.89)(PORT=1630)
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx.xxxxx.xxxxx.rds.amazonaws.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)

Check if you can connect to Oracle Database by using the following command:

[ec2-user@ip-10-0-0-100 admin]$ sqlplus admin/*******@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 16 07:06:36 2020
Version 19.3.0.0.0

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

Last Successful login time: Fri Oct 16 2020 02:12:18 +00:00

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

SQL> select * from dual;

D
-
X

SQL>

You see that connection is established successfully. You can now verify that you’re connecting Oracle Connection Manager by using newly configured 1630 port.

Run the following netstat Oracle Instant Client (10.0.0.100) is connecting Oracle Connection Manager EC2 (10.0.0.89:1630) by using 1630/tcp port. Also, Oracle Connection Manager EC2 (10.0.0.89:22698) is connecting to Oracle RDS (10.0.1.24:1521).

[oracle@ip-10-0-0-89 ~]$ netstat -an | egrep "1630|1521"
tcp        0      0 10.0.0.89:1630          0.0.0.0:*               LISTEN
tcp        0      0 10.0.0.89:22698         10.0.1.24:1521          ESTABLISHED
tcp        0      0 10.0.0.89:1630          10.0.0.100:39126        ESTABLISHED
tcp        0      0 10.0.0.89:1630          10.0.0.89:28762         ESTABLISHED
tcp        0      0 10.0.0.89:28762         10.0.0.89:1630          ESTABLISHED
[oracle@ip-10-0-0-89 ~]$

This example showed that you can give additional database service ports to the client by using Oracle Connection Manager EC2, which is useful when you need to hide the original Oracle database service port.

Conclusion

In this post, I showed how to configure Oracle Connection Manager EC2 when you have to use Amazon RDS for Oracle. In addition, I explained various best practice use cases that can utilize Oracle Connection Manager EC2, such as database proxy, session control, and support for additional service ports. Security groups also support IP-based session control. However, you may have some security applications or staff that have to control database sessions and don’t have modification privileges for security groups. In that situation, you can use Oracle Connection Manager EC2 to conduct database access control.

About the Author

Byeong-eok Kang is a Solutions Architect at AWS. He specializes in databases and has a deep understanding of the finance industry. Outside of work, he likes riding his bike and playing with his cat.

AWS Database Blog