Oracle 18c became available on Oracle Cloud and Engineered systems in February 2018 and for DBaaS in March 2018. Though not yet available on-premise; the release cycle has begun.

So, where are we? Here’s a list of “recent” releases of the Oracle Database:

• Oracle 11g R1 August 2007
• Oracle 11g R2 September 2009
• Oracle 12c R1 June 2013
• Oracle 12c R1.0.2 June 2014
• Oracle 12c R2 Fall 2016 for Cloud (Exadata); March 2017 for all
• Oracle 18.1 February 2018 for Cloud (Exadata); March 2018 DBaaS

(the rest later this year?)

If you’re still lagging along on Oracle 11g (or any earlier release); it’s really time to get a move on!

18c? What Happened to 13-17?
Oracle is changing the way they release software and number products! Oracle has begun a more-agile, “dev-ops” release cycle. Products will now be released more-frequently and on a schedule. Each year starts with a release using the last two digits of the year; updates will be “dot” release. Oracle 12.2.0.1.0 is followed by Oracle 18c, an annual major release (you guessed it, next January will be version 19). 18.1 to 18.4 will be Quarterly Release Updates (kind of like the old Bundle Patches (BP)). 8.1.x will be called a Release Update Revision (RUR) featuring security and bug fixes (like old PSU). You can read more about them in mos note 742060.1 and 2285040.1

Oracle 18c Overview
Oracle 18c is an incremental upgrade to Oracle 12c rather than a big new release. Areas with notable improvements include:

• Multi-tenant, In-Memory, Sharding, and Security
• Reduction in undo requirements for RAC and Exadata
• Integration of Oracle Database and Microsoft Active Directory for authentication & authorization without Oracle Internet Directory
• APEX 5.1 various updates
• Several JSON, PL/SQL, Java improvements

Introduction to Oracle ADWC
Just before Oracle Open World 2017 Oracle announced the Oracle Autonomous Data Warehouse Cloud (ADWC); since then they have announced other Autonomous Cloud product are on the way. The idea is to create a database that takes care of itself and is: efficient, highly available, and provides lower costs. Oracle Autonomous Data Warehouse Cloud is built on top of Oracle Database 18c (or whatever the current Oracle Database version is at the time). ADWC represents cloud services on top of the database. Somehow “Autonomous” became synonymous with Oracle 18c – NOPE! Again, ADWC is built upon the Oracle database; the corresponding release happens to be 18c. According to Maria Colgan:

Autonomous Database = Oracle 18c

+ Oracle Cloud Automation and Innovation
+ Secret Sauce

What Do DBA’s Do?
To understand what Autonomous Database is about you must look at what DBA’s typically do. A DBA’s tasks can be loosely grouped into 2 types:

• Standard Database Tasks (ho-hum)

• Configuration of systems, network, and storage
• Provisioning, patching
• Optimizing database
• Backups, High-Availability, Disaster Recovery

• Business-Related Tasks (where the fun is)

• Architecture, planning, data modeling
• Security and data lifecycle
• Application-related tuning
• Managing SLAs

Is it Scripted/Scriptable?
Many of the day-to-day tasks DBAs are asked to do can be boring, mundane, repetitive, and error-prone. Most DBA’s I know have created or obtained scripts for these activities and the running of the scripts is then automated or delegated to junior people.

We All Know The Answer…

If something can be automated… IT WILL BE

The time is now to automate the boring stuff in our lives and embrace tools that take away tedium and mistakes.

Should I Be Worried?
If you’ve allowed your job to become one of repetitive, scriptable tasks – YES! It’s time to grow or move on.

If you’re known as “the one” who has the answers that bring value to your organization

– NO! You’ll have even more time to be the superstar with the other stuff out of the way.

Oracle’s Journey to Autonomy
Oracle has been automating our work for years; here’s a table of some of the automations already in place today.

Oracle Database 9i

Automatic Query Rewrite
Automatic Memory Management
Automatic Undo Management

Oracle Database 10g

Automatic DB Diagnostic Monitor (ADDM)
Automatic Workload Repository (AWR)
Automatic Storage Management (ASM)
Automatic Statistics Gathering
Automatic Standby Management (Broker)
Automatic Segment Space Management

Oracle Database 11g

Automatic SQL Tuning
Automatic Capture of SQL Monitor
Automatic Workload Replay
Automatic Data Optimization
Automatic Storage Indexes

Oracle Database 12g

Automatic Refresh of Database Cloning
Automatic Diagnostic Framework
Automatic Columnar Cache
Autonomous Health Framework

How many of these do you use regularly? Do you consider yourself less of a DBA because you use the tools provided?

