Oracle Autonomous Database: Dedicated Infrastructure

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

Resource Components:

  1. Exadata Infrastructure
  2. Container Database
  3. Autonomous Database
  • Fleet Administrator provisions Exadata Infrastructure by specifying its size, availability domain, and region
  • Fleet Administrator then partitions the system by provisioning desired clusters and container databases
  • Developers & DBAs provision databases (PDBs) within container databases
  • Billing is based on size of Exadata Infrastructure and number of CPUs used by the databases

ADB-Dedicated General Selection Considerations

  • Higher cost entry. Must subscribe to minimum 48 hours Exadata Infrastructure
  • Includes Availability Domain placement controls for latency sensitive apps
  • Policy controls for patch scheduling and software versioning.
    Oracle manages all patching, but you can control what version and when.
  • You must setup a VCN for ADB-D. It is mandatory. VCN must exist before provisioning Exadata infrastructure.

ADB Feature Comparison

Feature | ADB Dedicated | ADB Shared

Create Tablespace | YES | NO
Transparent Application Continuity | YES | NO (Application Continuity only)
Clone Support | YES | YES
Profile | YES | NO
Auto Scaling | YES | YES
OCI Notification Services Integrated | YES | NO (Classic Admin Notifications)
EM Monitoring | YES | NO
Private Single Tenant | YES | NO
Controllable Software Update Version | YES | NO
Controllable Software Update Schedule | YES | NO
Availability Domain Placement Choice | YES | NO
Non-TLS SQL*Net | YES | NO

Oracle Autonomous Database: Connecting to Autonomous Database

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

Connecting to the Oracle Autonomous Database relies upon a wallet to help with an encrypted SSL connection over the public internet. Keep in mind that the wallet is mandatory when connecting to Autonomous Database Shared. However, with Autonomous Database Dedicated, you can choose to connect directly without using a wallet. So most of this post is 100% required when connecting to the Autonomous Database Shared. It’s optional when connecting to Dedicated.

So with that being said, all apps will use a secure connection to connect to the Autonomous Database, and the Autonomous Database uses certificate authentication and SSL. This ensures that no unauthorized access is available to the ADB, and it also ensures that communications between the app and the server are fully encrypted and cannot be intercepted or altered. This information that’s required to authenticate to the database is stored in a wallet that’s available on both the client (where your application is running) and the server (where your database is running), and is automatically generated for you when provisioning the Autonomous Database.

When you actually download this wallet from your Autonomous Database, you’ll see there’s a collection of files that are included with it– like a couple of key files– in addition to a TNS entry, a tnsnames names file, and a sqlnet file. These files have existing service handles and connect strings to connect to your Autonomous Database. And you will also see there’s a readme file. The readme file contains information about when the wallet is expected to expire. And just so you know, the expiration date of a wallet is around about 2 and 1/2 year mark. So you’re looking at a long period of time before these wallets do expire. And you will get warnings before that’s required to replace it.

There are two types of wallets:

For ADB-Shared:

  • Instance Wallet file
    Instance wallet contains only credentials and keys for a single Autonomous Database
  • Regional Wallet file
    Regional wallet contains credentials and keys for all Autonomous DBs in a specified region.
    Regional wallets should only be used by DBAs.

For ADB-Dedicated, the wallet file contains only credentials and keys for a single autonomous database. There is no regional file.

The ADB only accepts secure connections to the database, and you need to download that wallet first. You can download it both from the DB Connection button on your main instance details page, as well as through the Service Console. You can also use API calls to download the wallet as well. It’s definitely a good practice to store the credential file in a secured location and only share the file contents with people that have authorized access. However, with that file alone, you cannot connect to the database. You still need to know the database password. It provides a secure way to connect to your database to route you to it, but not to actually log into the database.

You also have the option to rotate either the instance wallet or the region wallet. If you rotate the instance wallet, all the keys for that associated instance are discarded, and it generates a completely new wallet. With a regional wallet, if I were to replace or rotate my instance, the regional wallet will continue to work. However, all existing connections will continue to work, but any new connections will be terminated and will need to be re-established using the new wallet. So ADB gracefully allows the controlled operation of rotating your wallet.

Predefined Database Service Names

If you look inside your wallet file, you will notice that there are several connections that are predefined for you– connection services– depending what service you’re running.

  • tpurgent (ATP only):
    The highest priority application connection service for time critical OLTP operations.
    This connection supports manual parallelism
    Share of resources: 12
  • tp (ATP only):
    A typical application connection service for OLTP operations.
    This connection service does not run with parallelism.
    Share of resources: 8
  • high:
    A high priority application connection service for reporting and batch operations.
    All operations run in parallel and are subject to queuing.
    Share of resources: 4
  • medium:
    A typical application connection service for reporting and batch operations.
    All operations run in parallel and are subject to queuing.
    Using this service, the degree of parallelism is limited to 1.25 × OCPUs
    Share of resources: 2
  • low:
    A lowest priority application connection service for reporting and batch processing operations.
    This connection service does not run with parallelism.
    Share of resources: 1

Please note: After connecting to the database using one service, do not attempt to manually switch that connection to a different service by simply changing the consumer group of the connection. When you connect using a service, ADB performs more actions to configure the connection than just setting its consumer group.

If we were running an Autonomous Data Warehouse (ADW), we would only have high, medium, and low. In the case of Autonomous Transaction Processing (ATP), we have both tpurgent and tp in addition to high, medium, and low. When you go to download or to connect to the database, the autonomous database will automatically create the service handles for you. But they will be prefixed with your database name. e.g. dbname_high, dbname_medium, and dbname_low.

The tp and tpurgent are focused on OLTP workloads, they’re intended to support a very high concurrency. As you would typically see for a transactional workload, you’ll see that ADB supports up to 100 times the number of CPUs before it starts queuing, versus the high, medium, and low data warehouse ones have a limitation on the number of concurrent sessions that you would see connected to the database.

With that being said, the parallelism for the high, medium, and low is different. For high, ADB allows you to run parallelism– so operating parallel operations that might run with parallel query can run across all CPUs. Medium would run with a default of 4, and low would run with a default of 1. And it’s not possible to change that for low.

With OLTP, ADB explicitly sets tp as only ever using one parallel query slave. And that’s because this is a transactional workload. It’s not typical to run things with parallel query enabled. If, however, you have some batch process or some operation that requires some level of parallelism, if you’re using tpurgent as your service to connect to, you can explicitly define your parallel degree on the operation you’re performing.

Now, in addition to parallelism, ADB also controls the amount of resource shares. So you’ll notice OLTP tpurgent has a high number of shares of resources that it gets slices to, if you wish. tp doesn’t have as high a number of resource shares, but it is up there, because we’re intending to run operations concurrently and simultaneously. And in transactional workloads, typically you’re going to see these will be short, bursty style operations.

With warehouse, batch, and reporting, on the other hand, the number of resource shares you get are diminished. And that’s because the idea is this is more of an ad-hoc, asynchronous style of performing work, where the units of work could be varied in complexity and size. So we typically do not share as many of these resources, bearing in mind that these resources could also be consuming a lot of memory or running for an inordinate amount of time when compared to an OLTP workload.

So connecting to the Autonomous Database– I will point out that the Autonomous Database licensed on port 1522. And that’s the standard on ADB Shared. And it’s the default for ADB Dedicated. All connections, as I pointed out earlier, are using SSL for encryption. And then, unlike other cloud services, you do not have a Unix command line interface on the system you’re connecting to. Whether you’re running in Autonomous Database Shared or Dedicated, you do not have access to the underlying operating system.

Having said that, all Autonomous Databases can be connected through the typical client utilities, such as SQL*Net, JDBC thick or thin, OCI, etc. Remember, the clients will require the security credentials wallet to connect when using those tools. And there’s a few connectivity options. The first one is through the public internet. And when connecting through the public internet, you could do public endpoints or private endpoints. So there’s two parts to that.

And then the additional approach is using the FastConnect model with public peering, in which you could connect through either a private connection from an on-premise network or from another cloud vendor. And obviously, through public internet, you could set up private endpoints, or you could set up a FastConnect private connectivity to other clouds. FastConnect is useful for extremely high bandwidth. Perhaps you have an application tier that does require the benefit of being on a dedicated pipe to connect to.

