Connect OAC with ADW

To connect OAC with ADW requires two configurations. One connection for use with DV and one for use with OAC Answers & Dashboards.

To create both connections, first download the Security Wallet from ADW and create the Admin Password (if not already done).

Prepare configuration of ADW

Download Wallet:

1) Go to ADW instance and click DB Connection Button

image

3) click Download Wallet

image

which stores the .zip file to you local drive:

image

This .zip file can already be extracted, since the tnsnames.ora and the cwallet.sso file are needed for later steps:

image

Set Admin Password

If not already done navigate to the ADB Service Console

image

and click Administration and set the Admin password:

image

Configure Connection for ADW of OAC (Answers & Dashboards)

To configure the connection to ADW for Answers and Dashboards of OAC navigate to the OAC Service with a similar URL like below:

https://***tenant.name***.analytics.ocp.oraclecloud.com/dv/ui?pageid=home

and click on Console:

image

click on Connections under Configuration and Administration

image

click on Upload wallet on the right Burger Menu:

image

This allows to upload the previously downloaded wallet from the hard drive (you may need to acknowledge a virus scan warning before):

image

After uploading the Wallet successfully a success message will be displayed:

image

After click the Burger Menu on the right again the button will now display “Replace Wallet” instead of “Upload Wallet”:

image

After the Wallet has been uploaded, the connection can be defined using the Create Button on the right. Within this Menu its important to select “Host, Port and Service Name” from the Connect Using dropdown Menu and enable SSL.

image

The Host, Port and Service name can be retrieved from the extracted tnsnames.ora file of the Wallet. This file can be opened in any editor to check the values:

image

The Connection will be displayed in the list:

image

A new metadata model can now be created and uploaded to OAC using this connection.

Configure Connection for ADW of DV (Data Visualisation)

On the Homepage of OAC click Data and select Connection:

image

click ADW from the available connection types:

image

specify the connection details and upload the extracted sso credential files from the wallet (cwallet.sso):

image

The connection will be displayed in the list of connections:

image

After the connection is established, a Data Set can be created using this Connection to ADW.

Overview of Oracle Autonomous Database Cloud benefits

The Oracle Autonomous Database runs Oracle Database on the Exadata platform with infrastructure automation and a fully automated data center operations by Oracle. Autonomous Data Warehouse(ADW) is optimized for complex analytics, while Autonomous Transaction Processing(ATP) is optimized for high-throughput transaction processing. Thus, Autonomous Transaction Processing (ATP) only differs from Autonomous Database Warehouse(ADW) when it comes to the optimization for each specific workload within the database.

E.g. when data is loaded into the Autonomous Database, the data is stored in a appropriate format for each workload. If it’s in Autonomous Data Warehouse, then data is stored in columnar format (which is best format for analytics processing). If it’s Autonomous Transaction Processing, then the data is stored in row format as that’s the best format for single-row look-ups and Indexing.

Both Autonomous Services provide capabilities for: provisioning, patching, upgrading, online backups, monitoring, scaling, diagnosing, performance tuning, optimizing, testing, and automatically handling of failures and errors.

By this, Oracle has created a new kind of category for Database Services.

image

The autonomous database provides a service that is self-driving, which will automatically take care of all database and infrastructure management as well as monitoring and tuning. The database is also self-securing, protecting itself from both external and internal malicious attacks by automating encryption of all data and automatically applying security updates with no downtime. It’s also self-repairing by automatically recovering from any failure and thus minimizing downtime (including planned maintenance), with an SLA guarantee of 99.95% availability.

Self-Driving

The self-driving capabilities include the ability of rapid provisioning a RAC and Exadata based Database within less then half an hour, self-scaling of the infrastructure (CPUs/Memory/Storage) using the web Console or REST-APIs and automatic tuning (automatic indexing). In autonomous database, optimizer statistics are gathered automatically, if users need additional statistics, they can gather stats manually at any time. Machine learning also allows autonomous database to optimize executions based on usage patterns of each database. By default, optimizer and parallel hints are ignored, since this will be automatically managed by the database.

