🏗️ OCI 3-Tier Architecture — Web, App & Autonomous DB

Goal: Build a 3-Tier Architecture on OCI with Web, App, and DB tiers.
The Web tier exposes the application via a Load Balancer, the App tier hosts the business logic (Flask app), and the DB tier uses Oracle Autonomous Database for data persistence.

---

✅ Prerequisites

• OCI tenancy with rights to create VCN/Subnets, Compute, Load Balancer, and Autonomous DB
• 3 Subnets (Web / App / DB), each with appropriate Route Tables and Security Lists
• Wallet access for ADB (downloaded and stored securely)
• Linux VMs (Oracle Linux preferred) with sudo access

---

🧭 Architecture Overview

Web Tier → Load Balancer (Public Subnet)
App Tier → Application VM (Private Subnet)
DB Tier → Autonomous DB (Private Subnet)

User → LB (Web) → App VM (App Subnet) → Autonomous DB (DB Subnet)

VCN & Subnets

Component	CIDR	Purpose
Spoke_VCN	10.1.0.0/16	Network base
Web Subnet	10.1.1.0/24	Load Balancer
App Subnet	10.1.2.0/24	Application VM
DB Subnet	10.1.3.0/24	Autonomous DB

---

1️⃣ Provision Autonomous Database (DB Tier)

Follow the ADB creation wizard:

1. ☰ → Oracle Database → Autonomous Database → Create Autonomous Database
2. Choose ATP/ADW, workload size (Always Free / Dev)
3. Configure network (Private endpoint recommended for production)
4. Set admin password and create DB

Once provisioned:

• Click DB Connection → Download Wallet
• Securely copy & extract to App VM at:

/var/www/html/Wallet_testdb

• Keep credentials private (chmod appropriately).

---

2️⃣ Prepare App VM

SSH to your App VM in the App Subnet and install:

sudo yum install -y python3-pip
pip3 install --upgrade pip
pip3 install oracledb flask

Ensure wallet files (tnsnames.ora, sqlnet.ora, etc.) are in /var/www/html/Wallet_testdb.

---

3️⃣ Create Table in ADB

On the App VM, create db_connection.py:

import oracledb

wallet_location = "/var/www/html/Wallet_testdb"
dsn = "testdb_high"

connection = oracledb.connect(
    user="admin",
    password="<DB_PASSWORD>",
    dsn=dsn,
    config_dir=wallet_location,
    wallet_location=wallet_location,
    wallet_password="<WALLET_PASSWORD>"
)

cursor = connection.cursor()
cursor.execute("""
CREATE TABLE my_table (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(100),
    age NUMBER,
    PRIMARY KEY (id)
)
""")
print("Table created successfully.")
cursor.close()
connection.close()

Run it:

python3 /var/www/html/db_connection.py

Expected: ✅ “Table created successfully.”

---

4️⃣ Deploy Flask App (Application Tier)

Create app.py in /var/www/html (same as 2-Tier lab):

Implements / route for insert form

/view route to display rows

Connects to ADB using wallet

Run:

python3 /var/www/html/app.py

App listens on port 5000.

---

5️⃣ Create Load Balancer (Web Tier)

☰ → Networking → Load Balancers → Create Load Balancer

Choose Public type

Place in Web Subnet

Backend Set → Add App VM (private IP, port 5000)

Create Listener → HTTP : 80 → Forward to backend set

All 3 Subnets have dedicated Security List rules

---

6️⃣ Security Lists & NSGs

From → To	Protocol	Port	Description
LB → App VM	TCP	5000	Allow backend traffic
App VM → ADB	TCP	1522	Allow SQL*Net traffic
User → LB (Web Tier)	TCP	80	Access from browser (restricted)

✅ Ensure ingress/egress rules are set on the Web, App, and DB Security Lists.

Login to Bastion server and then SSH to the Application Server. Run the App.py script in /var/www/html

---

7️⃣ Validation

1. Open a browser and navigate to:

http://<LB_PUBLIC_IP>

2. Insert a few rows in the form.

3. Click View Data → Data should be fetched from ADB.

✅ Summary

We created a 3‑Tier application where the App VM inserts and reads data from Oracle Autonomous DB securely over OCI networking.