There is no SYS or SYSTEM. Admin is the highest user you will have in an Autonomous Database.

Transparent Application Continuity

Preconfigured with Autonomous Dedicated and can be enabled in Autonomous-Shared with DBMS_CLOUD_ADMIN.ENABLE_APP_CONT

Transparent application continuity is there to help with failover and masking transferring of instances from one machine to another. So remember, Autonomous Shared provides application continuity versus Dedicated provides the full on transparent application continuity. Both have a full view of session state, but application continuity allows customers the flexibility to keep their side effects and restore more complex states, such as session during lobs at failover for example. And you can use the ORAchk protection report to identify the protection level, to see where things are at.

Oracle Call Interface (OCI), ODBC, JDBC, OCI

  • Wallet File is required
  • Set WALLET_LOCATION in sqlnet.ora
  • Set TNS_ADMIN environment variable to credentials file
  • Oracle Client or higher
  • HTTP Proxy in tnsnames.ora

So to connect through OCI, the traditional TNS model, you still require your wallet. But you would set your wallet location in the sqlnet.ora file, and you would point to wherever it is you’ve downloaded that wallet. Once you’ve done that, SQLNet, if you’re running a client or higher, is capable of knowing and reading that wallet to find what it needs. And you would also set your TNS_ADMIN environment variable to point to that location of that wallet, or where you’ve put your tnsnames.ora file if you’ve unzipped your wallet.

Connecting with .NET applications, you would download and install ODT and ODP.NET. And then you can go ahead and run Visual Studio, and use the NuGet package manager, and then you can download ODAC, which is the Oracle Data Access Components for Windows. Once you’ve got all that configured and setup, you can then set your wallet location in sqlnet.ora, and you would ensure that you’ve set TLS 1.2 to be enabled. And then your .NET applications will connect no problem.

JDBC Thin or Universal Connection Pool (UCP)

A couple of requirements– you need to install JDK8u161 or higher. Or just use JDK9, and you’re good to go– or higher. And you would download either the JDBC Thin driver, ojdbc8.jar. Or you could use the 12.1 version, and you would patch it up to use the upc.jar for the universal connection pool.

Once you’ve set that up or you’ve got the correct driver working, you would want to go ahead and copy the file, which is inside your wallet. You could unzip your wallet, copy those contents to your TNS_ADMIN directory, set the properties to be your net wallet location. And then the JDCB URL is going to use oracel:thin@service_name– so the service name that’s inside your tns entry is what you would point to.

Now, if you’re not using a TNS_ADMIN environment variable, if you’ve not set that, you would then point to the TNS_ADMIN = wallet directory as part of your connect string. If you’re using a HTTP proxy– perhaps you have a proxy server to block your requests– that can easily be configured. You would add the HTTP proxy hostname to tnsnames.ora very easily. And that’s documented for you as well.

Using SQLNet connections

Changes required to sqlnet.ora

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=”?/network/admin”)))

ADB also supports connecting through Python, Node,js, and other scripting languages. You install the Instant Client, install the language driver, unzip the credentials wallet, and use the TNS entry file with the various contents. In some cases, like Python, you can simply run a yum install command to get that.

Perl: DBD-Oracle
Python: cx_Oracle
Node.js: Node-oracledb
Ruby: Ruby-oci8

ADB also supports connecting through Active Directory. So if you are running Active Directory, traditionally, in the past, you would have been expected to authenticate through your LDAP repository, Oracle LDAP. Now, since Oracle 18c, we are able to directly connect to Active Directory and authorize and authenticate your users and manage them through Active Directory and have them routed to your Autonomous Database. So if you have a Windows or a Microsoft-based application that uses Active Directory to connect to your workloads, that is supported to work natively with the Autonomous Database. Just keep in mind that when using Autonomous Database with Active Directory, both users and roles are mapped in Active Directory and not in the Autonomous Database.