Oracle’s recommendations of the self-tuning provided by the autonomous database include:

  1. tables do not need to be partitioned. If databases are being migrated to autonomous database partitioning should be removed, unless there’s a specific operation reason for use.
  2. indexes should not be used on tables for performance reasons. Only for application compatibility or referential integrity, these should be used.
  3. autonomous databases use compression of data in the database. So additional compression does not need to be used.
  4. in-memory tables cannot be used in autonomous database, since they’ll be created automatically when needed
  5. tables spaces do not need to be created, since they’ll be created automatically
  6. Manual tuning with partitioning, indexes, materialized views, and compression is available, but should only be used with careful consideration (such as in cases where migration of an existing system whose data loading scripts rely on partitioning or indexing is used for referential integrity.)

The database execution plans will change as the data distribution changes. With auto-indexing, new indexes will be added as the workload continues. Changes in data volume and SQL workloads are continuously captured into a SQL repository with SQL plans, bind values, execution statistics, etc. The system then identifies candidates for indexes that may benefit the newly captured SQL statements and will create these. There is also a monitor capability for administrative users which monitors index creation, status and usage.

Two functions of the automated management feature of ADB are backups and patching. Backups are scheduled on a nightly basis to Database Backup Cloud Service with a retention period of 60 days. The cost of backup and storage is included with the price of ADB. A GUI console shows detailed information about backups that have been taken and allows to restore from any of them. Full-stack patching is done once a quarter in rolling fashion across nodes of cluster to maintain the availability of the service. Time to apply patches is automatically selected by Oracle Cloud operations, but customers can override the selection and select an alternate time.

Because database applications are part of a much larger ecosystem controlled through scripting or other tools, autonomous database provides REST APIs to perform any supported operation e.g. database creation, termination, backup, restore, start, and stop.

Monitoring capabilities for CPU usage, memory allocation, sessions etc. are available through the Cloud Service dashboard with an UI but can also be performed through Enterprise Manager Cloud controls. Developers and DBAs can use the SQL Developer tool for performing DBA management operations.

Using Oracle REST Data Services, ORDS developers can easily build REST APIs for data and procedures in the database. Connecting to the autonomous database is done using credential wallets via SQL*Net, JDBC, or ODBC.

Self-Securing

To protect all data, it is stored in encrypted format (only authenticated users and applications can access the data). All connections use certificate-based authentication and Secure Socket Layer, SSL. Thus, communications between the client and server are fully encrypted and keys are stored in a wallet on both the client and the server.

Patching is very expensive, because it requires downtime and several man hours to patch all the databases in environments. Also it’s an ongoing effort. Autonomous database will patch the systems while the database is running and doesn’t need any downtime or manual effort. This protects the data from any known cyber-attacks.

No logins as root or user are allowed to the Operating System Level (OS) or SYSDBA  and no call-outs from the database to the OS. This prevents installing or modifying any software on the system. Database clients can connect securely using a TLS wallet. Oracle automatically applies updates and the latest security patches on a quarterly or off cycle for high-impact security vulnerability.

Oracle tools leveraged by autonomous database for security are Data Masking and Database Vault. Masking of data can apply security policies as data leaves the database i.e. convert social security number to a representation like xx and the last four digits of the number. Static masking for test/dev databases can simply convert these sensitive fields. Metadata tagging is part of the label security option and data can be marked as sensitive, confidential, etc.

Database auditing is configured by default and customizable to meet specific needs. Autonomous database comes pre-configured using Oracle Unified Audit. This feature includes automated auditing for privileged user activity and login failures and optional pre-configured policies for the Center for Internet Security audit benchmarks, account management, and much more. The audit trail is available for retrieval through service REST call invocation. Future release will include detailed auditing through additional security services.

