Beyond the Create Button: Architecting Oracle Autonomous Database on Microsoft Azure into a Live Data Pipeline

1. Create blade: pick Transaction Processing:

Why OLTP: the app writes small, concurrent rows (customer profiles, orders, sessions). Transaction Processing tunes for high concurrency and short statements; Data Warehouse favors large scans. Pick it first because it cannot be switched in place later.

2. Database configuration in depth, with auto scaling on

Setting	Value	Reasoning
Database version	19c	Long-term-support release; broadest driver and tool compatibility.
ECPU (base)	2	Start small; you pay for base ECPUs continuously, so size for the quiet period.
Compute auto scaling	On	Lets the database burst to roughly 3x base on demand and fall back; you are billed for actual use above base. Covers peaks without a resize event.
Storage	1 TB + auto scaling	Auto scaling prevents a full-disk stall on growth. Set a budget alert since storage can only grow elastically.
Backup retention	60 days	Automatic backups are managed by Oracle and billed separately from database storage. Older backups are deleted past the window.
Admin credentials	ADMIN	Use a long unique password stored in Key Vault; create named app users and avoid daily ADMIN use.
License	License included	Switch to BYOL only if your Oracle agreement allows it.
Networking	Private endpoint on a delegated subnet, NSG attached	No public access; mTLS optional (see Screen 4).

Equivalent CLI

3. Deployment in progress:

Takeaway: the Azure resource appears fast, but the database lifecycle completes in OCI. Wait for the OCI state to read Available before connecting.

4. OCI console: nanubalu is Available:

Takeaway: every choice from Screen 2 is confirmable here. Buttons across the top (Database actions, Database connection, Performance hub, Manage resource allocation) are the daily-ops entry points. Verify from SQL:

SELECT name, open_mode, database_role FROM v$database;

SELECT * FROM v$version;

SELECT * FROM v$services; -- HIGH / MEDIUM / LOW / TP / TPURGENT

SELECT value FROM v$parameter WHERE name = 'cpu_count';

5. MongoDB Compass: saved queries on the source

Takeaway: Oracle will hold the transactional and relational side of the same application.

6. Querying movieCollection:

Takeaway: nested arrays (cast, crew, awards) are why a document model suits the catalog while customer demographics stay relational.

7. Data Factory: factory resources:

Takeaway: one pipeline, two datasets: a CSV source in ADLS and an Oracle sink pointing at nanubalu.

8. Source dataset on the bronze layer:

Takeaway: "bronze" signals a raw landing zone; keep it immutable and let the pipeline shape data on the way into Oracle.

9. Pipeline run: Copy data:

Takeaway: add a trigger (schedule or storage event) once the manual run succeeds, and alert on failure in Azure Monitor.

10. Verify in SQL:

begin
  dbms_cloud_repo.install_file(
    repo          => get_repo,
    file_path     => 'tables/demographics.sql',
    branch_name   => 'main',
    stop_on_error => false);
end;
/
select * from demographics;

Query result: fetched 200 rows in 0.238 seconds

CUST_ID	AGE	COMMUTE	CREDIT	EDUCATION
1238607	41	23	426	LessThanHS
1239220	52	40	19	High School
1242077	44	17	492	Bachelors

Takeaway: install the DDL from a Git repo with DBMS_CLOUD_REPO, then prove the load. Add these checks and a masking rule so personal data never reaches lower environments:

SELECT COUNT(*) FROM demographics; -- expect 200

SELECT education, ROUND(AVG(credit_balance),0) avg_credit, COUNT(*) n

FROM demographics GROUP BY education ORDER BY n DESC;

-- Mask email for a read-only analyst role

BEGIN