Oracle Autonomous Database: Autonomous Database for Experienced Users

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


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_*

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
WHERE group_or_subplan IN ( SELECT resource_consumer_group
FROM v$session

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
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 => ‘’ );
DBMS_CLOUD_ADMIN.DISABLE_APP_CONT( service_name => ‘’ );

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:



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.


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.


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.


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

Database Design and Tuning

Physical design: is an activity where the goal is to design the appropriate structuring of data in storage.

It is not possible to make meaningful physical design decisions and performance analyses until we know the queries, transactions, and applications that are expected to run on the database in a high-level form.

Factors That Influence Physical Database Design

  • Analyzing the Database Queries and Transactions
    For each query, we should specify the following:
    • The tables that will be accessed by the query.
    • The attributes on which any selection conditions for the query are specified.
    • The attributes on which any join conditions or conditions to link multiple tables or objects for the query are specified.
    • The attributes whose values will be retrieved by the query.
  • Analyzing the Expected Frequency of Invocation of Queries and Transactions
    • Expected rates of invocation with attributes in each file as a selection attribute or a join attribute, over all the queries and transactions.
    • Generally “80-20 rule” applies. It is sufficient to determine the 20 percent or so most important ones that may use 80% of processing.
  • Analyzing the Time Constraints of Queries and Transactions
    • May have stringent performance constraints.
    • The selection attributes used by queries and transactions with time constraints –> higher-priority candidates for primary access structures.
  • Analyzing the Expected Frequencies of Update Operations
    • A minimum number of access paths (e.g. index) should be specified for a file that is updated frequently, because updating the access paths themselves slows down the update operations.
  • Analyzing the Uniqueness Constraints on Attributes
    • Access paths should be specified on all candidate key attributes-or sets of attributes-that are either the primary key or constrained to be unique.

Physical Database Design Decisions

  • Whether to index an attribute:
    The attribute must be a key, or there must be some query that uses that attribute either in a selection condition (equality or range of values) or in a join.

    One factor in favour of setting up many indexes is that some queries can be processed by just scanning the indexes without retrieving any data.
  • What attribute(s) to index on:
    An index can be constructed on one or multiple attributes.
    For example, (garment_style_#, color) in a garment inventory database, a multiattribute index is warranted.

    The ordering of attributes within a multiattribute index must correspond to the queries. For example, the above index assumes that queries would be based on an ordering of colors within a garment_style_# rather than vice versa.
  • Whether to set up a clustered index:

A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table.

Please note: There is no such thing as create clustered index in Oracle. The Oracle database calls this concept index-organized tables (IOT), other databases use the term clustered index.

If a table requires several indexes, the decision about which one should be a clustered index is needed.

– Range queries benefit a great deal from clustering.
– If several attributes require range queries, relative benefits must be evaluated which attribute to cluster on.
– If a query is to be answered by doing an index search only (without retrieving data records), the corresponding index should not be clustered, since the main benefit of clustering is achieved when retrieving the records themselves.

  • Denormalization as a Design Decision for Speeding Up Queries

The normalization sometimes is sacrificed in favour of faster execution of frequently occurring queries and transactions.

This process of storing the logical database design (which may be in BCNF or 4NF) in a weaker normal form, say 2NF or 1NF, is called denormalization.

Typically, the designer adds attributes to a table that are needed for answering queries or producing reports so that a join with another table, which contains the newly added attribute, is avoided.

This reintroduces a partial functional dependency or a transitive dependency into the table, thereby creating the associated redundancy problems.

Other forms of denormalization consist of storing extra tables to maintain original functional dependencies that are lost during a BCNF decomposition.


  • Inputs to the tuning process include (internally collected in DBMS):
    • Sizes of individual tables.
    • Number of distinct values in a column.
    • The number of times a particular query or transaction is submitted/executed in an interval of time.
    • The times required for different phases of query and transaction processing (for a given set of queries or transactions).
    • Storage/tablespace statistics
    • I/O and device performance statistics
    • Query/transaction processing statistics
      • Execution times of queries and transactions, optimization times during query optimization.
    • Locking/redo logging related statistics
      • Rates of issuing different types of locks, transaction throughput rates, and redo log records activity.
    • Index statistics
      • Number of levels in an index, number of non-contiguous leaf pages, etc
  • Tuning Indexes

The initial choice of indexes may have to be revised for the following reasons:

– Certain queries may take too long to run for lack of an index.
– Certain indexes may not get utilized at all.
– Certain indexes may be causing excessive overhead because the index is on an attribute that undergoes frequent changes.

  • Tuning the Database Design

The database design has to be driven by the processing requirements as much as by data requirements. Dynamically changed processing requirements need to be addressed by making changes to the conceptual schema if necessary and to reflect those changes into the logical schema and physical design.

We may revert to the logical database design, make adjustments to the logical schema, and remap it to a new set of physical tables and indexes.

e.g. Existing tables may be joined (denormalized) because certain attributes from two or more tables are frequently needed together:
This reduces the normalization level from BCNF to 3NF, 2NF, or 1NF

  • Tuning Queries

Mainly two indications to suggest that query tuning may be needed:

1. A query issues too many disk accesses (for example, an exact match query scans an entire table).

2. The query plan shows that relevant indexes are not being used.

Many query optimizers do not use indexes in the presence of:
– arithmetic expressions (such as SALARY/365 > 10.50);
– numerical comparisons of attributes of different sizes and precision (such as AQTY = BQTY where AQTY is of type INTEGER and BQTY is of type SMALLINTEGER);
– NULL comparisons (such as BDATE IS NULL), and
– substring comparisons (such as LNAME LIKE “%MANN”).

Some DISTINCTS may be redundant and can be avoided without changing the result (the result is distinct). A DISTINCT often causes a sort operation and must be avoided as far as possible.

If multiple options for join condition are possible, choose one that uses a clustering index and avoid those that contain string comparisons.

One idiosyncrasy with query optimizers is that the order of tables in the FROM clause may affect the join processing. One may have to switch this order so that the smaller of the two relations is scanned and the larger relation is used with an appropriate index.

A query with multiple selection conditions that are connected via OR may not be prompting the query optimizer to use any index. It may be split up and expressed as a union of queries.

For example:
WHERE AGE> 45 OR SALARY < 50000;

Splitting previous query up as:

may utilize indexes on AGE as well as on SALARY.

WHERE conditions may be rewritten to utilize the indexes on multiple columns. For example,


May use an index only on REGION# and search through all leaf pages of the index for a match on PRODUCT_TYPE:


May use a composite index on (REGION#, PRODUCT_TYPE) and work much more efficiently.

First Steps When Performance Tuning

A well-planned methodology is the key to success in performance tuning. Without a proper diagnosis of the issue any troubleshooting step taken will further delay the resolution. Example, a DBA can not just blindly start tracing/tuning a SQL/Sessions when all symptoms on careful analysis points towards issues with Disk/Memory/Network etc.

Also remember that most common performance gains are attained through SQL/Code/application tuning and increasing hardware resources should always be your last resort. Developers play an important role in writing applications with effective SQL statements so it is important that the DBA and Developers should work together when facing a database/application issue.

When performance issues are reported to a DBA, the first step is to get as much information as possible that is related to the issue. You can ask below example preliminary questions to users/developer to collect the first level of information..

  1. What operations/program are executed?
  2. Is it Oracle seeded or custom program?
  3. How much time it used to take earlier?
  4. Is the run time increased over time or you are seeing sudden increase in run time?
  5. Was there any recent code change/migration?
  6. Is it always slow or for certain time of the day only?
  7. Is it slow for all parameters or for some specific parameters?
  8. How much data is getting processed?
  9. What is the frequency of job execution? Was there any change in frequency?
  10. Does the problem happens on both their test and production systems?

Asking above kind of questions will help you in deciding what part of system you should target.

  • Target the whole system
  • Target a single session
  • Target a single SQL statement

Your goal should be to answer below three questions:

Where is time spent?
You need to identify where the time is getting consumed. Is it the code or network latency or disk I/O bottlenecks or the CPU/Memory crunch

How is time spent?
You need to find out how the time is spent and how much time is spend in each layer – database, application, network, disk etc .

How to reduce the time spent?
Finally, based on above information see where the major chunk of time is spent and how to reduce it.

High level steps for System level performance issues:

Use vmstat top/prstat to identify system wide CPU/Memory consumption.
Use iostat to verify if disks are the bottleneck
Use netstat/tnsping etc to verify if network is issue.
Verify if any other resource intensive processes are running on server.
Verify filesystem space.
Check alert logs, application logs, traces etc.
Check database locks
Generate AWR reports to see what is eating up resources.
Check if increasing application/database memory/redo/undo/temp/SGA will help.

High level steps for Session level performance issues:

Find the Top SQLs executing under session.
Apply SQL optimization techniques on top SQLs.
Verify locking at session level
Generating AWR/ASH for that duration may help in providing useful information.

High level steps for SQL level performance issues:

Apply trace, Generate TKPROF file and analyze for top resource consuming SQL.
Avoid full table scans on large tables
Possible indexes on columns contained in the WHERE clause
Use AWR/ASH reports to get collective information
Verify is statistics are current
Verify if indexes are valid and are adequate.
Verify is parallelism/materialized views/Baselines/SQL Profiles will help
Monitor V$SESSION_LONGOPS to detect long running operations
Decide on using Hints if those are helping.
Table partitioning can be thought of as an option based on kind and size of tables.

Perspective on SQL Tuning

Fire up your favorite search engine, enter “SQL tuning” as the search term, and you are likely to detect a common theme in the results. They typically relate to modification of the database structure, such as adding or removing indexes; modification of the execution environment, such as gathering or modifying optimizer statistics; or more-substantial modifications such as rewriting the SQL or changing the physical design of the database. What is often missing in those same search engine results is a warning that any such modifications carry an inherent risk to the entire database environment in which the SQL statement is running. The larger the modification, the higher the risk.

As Oracle Database has evolved, the number of tuning tools has grown and the level of sophistication of each of these tools has also increased, giving developers and DBAs a plethora of options to explore. One consequence is that it is easy to dive straight into utilizing these tuning tools without stepping back and asking a few key questions about the SQL statement first:

  • What is the business functional requirement being served by the SQL?
  • Is the SQL correct?
  • Can this SQL ever run fast enough to meet the requirements?

Even without diving into low-level tools, it is easy to forget these questions. I’m frequently in meetings to assist with performance tuning issues, and upon my arrival, often the first thing presented to me is a single SQL statement, with no context or explanation surrounding it, and this plea: “This is the problem! Please solve it.” It may seem counterintuitive, but the first step of SQL tuning is to forget about the SQL.

The Business Requirement

No organization I’m aware of has ever had a business model of “Let’s make sure SQL runs fast,” unless that business was a SQL tuning consultancy! SQL statements, the applications that run them, and the IT departments that build and support those applications exist to meet a suite of business functional requirements. Those requirements may be created by the business as part of its desire to thrive commercially, or they may be imposed on the business by regulatory bodies. In either case, satisfying business requirements must be kept uppermost in a developer’s mindset when it comes to tuning SQL, because it drives the very decisions made by developers in creating the database design and the SQL that runs on it. Ignoring the underlying business requirements is a catalyst for poorly performing SQL.

SQL Correctness

Once the business functional requirement has been confirmed, you can turn your attention to the SQL statement. Just because a SQL statement returns without error, or even if the statement returns the correct results, that does not constitute a guarantee that the SQL statement is actually correct. Often the cause of poorly performing SQL is malformed construction, which can easily slip through testing cycles if the query results are still plausible (especially with small test datasets). Hence, when I’m asked to tune a SQL statement, I will spend a few minutes before tackling any performance-related avenues making sure that the SQL statement does not have any obvious syntax errors. Here are some of the common errors I see that typically cause SQL statements to be misdiagnosed as performance problems.


For IT professionals, there is always the temptation of “Let’s just jump into the code,” no matter what language the code is written in, and SQL code is no exception. And when it comes to the tuning of SQL, that temptation is even greater, considering that Oracle Database offers such a plethora of SQL tuning facilities. Throughout the various versions of Oracle Database, technologies such as SQL profiles, stored outlines, SQL plan baselines, tuning advisors, and optimizer hints all provide enormous opportunities for developers to improve SQL code performance.