Oracle 19c - 6 Useful Enhancements for DBAs!

March 24, 2020  

Oracle 19c - New Features:

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 Open World 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.
  
  Source: Oracle Documentation