Oracle Release Notes

SQL Macros (SQM)

You can create SQL Macros (SQM) to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements. Starting with Oracle Database release 19c, version 19.7, SQL table macros are supported. SQL table macros are expressions, typically used in a FROM clause, to act as a sort of polymorphic (parameterized) view.

SQL macros increase developer productivity, simplify collaborative development, and improve code quality.

View Documentation

Database In-Memory Base Level

Database In-Memory is an option to Enterprise Edition. Database In-Memory now has a new Base Level feature. This allows the use of Database In-Memory with up to a 16GB column store without triggering any license tracking.

This feature allows you to use Database In-Memory without having to license the option. The column store is limited to 16GB when using the Base Level. This helps to show the value of Database In-Memory without having to worry about licensing issues.

View Documentation

CellMemory Level

You can use the CellMemory feature without enabling the IM column store by setting

INMEMORY_FORCE=CELLMEMORY_LEVEL

and

INMEMORY_SIZE=0

This feature allows you to use CellMemory without incurring the overhead of enabling the IM column store.

View Documentation

Oracle Grid Infrastructure SwitchHome

You can use the -switchGridHome option with gridSetup.sh to switch from one Oracle Grid Infrastructure home to another.

You can use the -switchGridHome option for patching and upgrading Oracle Grid Infrastructure. Use the -switchGridHome option to switch from the source Oracle Grid Infrastructure home to the patched Oracle Grid Infrastructure home. All Oracle Clusterware and Oracle Restart services start from the patched Oracle Grid Infrastructure home automatically.

View Documentation

Support for DBMS_CRYPTO Asymmetric Key Operations

Starting with this release, the DBMS_CRYPTO PL/SQL package supports asymmetric key operations, in addition to the existing support for symmetric key operations.

To implement the support for asymmetric key operations, the following procedures have been added to the DBMS_CRYPTO package:

• PKENCRYPT
• PKDECRYPT
• SIGN
• VERIFY

View Documentation

Enhancements for Identity and Access Management Integration with Oracle Database Environments

Available for Oracle Database release 19.16 are enhancements to the integration of Identity and Access Management (IAM) users with Oracle Database Environments.

• Additional Oracle Database environments: The full list of supported Oracle Database environments is as follows:
  • Oracle Autonomous Database on Dedicated Exadata Infrastructure
  • Oracle Autonomous Database on Shared Exadata Infrastructure
  • Oracle Base Database Service
• Ability to use the IAM user name and password to retrieve an IAM token: Retrieving a token using an IAM user name and password or secure external password store (SEPS) is more secure than using the password verifier method of database access.

View Documentation

Oracle Data Guard Redo Decryption for Hybrid Disaster Recovery Configurations

Available for Oracle Database release 19.16, Oracle Data Guard enables you to decrypt redo operations in hybrid cloud disaster recovery configurations where the Cloud database is encrypted with TDE and the on-premises database is not.

Hybrid disaster recovery is often considered a quick-stepping stone to cloud adoption. By enabling the ability to quickly configure disaster recovery even in cases where on-premises databases might not already be encrypted with TDE, the steps required to configure hybrid disaster recovery environments are reduced while still ensuring that redo data is still encrypted during the transportation process.

To enable this feature, Oracle Database introduces the TABLESPACE_ENCRYPTION initialization parameter, which enables you to control the automatic encryption of tablespaces in both the primary and standby databases, for on-premises and Oracle Cloud Infrastructure (OCI) environments. For example, an on-premises database can be unencrypted and an OCI database can be encrypted.

View Documentation

Starting with Oracle Database 19c RU 19.18.0, all available DST patches are installed with the RU, and deployed into the Oracle_home/oracore/zoneinfo directory. Installing DST patches does not affect database operation. However, installing the patches with the RU makes it easier for you to adjust the timezone version of your database, if you have a requirement to do so. For example, if you are using Transportable Tablespaces, or Full Transportable Export/Import, then you must ensure that your source and target databases are using identical character sets and identical time zone settings. With this change, you can more easily choose to change your destination database to use a different time zone file version than the default.

View documentation:

• Choosing a Time Zone File
• RUs contain now all available DST patches
• Create a database with NON-DEFAULT Time Zone

By default, AutoUpgrade changes the database time zone to the latest available level. If you don’t want the time zone to be upgraded, then you must explicitly set the local parameter timezone_upg in your AutoUpgrade configuration file to no. For example:

upg1.timezone_upg=no

If you choose to use an older database time zone file, then set the database environment variable ORA_TZFILE to the older time zone file. The time zone files are located in Oracle_home/oracore/zoneinfo. By using the environment variable ORA_TZFILE, you can override the default of using the highest-numbered time zone file.

In-Memory Eligibility Test

Many workloads benefit from Database In-Memory, however some may not. The In-Memory Eligibility Tool determines if a given workload would benefit or not benefit from Database In-Memory and assesses its eligibility for use of this feature. Eligibility is gauged by the percentage of analytical activity in the workload. If you are planning to implement Database In-Memory, you can use this tool to quickly identify and filter out databases that are ineligible - those where analytic activity is low and where you would see no substantive gain from the use of Database In-Memory. You can then focus your Database In-Memory deployment on databases whose workload includes more intense analytic activity and could therefore benefit substantially. The higher the percentage of analytical activity in the workload, the more benefit you gain from Database In-Memory.

