Oracle Autonomous Database: Autonomous Database for Experienced Users

This post is part of a series of blog posts on the Oracle Autonomous Database.

Patching

For ADB-Shared:

  • Oracle patches the database during scheduled maintenance windows
  • The database remains available throughout the maintenance operation (usually 2 hours)
  • The date/time for the next scheduled maintenance is shown on the OCI Console page for each instance
  • Currently, users cannot change the patching schedule

For ADB-Dedicated:

  • Oracle is responsible for all software update operations
  • Database is continuously available to application
    –> Updates applied rolling across RAC nodes and Exadata storage servers
    –> Applications using Application Continuity best practices run without interruption
  • Quarterly updates of all components (on-demand for critical security issue)
    –> Firmware, OS, Storage, Network, Hypervisor, Clusterware, Database
  • Updates are automatically scheduled based on customer preferences
    –> Customers can adjust timing to accommodate critical business periods
    –> Deploy new versions on !PRD before PRD
    –> Avoid updates during peak periods (e.g. month-end)
    –> Control upgrade to confirm to application certified version
    –> Customer chooses target software (current or previous RU quarterly update)
    –> Can skip up to 2 quarterely patches
    –> Release Update (RU) is set at the CDB level
    –> Release Update Revision (RUR) is not currently supported

Database features and behaviour

  • Administrative interfaces and utilities such as RMAN are not available
  • The default data and temporary tablespaces for the database are configured automatically
  • Adding, removing, or modifying tablespaces is not allowed
  • Parallelism is enabled by default. Degree of parallelism for SQL statements is set based on the number of OCPU’s in the system and the database service the user is connecting to. Parallel DML is enabled by default. It is possible to disable parallel DML in your session: ALTER SESSION DISABLE PARALLEL DML;
  • Compression is enabled by default. ADB uses Hybrid Columnar Compression for all tables by default. You can also use different compression methods for your tables by specifying the compression clause in your CREATE TABLE or ALTER TABLE commands
  • Oracle Database Result Cache is enabled by default for all SQL statements. Changing the result cache mode is not allowed
  • Direct access to the database node and local file systems are not allowed
  • Database links are supported, both to and from other databases to ADB are allowed
  • Calling PL/SQL programs using database links is not supported
  • The Java VM (JVM) is not installed in ADB
  • ADB uses Unicode AL32UTF8 as the database character set
  • Database initialisation parameters are based on the compute and storage capacity you provision. You do not need to set any init parameters to start using your service. But, you can modify some parameters if required.
    • APPROX*
    • NLS*
    • OPTIMIZER* (ADW ignores HINTS by default, ATP honours them)
    • PLSQL_*
    • STATISTICS_LEVEL (only with ALTER SESSION)

ADB Consumer Groups

Run the following SQL to check the resource allocations of your connection session:

SELECT plan, group_or_subplan, mgmt_pl, parallel_degree_limit_pl, parallel_server_limit
FROM DBA_RSRC_PLAN_DIRECTIVES
WHERE group_or_subplan IN ( SELECT resource_consumer_group
FROM v$session
WHERE sid = SYS_CONTEXT(‘USERENV’,’SID’));

Run the following SQL to check the resource allocations of other consumer groups:

SELECT plan, group_or_subplan, mgmt_pl, parallel_degree_limit_pl, parallel_server_limit
FROM DBA_RSRC_PLAN_DIRECTIVES
WHERE group_or_subplan IN (‘TP’,’TPURGENT’,’LOW’,’MEDIUM’,’HIGH’);

Run-Away Query Management

Run-away query limits can be set using ADB Service Console or using CS_RESOURCE_MANAGER package.

Users can specify the following limits for Consumer Groups:
– Statement runtime is greater than
– Amount of I/O is greater than

Statements exceeding the limits will be cancelled, but the session stays connected

By default a pre-defined session idle time limit of 5 minutes is set to prevent idle sessions from holding system resources for a long period of time.

License Management

BYOL Licensing: Provides a significant discount on OCPU/Hour rate

