60_4 ‐ Complete 3_Tier Architecture_OCI - SanjeevOCI/Ocidocs GitHub Wiki

🚀 3-Tier Architecture

We need to create a 3-Tier application with three subnets (Web, App & DB).

The application in the App Instance (App Subnet) is accessible through a Load Balancer in the Web Subnet.
The App is able to insert and read data from Oracle Autonomous DB (DB Subnet) securely over OCI networking.

🔹 Planned Setup

VCN

Spoke_VCN: 10.1.0.0/16

Subnets

Spoke_Web_Subnet (10.1.1.0/24) → Internet Gateway attached
Spoke_App_Subnet (10.1.2.0/24)
Spoke_DB_Subnet (10.1.3.0/24)

Each subnet will have its own custom Security List and Route Table.

🔹 Components

Autonomous Database → In DB Subnet
Bastion Server → In Web Subnet (from custom image, wallet configured in /var/www/html)
App Server → In App Subnet (from the same custom image)
Load Balancer → In Web Subnet

1) Provision Autonomous Database (DB Tier)

Follow the ADB creation wizard:

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

Provisioning flow (illustrated):

Autonomous Database Provisioning_1

Autonomous Database Provisioning_2

Autonomous Database Provisioning_3

Autonomous Database Provisioning_4

Autonomous Database Provisioning_5

Autonomous Database Provisioning_6

Autonomous Database Provisioning_7

Autonomous Database Provisioning_8

Autonomous Database Provisioning_9

Then:

From ADB details page, click DB Connection → Download Wallet.
Securely copy the wallet zip to the App VM (e.g., /var/www/html/Wallet_testdb).

Security tip: Keep wallet and credentials in a protected directory with least privileges; avoid committing to Git.

Autonomous Database Provisioning_10

Autonomous Database Provisioning_11

Autonomous Database Provisioning_12

2) Prepare App VM (Python, Oracle DB driver, Flask)

# As root or with sudo
sudo yum install -y python3-pip  # or dnf on OL9
pip3 install --upgrade pip
pip3 install oracledb flask

Place the wallet directory at: /var/www/html/Wallet_testdb (Ensure the wallet zip is extracted so that tnsnames.ora etc. are present)

3) One‑time DB Table Creation (db_connection.py)

Create /var/www/html/db_connection.py:

import oracledb

# Wallet and TNS
wallet_location = "/var/www/html/Wallet_testdb"
dsn = "testdb_high"  # From tnsnames.ora

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

cursor = connection.cursor()

create_table_sql = """
CREATE TABLE my_table (
    id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(100),
    age  NUMBER,
    PRIMARY KEY (id)
)
"""

cursor.execute(create_table_sql)
print("Table created successfully.")

cursor.close()
connection.close()

Run it:

cd /var/www/html
python db_connection.py
# Output: Table created successfully.

Proof:

Table created successfully

4) Build the Web UI (index.html)

Create /var/www/html/index.html:

