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

    Jan 26, 2018 2:05:27 PM by Deiby Gomez

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



    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!

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator