First take on Oracle 19c: 6 useful enhancements for DBAs

With Oracle now releasing a new version of Oracle Database every year, it's time for database administrators like me to delve into the new features that the company is providing in the 2019 update -- Oracle Database 19c.

As has been the case with the last few versions of the database software, including Oracle Database 18c last year, Oracle 19c is being released in stages. Oracle first made the new release available in January 2019 on Live SQL, its free SQL development and tutorials platform; in mid-February, 19c also became available in on-premises versions of the Oracle Exadata engineered system.

Oracle has said it plans to add Oracle 19c to its other engineered systems and its cloud database services before the end of March. Customers that run Oracle databases on conventional on-premises systems will have to wait a little longer for 19c -- it's due for release on a variety of server platforms during the second quarter of the calendar year.

Database administrators (DBAs) who want to try out Oracle 19c and don't have an Exadata system installed can work with the new database on the Live SQL site. As you can see in the screenshot below, Live SQL now runs the production version of Oracle Database 19c Enterprise Edition. To use the cloud-based platform, you'll need an Oracle single sign-on account; registering for one is free if you don't already have an account.

While Oracle has said improved stability is its top development priority for Oracle 19c, the software does include some new features. Oracle officials gave us a nice preview of them at Oracle OpenWorld 2018, and it has now posted the 19c documentation online. Here's a list of some of my favorite new features in 19c, based on what I've seen so far:

• Schema-only accounts. If you create a user account in Oracle 19c without specifying a password, it won't include the ability to log in to the database. This is a great way of creating an account to be the schema owner for database objects, while protecting it from unauthorized access. In a 19c database, the ACCOUNT_STATUS column of the DBA_USERS view will show a value of NONE for schema-only accounts.
• Automatic indexing. This new feature puts Oracle's automation capabilities to work. If Oracle 19c thinks a database table would benefit from an index, the system will automatically create the index and initially mark it as invisible so it can't be used. Oracle 19c will then run SQL statements from your application to see if the index improves query execution. If the index does help, it will be made visible for application use; if it doesn't do so, the index will become unusable and eventually be removed. You can control this feature with DBMS_AUTO_INDEX, a new PL/SQL package that's included in 19c.
• Quarantining of runaway SQL statements. Using Oracle's Resource Manager tool is a great way to make sure SQL statements don't become resource hogs and slow down database performance for everyone. If a statement asks for more system resources than the DBA allows, Resource Manager kills it. However, in existing versions of Oracle Database, nothing stops users from executing problematic SQL statements again. In Oracle 19c, Resource Manager can automatically quarantine the statements; should a user try to issue one again, it won't run at all.
• Automatic flashback of standby databases. In prior versions, if DBAs wanted to use Oracle's flashback features to return a primary database to a previous state, they needed to manually rebuild an associated standby database before it could resume normal operations. In Oracle 19c, a DBA can put the standby database in MOUNT mode with no managed recovery and then flash back the primary one; the standby will also be reverted, thus keeping it in sync with the primary.
• Built-in privilege analysis. A good DBA likes to give database users the least amount of privileges needed for them to be able to perform their jobs. Oracle's privilege analysis feature reports on privileges that users have been assigned but haven't used; DBAs can use this information to create a stricter security policy without taking away any privileges that users need. The feature was originally released in 2014 as part of Oracle Database 12c, but it previously required the extra-cost Oracle Database Vault software. The Enterprise Edition of Oracle 19c now includes privilege analysis as a core feature.
• Read/write Oracle Multitenant source databases. If you use the optional Oracle Multitenant architecture technology, you can clone a pluggable database (PDB) to create a duplicate one. Previously, if the clone was a snapshot copy that used sparse files without much data in them, the source PDB needed to be set to read-only and had to stay that way for the life of the clone. With Oracle 19c, the source PDB can now support both reads and writes.

That just scratches the surface of the new features in Oracle Database 19c. It's a new year, with a new version of Oracle's flagship product becoming available, which means that there's, once again, much to learn for all of us who are Oracle DBAs.