OCIDatabasesintermediate

Autonomous Database on OCI

Deploy and manage Oracle Autonomous Database with auto-tuning, scaling, and the Always Free tier.

CloudToolStack Team20 min readPublished Mar 14, 2026

Prerequisites

Basic SQL and database concepts
OCI account with database permissions

Autonomous Database on OCI

Oracle Autonomous Database (ADB) is a fully managed, self-driving database service that automates patching, tuning, backups, and scaling. Built on Oracle Database technology running on Exadata infrastructure, ADB eliminates the operational overhead of traditional database management while delivering enterprise-grade performance, availability, and security. It is one of OCI's flagship services and a primary reason many organizations adopt Oracle Cloud.

ADB comes in several workload-optimized variants: Autonomous Transaction Processing (ATP) for OLTP workloads, Autonomous Data Warehouse (ADW) for analytics and data warehousing, Autonomous JSON Database for document workloads, and APEX Application Development for low-code applications. Each variant uses the same underlying Oracle Database engine but is pre-tuned for its specific workload type.

This guide covers the ADB architecture, deployment options, OCPU vs ECPU billing models, auto-scaling, connectivity, Always Free ADB instances, and operational best practices.

Always Free Autonomous Database

OCI's Always Free tier includes two Autonomous Database instances, each with 1 OCPU (or 2 ECPUs) and 20 GB of storage. These instances never expire and are an excellent way to learn Oracle Database, build prototypes, or run small production workloads at zero cost. Always Free ADB instances include all enterprise features including encryption, automatic backups, and Oracle APEX.

ADB Deployment Options

Autonomous Database offers two deployment models that determine the level of isolation and infrastructure control:

Serverless vs Dedicated

Feature	Serverless (Shared)	Dedicated
Infrastructure	Shared Exadata infrastructure	Dedicated Exadata infrastructure
Isolation	Database-level isolation	Full infrastructure isolation
Provisioning	Minutes	Hours (infrastructure must be provisioned)
Pricing	Per OCPU/ECPU per hour	Dedicated Exadata cost + per-database cost
Scaling	1-128 OCPUs, 1 TB-128 TB storage	Based on Exadata capacity
Best For	Individual databases, dev/test, SaaS apps	Enterprises needing full isolation, compliance
Always Free	Available	Not available

Creating an Autonomous Database

Provisioning an ADB instance takes just a few minutes. You specify the workload type, compute capacity, storage, and admin password. OCI handles everything else: Exadata infrastructure, Oracle Database installation, RAC configuration, automatic backups, encryption, and ongoing patches.

bash

# Create an Autonomous Transaction Processing (ATP) database
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "myatpdb" \
  --display-name "My ATP Database" \
  --db-workload "OLTP" \
  --compute-model "ECPU" \
  --compute-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password "YourStr0ngP@ssword!" \
  --is-auto-scaling-enabled true \
  --is-mtls-connection-required false \
  --license-model "LICENSE_INCLUDED"

# Create an Autonomous Data Warehouse (ADW) database
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "myadwdb" \
  --display-name "My ADW Database" \
  --db-workload "DW" \
  --compute-model "ECPU" \
  --compute-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password "YourStr0ngP@ssword!" \
  --is-auto-scaling-enabled true

# Create an Always Free ATP instance
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "freetier" \
  --display-name "Always Free ATP" \
  --db-workload "OLTP" \
  --compute-count 1 \
  --data-storage-size-in-tbs 0.02 \
  --admin-password "YourStr0ngP@ssword!" \
  --is-free-tier true

# List Autonomous Databases
oci db autonomous-database list \
  --compartment-id $C \
  --query 'data[].{name:"display-name", workload:"db-workload", state:"lifecycle-state", ocpus:"cpu-core-count", storage:"data-storage-size-in-tbs"}' \
  --output table

Admin Password Requirements

The ADMIN password must be 12-30 characters, contain at least one uppercase letter, one lowercase letter, and one number. It cannot contain the double quote character or the username "admin". Store this password securely in OCI Vault or a password manager. You will need it to connect to the database and access the SQL Developer Web and APEX interfaces.

