Home Data Oracle Autonomous Database in Enterprise Architecture

Oracle Autonomous Database in Enterprise Architecture

By Bal Mukund Sharma , Krishnakumar KM , Rashmi Panda
books-svg-icon Book
eBook $37.99 $25.99
Print $46.99
Subscription $15.99 $10 p/m for three months
$10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
BUY NOW $10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
eBook $37.99 $25.99
Print $46.99
Subscription $15.99 $10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
  1. Free Chapter
    Chapter 1: Introduction to Oracle's Autonomous Database
About this book
Oracle Autonomous Database (ADB) is built on the world’s fastest Oracle Database Platform, Exadata, and is delivered on Oracle Cloud Infrastructure (OCI), customer data center (ExaCC), and Oracle Dedicated Region Cloud. This book is a fast-paced, hands-on introduction to the most important aspects of OCI Autonomous Databases. You'll get to grips with concepts needed for designing disaster recovery using standby database deployment for Autonomous Databases. As you progress, you'll understand how you can take advantage of automatic backup and restore. The concluding chapters will cover topics such as the security aspects of databases to help you learn about managing Autonomous Databases, along with exploring the features of Autonomous Database security such as Data Safe and customer-managed keys for Vaults. By the end of this Oracle book, you’ll be able to build and deploy an Autonomous Database in OCI, migrate databases to ADB, comfortably set up additional high-availability features such as Autonomous Data Guard, and understand end-to-end operations with ADBs.
Publication date:
December 2022
Publisher
Packt
Pages
294
ISBN
9781801072243

 

Introduction to Oracle's Autonomous Database

This chapter is an introduction to Oracle’s Autonomous Database (ADB). It explains the hardware architecture supporting this service. You will learn how it differentiates from traditional database deployments and the reasons to select ADB. We will explore various use cases for ADB along with business benefits in terms of Total Cost of Ownership (TCO)/Return on Investment (ROI), compared to traditional deployment.

With this chapter, you will build a solid foundation on ADB, which will be very useful later when you start architecting your application use case.

In this chapter, we will cover the following topics:

  • Learning what an Autonomous Database is
  • Technology building blocks – ADB
  • Classification of ADB based on workload
  • ADB infrastructure deployment choices – shared or dedicated?
  • Understanding why to use an Autonomous Database
  • Reviewing use cases for ADB
  • Understanding the business benefits of using ADB
  • BOM and SKUs for Autonomous Databases

By the end, you should have a clear understanding in terms of which flavor of ADB is good for your use case.

 

Technical requirements

For this chapter, although there are no technical requirements, if you are familiar with the Oracle Cloud Infrastructure (OCI) Console, you will be able to visualize the topics discussed.

 

Learning what an Autonomous Database is

Before we start learning about ADB, let’s first understand what OCI is.

OCI was designed to satisfy the needs of enterprise workloads that often require high performance, security, elasticity, availability, and integrity for their critical applications. Enterprises today want to lower their cost and move from a traditional CAPEX-based model to an OPEX-based culture. At the same time, they need a rich set of cloud services and automation capabilities built using cloud-native technologies to provide a comprehensive cloud solution for customers. OCI provides services around infrastructure, data management, analytics, applications, development/DevOps, governance, and security to cater to requirements from big to small enterprises. OCI is not just limited to Oracle’s data center but can also be extended to customers’ data centers; an offering called Cloud@Customer, which runs behind the company’s firewall, is available, and solves data sovereignty requirements. OCI is also available as dedicated regions for those workloads that require an in-country location or have data sovereignty requirements.

OCI’s ADB is a self-driving, self-securing, and self-repairing fully-managed database environment available on the cloud as well as on-premises. As of right now (the cloud is all about change), four distinct workload types are available with autonomous databases: Autonomous Transaction Processing (ATP), Autonomous Data Warehouse (ADW), Application Express (APEX), and Autonomous JSON Database (AJD).

You can build data-driven apps and gain operational insight in real time without worrying about the operational aspects of a database in terms of maintenance tasks such as backups, patching, upgrades, and performance tuning. You can scale the number of CPU cores or the storage capacity of the database at any time without impacting the availability or performance of the database system. With cloud-native developments and auto, we have highlighted and discussed auto scaling in detail in other chapters. Here, we give an overview of all the scaling features OCI provides and its automated responses to your workload needs.

ADB is built upon a very solid foundation with more than three decades of technical innovations developed by Oracle, providing customers flexibility combined with Machine Learning (ML) and Artificial Intelligence (AI). Oracle manages everything for you, so you can focus on your data, development, and delivering solutions that impact your business.

ML models and algorithms run inside Oracle ADB. It brings the following advantages:

  • Data stays in place
  • Massive parallel execution
  • Flexible model building

In addition to these benefits, ADB also supports key Oracle database features and open source programming languages:

  • Structured Query Language (SQL), R, or Python
  • Oracle Data Miner (ODM)
  • Oracle AutoML

With Oracle Machine Learning and Oracle ADB, users have a variety of options for building and deploying models involved in data science projects, whether they use in-database algorithms or open source Python algorithms. An autonomous database uses AI and ML to achieve a complete, automated provisioning experience, applying security, automated patch updates, continuous availability, and performance tuning based on the workload types; managing changes; and avoiding mistakes. Oracle Machine Learning for Python (OML4Py) in Oracle ADB upholds versatile in-dataset information investigation and arrangement utilizing local Python grammar, conjuring in-dataset calculations for model structure and scoring, and implanting the execution of client-characterized Python capacities from Python or REST APIs. Likewise, OML4Py incorporates the AutoML interface for automated calculations and component choice and hyperparameter tuning to augment model execution. On the other hand, ODM, which is an extension of Oracle SQL Developer, helps develop ML methods.

Let’s discuss a bit about security in autonomous databases. We will go through the details of it in Chapter 7, Security Features in Autonomous Database. All data in ADB is encrypted, and users or applications need to be authenticated in order to use the database. ADB does not require any manual configuration for providing encryption – whether data is at rest or in motion, all connections use certificate-based authentication over Secure Socket Layer (SSL). ADB enforces strong password complexity for all users based on Oracle Cloud Security standards. ADB provides a network Access Control List (ACL), using which databases can only accept connections from allowed IP addresses and reject all other client connections. ADB also provides network access through private endpoints that help organizations implement strict security mandates to only allow connections privately from inside a Virtual Cloud Network (VCN), and traffic never uses public subnet and public internet within your tenancy VCN.

Quick note

In OCI, ADW was the first offering launched in 2018. Later, ATP was added to the service portfolio of offerings at beginning of 2019. Recently, in August 2020, Oracle also added JavaScript Object Notation (JSON) databases to the Autonomous Database service catalog, known as AJD.

Quick note

You can think of a VCN as a private network set up inside an Oracle data center, which consists of several firewall rules and communication gateways. The components of a VCN are one or more subnets, Internet Gateways (IGWs), Dynamic Routing Gateways (DRGs), route tables, security lists, and DHCP options. When you create a VCN inside OCI, most of these components are created by default. Another thing to keep in mind is that a VCN covers a single, continuous IPv4 CIDR block of your choice. In other words, you can say that a VCN provides software-defined networking in OCI.

Always Free ADB

Always Free ADB is available through Oracle’s Free Tier, which provides customers with up to two instances of ADB (Serverless/Shared) for every tenancy. Always Free ADB supports both ATP and ADW workload types. Customers can upgrade a Free database to Paid anytime.

Quick note

You can sign up for an Oracle Free Tier account by navigating to https://www.oracle.com/cloud and clicking on the Try Oracle Cloud Free Tier button on the right side.

The key characteristics are as follows:

  • It has a fixed configuration: 1 OCPU, 20 GB of storage, and 8 GB of memory
  • Up to two Always Free instances in every tenancy’s home region
  • Most ADB functionality available, except Scale Up/Down, Storage auto scaling, Update License Type, Manual backup, and Restore
  • Upgrade an Always Free database to Paid anytime

Always Free ADB gets automatically stopped after 7 days of continuous inactivity. After 90 (cumulative) days of continuous inactivity, Free ADB instances are also automatically terminated. Users are notified via console UI banners for both of these events.

Always Free autonomous databases can only be created in your account’s home region as shown:

Figure 1.1 – The Always Free ADB option during deployment

Figure 1.1 – The Always Free ADB option during deployment

Customers can create autonomous databases quickly and easily using the OCI Console, Command-Line Interface (CLI), Software Development Kit (SDK), and Terraform. To create a database, customers can log in to their OCI Console and select Oracle Database | Autonomous Data Warehouse | Transaction Processing | JSON Database. You need to provide details such as the database name, the number of OCPUs, storage (in TB), and the admin password. In a couple of minutes, a fully ready autonomous database is ready for use by the customer. Customers can perform various management operations on their databases, such as starting, stopping, restarting, backing up, cloning, using Data Guard, and monitoring. Backups are automatic and the customer has the option to take a full backup anytime, as well as the ability to restore to a “point in time” backup. Backups are retained for 60 days by default and the customer can configure it to be more or less. The customer can scale their database CPUs and storage without any downtime. Using administration credentials, customers can access and start using the ADB service using a separate service console. You can also update admin credentials anytime.

Technically, ADB is built on OCI Exadata infrastructure. Each ADB database is an independent Pluggable Database (PDB) to which the customer doesn’t have host access. Oracle manages the entire life cycle activities of the database based on customer inputs and preferences. You can check the ADB page within Oracle Cloud Console as depicted in the following screenshot. It shows deployed ADBs within a region.

Figure 1.2 – ADB page on the Console

Figure 1.2 – ADB page on the Console

As we can see in Figure 1.2, a single page has both a shared and dedicated infrastructure link for the easy creation of these services and navigation capabilities.

 

Technology building blocks – ADB

Let’s see what makes an Oracle database autonomous. We will look at various building blocks for autonomous databases. You will notice that starting from Oracle Database version 9i, Oracle introduced several automation capabilities around memory management, workload monitoring, and self-tuning capabilities, which set the base for autonomous databases. With the acquisition of Sun Microsystems, Oracle drove a database infrastructure with engineered systems focused on more automation capabilities and bringing data processing to the storage layer, with innovations such as Smart Scan, query offloading, a storage index, columnar compression, and so on. These database platforms are preconfigured and highly optimized for running database workloads, pre-tested across thousands of deployments, thus forming the base for autonomous databases.

The ADB building blocks are as follows:

  • Oracle Database Enterprise Edition (DBEE)
  • Oracle Exadata Database Machine
  • OCI
  • ML
  • Oracle’s best practices
  • Oracle’s knowledge base

We will talk about each block in detail in the next sections.

Oracle DBEE

If you have prior knowledge of Oracle databases, you will already know that Oracle had two distinct editions of databases targeted for different market segmentation: a Standard edition and an Enterprise edition. As the Enterprise edition was built to suit the high-performance requirements of enterprise customers for transactional and analytical workloads, it has several features that make it enterprise-class. With traditional database deployments, the DBA needs to tweak several configuration parameters based on workload types, not just the database but also the Operating System (OS) and network configuration – everything that goes with any production-ready database deployment. ADB removes these complexities and comes preconfigured with optimal values based on deployment types.

Oracle DBEE sets the foundation for autonomous databases. The database options available with DBEE provide the required capabilities to run ADB. The following options give ADB autonomous capabilities:

  • Real Application Clusters (RAC): Provides high availability functionality, including scale-out architecture, failover in case of instance failure, and online patching to avoid downtime
  • Active Data Guard: Provides standby capabilities and is used for disaster recovery purposes
  • Parallel SQL: A core feature for prioritizing SQL’s parallel degree based on system resources and policies
  • Multitenant option: Provides the required functionality for Agile development
  • Database In-Memory: Provides high performance for analytic queries
  • Transparent Database Encryption (TDE): Part of the Oracle Advanced Security options – a default for data encryption
  • Database Vault: Segregation of duties running within a database kernel – used for compliance requirements and blacklisting and whitelisting of users and programs

Quick note – database parameters

All database parameters are set to optimal values based on workload type. Users can only change a limited number of parameters. These parameters are shown in the following screenshot.

Figure 1.3 – List of allowed parameters for modification in ADB

Figure 1.3 – List of allowed parameters for modification in ADB

As we can see, most of the changeable parameters are around the user’s profile, related to NLS, time zones, and so on. Oracle sets all other parameters to optimal values by default.

Oracle Exadata Database Machine

If you are new to Exadata Database Machine, you can consider it a combination of software and hardware optimized to run Oracle Database. The current version of Exadata is X9-M and it will be keep being updated based on the latest version. Normally, Oracle follows a cycle of 12 to 18 months to release a new generation of Exadata machines. The very first version of Exadata was released back in 2008. With each Exadata refresh cycle, customers get the most recent CPU processors, memory, increased disk capacity, flash, and high-speed networking components, which provide increased performance, security, availability, and management capabilities. Exadata is known as a great consolidation platform because of the massive capacity and performance available with these machines.

Oracle introduced a storage layer within the database machine, with several innovations supporting scale-out architecture, and parallel query operation, which greatly optimized data processing at its storage layer. Exadata solved two major problems: avoiding network bottlenecks for data movement within the machine through SQL offloading and, at the same time, providing a larger network bandwidth (100 Gbit/s) Ethernet fabric for data access. Exadata also provided separate Ethernet ports for data center connectivity and management operations such as backups. Some of the key innovations within machines can be considered Smart Scan, query offloading to storage, storage indexes, flash caching, resource management, Hybrid Columnar Compression (HCC), and in-memory database capabilities with fault tolerance.

Quick note – ADB platform

ADB runs on RAC on Exadata. ADB decides where to place each database during provisioning. A fewer number of instances are preferred when possible. Even though it’s running on RAC, the database can only be open on one node.

OCI

OCI provides required technologies such as networking elements, VCNs, subnets, virtual firewalls (network security groups), security lists, communication gateways, identity and access control, automated provisioning, logging, audit, monitoring capabilities, and so on, which are needed to run Exadata Cloud Service natively. OCI provides end-to-end security with a focus on the unified, automated, prescriptive security experience that makes life easier for customers. Identity management is a key focus for OCI, which helps simplify a customer’s security landscape, starting with data and then moving through the infrastructure, network, monitoring, and edge services. At the data or database layer, OCI supports encryption at rest and in transit and supports hardware security modules.

Within infrastructure, for compute instances, OCI supports hardened OS images, autonomous Linux, hardware root-of-trust, and signed firmware. In the networking domain, OCI supports isolated network virtualization with off-box Network Interface Cards (NICs), private networking with FastConnect, and security zones, which can be used to apply context-specific security policies to compartments. For monitoring, OCI has integrated Cloud Security Posture Management with Cloud Guard. Cloud Guard is very dynamic and OCI releases new services every week for customer needs. Recently, Oracle announced Scanning Service, which scans compute hosts and container images for vulnerabilities. The Bastion service automates the configuration of secure Bastion servers. The Certificates service automates the provisioning and management of private and public certificates. Threat Intelligence Service centralizes threat intelligence and vulnerability feeds integrated across cloud services.

Oracle best practices

Every organization emphasizes adopting best practices, Oracle has published several “best practices,” which are based on expert recommendations for deploying a product, fine-tuning, configuration changes, and so on. In addition to this, Oracle’s Maximum Availability Architecture (MAA) focuses on best practices for the availability of applications based on the categorization of Service-Level Agreements (SLAs). Oracle has a set of best practices around security called defense-in-depth. Best practices allow the Oracle database to run with optimal efficiency. Oracle uses several of its core features to provide the required level of optimization; using technologies such as online reorganization allows online operation for table redefinition without compromising the availability of the system. Using Resource Manager, Flashback Technology, Application Continuity, and Transparent Application Continuity protects against several kinds of failures. RAC protects against node failures, and this also enables rolling patches, service draining, and zero-downtime planned maintenance. Application Continuity protects transactions from failures, allowing safe transaction replay using a JDBC replay driver and Transaction Guard. Using best practices ensures that Oracle technologies achieve the highest level of performance, availability, and security.

Oracle knowledge base

Oracle has a knowledge base of several years built from a diverse set of customer issues, whether service requests, product management contributions, development experiences, bugs reported by customers, or enhancement requests for features and services. The Oracle system for support tickets, known as MOS, is an interface for Oracle and customers that allows them to open up support tickets in case they need help. Customers can visit MOS to explore several knowledge base articles, how-tos, and so on. As part of problem diagnostics and resolution, customers often provide logs, screen shares, diagnostic collection, OS details, trace files, and so on. These files are a great source of information for Oracle for using these as input to build AI and ML for intelligent data analysis and problem-solving.

ML

ML is an important function in the autonomous database, where the database uses ML algorithms and automates the most important aspects of the database, such as security, database backups, patching, performance tuning, index creations, and several routine tasks that are typically performed by a DBA. It has increased productivity, as no human intervention is needed, thus freeing up time for other inventions. OCI also provides an ML platform as a service that can be used by customers to implement their own solutions while using ADB as the database of choice. ML is also a set of tools available in Oracle Cloud that customers can use to implement their own solutions.

We have learned about the different building blocks for ADB, so now it is time to look at the classification of ADB based on workload characteristics. OCI has tailored these databases based on types such as DWH, OLTP, and JSON.

 

Classification of ADB based on workload

With the optimization and integration of hardware and software capabilities available across the stack in database machines, along with Oracle Enterprise edition databases, Oracle offers three distinct flavors of ADB for running your workload: ATP, ADW, and AJD.

Oracle ADW is designed to run data warehousing, data marts, data lakes, analytics, and ML workloads. Oracle ATP is designed for online transaction processing, batch, reporting, the Internet of Things (IoT), application development, ML, and mixed workload environments.

In the following sections, we will discuss each flavor in detail.

ADW

This is the first offering available with OCI in the ADB service portfolio. As the name indicates, Oracle ADW is designed for data warehouses and related workloads, including data marts, data lakes, and ML workloads. Most organizations architect analytical workloads to run on a separate system other than their OLTP systems, as the requirements for these systems are different and widely used for decision-making and data analytics business use cases. Data warehouses are characterized by star schemas and snowflake schemas and normally have very high data ingestion rates. As part of the data warehousing requirements, facts are often derived from several dimensions, and keeping aggregated data is often considered as summary tables for data analysis. This system demands a high level of parallelism for running SQLs as well as a faster response time to serve business users. Oracle ADW is specifically designed to provide faster response times to queries and desired level of parallel processing for data ingestion.

Quick note

ADW optimizes complex SQL. It uses the columnar format and creates data summaries. Optimizer and PARALLEL hints are ignored in ADW. Users can override this behavior by changing two parameters, optimizer_ignore_hints and optimizer_ignore_parallel_hints, to FALSE, which, by default, are usually set to TRUE.

Optimizer statistics: Stats are gathered automatically for direct load operations. If your workload uses conventional DML in ADW, gather stats manually with the GATHER AUTO option. For example, see the following:

BEGIN

DBMS_STATS.GATHER_SCHEMA_STATS('SH', options=>'GATHER AUTO');

END;

/

ATP

Oracle ATP is designed for online transaction processing and workloads that are not data warehousing-related. ATP is primarily suited for mission-critical transactional workloads that often include operational reporting or batch data processing. With ATP, you can run mixed workloads in a single database, which eliminates the need to segregate transactional data from analytics data. Users can run their mixed workload in the same system without worrying about any potential data management options. ATP also supports the IoT and ML, in addition to OLTP workloads. ATP makes application development much simpler, as there is no need for traditional data management skills for someone to get started with these services.

Quick note

ATP optimizes the response time for SQLs. Data is stored in a ‘ROW’ format and creates indexes as required automatically. Optimizer and PARALLEL hints are honored in ATP and are set to TRUE by default. Users can override this behavior by changing two parameters, optimizer_ignore_hints and optimizer_ignore_parallel_hints, to TRUE, which are set to FALSE by default. This will disable both the default behavior and the setting.

ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=TRUE; 

ALTER SESSION SET OPTIMIZER_IGNORE_PARALLEL_HINTS=TRUE;

Optimizer statistics: ATP gathers stats with a nightly auto stats job. Real-time statistics collection gathers a subset of optimizer statistics for conventional DML operations: number of rows, MAX and MIN column values, and so on. High-frequency statistics collection gathers full optimizer statistics every 15 minutes if statistics are stale.

AJD

Oracle AJD is a new cloud service launched by Oracle around mid-August 2020. It is built for organizations and developers who want to build interactive applications and microservices that primarily deal with JSON data without compromising scalability, availability, performance, full ACID support, and complete SQL functionality. With cloud-native development all around, JSON is becoming a more and more popular choice to store data, as it can be easily consumed by several programming languages and provides a persistent format for application objects – another reason being that JSON is schema-flexible, so applications can change over time to accommodate new types of data without having to modify backend data definitions. This lets you quickly react to changing application requirements without requiring you to normalize data into relational tables and with no restriction to changing data structure or organization at any time.

With AJD, your JSON document-centric applications typically use Simple Oracle Document Access (SODA). SODA is a set of NoSQL-style APIs that help create and store collections of documents in JSON format and eliminate the need for SQL expertise for retrieving and querying JSON data. SODA collection APIs are exposed in several forms:

  • Database tools, SQL Developer Web, and SQLcl
  • SODA REST services
  • Programming language drivers for Java, Node.js, Python, C, and PL/SQL

Quick note

AJD is similar to ATP with largely equivalent functionality and the same performance characteristics.

AJD provides all of the same features as ATP but with important limitations as you can only store up to 20 GB of data other than JSON document collections. There is no storage limit for JSON collections though. This could be a possible reason why AJD is offered at a lower price than ATP.

You can promote an AJD service to an ATP service to remove the 20 GB restriction on non-JSON data. You can not convert AJD to ATP, however.

AJD uses document-based databases and provides most of the same benefits that are typically associated with NoSQL document stores:

  • High availability and performance at scale: Transparently scale the compute and storage capacity of your database while maintaining millisecond latencies for reads and writes.
  • Simple document APIs: The SODA APIs make it easy to store JSON natively in the database. Using these APIs, you can build an entire application without having to write SQLs.

As I said, AJD is much more than a simple document store. It provides a rich set of features that are typically not found in NoSQL databases.

  • Automatic administration and performance tuning: Routine database administration tasks, such as provisioning, performance tuning, encryption, patching, and taking backups, are performed automatically, so you can focus on developing your application.
  • Full SQL query support: Natively-stored JSON using the document store APIs is fully accessible using ISO standard SQL. With AJD, you can use SQL to perform real-time analytics over JSON collections. You can also create real-time relational views over JSON to expose collections to existing relational tools and applications.
  • ACID transactions: AJD supports robust transactions over JSON collections. ADB follows ACID protocols for JSON datatypes, like other RDBMS workloads, and makes it easy to perform complex operations over multiple collections atomically.
  • Advanced security: Encryption and data-safe options are available with AJD.
  • Supporting tools and services: AJD comes with a number of supporting services for processing and accessing data, including the following:
    • Oracle REST Data Services (ORDS): This can be used to build custom REST services over your JSON data.
    • APEX: This is used for building low-code applications over JSON collections.
    • Oracle Machine Learning Notebooks: This enables data scientists and data analysts to explore JSON data visually and develop analytical methodologies.

Keep one thing in mind: any SODA collection within AJD will have only JSON data. It cannot be mixed with LOB documents, unlike ATP databases.

Autonomous Data Guard is available for AJD. A standby database can be enabled either in a local region, cross-region, or both based on availability requirements. With Autonomous Data Guard, both the primary and standby (local or remote standby) databases are monitored for transactions and take the following actions:

  • In case of the failure of the primary database, the standby database is converted into the primary database without user invention and with minimal interruption. Once failover is completed, a new standby database is automatically created by Autonomous Data Guard.
  • Additionally, application or database admins can perform a manual switchover operation to convert the primary database into a standby database and vice versa.

Note that this is for shared infrastructure ADB only.

Now, since we have already looked at the classifications of ADB based on workload types, we will explore which infrastructure option is appropriate for your workload in terms of deployment.

 

ADB infrastructure deployment choices – shared and dedicated

Autonomous databases can be deployed on either the shared or dedicated Exadata infrastructure available in OCI. Shared Exadata infrastructure provides the smallest minimum commitment to get started, while dedicated Exadata infrastructure provides customers with more control over infrastructure operations, such as controlling the software version and update schedules.

There are several considerations for deciding which one to pick. We will go through them later in the chapter. For now, we will try to understand these services’ classifications on a high level.

Shared

The shared deployment offering is a fully managed multi-tenant environment on a shared infrastructure, powered by Exadata-engineered system hardware on the backend. This environment is shared with several customers but Oracle makes sure you get the needed isolation in terms of resources to run your workloads, such as CPUs and storage. The provisioning is super simple to deploy with very minimal input and customers can get started immediately upon provisioning. It’s also an effort to standardize the database environment for customers, including patching, without worrying about multiple life cycle environments, such as database software, versioning, environment separation, and so on. You can see that this environment is very simple to use, is commercially attractive, has no major commitment, has a flexible pricing model, which uses per-second billing; and can be configured for automatic scaling on demand.

Shared infrastructure is great for customers who want to be database users without worrying about any database operations, including software updates. Oracle has tried to bring uniformity, simplicity, and standardization, which can be considered the key to extreme scalability and performance. It is an ideal choice for a line of business, departmental applications, or data marts, as well as making an excellent environment for developers or data scientists. You can start to deploy your workload in a shared offering of ADB and transition to a dedicated Autonomous Database when you are done with all sorts of migration, testing, and user acceptance.

Quick note

For ADB on shared infrastructure, the minimum database version is 19c.

Dedicated

With dedicated infrastructure, customers have their own dedicated Exadata infrastructure in OCI. It is a little more customizable than the shared infrastructure offering and intentionally created by Oracle to cater to those customer bases that require more control for IT, DBAs, and storage admin groups within an organization. The capacity of dedicated infrastructure is controlled by customers for their workload. This offering provides completely isolated environments to customers, which could be a requirement for them. It provides an opportunity to create one or more container databases hosting thousands of PDBs within it.

The overall idea behind the dedicated deployment model is its customizable policy for the databases. The question comes – why does Oracle allow customization to this environment? To understand the answer to this, consider customer bases that are using Oracle databases for their workloads. There are customers with thousands of databases and several life cycle environments to manage. Not only that but certain applications could also be sensitive to database versions. Customers also want to control the update frequency, timing, and segregation of databases based on their behavior and workload types, which demands control of the database infrastructure. Consolidation is another important factor that is considered in dedicated offerings. These customizations provide flexibility to customers – yet Oracle manages operations that are repetitive in nature, such as backup, provisioning, patching, and so on.

Quick note

For autonomous databases on dedicated infrastructure, users have the ability to choose to patch Exadata infrastructure or container databases immediately via the Patch Now option. They can also reschedule an already scheduled Exadata infrastructure maintenance via the Edit options.

The following screenshot shows the flexibility of controlling the maintenance schedule in a dedicated infrastructure deployment. Customers can control this behavior based on their operational requirements.

Figure 1.4 – Maintenance schedule option for ADB

Figure 1.4 – Maintenance schedule option for ADB

A dedicated ADB offering is available for both ADW and ATP workloads. A customer can have a dedicated ADB deployment and have either or both of these workloads running within it based on requirements. You can consider this as your own infrastructure running in the Oracle public cloud with services that are most concerning automated, such as provisioning, performance tuning, scaling, patching, database encryption, and backup and restore. As you know, Oracle Database has several options, such as RAC, Database In-Memory, Partitioning, Advanced Compression, and so on, to name a few, which are available within ADB offerings. Apart from this, additional Enterprise Manager Packs, such as Diagnostics Pack, Tuning Pack, RAC, and so on, are available within an ADB deployment.

There are broadly three logical roles that are involved in setting up a dedicated Autonomous Database: fleet administrators, DBAs, and database users. Depending on the separation of roles required for the deployment, you can have a specific role assigned or be part of multiple roles. Fleet administrators are mostly related to management aspects, such as provisioning the infrastructure, managing container resources within monitoring, and managing VM clusters within ADB dedicated on Exadata. A DBA, on the other hand, is responsible for creating, monitoring, and managing Autonomous Databases. These administrators have the ability to create application users, do backups, and restore configurations. The third type of user, database users, are mostly developers who are focused on application development and concerned about making a connection to the database for their work and queries. These users need not be part of Cloud Console users, as they can get connectivity information and required security wallets from an administrator in order to make database connections.

Quick note

ADB dedicated provides 99.995% SLA, which translates to <2.5 minutes of downtime per month. It is fully isolated from other tenants, as the VCN is hardware-enforced.

ADB on Exadata Cloud@Customer

As we saw with a dedicated offering for ADB running in the Oracle public cloud, this Cloud@Customer offering is different in the sense that ADB runs at a customer data center, behind their firewall. It also uses the same Exadata Database Machine infrastructure with networking configuration allowing Oracle to manage from the Oracle Cloud control plane. This option was brought by Oracle for customers who have strict data sovereignty requirements.

The most common reasons for using the Exadata Cloud@Customer (ExaC@C) model include the following:

  • Corporate policies and regulations
  • Network latency
  • Integration needs
  • Compliance and risk

Sometimes, it is hard for companies to move their workload to the public cloud, mostly because of country laws related to handling data. System integration needs could be more complex where several applications, hardware, and so on need to be tightly integrated. Network latency requirements also play a major role. Adopting a hybrid cloud strategy is another consideration, which plays a major role when considering cloud adoption. By keeping these in mind, Oracle came up with this offering under ADB on its engineered system platform for customers. Certain key facts about dedicated ADB deployments are listed here:

  • The database offering named Oracle ExaC@C is not very new and was already announced in 2017. This option provided cloud flexibility to customers who were not able to move to the public cloud.
  • With an ADB offering, Oracle was able to bring cloud self-service and a pay-per-use financial model to customers.
  • Customers get the full advantage of ADB along with the benefits of dedicated infrastructure in their own data center.
  • You can run a mix of Autonomous Transaction Processing-Dedicated (ATP-D) and Autonomous Data Warehouse-Dedicated (ADW-D) databases on the same dedicated ADB Exadata rack.
  • Services can be managed via the OCI Console, the CLI, and APIs.

Quick note

There are a few key differences around networking configuration and backup support when you compare dedicated ADBs in the public cloud versus Cloud@Customer. On OCI, networking is configured via a VCN by the customer, whereas, on ADB ExaC@C, it requires connectivity to the cloud control plane and the Exadata system primary and client networks to be set up, which the Oracle field engineer will validate on-site.

  • Backup locations: On OCI, only object storage is supported. On ExaC@C, customers have the option to back up to object storage, local Exadata storage, their own network-attached storage (NFS), or Zero Data Loss Recovery Appliance (ZDLRA), also known as Recovery Appliance (RA).

Quick note

ZDLRA is Oracle’s optimized solution for database backup and recovery. This RA changes the way backups and recovery are carried out in any traditional database deployment. It enables incremental forever backups and efficient any Point-in-Time Restore (PITR). The purpose of this appliance is to enable zero data loss functionality for RA backup destinations. The customer has the ability to choose to enable Real-Time Redo Transport in RA-configured environments, which enables the database to automatically ship all redo logs in real time to RA for a zero data loss (less than 1 second) recovery setup.

I believe that now that we have looked at every classification of ADB in terms of infrastructure type, you can make a decision based on your workload requirements to either go with shared or dedicated options. Let’s try to understand some of the merits to consider while considering this move with ADB.

 

Understanding why to use an Autonomous Database

There has been a major change starting with Oracle Database 9i, where Oracle started to introduce and enhance many automation capabilities, from memory management to workload monitoring and performance tuning. All of these set a solid foundation for autonomous capabilities and are used in ADB. Not only database automation but also database infrastructure with engineered systems was invested in and brought in by Oracle, which is considered the best platform for the Oracle Database workload, as these systems are preconfigured, pretested, and optimized converged platforms for the database. Take a look at the following automation capabilities over the span of a decade to get a view of the enhancements in Oracle Database:

Figure 1.5 – Depicting Oracle Database automation over the years

Figure 1.5 – Depicting Oracle Database automation over the years

In addition to these innovations, Oracle has spent more than 10 years automating the database infrastructure:

Figure 1.6 – Depicting Oracle’s infrastructure automation over the years

Figure 1.6 – Depicting Oracle’s infrastructure automation over the years

Decades of innovation in the ML capabilities of Oracle Database

After all these innovations, let’s see how the Oracle database uses ML and AI right inside the database.

In Dec 2019, Oracle Machine Learning (earlier known as Advanced Analytics) became a free feature of every Oracle database. Oracle Machine Learning extends Oracle database(s) by delivering more than 30 in-database ML algorithms and automated ML functionality via SQL APIs (OML4SQL) and integration with open source Python (OML4Py) and R (OML4R). Oracle Machine Learning “moves the algorithms, not the data,” processing data where it resides. It further enhances the benefits of Oracle’s multi-model converged architecture by supporting various data types and data models such as Spatial, graphs, XML, JSON, and so on. Through innovation in infrastructure software and various algorithms for ML and statistical functions, Oracle optimized the platform to run different workload types, such as OLTP and DSS workload types. Without additional cost, customers can take full advantage of the enterprise-class performance, reliability, and security of Oracle Database for particular use cases, such as the following:

  • Processing and analyzing all types of spatial data in business applications, Geographic Information Systems (GIS), and operational systems
  • Using graph analysis to discover relationships in social networks, detect fraud, and make informed recommendations
  • Building and deploying ML models for predictive analytics

Let’s take a look at various capabilities related to the uses of ML in ADB:

  • ML is integrated across the database stack and infrastructure, resulting in the autonomous capabilities of the full Oracle stack being leveraged.
  • By introducing ML, different personas such as data scientists, data analysts, developers, and DBAs/IT benefit.
  • Complete stack intelligence eliminates expensive data movement and minimizes access latency between systems.
  • Data exploration, data preparation, and ML algorithms are faster. More than 30 algorithms that support regression, classification, time series, clustering, feature extraction, and anomaly detection are included.
  • ADB supports open sources, such as R and Python integration, useful for a data scientist.
  • You can develop an ML model and R/Python with the provided tools.
  • ADB automates key ML processes, such as workload patterns, auto-indexing behavior, SQL profiles, and so on.
  • ADB supports existing enterprise backup, recovery, and security mechanisms and protocols.
  • Most importantly, it brings the algorithms to the data.

With Oracle Machine Learning, along with Oracle Database and infrastructure capabilities, Oracle has evolved as a fully converged data management platform.

For any modern organization, databases play a critical role in storing important application and business information and are essential for the efficient operation of an organization. Databases are managed by DBAs and they are often occupied with operational things such as backups, patching, and performance tuning, and spend the majority of their time on this kind of manual task to make sure the database is up and running. Any DBA errors because of manual work can lead to a serious impact on database availability, performance, and security.

On the other hand, today, several data architecture challenges exist – several customers try to experiment with many different kinds of databases, such as relational, document store, graph and spatial, time series, NoSQL, and Big Data analytics to name a few. It brings several administration challenges:

  • Hard to enforce consistency of data across applications
  • Hard to do consistent backups for different databases
  • Issues with cross-database compatibility
  • Separate security patches for every database
  • Separate upgrade cycles for databases
  • Varied level of performance levels across databases
  • Varied availability – different ways to implement disaster recovery
  • Complex integration for analytics
  • Hard to match varied skills for different databases

These challenges can be easily addressed with ADB because of its unified, simplified, and standardized approach.

If we explore it further, we see that failing to apply a patch or security update can leave open vulnerabilities in databases. With massive data growth, it brings more complexity, making them even more difficult for DBAs to manage, secure, and tune for maximum performance. Needless to say, databases that are not performant or slow-running can negatively impact employee productivity and impact customers. Poorly designed disaster recovery plans can lead to huge business impacts.

ADB uses ML in three key areas of ADB: diagnostics, recovery, and optimizations for each layer of the deployment stack, each respectively corresponding to the following:

  • Inside database infrastructure, ADB automatically detects and recovers from failed servers, storage, or network switches or links
  • Automatically detecting an anomaly or hangs in the database behavior, comparing these behaviors with known issues, and automatically identifying known issues and fixing them or automatically opening a Service Request (SR)
  • Automatically optimizing customer workloads with real-time statistics and automatic indexing

So far, we have explored the generic benefits of using ADB.

Because ADB runs on Exadata systems, RACs are also provisioned in the background to support the on-demand CPU scalability of the service. This is transparent to the user and administrator of the service. For all ADB offerings, there is an option of creating an optional remote standby database (Autonomous Data Guard) for automatic failover capabilities and disaster recovery purposes.

As mentioned, ADB runs on Exadata systems, but no Exadata installation, configuration, or management needs to be done or can be done. The init.ora parameters (database initialization parameters) are configured automatically in an Autonomous Database depending on the service selected – ADW, ATP, or AJD. Memory, parallelism, concurrency, number of sessions, and other parameters are automatically configured based on the number of CPUs allocated to the service. Most of these parameters cannot be modified, and the few that can be modified should only be done for very specific reasons by qualified DBAs. This is discouraged in ADB, as it defeats its purpose.

Quick note

When an Oracle instance starts, the very first file it reads is initialization parameters (init.ora) from an initialization parameter file. As of today, customers do not have the capability to set database parameters or resource manager configs while creating ADB instances or modifying them via the Console, APIs, or an SDK after the instance is created. All parameters are set to optimal values based on the specific workload type and this may be different from regular database defaults. The ATP and ADW have two different resource management plans. The ADW plan name is DWCS_PLAN and the ATP plan name is OLTP_PLAN.

Tablespace management is performed automatically by ADB and cannot be changed by the customer. Customers have full access to view the information of the space allocated to their instance, but it cannot be changed. The only input the customer needs to provide is the number of terabytes of data they would like the database to be able to hold. This number can be increased or decreased in real time. ADB handles adjusting the data location based on this user setting. The default tablespace is the same for ATP and ADW.

Loading and maintaining data in ADB can be done as one-time loads, best when staged through Oracle Object Store, or as continuous data ingestion or synchronization with other sources. ADB supports three object stores and can read and write directly to these three. The supported object stores are Oracle Object Store, Amazon S3, and Azure Object Store. Object stores are ideal for staging export dump files that are going to be imported into the Autonomous Database.

The same applies to flat files that would be loaded into the database. An ADB supports the Oracle Database external tables feature – so flat files on the object store can act as autonomous external tables. Please note that it is best to host these tables on Oracle Object Stores that are fast and connected to the Autonomous Database to reduce latency and other issues around access time to database objects.

Also available for transactions and data work location in real time or near real time, or to maintain synchronized copies of databases, is Oracle GoldenGate, which can be configured with an Autonomous Database as a target database. This allows ADB to become a full replica copy of another database for uses such as reporting, disaster recovery, development, testing, and QA. Once a decision is made to move the database to ADB services, the next step is to determine what to do with the application accessing that database.

Just like rehosting the database in the cloud, rehosting the application to the cloud may have its own benefits. If the application using the Autonomous Database is an existing application, there are two preferred options for hosting the application. The first option is to keep the application in its existing environment and replace the existing database with access to the Autonomous Database. The second option is to rehost the application to OCI. Rehosting the application may be straightforward or may require substantial reconfiguration.

Autonomous Database value proposition

OCI provides the required elasticity, agility, security, and global reach, and helps customers focus on their workloads and not the infrastructure. With OCI DbaaS, OCI is helping customers lift and shift their on-prem database workloads to the cloud. While OCI continues to add more tooling to the OCI DBaaS, customers want complete automation and no management overhead. They want their database service to be fully managed, where routine tasks such as patching are automatically done, backups are available to customers when they need it, systems scale automatically to workload requirements, and much more. ADB services such as ATP, ADW, and AJD aim to fully manage customer databases based on customer preferences. All exceptions and failure cases are automatically managed by Oracle.

The key principles of the ADB service are as follows:

  • Self-driving: Customers define the service level and ADB makes it happen
  • Self-securing: The service protects against external attacks and malicious internal access
  • Self-repairing: The service enables higher availability with automated protection from downtime

The key customer benefits of ADB services are as follows:

  • Reduced services costs and increase productivity for customers.
  • Reduced admin costs, with complete automation of operations and tuning.
  • Reduced runtime costs by dynamically adjusting resources and eliminating underutilization.
  • Deploying new apps in minutes, with faster Time to Install (TTI) and faster Time to Deliver (TTD).
  • Reduces the cost of downtime – less than 2.5 minutes per month.
  • Elastically grows and shrinks compute or storage without downtime. Pay only for what you use.
  • Eliminates human labor to provision, secure, monitor, backup, recover, troubleshoot, and tune.
  • Automatically upgrades and patches itself while running. Testing automation ensures changes are safe.
  • Protection from external attacks and from malicious internal users.
  • Protection from attacks by automatically applying security updates with no downtime.
  • Automatic encryption of all data.
  • Most reliable.
  • No human labor, and hence no human error
  • Zero downtime with scale on-demand.
  • SLA 99.995% guarantee available with the Active Data Guard (ADG)- Dedicated option. SLA 99.95% guarantee available with the ADG-Shared option.
  • Automation eliminates administrator errors.
  • SLA guarantees 99.995% availability. < 2.5 minutes of downtime per month, including planned maintenance.

So far, we have seen how Oracle Database has evolved over time to bring automation capabilities along with innovation in infrastructure and this innovation is continuing with each release. The current release of the Exadata platform, X9M, is the fastest database machine engineered to run ADB. Now, let’s review a few use cases and considerations for ADB.

 

Reviewing use cases for ADB

Customers who want a highly performant database service capable of automatically managing life cycle operations would use ADB. Things to remember – ADB provides no host access and no OS customization capabilities. Customers who are looking for custom requirements and host access will use OCI DbaaS services, where customers can choose from VMs (DbaaS VM images), BM options (DbaaS bare metal services), and Exadata shapes, which include Exadata Cloud Infrastructure (ExaCS) and ExaC@C deployments within the Oracle Cloud Infrastructure portfolio of services.

Let’s discuss having no access to the host OS and no OS customization capabilities a bit. This means a lot when it comes to operational efficiency and standardization. No root or sysdba logins are allowed – the only logins allowed are admin, privileged default ADB user, or regular database user. It means no call-outs to the OS – thus, preventing installation or modification of any software on the system. It is sealed from external administrative access and powered by AI. ADB eliminates any possibility of misconfiguration, database vulnerabilities, malicious activities, and human errors. Database clients can connect securely using a TLS wallet. Oracle manages all the operational aspects to make sure you focus on your application and leave SLAs, patching the OS, database upgrades, security, and several performance-tuning aspects to be part of the autonomous capabilities of the offering.

Some of the stats on database manageability are as follows:

  • More than 39% of DBAs handle 50 or more databases (source: From Database Clouds to Big Data: 2013 IOUG Survey on Database Manageability)
  • Challenges with the typical patch management process – it’s complex, time-consuming, and involves dependency on multiple stakeholders
  • Most companies face high downtime because of a lack of standardization across database fleets within an organization
  • Building high availability for databases takes significant time, effort, and expertise, and an enterprise needs it at the click of a button

Quick note

The Independent Oracle Users Group (IOUG): The major focus of the IOUG is on Oracle technology and database advocates. It promotes empowerment through education so that the users can be more productive in their work related to these technologies and also help take better business decisions through providing technology direction and networking opportunities, sharing best practices, and delivering education.

Now, let’s go over some typical considerations that a customer can evaluate when implementing an Autonomous Database. Unlike on-premises deployments, many steps are not needed with Autonomous Databases and because of this, it is easy to deploy as well. Still, there could be several considerations, such as the level of automation and functionality required, workload characteristics of databases such as ATP, ADW, or AJD, provisioning and loading data to Autonomous Databases, connecting your applications to them, and many more.

Oracle ATP supports all operational business systems, including both departmental as well as mission-critical applications, but unlike other cloud providers, ATP doesn’t just support one transaction processing use case; it can also support mixed workloads where you have a mixture of transaction processing, reporting, and batch processing, making it the perfect platform for real-time analytics based on operational databases. This enables users to get immediate answers to any question.

Integrated ML algorithms make it the perfect platform for applications with real-time predictive capabilities. Advanced SQL and PL/SQL support make it the perfect platform for application developers, as developers can instantly create and effortlessly use ATP, eliminating any dependence and delays on others for hardware and software. The fact that it’s self-tuning also eliminates any need for database tuning and accelerates developer productivity. With the availability of APEX within ATP, you can deploy applications faster for their use case or modernize legacy applications with them.

Oracle ADW supports all types of analytical warehouses and decision support database workloads. ADW is particularly well-suited to creating new dependent or independent data marts that allow analytical projects to be started easily. It is a good environment for sandbox experimentation on the part of data scientists, sifting through data and storing large amounts of data and data lakes. It includes analytics and visualization tools, Oracle Machine Learning and Oracle Data Visualization Desktop, and provides an end-to-end environment for application development, data analysis, and fast, flexible database services.

 

Understanding the business benefits of using ADB

Now that you have an understanding of Autonomous Databases and are considering migrating your database workload to the ADB platform, the very next thing that comes to your mind is what the business value is – potential savings, TCO, ROI, and more. Well, all these are valid concerns, as technical merits are not the only decision-making factor when considering the cloud as a platform for workload migration. You might be interested in comparing the cost of business as usual versus operating from the Oracle cloud. You might need to create a TCO/ROI analysis before a decision can be taken by the LOB or senior management. Oracle’s Data Management Cloud Services team provides a tool to provide guidance in this regard. A TCO calculator was made publicly available by the Oracle team to help organizations or individuals see a detailed breakdown of the potential cost savings that they would realize if they moved their database workload deployments to Oracle’s Database Cloud Service. The TCO calculator provides a side-by-side comparison to an equivalent on-premises deployment or competitive deployments, as well as a detailed report of potential cost savings in terms of compute, storage, software, and facility expenditures. We will look at the steps to calculate a TCO once we understand the various metrics that impact the TCO.

We can look at several metrics that can help you understand TCO comparisons with any traditional deployment or other cloud vendors:

  • Improved business performance gains – operating margin improvement due to targeted end process (business) improvement(s)
  • Improved DBA and system administrator productivity
  • Reduced development cycle time
  • Reduced IT infrastructure acquisition
  • Reduced IT infrastructure maintenance and support
  • Reduced non-compliance risks – data
  • Reduced planned and unplanned downtime
  • Reduced security and data breaches

We will discuss some of these benefits in detail in the next sections.

Improved business performance gains

Autonomous Databases can help drive business improvement efforts:

  • Customers focus on their data and application without worrying about any configuration in terms of infrastructure or database software such as RDBMS and Grid Infrastructure software. ADB automatically handles creating the ADW, ATP, or AJD offering (for example, the compute, storage, and network configuration), securing data (for example, encryption by default at rest and in transit), backing up the database, patching and upgrading the database without downtime, and scaling the database up or down (without impacting system performance or customer experience). As a result, customers can quickly deploy the database while freeing up technical staff to assist with business insight (rather than maintaining infrastructure).
  • Second, Autonomous Databases can easily integrate into a variety of on-premises, cloud, and hybrid systems, allowing firms to securely consolidate information for analysis and reporting. Additionally, ATP also supports both relational and non-relational data models to reduce data fragmentation and management issues resulting from siloed data stores.
  • Finally, on-demand scalability lowers the threshold for starting and growing data warehouse projects, so proof-of-concept and pilot projects can occur without major financial investments and related approvals. ATP provides in-database ML algorithms to make it easier to build ML models and analytical dashboards without moving data out of the database, resulting in improvided business insight.

Improved DBA and system administrator productivity

By design, Autonomous Databases can increase DBA productivity as follows:

  • With ADB, customers do not need to configure or manage any hardware or install any software. Database creation, backups, patching, security/encryption, and scaling up or down all are automatic. For example, all OS and SYSDBA operations are carried out automatically, settings are tuned, and errors are diagnosed and remediated. By continually monitoring the cluster for unusual changes, Oracle can compare any anomalies against known issues and automatically apply a fix (or create a service request and escalate as appropriate).
  • ADB provides automatic downtime protection with high availability built into every component and proven Oracle database technologies (for example, RAC, Exadata, and so on) and HA features such as Autonomous Data Guard and cloning.
  • ADB does not require manual tuning. Whether it is ADW or ATP, customers just need to load the data using a traditional method, such as Data Pump or GoldenGate, and they can get going with application queries without worrying about specific partitioning schemes, parallelism, indexing, compression algorithms, and so on. Based on the workload behavior (ADW or ATP), the database is automatically configured for high performance, leveraging ML at the backend. ADB continuously optimizes memory, data formats, indexes, parallelism, queries, and so on for each workload using ML.
  • ADB offers dedicated cloud-ready migration tools for easy migration from Amazon Redshift, SQL Server, and other databases. As with other Oracle cloud solutions, ADB is fully compatible with existing Oracle on-premises data management workloads, so customers can leverage existing infrastructure and knowledge.
  • Best practices for performance, availability, and security can be consistently and automatically implemented from the beginning.

Reduced development cycle time

Autonomous Databases help reduce development time:

  • Architecture is the same for both the cloud and on-premises; there is also support for familiar development and administrative tools, such as Oracle SQL Developer, Data Pump, and SQL*Loader, so developers don’t need to learn about new ones when they use ADB.
  • Customers get full interoperability with their on-premises Oracle databases, as well as integration with other Oracle (for example, Analytics Cloud) and third-party cloud services (for example, Amazon, Azure, various BI/analytics tools, and so on); migration tools are also provided for all major database providers. ADW supports multi-model data and workloads (e.g., analytical SQL, ML, graph, and spatial). ATP supports both relational and non-relational data models, along with Oracle’s low-code application development platform (APEX).
  • Autonomous Databases do not require any tuning. ADB is designed as a “load and go” service: you start the service, define tables, load data, and then run queries. Customers do not need to consider any details about parallelism, partitioning, indexing, or compression. The service automatically configures the database for high-performance queries with ML optimizations.
  • Customers can work directly with their data by connecting via any of the available Oracle client language libraries, including Oracle Net (SQL*Net), JDBC, and other drivers.
  • The ability for developers and related groups to autonomously provision and elastically scale databases, rather than waiting weeks or months for traditional infrastructure acquisition, installation, and provisioning, can significantly improve productivity.

Reduced non-compliance risks – data

Autonomous databases provide enterprise-class security:

  • An autonomous service, so it always runs the latest security patches and avoids the inevitable delays when administrators try to maintain hundreds or thousands of servers.
  • Data is automatically encrypted by default in the cloud, as well as in transit and at rest, with customer-controlled keys. Data at rest is encrypted by default using TDE, and only authenticated users and applications can access the data when they connect to the database. Connections to ADB are made via SQL*Net. TCP and TCP with SSL security protocols are supported methods. TCP with SSL uses certificate-based authentication and the SSL security protocol. Using this mechanism, ADB ensures secure communication between the Oracle database and any clients that are connected to it and prevents any breach of the confidentiality of data during communication because of the encryption in place.
  • ADB uses strong password complexity rules for all users based on Oracle Cloud security standards.
  • Oracle’s autonomous cloud services offer adaptive intelligence-enabled cyber threat detection and remediation.
  • Administrative access privileges are greatly reduced, increasing resiliency against human errors, malicious insiders, and hackers.
  • Security best practices can be consistently implemented from the beginning. By improving the overall security and offering the agility to meet new requirements, ADB can help reduce non-compliance security risks.

Reduced planned downtime

Autonomous Databases offer excellent availability features with a 99.995% SLA (less than 2.5 minutes of unplanned and planned downtime per month).

ADB is based on proven, enterprise-class Oracle technologies and solutions (for example, RAC, Exadata, etc.), offering high availability characteristics and redundancies. Secondly, patching, updates, and backups are automatically applied without service interruptions; the same is true for the on-demand scaling up/down of compute and storage resources. In fact, since all aspects of the service are autonomously handled on a standardized, extremely scalable platform operated by Oracle, planned maintenance activities can be more easily handled, and best practices (for example, rolling patching) are consistently applied from the beginning.

Reduced unplanned downtime

Autonomous Data Warehouse Cloud Service (ADWCS) offers excellent availability features with a 99.95% SLA. First, ADB is based on proven, enterprise-class Oracle technologies and solutions (e.g., RAC, Exadata, Autonomous Data Guard, etc.) offering high availability characteristics and redundancies. Secondly, since ADB is fully compatible with on-premises Oracle databases and all existing applications, it is less likely to experience unplanned downtime.

Thirdly, since all aspects of the service are autonomously handled on a standardized platform operated by Oracle, the chance for human errors, as well as actions by malicious insiders and hackers, is greatly reduced. Moreover, availability best practices are consistently applied from the beginning. Equally, patching, updates, and backups are automatically applied without service interruptions; the same is true for the on-demand scaling up/down of compute and storage resources. Finally, ADWCS offers a single contact point for end-to-end support, which can further reduce the amount of time spent troubleshooting issues.

Reduced IT infrastructure acquisition

Autonomous Databases provide virtually unlimited scaling capacity with no upfront hardware and software costs for the hardware involved with either transaction processing, analytics, JSON, or mixed workload database infrastructure. Other than paying for an initial subscription, there are no other upfront costs. Additional resources can be purchased as needed in any combination of computing and storage sizes (that is, no rigid shapes). ADB offers both serverless and dedicated deployment options on ECS, so customers can balance costs and other considerations (for example, performance, security isolation, software control, and so on). ADB offers instant scaling up/down of compute or storage independently of each other with no downtime. This elasticity avoids the need to acquire and provision transaction processing and mixed workload infrastructure potentially months or even years in advance, thereby shifting customers to a cost model driven by actual usage, rather than longer-term projections.

Further, ADB offers proven Oracle features and solutions including Active (or Autonomous) Data Guard, RAC, multitenant options, and Exadata infrastructure. With these capabilities, customers can conserve bandwidth, improve performance, and reduce cloud requirements to further decrease costs. Finally, since Oracle architectures, standards, and products are similar for the cloud and on-premises, migrations, integrations, and other changes are relatively seamless, allowing transparent movement between the two. As a result, a firm’s existing transaction processing and mixed workload infrastructure can be reduced and simplified.

Reduced IT infrastructure maintenance and support

ADB provides a virtually unlimited scaling capacity without many of the ongoing infrastructure maintenance and support costs for traditional on-premises or third-party hosting environments involved with transaction processing and mixed workload database infrastructure. Firstly, with its elastic capacity, ADB customers avoid maintaining and supporting the under-utilized infrastructure that typically exists at their own sites. The compute and storage capacity can be adjusted independently. Secondly, ADB operates at a scale that isn’t achievable for most firms, providing further cost advantages. ADB offers both serverless and dedicated deployment options on ECS, so customers can balance costs and other considerations (e.g., performance, security isolation, software control, and so on). Thirdly, ADB offers proven Oracle features and solutions including Active (or Autonomous) Data Guard, RAC, multitenant options, and Exadata infrastructure. Finally, since Oracle architectures, standards, and products are similar for the cloud and on-premises, related knowledge and procedures are easily transferable. Together, these features and capabilities help reduce maintenance-related costs.

Steps to calculate the TCO

Please perform the following steps to calculate the TCO/ROI for your use case:

  1. First, go to the TCO tool at the following link: https://valuenavigator.oracle.com/benefitcalculator/faces/inputs?id=408D37F02AF0A012283B0D29A4A0708A.
  2. Once there, click on the Start Now arrow.
  3. Now, you will need to input your company name, select a location, and a currency type.
  4. Review and generate the report.

Let’s review each step in detail. Open the following URL in a browser: https://valuenavigator.oracle.com/benefitcalculator/faces/inputs?id=408D37F02AF0A012283B0D29A4A0708A. This will open up the ADW TCO page as shown in the following screenshot:

Figure 1.7 – TCO tool page for ADW

Figure 1.7 – TCO tool page for ADW

When you click on Review your figures, it presents you with a TCO calculation based on certain assumptions and also provides an opportunity for you to edit assumptions for your specific scenarios. For example, you can choose either Bring Your Own License (BYOL) or License Included for TCO calculations. Many other parameters can be changed to suit your needs.

In the next screenshot, you can see a place to describe the environment, such as the number of cores available in computing, the size of storage, which can be calculated based on usable or raw storage capacity, and so on:

Figure 1.8 – TCO comparison for ADW versus traditional deployments

Figure 1.8 – TCO comparison for ADW versus traditional deployments

For example, Figure 1.9 compares on-premises TCO versus the cloud TCO for ADB and shows a 70% cost reduction; if you look at the TCO section, it shows cost distribution across software, compute, storage, network, and labor. The Environment section compares cores or OCPUs and storage across two environments.

