Autonomous Database

Overview
Suchindran Kannan
Sathishkumar Rangaraj

1 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Today’s Agenda

• What is Autonomous Database

• How does Autonomous Database Work
• Auto Provisioning
• Auto Configuration
• Automatic Indexing and Statistics collection
• Automatic Paritioning
• Predefined Parameters and Services
• Cloning
• Encryption by default
• Auto Patching and Autonomous DataGuard
• Elastic and Auto Scaling
• Backup and Recovery and Performance tools.

2 Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.

 What is Autonomous Database?
Using the Cloud to eliminate all the complexity of mission critical databases

Oracle Complete Complete Automated

Autonomous Infrastructure Database Data Center
Database Automation Automation Operations

3 Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.

 What is Autonomous Database?
1. Multiple Deployment Choices

Shared
Infrastructure
Public
Cloud

Dedicated
Infrastructure

Exadata
Cloud@Customer
Oracle
Autonomous
Database

Dedicated Region
4 Copyright
Copyright
© 2021,
© 2021,
Oracle
Oracle
and/or
and/or
its affiliates
its affiliates.
| AllAll
Rights
rightsReserved.
reserved
Cloud@Customer
 How does Autonomous Database work?
1. Full Database Lifecycle Automation

Provision Secure Manage

Rapidly and easily creates Protects data from all external Automates all infrastructure and
mission critical databases and internal threats database maintenance

• Creates Exadata+ • Continuous threat detection • Patches all software online+

Cloud Infrastructure • Applies security updates • Tunes settings
• Real Application Clusters+ online+ • Performs all OS and
scale-out database • Prevents admin snooping, SYSDBA operations
encrypts all data
+
Unique to Oracle
5 Copyright
Copyright©©
2021,
2021,
Oracle
Oracle
and/or
and/or
itsits
affiliates
affiliates.
| Confidential:
All rights reserved
Internal/Restricted/Highly Restricted [Date] 5
 How does Autonomous Database work?
1. Full Database Lifecycle Automation

Protect Scale Optimize

Recovers from any failure Scales online for highest Optimally runs workloads
without downtime performance and lowest cost without human direction

• Automates backup, restore, • Instant online elasticity+ • Automatically optimizes

application transparent+ of serverless data formats, parallelism+
cluster failover, diagnoses and compute and storage memory, and plans for each
repairs errors+ • enables true pay-per-use+ workload

+
Unique to Oracle
6 Copyright
Copyright©©
2021,
2021,
Oracle
Oracle
and/or
and/or
itsits
affiliates
affiliates.
| Confidential:
All rights reserved
Internal/Restricted/Highly Restricted [Date] 6
 Fully automated data protection
1. Highly available out of the box, 99.95% SLA with cross-region Autonomous Data Guard

Autonomous Data Guard

Primary Standby
(Ashburn) (Phoenix)

Built-in highly-available, Enhanced protection against disasters

self-healing infrastructure
Now with cross-region standbys
Automatic recovery for
server/storage failures

7 Copyright
Copyright©©
2021,
2022,
Oracle
Oracle
and/or
and/or
itsits
affiliates
affiliates.
| Confidential:
All rights reserved
Internal/Restricted/Highly Restricted [Date]
 How Does an
Autonomous Database Work

8 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Self-Driving | Auto-Provisioning
1. Instantly deploy a mission critical database

Quickly deploys a RAC Database on Exadata

• Provides a HA configuration
• Ensures seamless scale-out
• Enables online patching

Autonomous Database decides where

to place each database during provisioning
• Fewer number of instances preferred
• Databases may be open on only one node
• Still RAC enabled

9 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Driving | Auto-Configuration
1. Simply Load and Go

• Automatically configures the database, to

optimize it for specialized workloads Click icon to add picture

• DW, TP, JSON or APEX

• Everything from memory, data formats, and access

structures are optimized to improve performance

• User simply define tables, load data, run queries

• Built-in resource-management plans

Full lifecycle managed using Service Console UI, REST APIs or Language SDK

10 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
 Self-Driving |Optimizer Statistics

1. High-frequency Statistics Collection

• Gathers full optimizer statistics every 15 minutes if statistics are stale
• Controlled using DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON’);