View Documentation

AutoUpgrade Automates Data Guard Operations During Database Upgrade

AutoUpgrade automates the steps necessary to upgrade a database in an Oracle Data Guard configuration. Note that in Oracle Database 19c, Data Guard operations are not fully automated. Oracle only supports upgrading a primary database and shutting down of redo apply to a standby database.

Steps that AutoUpgrade automates includes disabling and re-enabling the Data Guard Broker (if present), and deferring log file transport while the upgrade takes place. Automation of these Oracle Data Guard operations in the context of a database upgrade eliminates the need for manual or scripted steps that you otherwise would have to perform yourself. This in turn makes it easier to automate upgrades of databases in Oracle Data Guard configurations. This feature particularly helps to automate upgrades in high-end environments, where there can be many hundreds of such databases that you upgrade as part of your upgrade project.

AutoUpgrade Automates Steps Required for Oracle RAC Database Upgrade

AutoUpgrade automates all the steps needed to upgrade an Oracle Real Application Clusters (Oracle RAC) database.

AutoUpgrade automates steps that previously had to be performed manually or scripted. Steps that AutoUpgrade automates include the SRVCTL commands to stop and restart instances on multiple nodes, handling of CLUSTER_DATABASE, updating initialization parameter files, and restarting services, where appropriate.

This feature reduces the possibility of manual errors and decreases the work needed for DBAs to perform routine maintenance and upgrade activities.

AutoUpgrade Automates Upgrade and Conversion of Non-CDB to PDB

To simplify upgrades to Oracle Database 21c, AutoUpgrade automates steps to convert a database from a non-CDB to a PDB as part of a database upgrade.

Oracle Database 21c requires multitenant architecture. With this release, you can use AutoUpgrade for non-CDB to PDB upgrades from Oracle Database 12c Release 2 (12.2) and later releases. Using the automation features of AutoUpgrade reduces both the effort required of DBAs for upgrades, and the risk associated with typographical errors in a manual process.

AutoUpgrade Automates Upgrade of a PDB via Unplug-Plug-Upgrade

AutoUpgrade automates unplugging a PDB from a CDB, plugging it into a higher version CDB, and upgrading the PDB to that new target version.

As part of the process to simplify upgrade, you can upgrade pluggable databases (PDBs) using an unplug/plug, where the upgrade occurs when you plug the PDB into the target release container database (CDB). This feature adds another scenario in which AutoUpgrade can automate the entire database upgrade process.

AutoUpgrade Release Update (RU) Upgrades

AutoUpgrade supports the option of using AutoUpgrade to perform out-of-place Oracle home Release Update patching.

For an out-of-place patch of Oracle Database using AutoUpgrade, AutoUpgrade moves the source database that you want to patch to a new Oracle Database Oracle home, and then patches the database binaries in that target Oracle home with the Release Update that you select. With this option, you can use AutoUpgrade at any time that you want to move the database to a new Oracle home, either as part of a planned upgrade or as part of a patch plan. In a patch operation, AutoUpgrade performs the patch using the following workflow:

1. AutoUpgrade recognizes that the source database and the target Oracle Database are the same base release.
2. AutoUpgrade skips the upgrade steps.
3. AutoUpgrade patches the target database using the Release Update.

AutoUpgrade Sets Parallelism Based on System Resources

AutoUpgrade automatically evaluates system resources and makes an intelligent decision as to how many upgrade jobs can run simultaneously.

AutoUpgrade uses the CPU_COUNT value and system process parameters to determine available system resources, and calibrates both the number of upgrades that can run at a time and the number of parallel threads for each upgrade. Upgrades that exceed a safe threshold are put in a queue so that they can be run as system resources become available.

AutoUpgrade Supports Upgrades with Keystore Access to Databases Using TDE

AutoUpgrade enhances support for databases that use transparent data encryption (TDE) by enabling keystore generation.

AutoUpgrade now enables you to provide passwords to an external key manager generated and maintained by AutoUpgrade. With this configuration, AutoUpgrade supports unmanned or automated operations of TDE-enabled databases. AutoUpgrade can open the source database keystore without prompting for the keystore password, and enroll the target database into the TDE external keystore for key management, so that the target database can start automatically.

AutoUpgrade Unplug-Plugin Upgrades to Different Systems

You can now use the Oracle Database AutoUpgrade Unplug/Plug method to unplug a PDB from one system and plug into a different system and upgrade.

In earlier releases, AutoUpgrade supported unplug/plug/upgrades on the same server, but it was not possible to unplug a PDB from one server, plug it into a different system, and then upgrade the PDB. With this feature, you can now migrate and upgrade the PDB in a single operation, including migrations to the cloud.

FPP Integration with AutoUpgrade

Fleet Patching and Provisioning (FPP) integration with AutoUpgrade provides a new tool for automating and simplifying Oracle Database Upgrade.