Figure 1.9 – TCO savings with ADB

Figure 1.9 – TCO savings with ADB

The following screenshot shows the option to download the report for TCO for your use case. Inside the downloaded report, you will see input used for cost calculation and cost element per line item.

Figure 1.10 – TCO proposal download for ADB

Figure 1.10 – TCO proposal download for ADB

It also summarizes cost calculation across storage hardware, software, facilities, and productivity. This is shown in the following screenshot as an excerpt from the report:

Figure 1.11 – Three-year TCO savings representation with ADB

Figure 1.11 – Three-year TCO savings representation with ADB

From this screenshot, a comparison of business-as-usual deployment versus that of Oracle Cloud clearly shows two things: reduction in cost and fewer resources needed on autonomous services compared to a traditional deployment because of Exadata-based database capabilities. It is a great saving for customers and helps them make the decision to move their workload to the cloud with data points in hand.

With the TCO and ROI calculator report, the next step would be to get costing involved based on the Stock Keeping Unit (SKU) of the Autonomous Database and associated storage, and so on. Let’s see how you can easily create a Bill of Material (BOM) for your solution.

 

BOM and SKUs for autonomous databases

There are two components to Autonomous Databases:

  • Infrastructure component: You need to subscribe to Exadata Cloud Infrastructure for an OCI deployment or ExaC@C infrastructure for a C@C deployment.
  • Database OCPUs: Subscribe hourly per active OCPU consumed, using either License Included or the BYOL type. Billing happens monthly at an aggregate level, a total of all active OCPUs, for each Exadata Cloud Infrastructure resource.

There are two scenarios possible in terms of licensing: existing Oracle customers may have already invested in Oracle databases and options. Oracle provides flexibility to use BYOL while provisioning an Autonomous Database in the Oracle cloud. This option brings down the service cost significantly. The other option is the License Included service deployment where customers do not own prior licenses of Oracle databases.

Note

If you are using BYOL, it is feasible to consistently change from 16 or fewer OCPUs to more than 16 OCPUs. The main prerequisite is that your BYOL should incorporate RAC when scaling past 16 OCPUs. For the most accurate policies and the latest information, you should check Oracle’s site.

ADB-D on OCI pricing details can be found on the ADB pricing pages. ADB on ExaC@C pricing details can be found on Oracle’s website under the Pricing information page:

On OCI, the license type is defined at the Exadata infrastructure level. On ExaC@C, this is defined at the Autonomous VM cluster level. As of today, you can only use one license type (License Included or BYOL) on an Exadata rack until multiple VM clusters per rack are supported on ADB-D and ADB-ExaC@C, respectively.

 

Summary

Well, I hope you have had a great start to your ADB journey toward making a decision to take your workload to OCI ADB. You learned about deciding which kind of ADB is appropriate for your use case. We looked at the differences between shared versus dedicated infrastructure. We explored use cases for both kinds of deployments. Getting a TCO and ROI calculation for business is important and we have seen how you can quickly create them for your business case. With the evolution of Autonomous Databases, IT leaders must think of modernizing enterprise computing by transitioning it to a cloud-based model to take advantage of lower costs.

ADB combines the flexibility of the cloud with the power of ML to deliver data management as a service. I hope by now that you have an understanding of what ADB is, its technical building blocks, the reasons why you should consider ADB, use cases for ADB based on deployment types, business benefits, and creating BOMs and the TCO/ROI for your business justification.

In the next chapter, we will cover various deployment choices in detail such as provisioning, IAM and networking requirements, and connectivity.

 

Questions

  1. What are the available infrastructure options for ADB?
  2. What is the difference between a shared infrastructure and dedicated deployment?
  3. What are the methods to connect to ADB?
  4. Are there any limitations of ADB?
  5. What is backup retention for databases in ADB?
  6. What are the provisioning options for ADB?
  7. For BYOL, is it possible to seamlessly transition from 16 or fewer OCPUs to greater than 16 OCPUs?
  8. What is the minimum requirement for OCPUs in ADB? Does it support per-second billing?
  9. Can I use Enterprise Manager to monitor ADB Dedicated instances?
  10. Does ADB support an XML feature?
  11. What are the shapes of the Exadata Cloud Infrastructure racks that are available for ADB Dedicated on OCI?
  12. What are the shapes of the Exadata Infrastructure racks that are supported on ADB on ExaC@C?
  13. Can I associate a subscription to Block and Object Storage with ADB and not use Oracle ADB – Exadata Storage?
 

Further reading

  • Oracle Cloud Infrastructure for Solutions Architects by Prasenjit Sarkar
 

Answers

  1. The available infrastructure options are shared Exadata infrastructure and dedicated Exadata infrastructure.
  2. A shared infrastructure deployment provides easy-to-provision Autonomous Databases where multiple tenants can share an Exadata Cloud infrastructure. With dedicated deployment, you can consider it your own reserved Exadata infrastructure in OCI, which provides more flexibility and control than shared options.
  3. All standard connectivity methods, such as JDBC, SQL* Net, or any SQL client tools such as SQL Developer, are used for connectivity.
  4. ADB is a fully managed service from Oracle. You don’t have access to the OS; additionally, not all DBEE administrative features are available. You should look at the Oracle documentation, as features might change over time.
  5. Up to 60 days (7, 15, 30, and 60 days).
  6. OCI support both BYOL and a License Included model for provisioning.
  7. Yes, you can seamlessly scale beyond 16 OCPUs, although one of the requirements enforced by Oracle is to have a RAC license for scaling beyond 16 OCPUs in a BYOL scenario.
  8. You need at least one OCPU to provision ADB, although upon provisioning, you can shut down the instance and database OCPU billing will stop. Keep in mind that you will be responsible for storage as long as the service instance exists.
  9. Yes, Oracle Enterprise Manager 13.3, along with EM DB plugin bundle patch 13.32.0.190731 can be used. Keep in mind that only ATP-D databases are supported by EM at moment.
  10. Yes, ADB supports XML DB features with certain restrictions.
  11. Dedicated shape options include a quarter rack, half rack, and full rack. Underlying hardware could be one of Exadata X7, X8, X8M, or X9M shapes.
  12. Shape options include a base rack, quarter rack, half rack, and full rack. Underlying hardware could be one of Exadata X7, X8, X8M, or X9M shapes.
  13. No.
About the Authors
  • Bal Mukund Sharma

    Bal is a technology specialist with expertise in Oracle Cloud Infrastructure, Databases, & Engineered system. He has experience across various industries like Telecom, Healthcare, Financial Services and Insurance. Bal is currently working as Cloud Practice Manager with Oracle and have played various roles like Product Manager, Technical Delivery Manager, lead DBA, Content Architect, Software Engineer, QA Engineer in his previous roles. He holds several IT certifications across Oracle cloud Infrastructure (OCI) and databases. In his recent role he has been helping customers with implementation of OCI -Infrastructure, Networking & Security along with Oracle database technologies like High Availability, Engineered Systems and Autonomous Databases.

    Browse publications by this author
  • Krishnakumar KM

    K M Krishna Kumar is a database administrator with 14 years of experience with Oracle Database (8i to 12c). He started his career in 2004 as an Oracle database administrator and has been working with Oracle Corporation for the last eight plus years. He has expertise in Oracle Database installation, patching, upgrades, migration, backup and recovery and high availability. He is also proficient in Oracle public, private and Hybrid cloud environments. He has performed many real-time production database installations, upgrades, and migrations and has delivered sessions on database upgrades through various channels. He is certified in Oracle 12c administration and has been a speaker in Oracle Open World, AIOUG and Sangam

    Browse publications by this author
  • Rashmi Panda

    Rashmi has been a database enthusiast with a keen interest in oracle database technologies. He has spent over 16 years in different roles, as a developer, database administrator and consulting role. Besides databases, he has garnered an excellent experience around the data integration arena, conducting PoC demos to meet the extensive and complex data integration needs of customers. He has been successful in building customer solutions in designated platforms that adhere to the customer data security requirement complying to the country's GDPR requirements. His profiles reflect most of the certifications he has been able to complete in Oracle Cloud Infrastructure around different service offerings.

    Browse publications by this author
Oracle Autonomous Database in Enterprise Architecture
Unlock this book and the full library FREE for 7 days
Start now