If you run Oracle Database Enterprise Edition and the Multitenant option then your BYOL requirements are as follows:

  • For 1-16 OCPUs of Oracle Autonomous Database:
    • For each supported Processor license of Oracle Database Enterprise Edition plus Options: Multitenant, you may activate up to 2 OCPUs of the BYOL Cloud Service
    • For every 25 supported Named User Plus licenses of Oracle Database Enterprise Edition plus Options: Multitenant, you may activate 1 OCPU of the BYOL Cloud Service
  • For 17 OCPUs or more of Oracle Autonomous Database:
    • For each supported Processor license of Oracle Database Enterprise Edition plus Options: Multitenant and Real Application Clusters, you may activate up to 2 OCPUs of the BYOL Cloud Service
    • For every 25 supported Named User Plus licenses of Oracle Database Enterprise Edition plus Options: Multitenant and Real Application Clusters, you may activate 1 OCPU of the BYOL Cloud Service

Application Continuity

Note: By default Application Continuity is disabled.

Application Continuity masks outages from end users and applications by recovering the in-flight work for impacted database sessions following outages. Application Continuity performs this recovery beneath the application so that the outage appears to the application as a slightly delayed execution.

Change the failover type on Autonomous Transaction Processing using the DBMS_CLOUD_ADMIN procedures to enable or to disable Application Continuity. New sessions use the new failover type from the time when you modify the current value.

Enabling Application Continuity requires Oracle Client software version 18.3 (or higher).

If DRAIN_TIMEOUT (in V$SERVICES) has the value 0, this indicates that Application Continuity is disabled.

DBMS_CLOUD_ADMIN.ENABLE_APP_CONT( service_name => ‘nvt21_adb1_high.atp.oraclecloud.com’ );
DBMS_CLOUD_ADMIN.DISABLE_APP_CONT( service_name => ‘nvt21_adb1_high.atp.oraclecloud.com’ );

Use Database Resident Connection Pooling with Autonomous Database

Database Resident Connection Pool (DRCP) in Autonomous Database supports easier and more efficient management of open connections. Using DRCP provides you with access to a connection pool in your Autonomous Transaction Processing database that enables a significant reduction in key database resources required to support many client connections and when the database needs to scale for many simultaneous connections.

When you connect to Autonomous Database you choose one of the following depending on values specified in the tnsnames.ora configuration file:

  • A dedicated server process, which services only one user process.
  • A pooled server process, obtained from DRCP, which can service multiple user processes.

DRCP is enabled by default; however using DRCP is optional. To choose a pooled connection specify SERVER=POOLED in tnsnames.ora. If you do not specify SERVER=POOLED, you connect with a dedicated connection.

Oracle Autonomous Database: APIs and OCI-CLI

This post is part of a series of blog posts on the Oracle Autonomous Database.

Autonomous Database – REST APIs

  • Permit interaction with Oracle ADB cloud services programmatically over REST rather than log into the OCI Cloud Console and click through screens
  • A mechanism for developing customized deployment and management scripts that can be saved and reused for deployments, set gold standards, and store entire application infrastructure stacks as version controlled code
  • The Oracle Cloud Infrastructure APIs are typical REST APIs that use HTTPS requests and responses and support HTTPS and SSL protocol TLS 1.2
  • Calls to Oracle Cloud Infrastructure using REST APIs can be written in popular scripting languages such as node.js, Python, Ruby, Perl, Java, C#, bash or curl
  • All OCI API requests must be signed for authentication purposes

The steps to create and sign API requests are:

  • Form the HTTPS request (SSL protocol TLS 1.2 protocol required)
  • Create the signing string, which is based on parts of the request
  • Create the signature from the signing string, using your private key and the RSA-SHA256 algorithm
  • Add the resulting signature and other required information to the Authorization header in the request
  • You will also need to generate an ssh key pair in the pem format

These avoid using username/passwords (insecure communication of the internet)

Autonomous Database – Using OCI-CLI

  • The Command Line Interface (CLI) is a small footprint tool that you can use on its own or with the Shell Console to complete OCI tasks.
  • The CLI provides the same core functionality as the Service Console, plus additional commands. Some of these, such as the ability to run scripts, extend the Console’s functionality.
  • The CLI is built on Python. The Python code makes calls to OCI APIs to provide the functionality implemented for the various services.
  • Preinstalled in OL7 (or download using yum or curl or download from github)