<!DOCTYPE html>
<html>
<head>
  <title>Database Entry</title>
  <style>
    body { background-color:#0074D9; color:#fff; font-family:Arial, sans-serif; }
    .container { margin:100px auto; width:300px; padding:20px; background:rgba(0,0,0,0.2); border-radius:8px; }
    input,button { width:100%; margin:8px 0; padding:8px; border-radius:4px; border:none; }
    button { background:#001f3f; color:#fff; cursor:pointer; }
    button:hover { background:#005fa3; }
  </style>
</head>
<body>
  <div class="container">
    <h2>Enter Data</h2>
    <form action="/insert" method="post">
      <input type="text" name="name" placeholder="Name" required>
      <input type="number" name="age" placeholder="Age" required>
      <button type="submit">Submit</button>
    </form>
    <form action="/view" method="get">
      <button type="submit">View Data</button>
    </form>
  </div>
</body>
</html>

5) Flask App (app.py)

Create /var/www/html/app.py:

from flask import Flask, render_template_string, request, redirect, url_for
import oracledb

app = Flask(__name__)

# Oracle DB connection details
wallet_location = "/var/www/html/Wallet_testdb"
dsn = "testdb_high"
db_user = "admin"
db_password = "Opccompute@2025"
wallet_password = "Opccompute@2025"

form_html = """
<!DOCTYPE html>
<html>
<head>
    <title>Insert Data</title>
</head>
<body>
    <h2>Insert Name & Age</h2>
    <form method="POST" action="/">
        Name: <input type="text" name="name" required><br>
        Age: <input type="number" name="age" required><br>
        <input type="submit" value="Submit">
    </form>
    <form method="GET" action="/view">
        <button type="submit">View Data</button>
    </form>
    {% if message %}
        <p style="color:green;">{{ message }}</p>
    {% endif %}
</body>
</html>
"""

view_html = """
<!DOCTYPE html>
<html>
<head>
    <title>View Data</title>
</head>
<body>
    <h2>Data in my_table</h2>
    <table border="1">
        <tr><th>ID</th><th>Name</th><th>Age</th></tr>
        {% for row in rows %}
        <tr>
            <td>{{ row[0] }}</td>
            <td>{{ row[1] }}</td>
            <td>{{ row[2] }}</td>
        </tr>
        {% endfor %}
    </table>
    <a href="/">Back to Form</a>
</body>
</html>
"""

def get_connection():
    return oracledb.connect(
        user=db_user,
        password=db_password,
        dsn=dsn,
        config_dir=wallet_location,
        wallet_location=wallet_location,
        wallet_password=wallet_password
    )

@app.route("/", methods=["GET", "POST"])
def insert_data():
    message = ""
    if request.method == "POST":
        name = request.form["name"]
        age = request.form["age"]
        try:
            conn = get_connection()
            cursor = conn.cursor()
            cursor.execute("INSERT INTO my_table (name, age) VALUES (:1, :2)", (name, age))
            conn.commit()
            message = "Data submitted successfully."
        except Exception as e:
            message = f"Error: {e}"
        finally:
            cursor.close()
            conn.close()
    return render_template_string(form_html, message=message)

@app.route("/view")
def view_data():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, age FROM my_table")
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template_string(view_html, rows=rows)

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5000)

Install (if not already):

pip3 install flask oracledb

Optional (lab convenience only):
sudo systemctl stop firewalld
sudo systemctl disable firewalld
Prefer NSGs over disabling OS firewalls in real deployments.

Run the app:

python3 /var/www/html/app.py

6) Network & Security Rules (Critical)

A) Allow App → DB (SQL*Net)

Create an ingress rule on the DB NSG/SecList to allow TCP 1522 from App Subnet CIDR (10.1.1.0/24). This enables the VM to reach ADB.

IMPORTANT rule to connect to autonomous DB

B) Allow Browser → App

If your app VM is public and you want to access the Flask UI from your laptop, allow TCP 5000 ingress to the App VM’s NSG/SecList (restrict to your office IP in real setups).

7) Validate End‑to‑End

Open the app in a browser:

http://<APP_PUBLIC_IP>:5000/

If your form loads with 127.0.0.1, change it to your VM public IP.

open web form

open web form_1_1

open web form_2_1

Insert a few rows, then click View Data to verify:

open web form_submit_data_1_1

View_data_1_1

View_data_2

2) Provision Load Balancer

3_Tier_Load_Balancer1_9
3_Tier_Load_Balancer2_3
3_Tier_Load_Balancer2_4
3_Tier_Load_Balancer2_5
3_Tier_Load_Balancer2_6
3_Tier_Load_Balancer2_7
3_Tier_Load_Balancer2_8
3_Tier_Load_Balancer2_9
3_Tier_Load_Balancer2_10
3_Tier_Load_Balancer2_11
3_Tier_Load_Balancer2_12
3_Tier_Load_Balancer_Up