Oracle Database 19c New Features for Adminstrators

Oracle Database 19c, is the long term support release of the Oracle Database 12c. It belongs to the family Oracle Database 12.2.0.x and 19c is basically Oracle Database 12.2.0.3.Premier and Extended Support for the Oracle 19c is through to March 2023 and March 2026 respectively.

You can download Oracle database 19c from the below link
Download 19c

Oracle 19c offers lot of new features. Here is the some significant features of oracle database 19c new features from my point of view.

(1) Automatic Indexing

This is one of the important Oracle database 19c New feature.

Automatic Indexing is one of the great feature included in 19c .It automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle Database based on changes in the application workload.

This feature improves database performance by managing indexes automatically in an Oracle Database.
Oracle has provided new package DBMS_AUTO_INDEX for this

How to configure it

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

This enables automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

This puts it off

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

You can check automatic indexing configuration settings in the view
DBA_AUTO_INDEX_CONFIG

Report to find out auto-indexing operations

declare
   report clob := null;
 begin
   report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();
 end;

(2) SQL Quarantine

SQL statements that are terminated by Oracle Database Resource Manager due to their excessive consumption of CPU and I/O resources are automatically quarantined. The execution plans associated with the terminated SQL statements are quarantined to prevent them from being executed again.

This feature protects an Oracle Database from performance degradation by preventing execution of SQL statements that excessively consume CPU and I/O resources.

Enable quarantine configuration
A quarantine configuration is enabled by default when it is created.

 DBMS_SQLQ.ALTER_QUARANTINE

Viewing the Details of a Quarantine Configuration

select * from DBA_SQL_QUARANTINE ;

Deleting a Quarantine Configuration

The unused quarantine configurations are automatically purged or deleted after 53 weeks. You can also delete a quarantine configuration using the DBMS_SQLQ.DROP_QUARANTINE procedure. You can disable automatic deletion of a quarantine configuration using the DBMS_SQLQ.ALTER_QUARANTINE procedure.

Viewing the Details of Quarantined Execution Plans of SQL Statements

 select sql_quarantine,avoided_executions from v$sql where sql_id='&1';
 select sql_quarantine,avoided_executions from gv$sql where sql_id='&1';

(3) Automatic Database Diagnostic Monitor (ADDM) Support for Pluggable Databases (PDBs)

You can now use Automatic Database Diagnostic Monitor (ADDM) analysis for pluggable databases (PDBs) in a multitenant environment.
ADDM analysis at a PDB level enables you to tune a PDB effectively for better performance.

(4) Passwords Removed from Oracle Database Accounts

Most of the Oracle Database supplied schema-only accounts now have their passwords removed to prevent users from authenticating to these accounts.
This enhancement does not affect the sample schemas. Sample schemas are still installed with their default passwords.

Administrators can still assign passwords to the default schema-only accounts. Oracle recommends changing the schemas back to a schema-only account afterward.
The benefit of this feature is that administrators no longer have to periodically rotate the passwords for these Oracle Database provided schemas. This feature also reduces the security risk of attackers using default passwords to hack into these accounts.

(5) Privilege Analysis Now Available in Oracle Database Enterprise Edition

Privilege analysis is now available as part of Oracle Database Enterprise Edition.
Privilege analysis runs dynamic analysis of users and applications to find privileges and roles that are used and unused.
Privilege analysis reduces the work to implement least privilege best practices by showing you exactly what privileges are used and not used by each account.

Steps To Setup Privilege Analysis
(1) Create: DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
(2) Enable : DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE
(3) Wait: Leave the policy enabled for a period of time: hours, days, weeks, months.
(4) Disable DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE.
(5) Generate Reports DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT.
(6) Take action whereever appropiate
(7) Drop: DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE.

(6) New ALTER SYSTEM Clause FLUSH PASSWORDFILE_METADATA_CACHE

The ALTER SYSTEM clause FLUSH PASSWORDFILE_METADATA_CACHE refreshes the metadata cache with the latest details of the database password file.

Querying the V$PASSWORDFILE_INFO view retrieves the latest details of the database password file.
This functionality is useful when the database password file name or location is changed, and the metadata cache needs to be refreshed with the details of the updated database password file.

(7) Flashback Standby Database When Primary Database is Flashed Back

The standby database in an Oracle Data Guard setup can be automatically flashed back when a flashback operation is performed on the primary database.
When a flashback operation is performed on the primary database, the standby is no longer synchronized with the primary. In earlier releases, you needed to perform certain steps to synchronize the standby with the primary.
This feature introduces a new parameter that enables the standby database to be flashed back automatically when a flashback operation is performed on the primary database.

