Oracle 23ai : True Cache

Unveiling Oracle True Cache

True Cache is an in-memory, read-only cache that sits in front of an Oracle database.

Similar to Oracle Active Data Guard, True Cache acts as a fully operational, read-only replica of the primary database but primarily operates without disk storage.

Modern applications often need numerous connections and quick, low-latency access to data. One common solution is to implement caches in front of databases since applications tend to perform more read operations than updates, allowing them to read from the cache without burdening the database.

Unlike traditional caches, True Cache automatically maintains the most frequently accessed data in the cache, ensuring it stays consistent with the primary database, other objects within the same cache, and other caches.

Advantages of Oracle True Cache

• Enhance Scalability and Boost Performance by Offloading Queries from Your Primary Database
• Accelerate Performance: Minimize Response Time and Latency with True Cache Deployment
• Scale Beyond Limits: Seamlessly Expand Your in-memory Storage with Distributed True Caches
• Automatically maintains the cache contents
• Simplifies development and maintenance by being transparent to the application

Important Points for Implementing True Cache

• Memory Configuration: Ensure sufficient memory allocation for True Cache to keep your most frequently accessed data readily available in memory.
• Storage Requirements: True Cache requires minimal storage for standby redo log files. Essential configuration files such as control files, the SPFILE, and temporary data files are created automatically.
• Read-Only Cache: True Cache is designed as a read-only cache, meaning direct updates are not possible. However, you can use DML redirection to make indirect updates to the cache.

True Cache General Architecture

 

 

At a high level, here’s how it works:

• An application decides whether to query data from True Cache or the primary database.
• True Cache satisfies queries by using data that’s cached in its memory. When the data isn’t in the cache, True Cache fetches the data from the primary database.
• True Cache is empty when it starts up, so it reads large chunks of data to populate the cache. After a block is cached, it’s updated automatically through redo apply from the primary database.
• A query to True Cache returns only committed data, and the data is always consistent.
• Like all caches, the True Cache data might not be the most current data as it exists in the primary database.
• If multiple True Caches exist and serve the same database application service, the listener automatically distributes and load balances sessions to each cache.

Hands-On Demonstration to Configuring True Cache

To enable True Cache on Oracle FREE 23ai database, first install FREE database on the primary node, then set up True Cache on a separate True Cache node.

Configure two virtual machines in VirtualBox

Prior to starting the configuration process for True Cache, it’s necessary to create two virtual machines on VirtualBox. Running two database instances on a single FREE VirtualBox machine isn’t permitted by Oracle in their free setup. I encountered several challenges while setting up the environment. Here’s what I learned to prepare for True Cache configuration.

• The default 23ai VirtualBox image lacks the necessary DBCA executable libraries. Therefore, I need to set up an independent machine with Oracle Linux and manually install the 23ai binaries to configure a FREE database.
• When setting up two machines in VirtualBox, establishing network communication between them is crucial. To achieve this, I added additional network interfaces in the VM settings: one for “Host-only Network” and another for “NAT Network”. The “Host-only Network” enables internet access from the VMs, while the “NAT Network” is essential for communication between the two machines.

Below are my machine hostname

Primary Database – oracle23ai
True Cache instance – oracle23aitcache

Set Up Primary Database in ARCHIVELOG mode : oracle23ai

 
 SQL> archive log list
 SQL> shu immediate
 SQL> startup mount
 SQL> ALTER DATABASE ARCHIVELOG; 
 SQL> alter database open;
 SQL> archive log list

Copy the password file to True Cache node : oracle23aitcache

Copy the password file /opt/oracle/product/23ai/dbhomeFree/dbs/orapwFREE from the primary database node (oracle23ai) to the True Cache node (oracle23aitcache) under a temp location /opt/oracle/tmp.

Run DBCA to complete the True Cache configuration : oracle23aitcache

Run DBCA on True Cache node (oracle23aitcache) to complete the configuration.

 
 cd $ORACLE_HOME/bin
 ./dbca -silent -createTrueCache -gdbName FREE -sourceDBConnectionString oracle23ai:1521/FREE -passwordFileFromSourceDB /opt/oracle/tmp/orapwFREE 

During the setup of the True Cache instance, the following database parameters are automatically configured in true cache instance.

The ‘CREATE TRUE CACHE‘ command is used to initialize the True Cache instance.

In the primary database, the log archive destination is specified.

After the instance is successfully created, the following entries will be visible in the V$DATABASE view of the True Cache instance.

Create Database Application Services on the Primary Database : oracle23ai

From primary database, you can establish or update the True Cache and primary database services.
Each True Cache service can be linked to a single primary database service.
Below is an example demonstrating how to create these two services for a single instance primary database using the DBMS_SERVICE package:

 
 DECLARE
     db_params dbms_service.svc_parameter_array;
 BEGIN
    -- create a database application service for True Cache called SALES_TC using SALES_TC tnsname
    DBMS_SERVICE.CREATE_SERVICE('SALES_TC', 'SALES_TC', db_params);

    -- create a database application service SALES for primary database using SALES tnsname and associate it to the SALES_TC service name using TRUE_CACHE_SERVICE attribute 
    db_params('true_cache_service') := 'SALES_TC';
    DBMS_SERVICE.CREATE_SERVICE('SALES', 'SALES', db_params);

    -- or, modify an already existing primary database application service called SALES to associate it to the SALES_TC service name using the TRUE_CACHE_SERVICE attribute
    db_params('true_cache_service') := 'SALES_TC';
    DBMS_SERVICE.MODIFY_SERVICE('SALES', db_params);

 END;
 / 

Verify That the Database Application Services Are Created : oracle23ai

After you create services on the primary database, True Cache automatically inherits their definitions. Using SQL*Plus, make sure that you can see the same results on both the primary database and True Cache.

 
 SELECT name, true_cache_service 
 FROM DBA_SERVICES 
 WHERE name='SALES' or name='SALES_TC'; 

Start the Database Application Services

Using SQL*Plus, start the database application services on both the primary database and True Cache.

On True Cache Instance – oracle23aitcache

 
 SELECT database_role 
 FROM v$database; 

 EXEC DBMS_SERVICE.START_SERVICE('SALES_TC'); 
 
 SELECT service_id, name 
 FROM v$active_services 
 WHERE name='SALES_TC';

On Primary Instance – oracle23ai

 
 SELECT database_role 
 FROM v$database; 

 EXEC DBMS_SERVICE.START_SERVICE('SALES'); 
 
 SELECT service_id, name, true_cache_service 
 FROM v$active_services 
 WHERE name='SALES';

Verifying That True Cache Is Working as Expected

To verify that True Cache is applying redo and making progress, check the following queries on True Cache.

 
 SELECT database_role, open_mode 
 FROM v$database;

 SELECT current_scn 
 FROM v$database;

 SELECT THREAD#, SEQUENCE#, BYTES 
 FROM v$standby_log;

Verifying the Remote Listener Configuration

After the database application service starts on True Cache, use the lsnrctl command to validate the remote listener services for the True Cache service registration.

Monitoring True Cache

You can query the V$TRUE_CACHE view on the primary database and True Cache. On True Cache, the view displays a single row for the primary database that it connects to. On the primary database, the view shows one row for each True Cache that’s connected to the primary database. Each row displays the status for the True Cache.

To use this view, enter the following query:

 
 SELECT * 
 FROM v$true_cache; 

 

Stay tuned for surplus Oracle 23ai demos !!!