DBMS_REDACT.ADD_POLICY(

object_schema => 'ADMIN', object_name => 'DEMOGRAPHICS',

column_name => 'EMAIL', policy_name => 'MASK_EMAIL',

function_type => DBMS_REDACT.PARTIAL,

function_parameters => 'VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,*,1,20',

expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''ADMIN''');

END;

Auto scaling: how to prove it works:

-- Current vs base CPU allocation

SELECT name, value FROM v$parameter WHERE name IN ('cpu_count','resource_manager_plan');

-- Peak usage in the last hour from Active Session History

SELECT TRUNC(sample_time,'MI') minute, COUNT(*)/60 avg_active_sessions

FROM v$active_session_history

WHERE sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR

GROUP BY TRUNC(sample_time,'MI') ORDER BY 1;

Watch the Performance hub and the OCI metrics CpuUtilization and StorageUtilization during a load test; alert at 80 percent so you notice sustained scale-up before the bill does.

Architecture at a glance

Control plane is split by design: Azure owns the resource, networking, RBAC, tags and invoice; OCI owns database lifecycle (scale, backup, clone, Data Guard, Performance Hub). Write that into your RACI before go-live.

Sizing and auto-scaling mechanics

Topic	What to know
ECPU model	ECPU is the current billing unit for Autonomous (replacing OCPU). It is a hardware-agnostic compute unit, so do not carry OCPU counts over one-to-one; benchmark.
Compute auto scaling	Allows up to 3x the base ECPU. Base is billed continuously; usage above base is billed for the time it is actually used. Size base near your steady p50 and let burst absorb p95 to p99.
Storage auto scaling	Lets storage grow automatically (up to a multiple of the reserved size) so a load never fails on a full tablespace. It protects availability, not your budget, so add a cost alert.
Memory and IO	Memory and IOPS scale with ECPU. Scaling compute online needs no downtime.
Tuning rule	If the database sits at 2x to 3x for hours every day, raise the base. Burst is for spikes, and sustained use at burst is cheaper as a larger base.
Idle cost control	Use the auto start/stop schedule on non-production copies (it is Disabled on this instance, which suits prod).

Connection services: pick the right consumer group

Service	Use it for	Behavior
TPURGENT	Time-critical OLTP	Highest priority; parallelism only by hint.
TP	Default app traffic	No automatic parallelism; high concurrency. Use this for the application.
HIGH	Reports, ETL, admin	Largest share of resources, parallel queries, fewer concurrent statements.
MEDIUM	Mixed jobs	Balanced share with limited parallelism.
LOW	Background work	Lowest priority, highest concurrency, serial execution.

Point the Data Factory sink at HIGH or MEDIUM so bulk loads do not starve the app on TP.

-- Which service is this session using?
SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') svc,
       SYS_CONTEXT('USERENV','SESSION_USER')  usr FROM dual;

-- Who is connected, by service, right now?
SELECT service_name, COUNT(*) sessions
FROM   v$session WHERE type = 'USER' GROUP BY service_name;

Connectivity and transport security:

# python-oracledb, thin mode, TLS, no wallet (mTLS not required)
import oracledb
dsn = ("(description=(retry_count=3)(retry_delay=2)"
       "(address=(protocol=tcps)(port=1521)(host=nanubalu-db.nwf.internal))"
       "(connect_data=(service_name=<your_service>_tp.adb.oraclecloud.com))"
       "(security=(ssl_server_dn_match=yes)))")
pool = oracledb.create_pool(user="APP_USER", password=PW, dsn=dsn,
                            min=2, max=20, increment=2)

Item	Guidance
Access type	Private endpoint on a delegated subnet; no public IP. Resolve the endpoint through an Azure Private DNS zone and give apps a CNAME, never a hardcoded host.
Ports	1522 for mutual TLS (wallet required), 1521 for one-way TLS when mTLS is "Not required" (as on this instance). Allow only the app and integration subnets in the NSG.
mTLS	Leave it off only if the network is private and an NSG/ACL restricts sources. If clients are untrusted or cross-network, require mTLS and rotate the wallet on a schedule.
Network ACL	Add an access control list in addition to the NSG: two independent allow-lists beat one.

Resilience: backups are not disaster recovery

Capability	State on nanubalu	Expert note
Automatic backups	On, 60 days	Enables point-in-time restore inside the window. Billed separately from storage.
Long-term backups	Not scheduled	Use for compliance retention beyond 60 days (up to years).
Local standby (Autonomous Data Guard)	Backup-based only	Enable for a faster, near-zero-data-loss failover within the region.
Cross-region	Not enabled	Enable a cross-region standby for regional outages; plan DNS failover with a short TTL.
Restore test	Not yet done	Clone from a backup quarterly and run your validation queries. An untested backup is a hope.

Write the targets down: for example RPO 15 minutes and RTO 1 hour, then choose backup-only, local standby or cross-region standby to meet them, not the other way round.

Performance and tuning like an OCI admin

Auto indexing and automatic statistics are on by default in Autonomous; review them, do not fight them.
Performance Hub and Real-Time SQL Monitoring for live diagnosis; Operations Insights for capacity trends.
Resilient clients: use connection pools with retry in the connect descriptor and configure Application Continuity or Transparent Application Continuity so maintenance and scaling events are invisible to users.

-- Auto indexing: mode and what it did in the last day
SELECT parameter_name, parameter_value
FROM   dba_auto_index_config WHERE parameter_name IN ('AUTO_INDEX_MODE','AUTO_INDEX_SCHEMA');

SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSTIMESTAMP-1, SYSTIMESTAMP, 'TEXT') FROM dual;

-- Top SQL by elapsed time per execution
SELECT sql_id, executions, ROUND(elapsed_time/1e6,1) total_s,
       ROUND(elapsed_time/NULLIF(executions,0)/1e3,1) ms_per_exec,
       SUBSTR(sql_text,1,80) txt
FROM   v$sql WHERE executions > 0
ORDER  BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

Infrastructure as code

Provision from code so the portal clicks in Screens 1 and 2 are repeatable. Illustrative Terraform (check current azurerm resource and argument names before use):

resource "azurerm_oracle_autonomous_database" "nanubalu" {
  name                             = "nanubalu"
  resource_group_name              = "MovieStream"
  location                         = "eastus"
  subnet_id                        = azurerm_subnet.oradata.id
  virtual_network_id               = azurerm_virtual_network.data.id
  display_name                     = "nanubalu"
  db_workload                      = "OLTP"        # Transaction Processing
  db_version                       = "19c"
  compute_model                    = "ECPU"
  compute_count                    = 2
  auto_scaling_enabled             = true          # compute burst
  auto_scaling_for_storage_enabled = true
  data_storage_size_in_tbs         = 1
  backup_retention_period_in_days  = 60
  license_model                    = "LicenseIncluded"
  mtls_connection_required         = false
  admin_password                   = var.adb_admin_password   # from Key Vault
  tags = { workload = "moviestream", env = "prod", owner = "data-platform" }
}

Hardening the Data Factory pipeline

Run the copy through a self-hosted integration runtime in the same VNet so traffic stays on the private endpoint.
Make loads idempotent: land in a staging table, then MERGE. Re-running a failed pipeline must not duplicate rows.
Tune the copy activity: batch size, parallel copies and the sink service (MEDIUM/HIGH) to match the ECPU you have.
Alternative: let the database pull from object storage with DBMS_CLOUD.COPY_DATA, which is often faster for large files.

-- Idempotent upsert from staging
MERGE INTO demographics d
USING demographics_stg s ON (d.cust_id = s.cust_id)
WHEN MATCHED THEN UPDATE SET d.age = s.age, d.credit_balance = s.credit_balance,
                             d.education = s.education
WHEN NOT MATCHED THEN INSERT (cust_id,last_name,first_name,email,age,commute_distance,credit_balance,education)
  VALUES (s.cust_id,s.last_name,s.first_name,s.email,s.age,s.commute_distance,s.credit_balance,s.education);

-- Post-load reconciliation
SELECT (SELECT COUNT(*) FROM demographics_stg) staged,
       (SELECT COUNT(*) FROM demographics)     loaded FROM dual;

FinOps for the architect

Cost drivers: base ECPU hours, burst ECPU time, storage, backup storage and (if used) cross-region standby.
Tag every resource (workload, env, owner, costcenter) so Azure Cost Management can show Oracle beside AKS and Data Factory.
Review the base ECPU monthly using peak-to-base ratios; right-size before buying more.
Confirm with both vendors how Marketplace spend counts toward your Azure commitment.

Troubleshooting cheat sheet

Symptom	Usual cause	First check
ORA-12541 / ORA-12170 timeout	NSG, ACL, DNS or wrong port	Resolve the host from the client subnet; test port 1521 or 1522.
ORA-28759 / wallet errors	mTLS required but no wallet, or expired wallet	Match port to the mTLS setting; re-download the wallet.
ORA-01017	Wrong or expired password, or the wrong user	Check `dba_users.account_status`; unlock and reset.
Copy slow or throttled	Sink on a low-priority service, or base ECPU too small	Switch to MEDIUM/HIGH; watch Performance Hub for CPU and IO waits.
Unexpected bill	Sustained burst, backup growth or storage auto-scale	Cost analysis by tag; compare base to peak ECPU.

Oracle Applications R12.2.x| Exadata | OCI|Oracle GRC|ECC

Sunday, October 4, 2026