Understanding VCN Flow Logs in Oracle Cloud Infrastructure (OCI)

 

Overview

VCN Flow Logs in Oracle Cloud Infrastructure (OCI) provide deep visibility into network traffic within your Virtual Cloud Network (VCN).
They capture details about all traffic that passes through your Virtual Network Interface Cards (VNICs) — including both accepted and rejected connections.

For DBAs and Cloud Admins, flow logs are an essential tool for troubleshooting connectivity, verifying security rules, and analyzing performance or security anomalies between OCI compute instances, databases, and external services.

What Are VCN Flow Logs?

A VCN Flow Log records network traffic flow metadata between source and destination endpoints within your OCI environment.

It helps you answer questions like:

• Why is my database or application server not reachable?

• Which ports or protocols are being blocked by security rules?

• Is there any unusual outbound traffic from my subnet?

Each record represents a flow and includes:

• Source and destination IPs

• Ports and protocol

• Packets sent and received

• Action (ACCEPT or REJECT)

• Timestamps

• Traffic direction (Ingress/Egress)

Where Are Flow Logs Stored?

Flow logs are exported to OCI Logging service, where you can view, filter, and analyze them.
You can also configure Log Groups to automatically stream these logs to:

• OCI Object Storage

• OCI Logging Analytics

• External SIEM solutions (like Splunk or Elastic)

• OCI Service Connector Hub

This makes it easy to retain, search, and visualize flow data for audit or compliance purposes.

How to Enable VCN Flow Logs

You can enable flow logs either per subnet or per VNIC.

✅ Steps to Enable Flow Logs via OCI Console

1. Login to OCI Console

   • Navigate to Networking → Virtual Cloud Networks.

2. Select your target VCN.

3. Click on the Subnet or VNIC for which you want to enable flow logs.

4. Under the Resources section, select Flow Logs.

5. Click Enable Flow Logs.

6. Choose the Log Group and Log Name (create new if required).

7. Click Create Flow Log Configuration.

After a few minutes, logs will start appearing in the chosen Log Group.

Understanding Flow Log Record Fields

Each log entry contains several fields that describe the flow. Example log snippet:

{
  "sourceAddress": "10.0.0.5",
  "destinationAddress": "10.0.1.10",
  "sourcePort": 1521,
  "destinationPort": 34567,
  "protocol": "6",
  "action": "ACCEPT",
  "direction": "INGRESS",
  "startTime": "2025-11-11T10:10:20Z",
  "endTime": "2025-11-11T10:10:30Z",
  "packets": 20,
  "bytes": 15000
}

🔹 Action – Shows whether the packet was accepted or rejected based on network security rules.
🔹 Direction – Indicates if it’s inbound (INGRESS) or outbound (EGRESS) traffic.
🔹 Protocol – Uses the IANA protocol number (e.g., 6 = TCP, 17 = UDP).
🔹 Source/Destination Ports – Helps confirm if database/application ports are reachable.

Practical Use Cases for DBAs and Cloud Engineers

1. Database Connectivity Troubleshooting
   Check if TCP port 1521 (Oracle Listener) or 5432 (PostgreSQL) is reachable between app and DB subnets.

2. Network Security Validation
   Confirm that security lists or NSGs are not blocking legitimate database connections.

3. Audit and Compliance
   Maintain traffic logs to meet data protection or security audit requirements.

4. Performance Diagnostics
   Identify latency or packet drops due to rejected or delayed flows.

Pro Tip: Query Flow Logs in Logging Analytics

Use OCI Logging Analytics for advanced searching:

'VCNFlowLogs'
| where action='REJECT'
| summarize count() by destinationPort

This helps pinpoint which ports are most frequently blocked — useful for tuning your network security rules.

Important Notes

• Flow logs record metadata, not packet payloads — so they are secure and lightweight.

• Flow logs do not capture traffic to/from OCI-managed services (like Object Storage endpoints).

• It can take up to 10 minutes for new flow logs to start appearing after enabling.

Best Practices

✅ Enable flow logs for critical subnets (DB, App, and Bastion).
✅ Use short retention (e.g., 30 days) to save cost if not required for audit.
✅ Automate log archival to Object Storage for long-term retention.
✅ Regularly review “REJECT” entries to identify misconfigured security rules.

Oracle to PostgreSQL Data Migration Using HS Database Link (Step-by-Step)

 

📘 Introduction

In some enterprise environments, there’s a need to query or migrate data directly between Oracle and PostgreSQL without using ETL tools or third-party connectors.

Oracle’s Heterogeneous Services (HS) feature allows exactly that — it lets Oracle access non-Oracle databases such as PostgreSQL, MySQL, SQL Server, etc., using Oracle Database Gateway for ODBC (DG4ODBC).

In this article, we’ll walk through how to connect Oracle Database to PostgreSQL using HS, create a database link, and migrate data from Oracle to PostgreSQL seamlessly.

🧩 1. What Is Oracle Heterogeneous Services (HS)?

Heterogeneous Services is an Oracle feature that allows communication between Oracle and non-Oracle data sources through ODBC drivers.
It acts as a bridge layer — Oracle SQL queries are translated into the syntax of the remote system (PostgreSQL, in this case).

⚙️ 2. Prerequisites

Before starting, make sure you have:

Requirement	Description
Oracle Database	Version 12c or above
PostgreSQL Database	Version 12 or above
ODBC Driver	PostgreSQL ODBC driver installed on Oracle server
Oracle Client utilities	tnsnames.ora, listener.ora, sqlnet.ora configured
OS Access	Root or sudo privileges to edit Oracle networking files

🏗️ 3. Install PostgreSQL ODBC Driver on Oracle Server

For Linux:

sudo yum install -y unixODBC unixODBC-devel
sudo yum install -y postgresql-odbc

Verify installation:

odbcinst -q -d

You should see:

[PostgreSQL]

🧾 4. Configure ODBC DSN

Edit the ODBC configuration file (/etc/odbc.ini) and add a DSN for PostgreSQL:

[PG_LINK]
Driver=/usr/lib64/psqlodbcw.so
Description=PostgreSQL ODBC Connection
Servername=10.11.225.90
Port=5432
Database=ipp_data
Username=postgres
Password=Postgres@123
ReadOnly=no
Protocol=7.4

Test connectivity:

isql -v PG_LINK postgres Postgres@123

If successful, you’ll see:

Connected!

🔧 5. Create Oracle HS Initialization File

Create file:

$ORACLE_HOME/hs/admin/initPG_LINK.ora

Add:

HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.UTF8

🌐 6. Update Oracle Network Files

📝 listener.ora

Add an entry for HS service:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PG_LINK)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (PROGRAM = dg4odbc)
      (ENVS = "LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/19.0.0/dbhome_1/lib")
    )
  )

Restart listener:

lsnrctl stop
lsnrctl start

📝 tnsnames.ora

Add TNS entry:

PG_LINK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.60)(PORT = 1521))
    (CONNECT_DATA = (SID = PG_LINK))
    (HS = OK)
  )

🧠 7. Create Database Link in Oracle

Now connect to Oracle DB and create the link:

CREATE DATABASE LINK HO.WORLD
CONNECT TO "postgres" IDENTIFIED BY "Postgres@123"
USING 'PG_LINK';

Test the connection:

SELECT * FROM "public"."pg_table_name"@HO.WORLD;

If you can query successfully, your HS link is working fine 🎯

📦 8. Migrate Data from Oracle to PostgreSQL

Example PL/SQL block to copy data from Oracle table to PostgreSQL table:

SET SERVEROUTPUT ON
DECLARE
  CURSOR c1 IS
    SELECT group_id, ipp_name, data_source, tag_time_stamp, access_time_stamp, check_status
    FROM master.sample;

  v_count NUMBER := 0;
BEGIN
  FOR rec IN c1 LOOP
    INSERT INTO "public"."sample"@HO.WORLD
    (group_id, ipp_name, data_source, tag_time_stamp, access_time_stamp, check_status)
    VALUES
    (rec.group_id, rec.ipp_name, rec.data_source, rec.tag_time_stamp, rec.access_time_stamp, rec.check_status);

    v_count := v_count + 1;
    IF MOD(v_count, 1000) = 0 THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE(v_count || ' rows committed...');
    END IF;
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Total ' || v_count || ' rows migrated.');
END;
/

🔍 9. Verification

From Oracle:

SELECT COUNT(*) FROM "public"."ipp_33_tags_mda_status"@HO.WORLD;

From PostgreSQL:

SELECT COUNT(*) FROM public.ipp_33_tags_mda_status;

Both counts should match ✅

🏁 Conclusion

Using Oracle HS with DG4ODBC, you can directly integrate Oracle and PostgreSQL without ETL tools.
This approach is especially useful for incremental data migration, cross-database reporting, and archival automation.

For large-scale migrations, consider Oracle GoldenGate or Oracle Data Integrator (ODI), but for medium-volume transactional data, HS DB Link is a powerful and lightweight solution.