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).
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.
- Sharing the infrastructure with other tenants.
- Min Size – 1x OCPU & 1TB of storage (128 OCPU Maximum)
- Automatic scale online
- 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.
- Database actions are exposed through Cloud UI and REST APIs
- 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 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.
- sqlnet.ora – Connector Strings details
- README – expiry date – 2.5 years approx
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
|1||tpurjent (ATP)||Manual||12||100 X CPU|
|2||tp (ATP)||1||8||100 X CPU|
|5||medium||4||2||1.25 X CPU|
|6||low||1||1||100 X CPU|
- In ADW – there will be only high, medium & low.
- In ATP – All 5 services are present.
- Independently scale compute (OCPU) & storage for ADB
- Resizing occur instantly, fully online.
- Memory, IO bandwidth, concurrency scales linearly with OCPU
- 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
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
Happy Learning! Your feedback would be appreciated!Follow @shobhitsinghIN