What Oracle 12cR2 brought us in 2017 and what Oracle brings us for 2018

By Deiby Gómez (OCM11g , MAA OCM 12c and Oracle ACE Director)

The year 2017 brought us a lot of good Oracle stuff; Oracle Database version 12cR2 was released for On Premises, and the new Autonomous Database 18c was announced at Oracle Open World in San Francisco. In this article I will bring to your attention some of the best new features of Oracle Database 12cR2 and what to expect in 2018 from Oracle 18c and the Oracle Autonomous Database.

New Features in Oracle Database 12cR2

Materialized Views: Statement-Level Refresh: Oracle Introduced the “ON STATEMENT” clause to refresh materialized views. With this refresh mode, any changes to the base tables are immediately reflected in the materialized view. There is no need to commit the transaction or maintain materialized view logs on the base tables. If the DML statements are subsequently rolled back, then the corresponding changes made to the materialized view are also rolled back.

For more information about this feature you can read this article:http://blog.toadworld.com/2017/08/28/oracle-database-12-2-statement-level-refresh-for-materialized-views

Oracle Database 12.2 – How to track index usage: Oracle introduced two views, V$INDEX_USAGE_INFO and DBA_INDEX_USAGE. With these two new views, Oracle automatically tracks the usage of indexes. There are several columns in dba_index_usage that can be used to find out how many accesses the indexes have received, how many rows have returned, and, even better, there are buckets to create histograms for accesses and rows returned. The most recent time that the index was used is also recorded.

For more information about this feature you can read the following article: http://blog.toadworld.com/2017/07/25/oracle-database-12-2-how-to-track-index-usage 

Oracle 12cR2 RMAN New Feature: UNTIL AVAILABLE REDO: In Oracle database 12.2.0.1.0 the clause “UNTIL AVAILABLE REDO” is available. As its name indicates, this clause makes all the required calculations to recover the database up to the last available archive log. This is a really cool feature, since all the DBA has to do is catalog all the archivelogs available and use “UNTIL AVAILABLE REDO” in the “RECOVER DATABASE” phase, and Oracle will do all the work., This also lets us avoid human error in the calculations.

For more information about this feature you can read the following article:  http://blog.toadworld.com/2017/06/02/oracle-12cr2-rman-new-feature-until-available-redo

Oracle Database 12cR2 new feature: Lockdown Profiles: One of the most important features is “Lockdown Profiles”. Lockdown Profiles provides the granularity we were talking about. With this feature you can enable and disable database functions, features and options. It even lets you specify a range or list of values that may be used.

For more information about this feature you can read the following article:  http://blog.toadworld.com/2017/05/24/oracle-database-12cr2-new-feature-lockdown-profiles

Oracle Database 12cR2 new feature: Proxy PDB: A Proxy PDB is physically an empty PDB that has the minimum tablespaces required (SYSTEM, SYSAUX, UNDO), created in one CDB that references a remote Pluggable Database in a different CDB. All the operations (DDLs & DMLs) that are executed within the Proxy PDB are sent to the referenced Pluggable Database and remotely executed in it, except for the operations ALTER PLUGGABLE DATABASE and ALTER DATABASE.

For more information about this feature you can read the following article: https://blog.toadworld.com/2017/05/24/oracle-database-12cr2-new-feature-proxy-pdb

Introduction to Application Containers in Oracle Database 12cR2: This new feature helps developers a lot with the day-to-day tasks. With "Application Container", developers can create applications, every application can have its own data and version and developers decide which database should have which version of the same application and when to refresh the data. With "Application Containers" the developers keep the objects and data only in one side, not in every database in the organization, and sync from that principal side all the dependent databases.

For more information about this feature you can read the following article: https://blog.toadworld.com/introduction-to-application-containers-in-oracle-database-12cr2

Oracle Database 12cR2 new feature: Application Root Replica: Application Root Replica is a physical replica of a master Application Root but in another remote Container Database. This lets us synchronize applications in an Application Container across different and remote Container Databases without using solutions like RMAN, Data Pump, or remote cloning.

For more information about this feature you can read the following article: http://blog.toadworld.com/2017/05/24/oracle-database-12cr2-new-feature-application-root-replica

Oracle Database 12cR2 new feature: Container Maps: Container Maps allowhs  to use PDBs as if they were partitions. With PDB as partitions we can query data across all the PDBs in the CDB by filtering the data by a key.

