Step-by-Step: OCI Autonomous Database with Private Endpoint and Wallet Configuration [Report]

Overview

Oracle Cloud Infrastructure (OCI) Autonomous Database is a fully managed database service that uses machine learning to automate tuning, security, backups, and updates. This guide covers deploying Autonomous Transaction Processing (ATP) with a private endpoint for enhanced security, downloading and configuring the connection wallet, and connecting via SQL Developer.

Prerequisites

• OCI tenancy with a compartment for database resources
• IAM policy: Allow group DBAdmins to manage autonomous-database-family in compartment DB-Compartment
• OCI CLI installed: bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"
• VCN with a private subnet (minimum /28)
• SQL Developer 23.x installed locally

Step 1: Create Private Subnet for ADB

Autonomous Database with private endpoint requires a dedicated subnet within your VCN. This ensures the database is not accessible from the public internet.

# Create private subnet for ADB
oci network subnet create \
  --compartment-id $COMPARTMENT_ID \
  --vcn-id $VCN_ID \
  --display-name "adb-private-subnet" \
  --cidr-block "10.0.30.0/28" \
  --prohibit-public-ip-assignment true \
  --dns-label "adbpriv"

# Create Network Security Group
oci network nsg create \
  --compartment-id $COMPARTMENT_ID \
  --vcn-id $VCN_ID \
  --display-name "adb-nsg"

# Allow SQL*Net (port 1522) only from app subnet
oci network nsg rules add \
  --nsg-id $NSG_ID \
  --security-rules '[{
    "direction": "INGRESS",
    "protocol": "6",
    "source": "10.0.10.0/24",
    "sourceType": "CIDR_BLOCK",
    "tcpOptions": {"destinationPortRange": {"min": 1522, "max": 1522}}
  }]'

Step 2: Provision Autonomous Database

# Create ATP instance with private endpoint
oci db autonomous-database create \
  --compartment-id $COMPARTMENT_ID \
  --db-name "PRODDB01" \
  --display-name "Production ATP" \
  --admin-password 'C0mpl3x#Passw0rd!' \
  --cpu-core-count 2 \
  --data-storage-size-in-tbs 1 \
  --db-workload "OLTP" \
  --is-auto-scaling-enabled true \
  --is-mtls-connection-required false \
  --subnet-id $ADB_SUBNET_ID \
  --nsg-ids '["'$NSG_ID'"]' \
  --private-endpoint-label "proddbpe" \
  --license-model "BRING_YOUR_OWN_LICENSE"

# Check status (wait until AVAILABLE)
watch -n 10 'oci db autonomous-database get \
  --autonomous-database-id $ADB_ID \
  --query "data."lifecycle-state""'

Important: Provisioning takes 3-5 minutes. The database is ready when lifecycle-state shows "AVAILABLE".

Step 3: Download Connection Wallet

The wallet contains TLS certificates and connection descriptors required for secure connections.

# Generate and download wallet
oci db autonomous-database generate-wallet \
  --autonomous-database-id $ADB_ID \
  --password 'Wa11et#Secure2026' \
  --file ./adb_wallet.zip \
  --generate-type "SINGLE"

# Extract wallet
mkdir -p ~/oracle/wallet
unzip adb_wallet.zip -d ~/oracle/wallet/

# Verify contents
ls ~/oracle/wallet/
# Expected: cwallet.sso  ewallet.p12  keystore.jks  ojdbc.properties
#           sqlnet.ora  tnsnames.ora  truststore.jks

# Update sqlnet.ora to point to wallet directory
sed -i 's|?/network/admin|'"$HOME"'/oracle/wallet|g' ~/oracle/wallet/sqlnet.ora

Step 4: Connect via SQL Developer

Configure SQL Developer with the wallet for GUI-based database management:

1. Open SQL Developer → File → New → Database Connection
2. Name: PROD-ATP
3. Authentication Type: Default
4. Username: ADMIN
5. Password: (the admin password you set)
6. Connection Type: Cloud Wallet
7. Configuration File: Browse to adb_wallet.zip
8. Service: Select proddb01_tp (Transaction Processing)
9. Click Test → Status should show "Success"
10. Click Connect

Connect via SQLcl (Command Line)

export TNS_ADMIN=~/oracle/wallet

# Connect as ADMIN
sql ADMIN/'C0mpl3x#Passw0rd!'@proddb01_tp

# Verify
SQL> SELECT SYS_CONTEXT('USERENV','DB_NAME') FROM DUAL;
SQL> SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') FROM DUAL;
SQL> SELECT * FROM V$VERSION WHERE ROWNUM = 1;

Step 5: Create Application Schema

-- Connect as ADMIN first
CREATE USER app_user IDENTIFIED BY 'AppUser#2026Prod';
GRANT CONNECT, RESOURCE, CREATE VIEW TO app_user;
GRANT UNLIMITED TABLESPACE TO app_user;
GRANT CREATE SEQUENCE, CREATE TRIGGER, CREATE PROCEDURE TO app_user;

-- Enable REST access (optional)
BEGIN
  ORDS_ADMIN.ENABLE_SCHEMA(
    p_enabled => TRUE,
    p_schema => 'APP_USER',
    p_url_mapping_type => 'BASE_PATH',
    p_url_mapping_pattern => 'appapi'
  );
  COMMIT;
END;
/

-- Create sample table
CONNECT app_user/'AppUser#2026Prod'@proddb01_tp
CREATE TABLE customers (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(200) NOT NULL,
  email VARCHAR2(200) UNIQUE,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);
INSERT INTO customers (name, email) VALUES ('Test User', 'test@example.com');
COMMIT;

Step 6: Configure Scheduled Backups

# Autonomous DB creates automatic daily backups (60-day retention)
# View existing backups
oci db autonomous-database-backup list \
  --autonomous-database-id $ADB_ID \
  --query 'data[].{"Name":"display-name","State":"lifecycle-state","Type":"type","Time":"time-ended"}' \
  --output table

# Create on-demand backup before major changes
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "pre-release-$(date +%Y%m%d-%H%M)"

# Configure long-term monthly backup (1 year retention)
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --long-term-backup-schedule '{"repeatCadence":"MONTHLY","retentionPeriodInDays":365,"isDisabled":false}'

Step 7: Performance Monitoring

# Check database metrics via CLI
oci db autonomous-database get \
  --autonomous-database-id $ADB_ID \
  --query 'data.{"Status":"lifecycle-state","CPU":"cpu-core-count","Storage-TB":"data-storage-size-in-tbs","AutoScale":"is-auto-scaling-enabled","Used-Storage":"actual-used-data-storage-size-in-tbs"}'

# Performance Hub available in OCI Console:
# OCI Console → Oracle Database → Autonomous Database → Select DB → Performance Hub
# Shows: SQL Monitoring, ASH Analytics, SQL Tuning Advisor

# Set up monitoring alarm for CPU
oci monitoring alarm create \
  --compartment-id $COMPARTMENT_ID \
  --display-name "ADB-High-CPU" \
  --namespace "oci_autonomous_database" \
  --query-text 'CpuUtilization[5m]{autonomousDatabaseId = "'$ADB_ID'"}.mean() > 80' \
  --severity "CRITICAL" \
  --destinations '["'$NOTIFICATION_TOPIC_ID'"]' \
  --is-enabled true

Step 8: Security Configuration

-- Enable Unified Auditing
ALTER AUDIT POLICY ORA_SECURECONFIG ENABLE;
ALTER AUDIT POLICY ORA_LOGON_FAILURES ENABLE;
AUDIT POLICY ORA_SECURECONFIG;
AUDIT POLICY ORA_LOGON_FAILURES;

-- Network ACL - restrict outbound access
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.myapp.com',
    ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => 'APP_USER',
                        principal_type => xs_acl.ptype_db)
  );
END;
/

-- Register with OCI Data Safe
# OCI Console → Security → Data Safe → Target Databases → Register

Verification Checklist

• ✅ Autonomous Database lifecycle-state: AVAILABLE
• ✅ Private endpoint resolves within VCN (not publicly accessible)
• ✅ NSG allows port 1522 only from application subnet (10.0.10.0/24)
• ✅ Wallet downloaded and SQL Developer connects successfully
• ✅ Application user created with proper grants
• ✅ Auto-scaling enabled for CPU burst handling
• ✅ Daily automatic backups visible in console
• ✅ Long-term monthly backup configured
• ✅ Monitoring alarm set for CPU > 80%
• ✅ Unified auditing enabled

Cost Breakdown

Resource	Cost (ap-mumbai-1)
ATP 2 OCPU (BYOL)	~$730/month
ATP 2 OCPU (License Included)	~$1,460/month
Storage (1 TB)	~$118/month
Backup storage	Included in storage cost
Private endpoint	No additional cost

Tip: Use Always Free Autonomous DB (1 OCPU, 20GB) for dev/test to avoid costs entirely.