Materialized View Deployment Templates in Oracle 23ai

Oracle Database 23ai is the next long-term support release of Oracle Database. It includes over 300 new features with a focus on artificial intelligence (AI) and developer productivity. Oracle Database 23ai is the next long-term support release of Oracle Database. It includes over 300 new features with a focus on artificial intelligence (AI) and developer productivity. Features such as AI Vector Search^[1] enable you to leverage a new generation of AI models to generate and store vectors of documents, images, sound, and so on; index them and quickly look for similarity while leveraging the existing analytical capabilities of Oracle Database. This combined with the already extensive set of Machine Learning algorithms enables you to quickly create sophisticated AI-enabled applications. Oracle Database 23ai also uses AI to optimize many of the key database functions to make more accurate estimates on timings and resource costings.
New developer-focused features now make it simpler to build next-generation applications that use JSON or relational development approaches or both interchangeably. New microservice and messaging functionality improves upon Oracle Database's extensive support for this key design methodology. If you need to distribute or shard your database because of regulatory or performance requirements, Oracle Database 23ai adds new RAFT protocol^[2] support to make it easier than ever before.
Oracle Database 23ai also includes significant improvements to SQL and PL/SQL, introducing new data types and language enhancements to create new or improve existing OLTP or analytical applications. While Oracle Database is widely regarded as the most secure database in the industry, many new capabilities such as SQL Firewall enable you to control exactly what SQL is executed against your database. To help DBAs, Oracle Database 23ai further refines many of the key management tasks, reducing their complexity and improving their performance as well as introducing new functionality to simplify tasks, such as reclaiming free space in tablespaces. Oracle Database also adds new performance improvements both at an infrastructural level (with technologies like True Cache) and at the SQL level, ensuring some statements will execute many times faster.
The following section aligns with Oracle 23ai, where snapshots have been fully replaced by materialized views (MVs) and GoldenGate is the preferred solution for advanced replication:
Materialized View Deployment Templates for Oracle 23ai Administration
Oracle 23ai provides robust distributed database replication capabilities, including the ability to package and deploy a coordinated set of materialized views as a unified structure known as a Materialized View Deployment Template.
A Materialized View Deployment Template allows administrators to define, group, and distribute multiple materialized views, potentially hundreds of complex structures, into a single configuration unit that can be deployed across one or more remote replication sites. These templates facilitate 1) scalable, 2) secure, and 3) parameterized replication across enterprise environments.
✅ Advantages of Using Materialized View Deployment Templates:

• Centralized Control of Multi-Site Materialized Views: Enables consistent replication strategies and refresh schedules across all participating replication sites.
• Streamlined Deployment: Supports repeatable and version-controlled deployment of materialized view environments to new remote databases or development/testing environments.
• Parameterized Subset Replication: Allows replication of filtered or scoped data through template parameters for use cases such as regional subsets or department-specific data.
• Template-Based Security Management: Centralized control over replication privileges through authorized user lists embedded within the deployment template.

In addition, Oracle GoldenGate 23ai can be integrated with this deployment model for real-time change data capture (CDC)^[3], allowing the initial materialized view instantiation to serve as a snapshot baseline for GoldenGate instantiation from export. ⚠️ Note: This feature replaces the older snapshot deployment templates available in pre-19c versions. Oracle 23ai emphasizes materialized views and GoldenGate as the standard mechanisms for logical replication and offline/online instantiation workflows.

One of the distributed database replication features with Oracle is the ability to package a set of snapshots together into a single bundle called a deployment template. A snapshot deployment template can contain hundreds of complex snapshots that are bundled together into a template and deployed to remote sites. Some of the advantages to using snapshot deployment templates are:

1. Centralized control over multimaster snapshots
2. Easy capability to repeatedly deploy snapshot environment
3. Capability to replicate subsets of data at the remote site with template parameters
4. Centralized control over snapshot security using authorized user lists inside the template

To implement snapshot templates, the DBA creates a template at the master site. This template stores all of the information needed to deploy a snapshot environment, including the DDL to create the objects at the remote site and the target refresh group. This template also maintains links to user security information and template parameters for custom snapshot creation.
You can create snapshot templates by using the Replication Manager wizard or by calling the DBMS_REPCAT_RGT pre-defined package.

Click the Quiz link below to test your understanding of the concepts presented in this module.
Distributed Database Tools - Quiz

[1] AI Vector Search: AI Vector Search in Oracle 23ai is a new capability that allows you to perform semantic searches on various data types by leveraging vector embeddings, which capture the meaning of data. This enables querying data based on conceptual similarity rather than just keywords, enhancing search accuracy and relevance for AI-powered applications.

[2] RAFT protocol: The RAFT protocol in Oracle 23ai is a consensus-based replication protocol integrated into the Globally Distributed Database to automate replication configuration across all shards, ensuring high availability and data consistency. This built-in replication simplifies management compared to traditional methods like Data Guard, offering automatic failover and active-active configurations for distributed environments.

[3] True Cache: In Oracle 23ai, True Cache is an in-memory, read-only cache that acts as a mostly diskless, automatically managed replica of the primary database, significantly improving read performance and reducing load on the primary database by serving frequently accessed data. It maintains consistency with the primary database using redo apply, similar to Active Data Guard, ensuring applications read current and committed data with low latency.

[4] real-time change data capture: Real-time Change Data Capture (CDC) in Oracle 23ai refers to the capability to identify and track data modifications (inserts, updates, deletes) as they occur in the database, making these changes immediately available for other systems or processes. This enables near-instantaneous data synchronization and integration, crucial for real-time analytics and maintaining data consistency across diverse environments.

[5] Deployment template: A set of commands that create one or more snapshots at a remote site. The template can be used repeatedly to create identical sets of snapshots at multiple remote sites.