Oracle 19c New Features
Oracle 19c New Features
As Oracle Support have made 12.2 version of Oracle ,its has become important for us to upgrade our 12c database to 19c.And before upgrade any database to latest version , We need to know the latest new features of that particular version.
In the current blog , We are going to discuss about new features of the Oracle 19C database:
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.
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');
How to configure it:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
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;
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_pdb12) 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
13) Real Time Statistics for Conventional DML Operations
Oracle Database automatically gathers online statistics during conventional DML operations.
Statistics can go stale between execution of DBMS_STATS statistics gathering jobs. By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS. Fresh statistics enable the optimizer to produce more optimal plans.14)Active Data Guard DML Redirection
- Oracle Database Release 19c New Features,Allows DML to be executed against the standby database
- Incidental Data Manipulation Language (DML) operations can be run on Active Data Guard standby databases.This allows more applications to benefit from using an Active Data Guard standby database when some writes are required.On execution the DML operation is passed to the primary database.
- DML redirection helps in load balancing between the primary and standby databases. When incidental DML is issued on an Active Data Guard standby database, the update is passed to the primary database where it is executed. The resulting redo of the transaction updates the standby database after which control is returned to the application.
- DML operations on a standby can be transparently redirected to and run on the primary database. This includes DML statements that are part of PL/SQL blocks. The Active Data Guard session waits until the corresponding changes are shipped to and applied to the Active Data Guard standby.
You can learn in detail from below books:
==========================================================
Please check our other blogs for Oracle 19C database.
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.
(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
-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
13) Real Time Statistics for Conventional DML Operations
Oracle Database automatically gathers online statistics during conventional DML operations.
Statistics can go stale between execution of DBMS_STATS statistics gathering jobs. By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS. Fresh statistics enable the optimizer to produce more optimal plans.
Statistics can go stale between execution of DBMS_STATS statistics gathering jobs. By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS. Fresh statistics enable the optimizer to produce more optimal plans.
14)Active Data Guard DML Redirection
- Oracle Database Release 19c New Features,Allows DML to be executed against the standby database
- Incidental Data Manipulation Language (DML) operations can be run on Active Data Guard standby databases.This allows more applications to benefit from using an Active Data Guard standby database when some writes are required.On execution the DML operation is passed to the primary database.
- DML redirection helps in load balancing between the primary and standby databases. When incidental DML is issued on an Active Data Guard standby database, the update is passed to the primary database where it is executed. The resulting redo of the transaction updates the standby database after which control is returned to the application.
- DML operations on a standby can be transparently redirected to and run on the primary database. This includes DML statements that are part of PL/SQL blocks. The Active Data Guard session waits until the corresponding changes are shipped to and applied to the Active Data Guard standby.
You can learn in detail from below books:
==========================================================
Please check our other blogs for Oracle 19C database.
Comments
Post a Comment
Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !