59_1 ‐ 2‐Tier Architecture ‐ create a application on app layer and update the database - SanjeevOCI/Ocidocs GitHub Wiki

2‑Tier Architecture: Build an App Tier that Writes to Oracle Autonomous Database (ADB)

Goal: Provision an App VM (Tier‑1) and an Oracle Autonomous Database (Tier‑2) on OCI, secure the path, and validate end‑to‑end inserts/reads via a simple Flask app.

✅ Prerequisites

  • OCI tenancy with permissions to create VCN/Subnets, Compute, Autonomous Database.
  • Two subnets (recommended): App Subnet (VM) and DB Subnet (ADB private endpoint or public with restricted access).
  • NSGs/Security Lists to allow required ports (see Security section).
  • Linux VM (Oracle Linux/OL8/OL9 or similar) with sudo.

Naming used in lab

  • Wallet directory on VM: /var/www/html/Wallet_testdb
  • ADB TNS alias: testdb_high
  • Flask app runs on port 5000
  • Replace placeholders like <DB_PASSWORD>, <APP_PUBLIC_IP>, <APP_SUBNET_CIDR> with your values.

🧭 Architecture (2‑Tier)

App Tier → public/private VM (Flask app) DB Tier → Oracle Autonomous Database (ATP/ADW)

2_Tier_Architecture_1

2_Tier_Architecture_2

Example VM used:

Instance_used

1) Provision Autonomous Database (ADB)

Follow the standard ADB provisioning wizard. Suggested: Always Free/Dev‑friendly size, workload ATP/ADW per your need.

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:

  1. From ADB details page, click DB Connection → Download Wallet.
  2. 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

🔐 Hardening Tips (Production)

  • Use ADB private endpoint + Service Gateway; keep DB tier private.
  • Store secrets in OCI Vault; avoid hard‑coding passwords.
  • Prefer NSGs with least‑privilege, restrict port 5000 to trusted IPs.
  • Run Flask behind Nginx/Apache with TLS; consider OCI Load Balancer.
  • Enable ADB access control list and database users/roles as needed.

✅ Summary

You created a 2‑Tier application where the App VM inserts and reads data from Oracle Autonomous DB securely over OCI networking. You validated connectivity (port 1522), built a minimal Flask UI, and confirmed DB operations end‑to‑end.

⚠️ **GitHub.com Fallback** ⚠️