Autonomous Data Warehouse Cloud
Oracle’s first Autonomous Database product is the Oracle Autonomous Data Warehouse Cloud; it became available in March 2018.

To learn more about ADWC check: cloud.oracle.com/datawarehouse

ADWC Vision
Oracle’s vision for Autonomous Database is that it be:

• Self-driving; the database automatically follows user-defined service agreements.

• Self-securing; the database is safe from both internal and external attack.

• Self-repairing; the database is robust and highly-available.

What is Oracle ADWC?

• Easy

• Automated provisioning, patching & upgrades
• Automated backups
• Automated performance tuning

• Fast

• Exadata: high performance, scalable, reliable
• Uses Oracle Database capabilities: parallelism, columnar processing, compression

• Elastic

• Elastic compute & storage scaling; 0 downtime
• Pay only for resources consumed

The DBA Job’s Evolution Continues
The DBA’s job has changed over the years; DBA tasks continue to evolve, so must we. DBAs of today need to spend less time on maintenance, more time INNOVATING, more time on BUSINESS, and more time reducing backlogs. Data is at the heart of what a modern DBA does; we help the organization get more value from data and improve developer data access. The cloud can help here. Experimentation is easier and cheaper in the cloud; take advantage of it. Take charge of cloud service levels make sure you are the go-to person.

Leaving ADWC
Autonomous Data Warehouse Cloud is part of today’s automation wave. The Autonomous Data Warehouse Cloud eliminates many of the tedious, boring, and thus error-prone activities (many DBAs have scripted these already). DBAs are freed up to spend their energy making real improvements to data structure and performance.

Oracle 12c – Lots Exciting Stuff

Oracle 18c is really an extension of Oracle 12c. Oracle 12c introduced lots of new and updated features, most notably:

• Oracle In-Memory Database
• Multi-tenant Architecture:
  (first architecture change to Oracle since V6 in 1988!)
• Container Database (CDB)
• Pluggable Database(s) (PDB)

• Performance Improvements:

• Improved optimization
• Enhanced Statistics & New Histograms
• “Heat” maps
• Adaptive Execution Plans
• Lots more (maybe another day)

What is Multi-Tenant?
Multi-Tenant architecture is designed to achieve two specific goals: improved performance along with ease of management and consolidation. Multi-tenant has two types of databases:

• Container Database (CDB) “Main” database contains up to 252 PDBs in 12.1 and 4096 PDBs in 12.2 and 18.1 (Oracle EE); or exactly one PDB (Oracle SE)

• Pluggable Database (PDB) – “Application” databases containing application/function-specific users and data

Memory, space, and other resource requirements are greatly reduced with Multi-Tenant. CDB and PDBs share: a single SGA, single set of database processes, single database to patch and/or upgrade (CDB), single database to backup (CDB), single configured container as standby database, and single configuration for High-Availability, Data Guard, or RAC.

What’s the Big Deal? Less memory and space are required. Before Oracle 12c: 30 database instances might require approximately 20 background processes (each) to run; or, about 600 processes. With Oracle 12c 30 PDBs will share 20 background processes (that’s it).

Reasons to use Multi-Tenant include: fast provisioning of new databases or copies of existing databases, fast redeployment to new platforms, ability to quickly patch and upgrade database version ONCE for all PDBs, patch/upgrade unplugging PDB from one CDB and plugging into CDB at later version. With Multi-Tenant a given machine can run more databases as PDBs. No changes are required to user applications (unless the connect string is changed by the DBA).

18c Multi-Tenant Highlights
Oracle 18.1 improves Multi-Tenant in several ways including:

• “Snapshot Carousel” allowing PDB clones from many points in time.
• “Refreshable PDB Switchover” to create a PDB based upon an updated copy of a master PDB reversing the clone-master roles.
• Improved DataGuard can automatically include all necessary standby data files in a clone operation (should simplify DR). May not clone from an Active DataGuard Standby
• Backups of PDB’s taken before unplugging from a container may be applied after plugging into new container.
• Each PDB may now have its own key store for security.
• PDBs may be managed using CDB “fleets” (group of CDBs)

Oracle In-Memory Database
Oracle introduced the “In Memory Option” as part of Oracle Database 12.1.0.2 (so far only for Oracle EE). Oracle database normally stores data in tables; one row after another (on disk, pulled into memory for processing). With In-Memory Option the database ALSO stores table data in columnar format in memory. Data in columnar format can speed some queries significantly. This is accomplished via the so-called In-Memory “Secret Sauce” – both row and columnar formats are in memory at the same time; the optimizer decides which data store will work best; the SGA “In-Memory Area” (new pool) stores as much as will fit.

