Running Oracle Database on Amazon RDS removes patching, backup management, and storage provisioning from your team while keeping the Oracle engine your applications depend on. The setup decisions that matter most in production are edition and license choice, parameter group configuration, Multi-AZ topology for your failover requirements, and a networking model that keeps database traffic off the public internet.

Edition and License

RDS Oracle is available in Standard Edition 2 and Enterprise Edition, each with License Included or BYOL. SE2 supports up to 16 vCPUs and covers most production workloads. EE is required for Partitioning, Advanced Compression, and database-level TDE. Check which features your application uses before choosing SE2, because changing editions later requires a new instance and a data migration.

Step 1: Security Group and Subnet Group

resource "aws_security_group" "oracle_rds" {
  name   = "oracle-rds-production"
  vpc_id = var.vpc_id

  ingress {
    from_port   = 1521
    to_port     = 1521
    protocol    = "tcp"
    cidr_blocks = [var.app_subnet_cidr, var.dba_subnet_cidr]
    description = "Oracle listener from app and DBA subnets"
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = { Name = "oracle-rds-production", Environment = "production" }
}

resource "aws_db_subnet_group" "oracle" {
  name       = "oracle-rds-subnet-group"
  subnet_ids = var.private_subnet_ids
}

Step 2: Custom Parameter Group

resource "aws_db_parameter_group" "oracle_prod" {
  name   = "oracle-se2-19c-production"
  family = "oracle-se2-19"

  # 75% of instance RAM for SGA
  parameter { name = "sga_max_size"   value = "{DBInstanceClassMemory*3/4}" apply_method = "pending-reboot" }
  # 25% of instance RAM for PGA
  parameter { name = "pga_aggregate_target" value = "{DBInstanceClassMemory/4}" apply_method = "pending-reboot" }
  parameter { name = "processes"      value = "500"  apply_method = "pending-reboot" }
  parameter { name = "undo_retention" value = "900"  apply_method = "immediate" }
  parameter { name = "audit_trail"    value = "DB"   apply_method = "pending-reboot" }
  # Enable supplemental logging for GoldenGate replication from day one
  parameter { name = "enable_goldengate_replication" value = "TRUE" apply_method = "pending-reboot" }
  parameter { name = "nls_date_format" value = "YYYY-MM-DD HH24:MI:SS" apply_method = "immediate" }
}

Step 3: RDS Oracle with Multi-AZ

resource "aws_db_instance" "oracle_prod" {
  identifier     = "oracle-production"
  engine         = "oracle-se2"
  engine_version = "19.0.0.0.ru-2026-01.rur-2026-01.r1"
  license_model  = "license-included"
  instance_class = "db.r6i.2xlarge"

  allocated_storage     = 500
  max_allocated_storage = 2000
  storage_type          = "gp3"
  storage_encrypted     = true
  kms_key_id            = var.kms_key_arn

  db_name  = "ORCL"
  username = "admin"
  password = var.db_admin_password
  port     = 1521

  multi_az            = true
  publicly_accessible = false
  auto_minor_version_upgrade = false

  vpc_security_group_ids = [aws_security_group.oracle_rds.id]
  db_subnet_group_name   = aws_db_subnet_group.oracle.name
  parameter_group_name   = aws_db_parameter_group.oracle_prod.name

  backup_retention_period   = 14
  backup_window             = "03:00-04:00"
  maintenance_window        = "sun:04:30-sun:05:30"
  skip_final_snapshot       = false
  final_snapshot_identifier = "oracle-production-final"

  performance_insights_enabled          = true
  performance_insights_retention_period = 7
  monitoring_interval                   = 60
  monitoring_role_arn                   = aws_iam_role.rds_monitoring.arn

  tags = { Name = "oracle-production", Environment = "production", ManagedBy = "terraform" }
}

output "oracle_endpoint" { value = aws_db_instance.oracle_prod.endpoint }

Multi-AZ on RDS Oracle uses Oracle Data Guard in synchronous mode. The standby is in a different Availability Zone. During failover, RDS promotes the standby and updates the endpoint DNS. Applications reconnect to the same DNS name and reach the new primary. Failover completes in 60 to 120 seconds.

Step 4: FAN-Aware Connection Pool

import cx_Oracle, boto3, json, os
from typing import Optional

def get_creds() -> dict:
    c = boto3.client('secretsmanager', region_name=os.environ['AWS_REGION'])
    return json.loads(c.get_secret_value(SecretId=os.environ['DB_SECRET_ARN'])['SecretString'])

class OraclePool:
    _pool: Optional[cx_Oracle.SessionPool] = None

    @classmethod
    def get(cls) -> cx_Oracle.SessionPool:
        if cls._pool is None:
            creds = get_creds()
            dsn   = cx_Oracle.makedsn(os.environ['ORACLE_HOST'], 1521, service_name='ORCL')
            cls._pool = cx_Oracle.SessionPool(
                user=creds['username'], password=creds['password'], dsn=dsn,
                min=2, max=10, increment=1, encoding='UTF-8',
                events=True,       # FAN: evict stale connections immediately on failover
                ping_interval=30   # validate connections before returning from pool
            )
        return cls._pool

    @classmethod
    def query(cls, sql: str, params: dict = None) -> list:
        with cls.get().acquire() as conn:
            cur  = conn.cursor()
            cur.execute(sql, params or {})
            cols = [c[0].lower() for c in cur.description]
            return [dict(zip(cols, row)) for row in cur.fetchall()]

# Example query
orders = OraclePool.query(
    "SELECT order_id, total_amount FROM orders WHERE customer_id = :cid",
    params={'cid': 'CUST-001'}
)

The events=True flag enables Oracle Fast Application Notification. When Multi-AZ failover occurs, FAN notifies the pool immediately so it evicts stale connections rather than waiting for TCP timeout. This is the difference between a 60-second application disruption and a 5-minute one.

Step 5: CloudWatch Alarms

locals { db_id = aws_db_instance.oracle_prod.identifier }

resource "aws_cloudwatch_metric_alarm" "cpu" {
  alarm_name          = "oracle-prod-cpu"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 3
  metric_name         = "CPUUtilization"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 80
  alarm_actions       = [var.sns_ops_arn]
  dimensions          = { DBInstanceIdentifier = local.db_id }
}

resource "aws_cloudwatch_metric_alarm" "memory" {
  alarm_name          = "oracle-prod-memory"
  comparison_operator = "LessThanThreshold"
  evaluation_periods  = 2
  metric_name         = "FreeableMemory"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 2147483648
  alarm_actions       = [var.sns_ops_arn]
  dimensions          = { DBInstanceIdentifier = local.db_id }
}

resource "aws_cloudwatch_metric_alarm" "latency" {
  alarm_name          = "oracle-prod-read-latency"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 3
  metric_name         = "ReadLatency"
  namespace           = "AWS/RDS"
  period              = 60
  statistic           = "Average"
  threshold           = 0.020
  alarm_actions       = [var.sns_ops_arn]
  dimensions          = { DBInstanceIdentifier = local.db_id }
}

Step 6: Top SQL via Performance Insights API

import boto3
from datetime import datetime, timezone, timedelta

def get_top_sql(db_id: str, hours: int = 1, top_n: int = 10) -> list:
    pi   = boto3.client('pi', region_name=os.environ['AWS_REGION'])
    now  = datetime.now(timezone.utc)

    resp = pi.get_resource_metrics(
        ServiceType='RDS',
        Identifier=f'db:{db_id}',
        MetricQueries=[{
            'Metric': 'db.load.avg',
            'GroupBy': {
                'Group': 'db.sql',
                'Dimensions': ['db.sql.statement'],
                'Limit': top_n
            }
        }],
        StartTime=now - timedelta(hours=hours),
        EndTime=now,
        PeriodInSeconds=hours * 3600
    )

    rows = []
    for m in resp.get('MetricList', []):
        for k in m.get('Keys', []):
            sql  = k.get('Dimensions', {}).get('db.sql.statement', '')[:120]
            load = m.get('DataPoints', [{}])[-1].get('Value', 0)
            rows.append({'aas': round(load, 3), 'sql': sql})

    return sorted(rows, key=lambda x: x['aas'], reverse=True)

for r in get_top_sql('oracle-production', hours=24):
    print(f"{r['aas']:.3f} AAS | {r['sql']}")

Operational Notes

Character set is permanent. Choose AL32UTF8 before the first data load. Changing it afterwards requires an export, instance recreation, and re-import.

Always use the RDS endpoint DNS name, never a cached IP. The endpoint DNS is updated during failover. Applications caching the IP will not reconnect to the promoted standby and will stay down until the TCP connection times out.

Storage autoscaling via max_allocated_storage grows the volume automatically when free space drops below 10 percent. It never shrinks. Monitor storage growth trends and plan capacity increases before autoscaling triggers, because storage operations run in the background and can affect I/O performance briefly.

Regards,
Osama

#AWS #OracleDatabase #RDS #OracleOnAWS #Terraform #MultiAZ #DatabaseEngineering #TechBlog #Oracle #AmazonWebServices #CloudArchitecture #DevOps #CloudWatch #PerformanceInsights #FAN #ConnectionPooling #DataGuard #OracleDBA #BYOL #CloudDatabase