2. Statistics are gathered automatically for direct load operations

• CREATE TABLE … AS SELECT …; INSERT /*+ append */ INTO … SELECT …;
• Data Pump Import loads
• DBMS_CLOUD loads

3. Real-Time Statistics are gathered automatically for DML operations*

• Gathers a subset of optimizer statistics for conventional DML operations
• Number of rows, MAX and MIN column values, etc.

11 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved. * Controlled via DBMS_STATS – Not on by default
 Self-Driving | Automatic Indexing
1. Machine Learning auto-tunes your workload

• An expert system that implements indexes based on what a

skilled performance engineer would do
Capture
• Reinforcement Learning allows it to learn from its own
actions as all candidate indexes are validated before being
implementing
Monitor Identify
• The entire process is fully automatic
• Transparency is equally important as sophisticated
automation
Decide Verify • All tuning activities are auditable

12 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Driving | Automatic Partitioning
1. Applies Partitioning to Tables and Indexes to Improve Performance on Shared Infrastructure

Identifies candidate tables by analysing the captured workload

• Tables must be 64GB or larger and have up to date statistics

Uses one of the following strategies with a single column key: Capture
• INTERVAL AUTOMATIC: best suited for ranges of partition key values
• LIST AUTOMATIC: applies to distinct partition key values
• HASH: partitioning on the partition key's hash values Monitor Identify

Decide Verify

13 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Driving | Database Parameters
1. Ability to customize the service for a given region or time zone

All parameters set to optimal values based on workload type

• May be different than regular database defaults
Users can only change a limited number of parameters

Allowed Parameters
APPROX_FOR_AGGREGATION NLS_COMP NLS_TIMESTAMP_FORMAT
APPROX_FOR_COUNT_DISTINCT NLS_CURRENCY NLS_TIMESTAMP_TZ_FORMAT
APPROX_FOR_PERCENTILE NLS_DATE_FORMAT OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES (Session only)
AWR_PDB_AUTOFLUSH_ENABLE NLS_DATE_LANGUAGE OPTIMIZER_IGNORE_HINTS
D NLS_DUAL_CURRENCY OPTIMIZER_IGNORE_PARALLEL_HINTS
CURRENT_SCHEMA (Session only) NLS_ISO_CURRENCY OPTIMIZER_MODE
CURSOR_SHARING NLS_LANGUAGE PLSCOPE_SETTINGS
DB_BLOCK_CHECKING NLS_LENGTH_SEMANTICS PLSQL_CCFLAGS
DDL_LOCK_TIMEOUT NLS_NCHAR_CONV_EXCP PLSQL_DEBUG
FIXED_DATE NLS_NUMERIC_CHARACTERS PLSQL_OPTIMIZE_LEVEL
GLOBAL_NAMES NLS_SORT PLSQL_WARNINGS
HEAT_MAP NLS_TERRITORY RECYCLEBIN
MAX_IDLE_TIME NLS_TIME_FORMAT STATISTICS_LEVEL (Session only)
NLS_CALENDAR NLS_TIME_TZ_FORMAT TIME_ZONE

14 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
 Self-Driving | Pre-defined Services
1. Priority, concurrency and parallelism auto-configured

• Applications connect to one of five pre-defined database services

• Services control priority, parallelism, and concurrency

RESOURCE
SERVICES SQL FAN TAC
MANAGEMENT CONCURRENCY
NAME PARALELLISM ENABLED ENABLED
PLAN SHARES
P
TPURGENT 12 USER SPECIFIED 300 X CPUs YES YES
R
I
TP 8 1 300 X CPUs YES YES
O
R
HIGH 4 All CPUs 3 YES NO
I
T
MEDIUM 2 4 1.25 X CPUs YES NO
Y
LOW 1 1 300 X CPUs YES NO

For Transaction Processing For Reporting or batch processing

15 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Driving | Auto Stop/Start

• Scheduling automatic database

shutdowns and restarts for times
when the system is not in use to
stop charges

– E.g., weekends & holidays

• Available via the SDK, REST API

or cloud UI

• Only pay for storage when

Database is stopped

16 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Driving | Cloning
1. Quickly provision a point-in-time copy

