Oracle Autonomous Database

Oracle Autonomous Database – Machine learning driven cloud-based database. Fully Automated – Self driving, Self-securing & Self-repairing. As a end-user we just have to focus on our data/business logic etc.
Available for both types of workloads Transaction Processing or Data Warehouse in Shared/Dedicated Exadata Infrastructure.

In this blog we will explore the basics of Oracle Autonomous Database (ADB).


Workloads Types

Autonomous Data Warehouse (ADW)

  • Best Suited for Analytics workloads, where data not changing too frequently. Like OLAP Systems
  • Data Warehouse, Data Mart, Data Lakes & Business Intelligence etc.
  • When your data resides is mainly summarized & aggregated form
  • When you have Massive datasets for Machine Learning Testing

Autonomous Transaction Processing (ATP)

  • Best suited for Transnational system, where data changing frequently. Like OLTP Systems
  • When you have mixed workloads (OLTP + OLAP) – Hybrid Load.
  • Mainly suited for batch processing & reporting
  • ATP suited for Application Development & Real time analytics

Features of ADB:

  • Self Driving:
    • Rapid Provisioning, Self Scaling (Upto 3x of base OCPU) – No Downtime
    • Automatic Tuning & Indexing. No need to create by yourself.
    • Automatic stats gather, user can also do manually.
    • Hints disabled by default in ADW.
    • Self Tuning – Index, Materialized Views, Partition, Compression – Not Recommend in ADB.
    • Automated Backup – 60 days retention default. (For Dedicated ADB User can control retention days : 7,15,30,60)
    • Automatic Patching in Shared Hosting. Patching can be controlled way in dedicated.
  • Self Securing:
    • Only authenticated users/app allow to access data in ADB
    • Connection used certificate based authentication & SSL (Secure Socket Layer)
    • Certificate based authentication present at both sides client & server
    • Default Encryption for data at rest – Transparent Data Encryption (TDE). For Backups also data is encrypted.
    • Encryption for data at motion – Transport Layer Security (TLS v1.2) by default. User can select other option like Oracle Native Network Encryption
    • Admin is default database user and only privileged user. There is no sys, sysadmin or system users in ADB.
    • In ADB Oracle is responsible for Platform security, Patch & Upgrades. Data encryption by default.
    • Customer Responsibility : Security assessment, sensitive data discovery, data protection, data masking & redaction.
    • Oracle Data Safe : Free tool that provide – Sensitive data discovery, data protection, data masking, user auditing. Security Assessments.
  • Self Repairing:
    • Self Healing H/W & S/W. Automatically detect any fault & recover from it.
    • Advance ML based predictive failure capabilities
    • Detection of failures – 24*7 Monitoring
    • Pattern recognizing using ML for any problem using logs.

Deployment Choice

Shared Infrastructure

  • Sharing the infrastructure with other tenants.
  • Min Size – 1x OCPU & 1TB of storage (128 OCPU Maximum)
  • Automatic scale online

Shared Infrastructure

  • Dedicated, un-shared cloud infrastructure
  • Complete Isolation
  • More control over Infrastructure & Patching process

Main Architectural Components

Exadata Cloud Infrastructure:

  • ADB is placed on Exadata Systems hosted on OCI Data Centers
  • ADB Database storage directly attached to Exadata Systems
  • OCI Object Storage (Bucket) – used for file storage.
  • Automated backups stored on dedicated OCI storage, which cannot access by user directly.
  • Staging Files, Dump Files, External Tables are stored in Bucket.

Interfaces

  • Database actions are exposed through Cloud UI and REST APIs
    • Create/terminate/backup/restore/stop/start
    • Change – CPU or Storage
  • Monitoring – User can monitor using ‘Cloud Service Dashboard’
  • Developer – SQL Developer & Web etc.
  • ORDS – Oracle Rest Data Services

Oracle Data Visualization Desktop :

  • Analysis Tool – Light weighted
  • It can be installed on Windows & Mac OS10.
  • Offline Availability
  • No remote server required

Loading /Migrating Data to ADB

  • ORCL SQL*LOADER – Data sitting on local client.
  • ORCL DATA PUMP (impdp/expdp .DMP) Using Object Storage
  • .CSV/JSON/Parquet Files
  • External Tables

Refer DBMS_CLOUD package for more details for data loading & credentials setup.

Move data into the ADB database :

  • Data Pump – Portable way to load data in new ADB
  • Golden Gate – Replication for on-prem & non-autonomous. It has some restrictions for rowids, nested table & identity column.

Wallet Credentials:

  • Wallet is used for connecting the ADB.
  • Certificate authentication uses an encrypted key stored in a wallet on both client as well as server side.
  • Listener Port: 1522 (Standard on Shared & default on Dedicated)

Wallet contains collection of files.

  • truststore.jks
  • tnsnames.ora
  • sqlnet.ora – Connector Strings details
  • README – expiry date – 2.5 years approx
  • keystore.jks
  • ewallet.p12
  • cwallet.sso

Pre-defined Database Service

tp & tpurjent best suited for OLTP. low best suited for warehousing as high concurency.

  • tpurjent – ATP Only (Support Manual Parallelism) High concurrency & resources. For batch processing when parallel required.
  • tp – ATP Only (No Parallelism) as DOP=1. High concurrency & resources. When parallel not required
  • high (All operations parallel) All CPUs
  • medium DOP=4 limited
  • low – (No Parallelism) as DOP = 1
S.NoServiceSQL PARALLISMRESOURCESCONCURRENCY
1tpurjent (ATP)Manual12100 X CPU
2tp (ATP)18100 X CPU
3highALL CPU43
5medium421.25 X CPU
6low11100 X CPU
  • In ADW – there will be only high, medium & low.
  • In ATP – All 5 services are present.

Scaling

  • Independently scale compute (OCPU) & storage for ADB
  • Resizing occur instantly, fully online.
  • Memory, IO bandwidth, concurrency scales linearly with OCPU

Backup ADB

  • Automated backup
  • Retention period shared – 60 days
  • Retention period dedicated – 7, 15, 30, 60 days
  • Database can be restored at any point in time within retention
  • User can create manual backups also
  • Recovery can be done using Cloud Console/API – from timestamp (point in time) or using backup

Cloning ADB

Create a new database as a clone of an existing ADB.

  • Either full copy or Just Metadata depending upon requirement
  • Create clone from a live running DB instance or from a backup of ADB

Thanks!

Happy Learning! Your feedback would be appreciated!

One thought on “Oracle Autonomous Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s