(8) Propagate Restore Points from Primary to Standby Site

Restore points created on the primary database are propagated to the standby sites, so that they are available even after a failover operation.
Normal restore points or guaranteed restore points are defined at the primary site to enable fast point-in-time recovery in the event of logical corruptions.

These restore points are stored in the control file. In the event of a failover, the standby database becomes the primary database. However, the restore point information is lost. Propagating restore points from the primary to the standby simplifies the complexity of the restore and recovery process after a failover because the standby database is updated with the restore points created on the primary database.

(9) Workload Capture and Replay in a PDB

Oracle Real Application Testing was designed to capture and replay multitenant databases at the root multi-tenant container database (CDB) level.
Starting with Oracle Database Release 19c, you can capture and replay the workload from within an individual pluggable database (PDB).

This enhancement enables you to capture and replay workloads at the PDB level. This leads to better testing, less downtime, and more effective and efficient change control.

(10) About Image-Based Oracle Database Client Installation

Starting with Oracle Database 19c, installation and configuration of Oracle Database Client software is simplified with image-based installation.

To install Oracle Database Client, create the new Oracle home, extract the image file into the newly-created Oracle home, and run the setup wizard to register the Oracle Database product.
You must extract the image software (client_home.zip) into the directory where you want your Oracle Database Client home to be located, and then run the Setup Wizard to start the Oracle Database Client installation and configuration. Oracle recommends that the Oracle home directory path you create is in compliance with the Oracle Optimal Flexible Architecture recommendations

(11) Ability to Create a Duplicate of an Oracle Database, Relocate a PDB to Another CDB,Create a PDB by Cloning a Remote PDB Using DBCA in Silent Mode

dbca -createDuplicateDB 
     -gdbName global_database_name 
     -primaryDBConnectionString easy_db_connection_string
     -sid database_system_identifier
     [-initParams initialization_parameters
         [-initParamsEscapeChar initialization_parameters_escape_character]]
     [-sysPassword SYS_user_password]
     [-policyManaged | -adminManaged]
     [-policyManaged
         -serverPoolName server_pool_names
         [-pqPoolName pq_pool_name]
         [-createServerPool new_server_pool_name
             [-pqPoolName new_pq_pool_name]
             [-force]
             [-pqCardinality pq_cardinality_of_the_new_server_pool]
             [-cardinality cardinality_of_the_new_server_pool]]]
     [-adminManaged]
     [-nodelist database_nodes_list]
     [-datafileDestination data_files_directory]
     [-recoveryAreaDestination recovery_files_directory
         [-recoveryAreaSize fast_recovery_area_size]]
     [-databaseConfigType {SINGLE | RAC | RACONENODE}
         [-RACOneNodeServiceName service_name_for_RAC_One_Node_database]]
     [-useOMF {true | false}]
     [-storageType {FS | ASM} 
         [-asmsnmpPassword ASMSNMP_password]
         -datafileDestination database_files_directory]
     [-createListener new_database_listener]
     [-createAsStandby 
         [-dbUniqueName db_unique_name_for_standby_database]]
     [-customScripts custom_sql_scripts_to_run_after_database_creation]
     [-useWalletForDBCredentials {true | false}
         -dbCredentialsWalletPassword wallet_account_password
         -dbCredentialsWalletLocation wallet_files_directory]

 dbca -relocatePDB 
    -pdbName name_of_the_local_pdb_to_create
    -sourceDB database_name_of_the_local_pdb
    -remotePDBName name_of_the_remote_pdb_to_relocate
    -remoteDBConnString db_connection_string_of_the_remote_pdb
    -sysDBAUserName name_of_the_sysdba_user
    -sysDBAPassword password_of_the_sysdba_user
    -dbLinkUsername name_of_the_dblink_user_of_the_remote_pdb
    -dbLinkUserPassword password_of_the_dblink_user_of_the_remote_pdb

(12) DISTINCT Option for LISTAGG Aggregate

The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword.
The LISTAGG aggregate function orders the rows for each group in a query according to the ORDER BY expression and then concatenates the values into a single string. You can remove duplicate values from the specified expression before concatenation into a single string using the new DISTINCT keyword. This removes the need to create complex query processing to find the distinct values before using the aggregate LISTAGG function. Use the DISTINCT option to remove duplicate values within the LISTAGG function.

The result is simpler, faster, more efficient SQL

Oracle Database 19c new features

Leave a Comment Cancel Reply

Related Posts

Leave a Comment Cancel Reply