•Cloning creates a point-in-time copy of

• An Autonomous Database
• A backup of an Autonomous Database

•Three types of clone can be created:

• A full database clone
• A metadata clone (Schema but no data)
• Refreshable clone (read-only full clone)

•Easy and fast as user only decides:

Production DB Clone DB for • Location of DB for the clone (cross-region possible)
Development or test
• Name of the clone
• CPU and storage
• New ADMIN password
Copyright © 2022, Oracle and/or its affiliates. All rights reserved. * On Dedicated
Self-Securing | Encryption by Default
1. Secure by default

Encryption for Data at Rest Encryption for Data in Motion

• Automatically configured • Automatically configured

• All application data is encrypted within the • All network access is encrypted to and from the
database at the tablespace level database

• Database Backups are also encrypted • Choice of two methods

• Oracle Native Network Encryption
• Transport Layer Security (TLS) v1.2 (default)
• Oracle client credentials can be downloaded
via encrypted wallet files

18 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Securing | Auto Patching
1. Automatic patching without downtime

Automatic Patching of all components

Firmware, OS, Hypervisor, Clusterware, Database
(on-demand for critical security issue)

Database is continuously available to application

Patches applied in a rolling fashion across
Applications using Application Continuity
RAC nodes and Exadata storage servers best practices, run without interruption

Customer can adjust patching window within

Patching is automatically scheduled a time range on Dedicated deployments
Next patching windows shown on console

Note: Early access to patches now possible on both Shared & Dedicated Infrastructure

19 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Repairing | Autonomous Data Guard
1. Zero data loss

Site #1 Maintains a real-time remote copy of a production database

• Protects from physical disasters, network outages, database corruptions
• Completely transparent to customer applications
• Standby can be in the same or across regions

Primary Database Automates everything

Autonomous • Creation, operation, patching, backup, recovery, and monitoring
Data Guard
• Automatically handles fail over to the remote copy
Site #2
• Automates resynchronization after the primary site is restored

Maintains copy by applying logical changes, not physical changes

• Prevents propagating data corruptions to standby
• Validates data consistency as changes are applied
Standby Database
Note: ADB on Shared Infra does not provide access to standby database(s)

20 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Repairing | Autonomous Data Guard
1. Zero data loss

Cloud automation for

• Create
• Delete
• Switchover
• Failover
• Reinstate

CLI-support to configure
additional Data Guard
attributes

Enable Disaster Protection With a Single Click

21 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Self-Driving | Elastic Scaling
1. Instant elasticity

• Instant scaling online for highest performance and lowest

cost

• Size to the exact number of OCPUs and TBs required

• Not constrained by fixed building blocks or ‘t-shirt’ sizes
• Simple incremental growth
• Scale compute or storage completely independent of one
another

• All scaling operations occur online – while the application

continues to run

Copyright © 2022, Oracle and/or its affiliates. All rights reserved.

Self-Driving | Auto Scaling
1. Pay for exactly what you use

• Enables the database to use up to 3x CPU/IO

resources immediately when needed
• Helps CPU or IO bound workloads
• Does not scale up other resources
• Number of sessions
1 2 3 4 5
• Concurrency

• PGA, SGA, etc.

• Need to provision more OCPUs to scale up these resources

Copyright © 2022, Oracle and/or its affiliates. All rights reserved.

Backup and Recovery

• The Autonomous Data Warehouse Cloud console Backup tab allows you
to initiate Backup and point-in-time recovery for your database.

24 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
PERFORMANCE ANALYSIS TOOLS

• Autonomous Database includes automatic (real-time) statistics, automatic indexing, and automatic SQL plan
management

• ADB include access to the same in-depth performance analysis tools found in Oracle Database to provide
insight into performance of SQL and applications using Autonomous Database.

• These tools can be used to evaluate the effectiveness of a give database schema and functional correctness of
SQL. Autonomous Database performance analysis tools also include the following:

• Cloud User Interface

• Performance Hub

• Oracle Automatic Workload Repository (AWR)

• SQL Monitor

25 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Performance Hub

26 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Performance Hub …

27 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Performance Hub …

28 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]
Thank you!

37
29 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted [Date]