Cman Proxy

Clients → CMAN (EC2, private subnet) → RDS Oracle

# As root
yum install -y oracle-database-preinstall-19c.x86_64
mkdir -p /u01 && chown oracle:oinstall /u01

# As oracle user
export INSTALL_HOME=/u01
mkdir -p /u01/app/oracle/product/client19300 $INSTALL_HOME/stage
cd $INSTALL_HOME/stage
unzip LINUX.X64_193000_client.zip

cat > $INSTALL_HOME/stage/clientinstall.rsp <<EOF
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_HOME/stage/client/runInstaller -silent \
  -responseFile $INSTALL_HOME/stage/clientinstall.rsp \
  ORACLE_HOME_NAME=client19300

# As root
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/client19300/root.sh

export ORACLE_HOME=/u01/app/oracle/product/client19300
export PATH=$PATH:$ORACLE_HOME/bin

CMAN =
  (CONFIGURATION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (RULE_LIST =
      # Wildcard (SRC=*)(DST=*)(SRV=*) is for INITIAL TESTING ONLY and MUST be replaced before production use.
      # Restrict to your client CIDR(s) and service name, and reject everything else:
      (RULE = (SRC = 10.0.0.0/16)(DST = *)(SRV = ORCL)(ACT = ACCEPT))
      (RULE = (SRC = *)(DST = *)(SRV = *)(ACT = REJECT))
    )
    (PARAMETER_LIST =
      MAX_CONNECTIONS = 256
      MAX_GATEWAY_PROCESSES = 8
      MIN_GATEWAY_PROCESSES = 2
      LOG_LEVEL = USER
      SESSION_TIMEOUT = 0
      INBOUND_CONNECT_TIMEOUT = 60
      OUTBOUND_CONNECT_TIMEOUT = 60
    )
  )

(RULE_LIST =
  (RULE = (SRC = 10.0.0.0/16)(DST = *)(SRV = ORCL)(ACT = ACCEPT))
  (RULE = (SRC = 172.16.0.0/12)(DST = *)(SRV = ORCL)(ACT = ACCEPT))
  (RULE = (SRC = *)(DST = *)(SRV = *)(ACT = REJECT))
)

# Close sessions idle > 300 seconds
(RULE =
  (SRC = 10.0.0.0/16)(DST = *)(SRV = *)(ACT = ACCEPT)
  (ACTION_LIST = (MIT = 300))
)

export ORACLE_HOME=/u01/app/oracle/product/client19300
export PATH=$ORACLE_HOME/bin:$PATH

cmctl startup -c CMAN
cmctl show status -c CMAN
cmctl show connections -c CMAN

[Unit]
Description=Oracle Connection Manager
After=network.target

[Service]
Type=forking
User=oracle
Environment=ORACLE_HOME=/u01/app/oracle/product/client19300
ExecStart=/u01/app/oracle/product/client19300/bin/cmctl startup -c CMAN
ExecStop=/u01/app/oracle/product/client19300/bin/cmctl shutdown -c CMAN
Restart=on-failure

[Install]
WantedBy=multi-user.target

sudo systemctl daemon-reload
sudo systemctl enable oracle-cman
sudo systemctl start oracle-cman

Clients → NLB (TCP 1521) → CMAN-AZ1, CMAN-AZ2 → RDS Oracle

import oracledb
dsn = "cman-ec2-private-ip:1521/ORCL"
conn = oracledb.connect(user="dbadmin", password="<from-secrets-manager>", dsn=dsn)

String url = "jdbc:oracle:thin:@cman-ec2-private-ip:1521/ORCL";

ORCL_VIA_CMAN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cman-ec2-private-ip)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL)(SERVER = CMAN)))

REMOTE_LISTENER = <cman-ec2-private-ip>:1521

String url = "jdbc:oracle:thin:@(DESCRIPTION="
           + "(SOURCE_ROUTE=YES)"
           + "(ADDRESS=(PROTOCOL=TCP)(HOST=<cman-ec2-ip>)(PORT=1521))"
           + "(ADDRESS=(PROTOCOL=TCP)(HOST=<rds-endpoint>)(PORT=1521))"
           + "(CONNECT_DATA=(SERVICE_NAME=ORCL)))";

resource "aws_instance" "cman" {
  ami           = data.aws_ami.oracle_linux.id
  instance_type = "t3.medium"
  subnet_id     = var.private_subnet_id
  vpc_security_group_ids = [aws_security_group.cman.id]
  tags = { Name = "oracle-cman" }
}

resource "aws_security_group" "cman" {
  name_prefix = "cman-"
  vpc_id      = var.vpc_id

  ingress {
    from_port       = 1521
    to_port         = 1521
    protocol        = "tcp"
    security_groups = [aws_security_group.app.id]
    cidr_blocks     = var.onprem_cidrs
  }

  egress {
    from_port       = 1521
    to_port         = 1521
    protocol        = "tcp"
    security_groups = [aws_security_group.rds_oracle.id]
  }
}