For more information about this feature you can read the following article: http://blog.toadworld.com/2017/05/24/oracle-database-12cr2-new-feature-container-maps

Introduction to Oracle SQL Plan Directives in Oracle Database 12.2: Oracle SQL Plan Directives is part of the category “Adaptive Statistics”.  Basically, they are notes that the optimizer writes and stores in the database to “adapt” itself to the environment or data changes.

For more information about this feature you can read the following article: http://blog.toadworld.com/2017/04/13/introduction-to-oracle-sql-plan-directives-in-oracle-database-12-2

Oracle DB 12.2 Local Undo: PDB undo tablespace creation: Local Undo is a new kind of undo configuration for Multitenant Architecture and it is a new feature introduced in 12.2.0.1.0. When we say "Local Undo" basically we are saying that every Pluggable Database will have its own Undo Tablespace.

How to solve user errors with Oracle Flashback 12cR2 and its enhancements: Flashback Database has had several enhancements since it was introduced, with the biggest enhancements in 12.1 and 12.2. In Oracle Database 12.1 Flashback Database supported Container Databases (CDBs) supporting the Multitenant Architecture, however Flashback Database at the PDB Level was not possible. In Oracle Database 12cR2 Flashback Database added support at the PDB level.

Near Zero Downtime PDB Relocation in Oracle Database 12cR2: Two features that I really like are "Hot Cloning" and "Online Relocation". Basically it is the same feature as in 12.1.0.2 for cloning locally and remotely but now they can be done online. The source PDB can be in read-write.

What’s coming for 2018?

Well, in Oracle Open World 2017 Larry Ellison introduced the World’s first Self-Driving Database.  People have been using the terms “Autonomous Database” and “Oracle 18c” interchangeably. They different concepts, the best definition I have found is delivered by Maria Colgan in this article: “The Autonomous Database is a Cloud service running on top of Oracle Database 18c along with additional services to provide performance and availability SLAs.”

Oracle 18c itself is just the software with several new features but in itself is not an “autonomous database”. Oracle 18c is not released yet for on-premises databases; however, I took some notes at the OOW session delivered by Joan Loaiza about autonomous database. The following are some of the notes:

The difference between Automated and Autonomous:

• The customer can choose to just use automation or hand over all management to Oracle Cloud Operations for Autonomous operation.

• If the customer hands over management to Oracle then:
  • Database and OS Administrator Privileges are not needed and not provided
  • Exception and failure cases are handled by Oracle Experts

• The payoff is huge – Eliminate generic tasks, reduce labor, reduce costs, reduce errors, while increasing security and availabilty.

Autonomous Database removes generic tasks:

DBAs will have more time to innovate and improve the business.

• Tasks Specific to business
  • Architecture, planning, data modeling
  • Data security and lifecycle management
  • Application related tuning
  • End-to-end service level management

Automatically Diagnoses Performance

• Automatic Database Diagnostic Monitor (ADDM)
  • Automatically diagnoses root cause of performance issues
  • Active Workload Repository (AWR)
    • Automatically keeps detailed performance and resource utilization history
    • Real-Time SQL Monitoring
      • Automatically diagnoses how resources are used in SQL statements.
      • Many database algorithms self-optimize – caching, locking, storage indexes, offload, etc.
      • Automatic SQL re-tuning using machine learning.

The autonomous database subscription includes:

• Data Encryption
• Diagnostics Pack
• Tuning Pack
• Real Application Testing
• Data Masking, Redaction and Subsetting
• Hybrid Columnar Compression
• Database Vault
• Database in Memory (subset) – In Autonomous Data Warehouse
• Advanced Analytics (subset) – In Autonomous Data Warehouse

Conclusion

Since Oracle Database 12cR1, released in 2013, Oracle has introducing several functionalities that support Cloud adoption; in Oracle Database 12cR2 those functionalities were improved even more, introducing everything “online” so that no interruption is needed. Additionally, new features were introduced to support Cloud adoption totally. Starting in Oracle 18c and Autonomous Database, Oracle wants to offer companies a self-driven database-as-a-Service in Oracle Cloud. Definitely a futuristic service that can be used only in Oracle Cloud. Several new things are coming for 2018, looking forward to see them!