To install and use the CLI, you must have:

  • An OCI account
  • A user created in that account, in a group with a policy that grants the desired permissions
  • A keypair used for signing API requests, with the public key uploaded to Oracle. Only the user calling the API should possess the private key

The Cloud shell will start up a shell console to access your tenancy. It is an ephemeral machine from which you can run OS commands and scripts across your whole tenancy.

$ oci db autonomous-database

Please refer to the documentation for the available commands:

e.g.

change-compartment
create
create-from-backup-id
create-from-backup-timestamp
create-from-clone
create-refreshable-clone
data-safe
deregister
register
delete
disable-operations-insights
enable-operations-insights
fail-over
generate-wallet
get
list
list-clones
manual-refresh
restart
restore
rotate-key
start
stop
switchover
update

Oracle Autonomous Database: Overview

This post is part of a series of blog posts on the Oracle Autonomous Database.

The Oracle Autonomous Database (ADB) isn’t really a product or service itself. Rather, it’s an umbrella term that refers to two underlying services: the Autonomous Data Warehouse (ADW) and the Autonomous Transaction Processing database (ATP). The Autonomous Data Warehouse was introduced first in 2017 and to add complexity to the discussion, is often (or was originally) referred to as ADWC where the final “C” in the acronym is for cloud. But for clarity, from this point forward, we’ll refer to the ADB offering as the combination of ATP and ADW.

The next logical question is: what’s the difference between ATP and ADW? The answer is pretty simple: they’re both built on common underlying technology and components but are tuned and optimized differently for different workloads.

The ADW has some Oracle database settings that are tuned for data warehouse workloads. And significantly, the underlying data is stored in the columnar format. Columnar data stores allow for effective reads of large amounts of data at the column level. They’re less effective for transient data and row updates or queries that process many or all table columns.

Not surprisingly, the ATP uses Oracle database settings that are tuned for Online Transaction Processing (OLTP) type workloads that might involve some small tables, reasonably frequent deletes and updates, etc. It uses the more traditional row storage format.

A key aspect of ADB that’s often overlooked is that it’s fundamentally built on known technologies and components. Specifically, Oracle Exadata hardware leveraging software features such as Pluggable Database (PDB) with lockdown profiles to provide tenant isolation and restrict the customer’s ability to see or reach outside of their individual PDB. And a recently announced dedicated version for customers requiring full isolation of the infrastructure.

One can think of the ADB philosophy this way: over recent years (or decades) Oracle has built an elaborate set of hardware and software components for running Oracle databases. Exadata is at the core as their highly performant and scalable hardware. On top of that, they have horizontal scaling and redundancy with Real Application Clusters (RAC) and high availability via Data Guard. Exadata also adds storage redundancy and scalability connected with a high speed Infiniband network and Exadata-specific software allowing this hardware footprint to be used to its maximum potential while ensuring that a “build-your-own” similar system isn’t truly an equivalent. Inside the database they have many “somewhat” automated tuning and query stabilization features. And true multi tenant capabilities through the Container Database (CDB) and pluggable Database (PDB) architecture with resource sharing and governing software to reduce the risk of nosy neighbors hijacking the entire system.

So essentially what Oracle did was package all of these (their most advanced technologies) together, manage them for you (the automation part), add a modest amount of new features, and just make you the customer a consumer of all of this. The customer gets a PDB in an Oracle container database, running on RAC (after a certain number of allocated CPUs), locked down for isolation and security, on an Exadata system that Oracle procured and maintains in their own data center, and with Oracle managing the entire stack. That essentially is ADB. Some are tuned for data warehouse workloads for ADW and others are tuned for OLTP workloads for ATP.

When you create an Autonomous Database, you can deploy it to one of two kinds of Exadata infrastructure:

Shared: a simple and elastic choice. Oracle autonomously operates all aspects of the database life cycle from database placement to backup and updates.

Dedicated: a private cloud in public cloud choice. A completely dedicated compute, storage, network and database service for only a single tenant, providing for the highest levels of security isolation and governance.

LIMITATIONS OF THE ORACLE AUTONOMOUS DATABASE