Tables are added to memory with ALTER TABLE xxx IN MEMORY – which pivots data and adds to the columnar store.

Finally, In-Memory is part of the database; and transparent to applications once tables have been added.

18c In-Memory Highlights
Oracle 18.1 includes several improvements to In-Memory Database processing including:

• Improved single-key fetches via optimized lookup facility.
• Automatic population & aging-out of tables/partitions using Heat Map info.
• New light-weight threads to further speed scans.
• Number types use native representation greatly speeding calculations.
• External table data may be place in column store.

Oracle 18c & Active Directory Services
Oracle 18c features the integration of Active Directory Services with Oracle Database via the ALTER ROLE statement. Users may be authenticated and authorized directly using the Microsoft Active Directory. With Oracle 18c direct integration with Active Directory improves security making identity management configuration faster and easier by eliminating the difficulty and complexity required in earlier releases to integrate an Oracle Database to the enterprise directory service. It’s just this easy:

ALTER ROLE some_role IDENTIFIED GLOBALLY AS domain_name_of_dir_group

18c Developer Highlights
Oracle 18.1 has new features for developers including:

• APEX 5. Private Temporary Tables
• JSON SQL improvements
• JSON key maximum extended to 255 characters (from 64)
• SODA (Simple Oracle Document Access) for C and PL/SQL
• PL/SQL DBMS_HPROF (Hierarchical Profile) procedure
• Spatial improvements
• Graph improvements

18c APEX 5.1 Highlights
Oracle 18.1’s APEX 5.1 includes many new features (sorry, different topic, worth your time to look into sometime); the new features include:

• Page Designer Enhancements
• Interactive Grid
• New and Updated Packaged Applications
• Font APEX Icon Library
• Calendar Enhancements
• Oracle JET Charts

JSON Path Expression Item Methods
JSON Path Expressions first introduced in Oracle 12c may use ‘item methods’ to transform selected data. The JSON standard describes six item methods:

• 12c: abs(), ceiling(), double(), floor()
• 18c: size(), type()

Oracle extends the JSON standard with:

• 12c: date(), length(), lower(), number(), string(), timestamp(), upper()
• 18c: boolean(), booleanOnly(), numberOnly(), stringOnly()

(the “Only” methods will only transform the datatype listed; other ”non-Only” methods will attempt conversion)

Using Path Expression Item Methods changes a little between versions. Oracle 12c limits the use of item methods to the json_exists() function, Oracle 18c allows item methods in all querys including: json_exists(), json_value(), json_query(), and json_table().

Generating JSON

Oracle 12.2 provides SQL functions for generating JSON:

• JSON_OBJECT
• JSON_ARRAY
• JSON_OBJECTAGG
• JSON_ARRAYAGG

JSON Datatype Generation Oracle 12c and 18c
JSON generation functions in 12c limits data types to NUMBER, VARCHAR2, DATE, and TIMESTAMP. 18c generation now allows: BINARY_DOUBLE, BINARY_FLOAT, CLOB, BLOB, NVARCHAR2, RAW, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND (the resulting output JSON depends upon datatype input). Another new feature is that in 18c generation SQL may now return BLOB or CLOB values.

Oracle 18c Cancel Runaway SQL
Beginning with Oracle 18c you may manually terminate runaway queries using SQL.

ALTER SYSTEM CANCEL SQL ‘sid serial#’

/* optional parameters (within quotes) */
 @instanceid
 sqlid

Wrapping it all Up
Oracle 12c added significant new functionality to the already robust Oracle database environment; release 12.1.0.2, 12.2.0, and 18.1 add even more. Oracle 12c represents the first major architectural change to Oracle since Version 6. With the release of Oracle 18c it’s really time for your shop to finally move off of 11g R2.

Look for many new and improved features:

• Available in the Oracle Cloud first! (of course)
• Even smarter optimization
• Faster data movement
• Enhanced PDB management
• More stuff “in memory”
• Much more…

Check out the docs! (available online now) and watch for announcements!

About the Author:

John Jay King is a partner in King Training Resources. a
firm providing customized training for IT professionals since 1988. John has worked with Oracle database and its tools since Oracle Version 4; he is an Oracle Ace Director, member of the Oak Table Network, member of the Arizona Oracle User Group board, and long-time member of IOUG. Recently John has been training customers on IoT and Cloud topics in addition to Oracle products. John presents frequently at Oracle Open World and user group events in the USA and around the world.

Email:     john@kingtraining.com

Phone:    1.303.798.5727

Twitter:  @royaltwit

Website:        http://www.kingtraining.com/