OCPU vs ECPU Billing Models

Autonomous Database supports two compute billing models. The OCPU model is the traditional model where you pay per Oracle Compute Unit per hour. The newerECPU (Elastic Compute Processing Unit) model provides more granular and often more economical pricing. ECPUs are an abstraction of compute resources that allows finer-grained scaling.

Feature	OCPU Model	ECPU Model
Minimum compute	1 OCPU	2 ECPUs
Scaling granularity	1 OCPU increments	1 ECPU increments
Equivalence	1 OCPU = 1 physical core	~4 ECPUs = 1 OCPU (approximate)
Auto-scaling	Up to 3x base OCPUs	Up to 3x base ECPUs
Storage auto-scaling	Supported	Supported
Recommended for	Existing BYOL customers	New deployments, cost optimization

bash

# Scale compute (ECPU model)
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --compute-count 8

# Scale storage
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --data-storage-size-in-tbs 2

# Enable auto-scaling (compute scales up to 3x automatically)
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --is-auto-scaling-enabled true

# Enable storage auto-scaling
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --is-auto-scaling-for-storage-enabled true

# Change compute model from OCPU to ECPU
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --compute-model "ECPU"

Connecting to Autonomous Database

ADB supports two connection types: mTLS (mutual TLS, the default for older databases) which requires a wallet file, and TLS (one-way TLS) which allows direct connections without a wallet from allowed networks. TLS connections are simpler to configure and recommended for most use cases, especially when connecting from within OCI or through a private endpoint.

bash

# Download the wallet (for mTLS connections)
oci db autonomous-database generate-wallet \
  --autonomous-database-id <adb-ocid> \
  --password "WalletP@ssword1" \
  --file wallet.zip

# Unzip the wallet
unzip wallet.zip -d wallet/

# The wallet contains:
# - tnsnames.ora (connection descriptors)
# - sqlnet.ora (network configuration)
# - cwallet.sso (auto-login wallet)
# - ewallet.p12 (PKCS#12 wallet)
# - keystore.jks (Java keystore)
# - truststore.jks (Java truststore)

# Connect using SQL*Plus with wallet
export TNS_ADMIN=./wallet
sqlplus admin/YourStr0ngP@ssword!@myatpdb_tp

# Connect using TLS (no wallet needed)
# First, configure the ADB for TLS connections
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --is-mtls-connection-required false

# Then add your network to the ACL (Access Control List)
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --whitelisted-ips '["10.0.0.0/16"]'

# Get connection strings
oci db autonomous-database get \
  --autonomous-database-id <adb-ocid> \
  --query 'data."connection-strings".profiles[].{name:"display-name", value:value}' \
  --output table

Use Private Endpoints for Production

For production workloads, configure your ADB with a private endpoint. This places the database on a private subnet in your VCN, eliminating public internet exposure entirely. Traffic between your application and database stays within the OCI network backbone. Specify --subnet-id and --nsg-ids when creating the database to enable private endpoint connectivity.

Private Endpoints

By default, serverless ADB instances have a public endpoint (protected by mTLS or ACL). For enhanced security, you can configure a private endpoint that places the database endpoint in a subnet of your VCN. This is strongly recommended for production environments.

bash

# Create ADB with a private endpoint
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "privatedb" \
  --display-name "Private ATP" \
  --db-workload "OLTP" \
  --compute-model "ECPU" \
  --compute-count 4 \
  --data-storage-size-in-tbs 1 \
  --admin-password "YourStr0ngP@ssword!" \
  --subnet-id <private-subnet-ocid> \
  --nsg-ids '["<nsg-ocid>"]' \
  --is-mtls-connection-required false

# Switch an existing public ADB to private endpoint
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --subnet-id <private-subnet-ocid> \
  --nsg-ids '["<nsg-ocid>"]'

# Required NSG rules for private endpoint:
# Ingress: TCP port 1522 from your application subnet
# Egress: TCP port 1522 to the ADB private endpoint

Autonomous Database Tools

Every ADB instance comes with built-in web-based tools that you can access through the OCI Console without installing any client software. These tools are available at no additional cost.

Tool	Purpose	Access
Database Actions (SQL Developer Web)	Run SQL, manage schema, monitor performance	Built-in web interface
Oracle APEX	Low-code application development	Built-in, always available
Oracle REST Data Services (ORDS)	REST API access to database	Auto-configured
Oracle Machine Learning (OML)	In-database ML with notebooks	Built-in notebooks interface
Graph Studio	Graph analytics and visualization	Built-in for ADW
Data Transforms	ETL and data integration	Built-in data pipeline tool

bash

# Access Database Actions URL
oci db autonomous-database get \
  --autonomous-database-id <adb-ocid> \
  --query 'data."connection-urls"."sql-dev-web-url"' --raw-output

# Access APEX URL
oci db autonomous-database get \
  --autonomous-database-id <adb-ocid> \
  --query 'data."connection-urls"."apex-url"' --raw-output

# Access OML Notebooks URL
oci db autonomous-database get \
  --autonomous-database-id <adb-ocid> \
  --query 'data."connection-urls"."machine-learning-notebook-url"' --raw-output

Backup and Recovery

ADB automatically backs up your database every day with a retention period of 60 days. These automatic backups are stored in Oracle-managed Object Storage and are included in the service cost. You can also create manual backups for specific retention needs or before major changes. Point-in-time recovery (PITR) allows you to restore to any second within the backup retention period.

bash

# Create a manual backup
oci db autonomous-database-backup create \
  --autonomous-database-id <adb-ocid> \
  --display-name "pre-migration-backup" \
  --is-long-term-backup false

# Create a long-term backup (retained beyond 60 days)
oci db autonomous-database-backup create \
  --autonomous-database-id <adb-ocid> \
  --display-name "quarterly-backup-q1" \
  --is-long-term-backup true \
  --retention-period-in-days 365

# List backups
oci db autonomous-database-backup list \
  --compartment-id $C \
  --query 'data[].{name:"display-name", type:type, state:"lifecycle-state", "time-started":"time-started"}' \
  --output table

# Restore from a backup
oci db autonomous-database restore \
  --autonomous-database-id <adb-ocid> \
  --timestamp "2026-03-10T12:00:00Z"

# Clone a database (creates a full copy)
oci db autonomous-database create-from-clone \
  --compartment-id $C \
  --source-id <source-adb-ocid> \
  --clone-type "FULL" \
  --db-name "clonedb" \
  --display-name "Dev Clone" \
  --compute-model "ECPU" \
  --compute-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password "CloneP@ssword1!"

Data Loading

ADB provides multiple ways to load data: from Object Storage using DBMS_CLOUD, from local files using SQL Loader or Data Pump, through Database Links from other Oracle databases, and via Oracle GoldenGate for real-time replication.

sql

-- Load data from OCI Object Storage using DBMS_CLOUD
-- First, create a credential for Object Storage access
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_CRED',
    user_id         => '<user-ocid>',
    tenancy_id      => '<tenancy-ocid>',
    private_key     => '<api-private-key>',
    fingerprint     => '<key-fingerprint>'
  );
END;
/

-- Create an external table pointing to a CSV file in Object Storage
BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      => 'SALES_EXT',
    credential_name => 'OCI_CRED',
    file_uri_list   => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/<namespace>/b/<bucket>/o/sales_data.csv',
    format          => JSON_OBJECT('type' VALUE 'csv', 'skipheaders' VALUE '1'),
    column_list     => 'sale_id NUMBER, sale_date DATE, amount NUMBER(10,2), region VARCHAR2(50)'
  );
END;
/

-- Copy data from Object Storage directly into a table
BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'SALES',
    credential_name => 'OCI_CRED',
    file_uri_list   => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/<namespace>/b/<bucket>/o/sales_*.csv',
    format          => JSON_OBJECT('type' VALUE 'csv', 'skipheaders' VALUE '1', 'dateformat' VALUE 'YYYY-MM-DD')
  );
END;
/

-- Load Parquet files (common for analytics workloads)
BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'ANALYTICS_DATA',
    credential_name => 'OCI_CRED',
    file_uri_list   => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/<namespace>/b/<bucket>/o/data/*.parquet',
    format          => JSON_OBJECT('type' VALUE 'parquet')
  );
END;
/

DBMS_CLOUD Supports Multiple Cloud Providers

The DBMS_CLOUD package can load data not only from OCI Object Storage but also from AWS S3, Azure Blob Storage, Google Cloud Storage, and any S3-compatible storage service. This makes ADB an excellent choice for multi-cloud architectures where data may reside in different cloud providers. Simply create credentials for the target cloud provider and use the appropriate URI format.

Performance Monitoring

ADB includes built-in performance monitoring through the Performance Hub in Database Actions. Performance Hub provides real-time and historical views of database activity, SQL performance, wait events, and resource utilization. The Autonomous Database automatically tunes itself, but understanding the performance dashboard helps you optimize your application queries.

bash

# Get ADB performance metrics via OCI Monitoring
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace "oci_autonomous_database" \
  --query-text 'CpuUtilization[1h]{resourceId = "<adb-ocid>"}.mean()'

# Other available metrics:
# StorageUtilization - storage usage percentage
# Sessions - active session count
# ExecuteCount - SQL executions per second
# RunningStatements - currently executing statements
# QueuedStatements - queued statements waiting for resources

# Start/stop ADB to save costs when not in use
oci db autonomous-database stop \
  --autonomous-database-id <adb-ocid>

oci db autonomous-database start \
  --autonomous-database-id <adb-ocid>

# Terminate an ADB instance
oci db autonomous-database delete \
  --autonomous-database-id <adb-ocid> \
  --force

Autonomous Database Best Practices

Area	Recommendation
Security	Use private endpoints in production. Enable network ACLs. Rotate ADMIN password regularly.
Performance	Enable auto-scaling. Use ECPU model for finer granularity. Monitor via Performance Hub.
Cost	Stop databases when not in use. Use Always Free for dev/test. Right-size compute.
Data Protection	Automatic backups are on by default. Create manual backups before major changes.
Connectivity	Use TLS (not mTLS) for simplicity. Use ORDS for REST API access.
Operations	Use Terraform for IaC. Tag databases for cost tracking. Use cloning for dev copies.

Getting Started with Oracle Cloud OCI Security Best Practices Terraform on OCI

Key Takeaways

1Autonomous Database handles patching, tuning, scaling, and backups automatically.
2ATP is optimized for OLTP workloads while ADW is optimized for analytics and warehousing.
3ECPU pricing replaced OCPU pricing and provides more granular compute scaling.
4The Always Free ADB instance includes 20 GB storage and 1 ECPU permanently.

Frequently Asked Questions

What is the difference between ATP and ADW?

Autonomous Transaction Processing (ATP) is optimized for OLTP workloads with row-based storage and fast single-row lookups. Autonomous Data Warehouse (ADW) is optimized for analytics with columnar storage, automatic indexing for complex queries, and built-in data tools. Both share the same underlying technology but have different default configurations and optimization profiles.

How does ECPU pricing work?

ECPUs (Elastic Compute Processing Units) provide finer-grained compute scaling than the legacy OCPU model. You can scale in increments of 2 ECPUs (minimum), and approximately 4 ECPUs equal the compute capacity of 1 OCPU. ECPU pricing is per-hour for compute and per-GB-month for storage, with auto-scaling able to use up to 3x the base compute when needed.

Written by CloudToolStack Team

Cloud engineers and architects with hands-on experience across AWS, Azure, and GCP. We write guides based on real-world production patterns, not just documentation rewrites.

Disclaimer: This guide is for educational purposes. Cloud services change frequently; always refer to official documentation for the latest information. AWS, Azure, and GCP are trademarks of their respective owners.