This feature makes Oracle Database AutoUpgrade more flexible, provides better control over the upgrade flow mechanism, and provides better usability by showing progress bar and additional elements. You can upgrade multiple databases in parallel.

Oracle Database Automates Database Upgrades with AutoUpgrade

Oracle Database AutoUpgrade enables you to upgrade one or many databases without human intervention, all with one command, and with a single configuration file.

AutoUpgrade enables you to upgrade one or many Oracle Database instances at the command-line, using a single command and a single configuration file. AutoUpgrade runs the preupgrade tasks, performs automated fix-ups where needed, runs the database upgrade, and finishes by completing post-upgrade tasks. It includes automatic retry and fallback, the option to schedule upgrades for future points in time, and the ability to set, change, or remove initialization parameters as desired. Using AutoUpgrade can save you time and money by enabling you to upgrade hundreds of databases with one command, and avoid the need for high-maintenance upgrade solutions.

REST APIs for AutoUpgrade

To facilitate safe and secure remote use of the AutoUpgrade for Oracle Database upgrades, AutoUpgrade now provides REST APIs (ORDS and OCI).

The Oracle REST Data Services (ORDS) database API is a database management and monitoring REST API embedded into Oracle REST Data Services. The Oracle Cloud Infrastructure (OCI) REST API is enabled by configuring the REST Adapter connection to use the OCI Signature Version 1 security policy. You can now use these features to run AutoUpgrade upgrades remotely over SSH.

Enabling Scrambled Dequeues and Dequeue Flow Control

You can enable or disable the scrambling and flow control feature using the queue level parameter:

• To spread the concurrent dequeuers across different blocks or buffers, enable scrambled dequeues using the following queue parameter:

DBMS_AQADM.SET_QUEUE_PARAMETER('<schema_name>.<queue_name>', 'CQ_SCRAMBLED_DEQ', 1);

To disable scrambled dequeues, use the following queue parameter:

DBMS_AQADM.UNSET_QUEUE_PARAMETER('<schema_name>.<queue_name>', 'CQ_SCRAMBLED_DEQ');

• To internally restrict the number of concurrent sessions that execute the dequeue select for update on a queue, enable dequeue flow control using the following queue parameter:

DBMS_AQADM.SET_QUEUE_PARAMETER('<schema_name>.<queue_name>', 'CQ_DEQ_FLOWCONTROL', *N*);

Where N is the number of internal dequeue sessions allowed to do the core dequeue work concurrently, which causes waits and contentions. Note that N must be less than the number of actual external dequeue sessions.

To disable dequeue flow control, use the following queue parameter:

DBMS_AQADM.UNSET_QUEUE_PARAMETER('MY_QUEUE', 'CQ_DEQ_FLOWCONTROL');

Using these new parameters, you can now spread concurrent dequeuers across different blocks or buffers to reduce buffer busy waits contention and to avoid row lock contention and buffer busy waits contention.

Enhanced Online DDL Capabilities in the Presence of Supplemental Logging

Numerous DDL operations were blocking operations when supplemental logging was enabled. You can use the OGG_ONLINE_DDL initialization parameter to lift this restriction for several previously blocking DDL operations when minimal supplemental logging is enabled. Those commands are namely:

• ALTER TABLE ADD COLUMN
• ALTER TABLE SET COLUMN UNUSED
• ALTER TABLE ADD CONSTRAINT ENABLE NOVALIDATE
• ALTER TABLE DROP CONSTRAINT

Non-blocking DDL operations are crucial for mission-critical environments where it is hard to impossible to guarantee an idle time window for blocking DDL operations to succeed. Supplemental logging is a key requirement for GoldenGate replication support, which creates a dilemma for customers to choose between non-blocking DDL or using GoldenGate. Enabling more non-blocking DDL operations in the presence of supplemental logging make such a decision void. Customers benefit from non-blocking DDL operations and GoldenGate.

View Documentation

Features

• Automatic Block Repair in Active Data Guard per Pluggable Databases
• Automatic Standby Pluggable Database Instantiation and Standby Redo Log Creation
• DBMS_SEARCH Autocomplete for Hybrid Vector Indexes
• DML Redirection for Data Guard per Pluggable Database
• Deep Security Support in Java
• Duality View Inline Write Augmentation
• EBCDIC Binary Collation Support
• IBM-Compatible EBCDIC Client Character Set Support
• In-Place Conversion to Immutable Tables and Parallel CTAS Support
• JOIN TO ONE Join Method and Syntax
• Local HNSW Indexes
• Offload Creating Vector Indexes to Remote GPU Container
• Oracle Deep Data Security (Deep Sec)
• Parallel Verification of Blockchain Tables
• Private AI Container for Vector Embedding Generation
• Property Graph: In-Database Graph Algorithms
• Support for DECODE and CASE in SQL/JSON Path Expressions
• Support for Nested WITH Clause and Correlation
• TLS Authentication for Redo Transport
• Validation Directives for JSON Relational Duality Views
• WAIT and NOWAIT Support for All DML Statements