This defense in-depth, it is built over 30 years of on-Premise database experience with demanding organizations and high-threat environments.

Self-Repairing

There is a broad range of events that can cause database downtime, including component, storage, and servers failures, Human error (table that was dropped cable that was accidentally unplugged, or a tape that was lost), database crashes, or even site-wide or regional outages due to a natural or man-made disaster. Also data corruption can cause incomplete backups or render the data useless. These represent a significant role in many cases, for unplanned downtime. Additionally, planned activities such as patching, upgrades and maintenance are representing an increasingly disproportionate percentage of overall downtime for many growing organizations.

Oracle Autonomous Database performs automatic backups. Those backups get stored in private storage defined in the Oracle Object Store and no setup is required. Autonomous database allows users to create their own additional backups for other operational purposes, including point in time recover if needed.

Exadata continuously monitors for failing devices, it also provides redundant database servers that provide active, highly available cluster servers, hot-swappable power supplies and fans, redundant power distribution units, provides redundant storage grids that provide data mirrored across storage servers, and redundant, non-blocking IO paths, and redundant networks that include redundant InfiniBand (IB) connections and switches. The self-healing software automatically runs all monitoring and fault prevention tools in the background 24 hours a day.

Oracle also uses machine learning algorithms like anomaly detection, pattern recognition for problem signatures (video here) to detect and prevent issues and failures and fix known issues or raise SRs on the customers behalf.  With cloud region-based duplication, database hardening, RAC redundant compute, triple-mirrored storage, autonomous Database provides 99.95% service availability through the stack.

Oracle Autonomous Database storage is on Exadata Storage Servers, which are directly attached to the Exadata compute nodes. Users of the autonomous database are matched through load balancers that distribute load across available services.

Oracle has addressed all of these causes of downtime and disruption in on-premises environments for decades with the Oracle Maximum Availability Architectural, Oracle MAA. Oracle MAA is a set of advanced technologies and best practices that can be deployed to handle any service-level requirement, with solutions ranging from periodic backups to zero data loss and zero-downtime replication based disaster recovery. The MAA portfolio is also available in the Oracle Cloud and has been enhanced with automated functionality that minimizes or eliminates human intervention.

ADWC Performance Benchmark on SSB Schema

The SSB Schema is a generated database schema, that can be used to evaluate the Performance of a Datawarehouse system. This is used to evaluate the performance between ADWC in the cloud and a local Oracle Database which is running within a Virtual Box image on a Laptop. This test is not conducted under test lab conditions and should only provide a rough order of magnitude.

System (Hardware) Baseline

The following instances have been used for the Performance test.

ADWC Trial

based on Hardware specification shown:

image

This instances is only used as reference, since the sizing (with 2 CPUs) and query performance for some queries based on the same SSB schema are publically available.

ADWC

instance which only uses 1 CPU

VM Image

with 4GB Ram

image

and 1 CPU

image

Based on Laptop Host system (Lenovo Yoga 370 Laptop) with:

image

The image runs Oracle Database 12c R2 Enterprise Edition, with no specific/tuned configuration (parameters) for Datawarehousing.

System Schema Baseline

image

Benchmark Performance

For Prologue Queries based on these examples. As mentioned before, for the ADW instance “ADWC Trail”, some queries run times are publically available. These queries have also been executed within the other instances. The runtimes of the ADWC Trail have only been used as reference:

image

SSB Queries, as outlined here and here (Oracle Documentation).  The below shows the query run-times for the ADWC and local Oracle Database (running as a Virtual Box image). The connection to the ADWC instance is made with the high connection type. When using the medium or low connection type, all queries were showing longer response times (as expected). Thus, the high connection type has been used for conducting the Benchmark.

image

The Excel file containing the system baseline in terms of number of records, queries and run-time results can be downloaded here.

Below is a galary of the executed SSB Queries on ADW and Virtual Box Oracle Database 12c: