I’ve just noticed that Oracle 19c documentation is online here, so I’m writing this post after going over the “New Features” guide and finding some interesting stuff there.
DISTINCT option for LISTAGG aggregate
Listagg was introduced in 11.2, but now (finally) Oracle added the distinct option as well. You can read more about it in Connor’s post.
Simplified Database Parameter Management in a Broker Configuration
Data Guard parameters can now be changed using “ALTER SYSTEM” command in the database (so there will be no consistency issue between DG broker configuration and the database configuration).
Also, a new syntax of DGMGRL “EDIT DATABASE” command will allow changing a parameter on all databases in the configuration instead of doing that one by one.
Flashback Standby database when Primary database is flashed back
A mounted standby database can automatically follow the primary after a RESETLOGS in cases of “FLASHBACK DATABASE” or Point In Time Recovery. This will keep the standby in sync with the primary without any manual steps.
If the standby is open in read only mode, this will happen only after a restart of the standby database.
Active Data Guard DML Redirection
Active Data Guard is open in read-only mode. With this feature, DML operations against the Active DG database will be redirected to the primary instead of failing. This way we can simplify cases when an application is resource intensive in queries but rarely needs to update some data as well.
Real-Time Statistics
Updated statistics are crucial for the optimizer to perform well, but fast changing tables are challenging. In Oracle 10g, Oracle started gathering stats automatically when creating a new index. In 12c, statistics are automatically updated for “create table as select” commands. In 19c, Oracle will follow conventional DML operations and will keep some statistics about the changing table. This data is not a complete set of statistics and “gather statistics” is still needed, but it will provide more information to the optimizer when using this table, so it will generate a more accurate plan.
DBCA silent mode new capabilities
Starting with Oracle 19c, DBCA can be used in silent mode to perform duplicate of an existing database, clone a remote PDB and relocate a PDB to another CDB. This makes it easier to automate these processes and embed them into scripts.
SQL Quarantine
If a SQL was terminated by the resource manager because it reached the limit of I/O or CPU allowed, Oracle 19c can automatically quarantine the execution plans for this SQL. To do this, we need to create a quarantine plan (using DBMS_SQLQ) for a specific SQL (and optionally specific execution plans) with specified limits. Once the quarantine plan is enabled, if a plan is exceeding the limits, Oracle will disallow this plan to be executed again and therefor protect our system from resource intensive execution plans.
Real-Time SQL Monitoring for Developers
Finally! Users without the SELECT_CATALOG_ROLE will still be able to access SQL monitoring information. Users without this role won’t be able to see execution statistics and details for other users in the system.
Workload Capture and Replay in a PDB
Oracle 11g introduced the Real Application Testing (RAT) feature. This allows to capture activity from a source database (that can be 9i or 10g as well) and replay it on a target database (11g or newer) to check the affect of upgrade or any infrastructure change in the system. Until now RAT could capture only at the CDB level. From 19c, RAT can capture and replay workload at a PDB level as well, allowing us better use of it in a consolidated environment.
