How to Deploy Oracle on AWS

Why How to Deploy Oracle on AWS is important

-- Find top 5 customers by spend last quarter on RDS Oracle
WITH last_qtr AS (
  SELECT ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) AS start_qtr,
         TRUNC(SYSDATE, 'Q') - 1            AS end_qtr
  FROM   dual
)
SELECT c.id,
       c.name,
       SUM(o.total_amount) AS spend
FROM   Orders   o
JOIN   Customers c ON c.id = o.customer_id,
       last_qtr  lq
WHERE  o.order_date BETWEEN lq.start_qtr AND lq.end_qtr
GROUP  BY c.id, c.name
ORDER  BY spend DESC
FETCH  FIRST 5 ROWS ONLY;

AWS CLI to create a multi-AZ Oracle 19c RDS instance:

aws rds create-db-instance \
  --db-instance-identifier prod-oracle \
  --db-instance-class db.m6i.large \
  --engine oracle-ee \
  --engine-version 19.0.0.0.ru-2023-10.rur-2023-10.r1 \
  --allocated-storage 100 \
  --storage-type io1 \
  --iops 3000 \
  --master-username admin \
  --master-user-password "StrongP@ssw0rd" \
  --license-model bring-your-own-license \
  --multi-az \
  --backup-retention-period 7 \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --db-subnet-group-name prod-private-subnets \
  --enable-storage-encryption

SQL*Plus connection string from an EC2 app server:

sqlplus customers_app/Secr3t@prod-oracle.cdjf3xyz.us-east-1.rds.amazonaws.com:1521/ORCL

Sample ecommerce query:

SELECT c.name,
       o.order_date,
       p.name   AS product,
       oi.quantity,
       oi.quantity * p.price AS line_total
FROM   Customers   c
JOIN   Orders      o  ON o.customer_id = c.id
JOIN   OrderItems  oi ON oi.order_id   = o.id
JOIN   Products    p  ON p.id          = oi.product_id
WHERE  o.order_date >= TRUNC(SYSDATE) - 30;