As would be expected with a managed service, there are a number of features and functionality that can’t be used or are limited. This includes things like:

• The ability to change encryption or disable TDE.
• No control over tablespaces.
• No ability to control database instance memory sizes and configurations.
• No control over database initialization parameters.
• Inability to use many Oracle features such as: Java in the Database

In addition, a small number of commands such as CREATE DATABASE LINK or ALTER TABLESPACE are restricted using PDB Lockdown Profiles. However, the service is evolving rapidly and new capabilities are being added regularly.

DO WE STILL NEED A DBA IN AN AUTONOMOUS WORLD?

Short answer: “YES”. While a lot of the general maintenance tasks such as backups, monitoring, checking log files, patching, and quite possibly tuning are offloaded to Oracle, there is still a lot that the DBA needs to do. Your DBAs are still needed to:

• Move your data in and out of ADB.
• Create long-term retention backups/exports as may be required for regulatory or business needs.
• Perform specialized restoration or duplication activities.
• Model and design application schemas.
• Design, implement, and enforce database security (controls).
• Tune and help developers write well-structured queries.
• Manage resource scale and monitor spend.

So there’s still a lot that the DBA is needed for, even in the ADB world. Some of the above may be minimal for commercial, packaged applications and more applicable with custom or in-house developed applications.

WHY WOULDN’T WE WANT TO MOVE INTO ADB?

ADB is an attractive offering for a lot of customers and specific use cases, but as with anything, there are a number of exceptions. Some reasons where ADB may not be a good fit:

• Applications with tight restrictions on the Oracle database release or version.
• Applications that require specialized (known as “one-off”) patches to fix specific problems.
• Databases requiring very specific initialization parameter or tablespace settings.
• Applications that rely on some more advanced Oracle features such as Oracle Spatial or OLAP, for example.
• Workflows that involve logical replication of data (a subset of the entire database) out of the source database using tools such as Oracle GoldenGate.
• Businesses with a high expectation of performance consistency.

To expand on that last point: the mantra of “faster is better” isn’t always true. In the database world, sometimes “consistent” is more important than “fastest”.

Oracle Cloud Infrastructure

  • OCI Region – HA Building Blocks
  • Multiple fault de-correlated, completely independent data centres
  • Region: Has Data Centres (Availability Domain 1, Availability Domain 2, Availability Domain 3)
  • Grouping of hardware and infrastructure within an Availability Domain: Fault Domain
  • AD1: FD1, FD2, FD3
  • Predictable low latency and high speed, encrypted interconnect between ADs

An Autonomous Database is placed on an Exadata System based on region. Placement is invisible to customers except for the region (for Shared). ADB storage is on Exadata Storage Servers directly attached to the Exadata system. Connections to ADB run through connection managers attached to OCI network infrastructure.

Object Storage: Automatic backups (dedicated OCI Storage, cannot be accessed by database users), User backups (user created OCI storage), Staging (flat files, dump files).

Oracle Autonomous Database Attributes

  • Self-Driving
    Automates database and infrastructure management, monitoring, tuning
  • Self-Securing
    Protects from both external attacks and malicious internal users
  • Self-Repairing
    Protects from all downtime during planned maintenance

Allows customers to focus on business goals and not technology.

Self-Driving attributes:
* Rapid provisioning (create mission critical scale-out database in minutes)
* Self scaling
* Automatic tuning (physical schema tuning is not recommended)
* Automatic indexing

Performance resources are allocated proportionally to number of CPUs chosen.
Tables do not need to be partitioned or configured for in-memory. Indexes do not need to be created. Compression does not need to be specified. Machine Learning optimizes database for each workload. Continuously optimizes memory, data formats, indexes, parallelism, and plans for each workload.

ADB autonomously and continuously monitors overall system performance. Scaling up autonomously expands CPU-IO resources by up to 3x. Scaling is an online operation with no downtime to the database or applications.

Backups are scheduled on a nightly basis to OCI Object Storage. Retention time is 60 days by default.

Full stack patching once a quarter, in a rolling fashion across nodes of cluster for availability.

Self-Securing attributes:
* Encryption by default (Transparent Data Encryption is automatically configured)
* Self Patching
* Separation of Duties
* Auditing

Protects from both external and malicious internal threats.

Monitors threats, applies security updates online, stops admin snooping with DB Vault, encrypts all data.

All connections use certificate based authentication and SSL.
Encrypted key stored in a wallet on both the client and the server.
Encryption both for Data at Rest (TDE) & Data in Motion (TLS 1.2).
Database backups are also encrypted.

“Admin” is the default database user and the only privileged user. All other users need to be created and granted privileges. Outside OS calls are not allowed.

“Break glass” function to enable Cloud Operations access. Well-defined procedure (with customer approval) for Oracle Cloud Operations to access the system for exceptional scenarios.

In the cloud, security is a shared responsibility.

Security managed by Oracle:

  • Network security and monitoring
  • OS and platform security
  • Database patches and upgrades
  • Administrative separation of duties
  • Data encryption by default

Security managed by Customer:

  • Ongoing security assessments
  • Users & privileges
  • Sensitive data recovery
  • Data protection
  • Actively auditing
  • Data Masking and redaction

ADB leverages Oracle Unified Audit to capture security-relevant activity

  • Login failures
  • Changes to users, including creation of new accounts, grants, privileges, or roles
  • Changes to database structures

Oracle Data Safe is a unified control center for your Oracle databases which helps you understand the sensitivity of your data, evaluate risks to data, mask sensitive data, implement and monitor security controls, assess user security, monitor user activity, and address data security compliance requirements.

Self-Repairing attributes:
* Self-healing hardware
* Self-healing software

Recovers automatically from any failure and protects from downtime. 99.95% Service Level Objective

Database Infrastructure for ADB is provided by Exadata. Exadata provides advanced predictive failure capabilities. Unique detection of server failures without a long timeout avoids system hangs. Unique sub-second redirection of IOs around sick devices avoid database hangs.

Exadata environment continuously monitors for failing devices.
e.g. Automatically detects change in disk latency
–> Failing disk is automatically taken offline and database activities redirected to mirror
–> ASM automatically rebalances data off of failing disk to create additional mirror
–> DevOps is notified and fail drive is replaced
–> ASM automatically detects new disk and rebalances data to create additional mirror

Self-healing software: Uses Oracle’s 40 years of experience to build machine learning models. Applies machine learning algorithms and Oracle’s best practices to fully automate database operations. Uses pattern recognition to determine if it’s a known problem.

Cluster Verification Utility, EXAchk, Trace File Analyzer, Memory Guard, etc.

Maximum Availability Architecture: RAC Database, redundant compute, networking, triple mirrored storage, backup

Oracle Autonomous Database

In this blog series, I would like to cover arguably the most interesting new service in Oracle Cloud Infrastructure – the Oracle Automomous Database.

  1. Oracle Autonomous Database: Overview
  2. Oracle Autonomous Database: APIs and OCI-CLI
  3. Oracle Autonomous Database: Autonomous Database for Experienced Users
  4. Oracle Autonomous Database: Connecting to Autonomous Database
  5. Oracle Autonomous Database: Dedicated Infrastructure
  6. Oracle Autonomous Database: Identity & Access Management – OCI Policies
  7. Oracle Autonomous Database: Migration Considerations and Loading Data
  8. Oracle Autonomous Database: Monitoring and Maintaining ADB
  9. Oracle Autonomous Database: New Users, Backup, Recovery, Cloning, Scaling
  10. Oracle Autonomous Database: Oracle Machine Learning, APEX, SQL Developer Web
  11. Oracle Autonomous Database: Schema Advisor
  12. Oracle Autonomous Database: Service Limits and Compartment Quotas
  13. Oracle Autonomous Database: Setting up ACLs and Private Endpoints
  14. Oracle Autonomous Database: SYSDATE and Time Zones
  15. Oracle Autonomous Database: Using a Standby Database


Documentation home for Oracle Autonomous Database

Documentation home for Oracle Autonomous Transaction Processing (ATP)
ATP – Shared
ATP – Dedicated

Documentation home for Oracle Autonomous Data Warehouse (ADW)
ADW – Shared
ADW – Dedicated

FAQs For Autonomous Database