Oracle 19c database new features

Dec 29, 2020

Share this post

issues-after-qlikview-version-upgrade-on-passive-node-of-production-servers

Introduction:

In January 2019, Oracle 19c was launched and is the final Oracle 12c product family release.

The latest release of its features and accessibility for companies will be discussed in this blog to assess the value of the upgrade to Oracle Database 19c.

Description:

The final long-term release of the Oracle 12.2 family of products is Oracle Database 19c. In order to be more in line with how Cloud releases are represented and counted, Oracle updated the version number scheme back to Oracle 18c (12.2.0.2). Oracle 19c is thus equivalent to 12.2.0.3 and will be supported by the end of 2024, with extended support until April 2027.

Key benefits of Oracle Database 19c are:

- Support:

For product longevity and patching, Oracle strongly recommends upgrading to 19c which is the Long-Term Release with a support end date of April 30, 2027 (or April 30, 2024 if you choose not to pay Extended Support fees or purchase a ULA).

- Upgrade:

If you are currently running 11.2.x/12.1.x you will need to upgrade to the terminal release (i.e. 11.2.0.4/12.1.0.2) for the DB Release you are running and then continue the upgrade process by upgrading to the 19c.

If you are currently running 12.2.0.1 or 18c, you should upgrade to 19c before the error correction grace periods for 12.2.0.1 and 18c expire.

- Install/Deploy:

19c can be deployed as a Bare Metal, Virtual Machine or Exadata database, or as Autonomous.

- Multitenant enhancement:

Oracle Database 19c further extends Multitenant’s operational efficiency advantage with several enhancements, including a new CDB Fleet feature.

In a CDB, we can manage many PDBs as one. Now, with CDB Fleet, we can manage many CDBs as one. The multitenant architecture of Oracle Database 19c cost-effectively enables tenant isolation, and agility with economies of scale, whether deployed in the Oracle Cloud, on-premises or hybrid cloud environments.

Some of the new features of 19c:

- Real Time Statistics for Conventional DML Operations:

Statistics can become stale between each execution of DBMS_STATS for statistics gathering jobs. By gathering few statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS and fresh statistics enable the optimizer to produce more optimal plans.

- Propagate Restore Points from Primary to Standby Site:

Restore points from primary database will be propagated to standby site, so that those are available even though after a failover operation. It also simplifies complexity of restore and recovery process on post failover since standby database is updated with the restore points from primary database.

- Automatic Indexing:

Automatic indexing is a great feature to create, rebuild and to drop indexes in an Oracle database based on application workload. It also improves the database performance by managing indexes automatically.

Syntax:

To configure, run EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);

To enable automatic index, run

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’REPORT ONLY’);

To turn off, run EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF’);

To check automatic indexing, run below.

declare

report clob := null;

begin

report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();

end;

- Active Data Guard DML Redirect:

Customers can get more use from an active standby database by running reports and backups against it. In Oracle Database 19c, Oracle enables you to do transactions against the standby database.

To enable SYSTEM LEVEL DML Redirection, run alter system set ADG_REDIRECT_DML=TRUE scope=both;

To enable SESSION LEVEL DML Redirection, run alter session enable adg_redirect_dml;

- Hybrid Partitioned Tables:

This feature enables database administrators to manage a table between partitions inside the database and partitions held on low-cost read-only datastores outside the database. These datastores can reside on premises or in the cloud.

- JSON Support:

Support for JSON gets improved. Oracle improved and simplified the syntax for our JSON functions and introduced the capability to do a partial JSON update. In addition, Oracle Database 19c includes new Simple Oracle Document Access (SODA) APIs for Java, Python, C, and Node.js.

- SQL Quarantine:

SQL query statements which consume high CPU and I/O resources will be terminated by Oracle DBRM and automatically get quarantined to prevent to execute again in order to protect database from performance degradation from such queries.

To enable quarantine configuration, run DBMS_SQLQ.ALTER_QUARANTINE

To view details of quarantine configuration, run select * from DBA_SQL_QUARANTINE;

To delete the quarantine configuration, run DBMS_SQLQ.DROP_QUARANTINE

To view details of quarantined execution plans of sql, run select sql_quarantine,avoided_executions from gv$sql where sql_id=’&1′;

As seen above, the Oracle 19c database route from the 11gR2, 12cR1, 12cR2 and 18c versions has a clear upgrade. An update to Oracle Database 19c explicitly is not supported for the other versions/releases of the above-mentioned databases. However, you must first upgrade to an intermediate Oracle database release and then upgrade to 19c.