Get Started with EPM Cloud Planning

Additional guides:
Getting Started with Oracle EPM Cloud for Administrators:

Getting Started with Oracle EPM Cloud for Users:


choosing the right shape for OAC

when provisioning Oracle Analytics Cloud, different shapes are available to run the Service. For shapes based on only 1 OCPU auto scaling will not be available after the initial provisioning of the service. Thus, if the OAC service needs to support scaling after the initial creation a shape with at least 2 OCPUs should be selected:


Golden Gate available for free on Oracle OCI

Golden Gate software available for free on Oracle OCI

Golden Gate is available for free on OCI Marketplace until May 2020. Any customer of the cloud service must only pay for the compute resources of OCI which are being used. This will only be available until and a customer must use the OCI Marketplace. The OCI shape is available here:

Golden Gate can be used to synchronize a Autonomous Datawarehouse from a on-Premise source with near real-time data feeds:

for more information on Golden Gate on OCI, check here.

Using Match Recognize SQL to retrieve knowledge within data series

The match recognize clause can be used since Oracle Database 12c. This clause is useful for data transformation of sequence data i.e. web session data. Considering a simple data set with web sessions of only two Users where the time_id denotes a point in time on the time axis and User_id the respective User:


A web session is defined as a series of action (on the timeaxis) within a gap of less then 10 time units.

Match recognize allows to transform the above data into partitions that meet certain conditions e.g. actions within less the 10 units from the previous action. Using the following statement on above Table:

TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
PARTITION BY userid ORDER BY time_id
MEASURES match_number() as session_id
PATTERN (b s*)
s as (time_id - PREV(time_id) <=10)

The statement will create a new column that specifies a session indicator:


The statement can be enhanced to provide even more detail about the session in terms of start and end time and duration:

TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
PARTITION BY userid ORDER BY time_id
MEASURES match_number() as session_id,
COUNT(*) as no_of_events,
FIRST(b.time_id) start_time,
LAST(s.time_id) end_time,
LAST(s.time_id) - FIRST(b.time_id) session_duration
PATTERN (b s+)
s as (time_id - PREV(time_id) <= 10)

This will return the following calculations on the session:



Thus, using match recognize the data can also be aggregated to calculate measures.

A complete tutorial to create the test data can be found here.

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.


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.


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.


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.


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.