DatabaseVerse: Journey into the World of Databases

I have an earlier post about Oracle 23ai True Cache that you can take a look here: Enhancing Application Performance with Oracle 23ai True Cache – DatabaseVerse: Journey into the World of Databases, It is an introduction of this incredible feature and you can see how it works, but I have got some question about the earlier phase, and that means how to create an Oracle True Cache instance database, so I think that is quite interesting to share in this post and I want to cover other things that I’m considered important and quite relevant, so let’s start!

1. What is Oracle True Cache?
2. True Cache Configuration
   1. True Cache Uniform Configuration
   2. True Cache Partitioned configuration with colocation_tag
   3. True Cache Partitioned Configuration with Multiple Services
   4. One more thing! MAA with True Cache
3. DEMO
   1. How to create an Oracle True Cache “database”?
      1. Prerequisites
   2. Configuring True Cache Database Application Services
   3. Verifying the True Cache Configuration
   4. Monitoring True Cache!
4. Conclusion

What is Oracle True Cache?

If you haven’t checked the previous post this is a summary:

True Cache is an in-memory, read-only cache in front of an Oracle database. Like Oracle Active Data Guard, True Cache is a fully functional, read-only replica of the primary database, except that it’s mostly diskless.

True Cache Configuration

True cache have some interesting configuration, so before jumping into the creation I wanted to talk about the options available. General architecture is as follow:

At a high level:

• 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. This is similar to the update mechanism used in Oracle Active Data Guard.
• 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.

True Cache Uniform Configuration

In a uniform configuration, you can deploy multiple, identical True Caches that use the same True Cache database application service. Client sessions are evenly distributed among True Caches, which all cache the same set of data.

The diagram below shows an example of a uniform configuration with the following elements:

• One primary database (PRIMDBI)
• Two True Caches (TCDB1I and TCDB2I)
• One remote listener (sales_rl)
• Two applications

So, how this works?

In this setup, client sessions are evenly distributed among the True Caches, each caching the same set of data. This uniformity ensures consistent data access and load balancing across all caches. Applications can manage connections manually by directing queries to True Cache through its specific service and sending updates to the primary database via its service. For enhanced transparency, especially when using the JDBC method, primary database application services can be associated with corresponding True Cache services. This association allows applications to switch between read and write operations seamlessly by adjusting the setReadOnly flag in the JDBC connection. The True Caches and the primary database share a remote listener, which automatically distributes and balances sessions across the caches, optimizing performance and resource utilization.

True Cache Partitioned configuration with colocation_tag

Oracle’s True Cache offers a partitioned configuration feature that allows data to be distributed across multiple caches, each handling a specific subset of the data. This is achieved using the COLOCATION_TAG parameter within the CONNECT_DATA section of your network alias. By setting COLOCATION_TAG, you can direct all connections with the same tag to a designated True Cache, effectively bypassing load balancing. This setup is particularly beneficial for applications requiring data localization, as it ensures that sessions with a specific COLOCATION_TAG (e.g., ‘US’ or ‘EUROPE’) are routed to the corresponding True Cache. Additionally, associating primary database application services with True Cache services enhances application transparency, allowing seamless data access across different regions or data centers.

In this particular example we have:

• One primary database (PRIMDBI)
• Two True Caches (TCDB1I and TCDB2I)
• One remote listener (sales_rl)
• Two applications
  • Sessions with COLOCATION_TAG set to US go to one True Cache (TCDB1I).
  • Sessions with COLOCATION_TAG set to EUROPE go to the other True Cache (TCDB2I).

True Cache Partitioned Configuration with Multiple Services

This approach allows the combined cached data size to exceed what a single primary database or uniform cache configuration can manage. In this setup, different True Caches are associated with specific database application services. For instance, one True Cache might be linked to the SALES service, while another is connected to the HR service. Applications can manage connections manually by directing queries to the appropriate True Cache service and updates to the primary database service. Alternatively, for greater transparency, primary database services can be associated with corresponding True Cache services using the TRUE_CACHE_SERVICE parameter. This configuration allows applications to switch between read and write operations seamlessly by adjusting the setReadOnly flag in the JDBC connection. Both the True Caches and the primary database share a remote listener, facilitating efficient session distribution and load balancing across the caches.

In this particular example we have:

• One primary database (PRIMDBI)
• Two True Caches (TCDB1I and TCDB2I)
• One remote listener (sales_rl)
• Two applications connected to different database application services (SALES and HR)

One more thing! MAA with True Cache

There are some best practices for Maximum Availability Architecture (MAA) with True Cache:

• Configure True Cache for high availability with two identical True Caches per dataset using the uniform configuration architecture.
• Use the JDBC 23ai UCP connection configuration for the application to ensure minimal impact on the application if one True Cache becomes unavailable.
• Before enabling the database application service for True Cache, populate the cache by running critical workload queries. 
• Before shutting down True Cache for planned maintenance, stop the database application service on True Cache.
• Partition or design True Caches to minimize fetches from the primary database.

The behavior and restrictions of Pluggable Databases (PDBs) within Oracle’s True Cache environment are as follows:

• Non-CDB Databases: I know that you know! but just a reminder, True Cache does not support non-CDB primary databases, as non-CDB databases are deprecated in Oracle 23ai.
• PDB Administration: Administrative tasks such as opening, closing, or shutting down PDBs cannot be performed directly on True Cache. However, applications can connect to specific PDBs on True Cache similarly to how they connect to primary databases or Oracle Active Data Guard.
• PDB Availability: The state of a PDB on True Cache is dependent on its state on the primary database:
  • If a PDB is open on the primary database across all Oracle RAC instances, it will be open on True Cache.
  • If a PDB is not open on the primary database across all Oracle RAC instances, True Cache cannot read from that PDB, and it will appear in a mounted state.
  • When a PDB is closed on the primary database, it is marked as disconnected on True Cache but remains open unless an action on the primary database necessitates its closure, such as dropping, renaming, or using flashback.
  • If the primary CDB shuts down, the ROOT is marked as disconnected on True Cache and may remain so for up to 24 hours, a duration that is configurable.

DEMO

How to create an Oracle True Cache “database”?

Now with all that things keep in mind let’s create an Oracle True Cache “database”.

Prerequisites

Few things you need to be aware of, I have already mentioned this before, True Cache is kind of an “Active Data Guard” So you will need to make sure that there is remote connection between your primary database and the server where you True Cache will be running, so complete the following prerequisites:

• Install the Oracle Database software on the True Cache server(s)
• Set up a network path to access the primary database with an Easy Connect (EZConnect) string from the True Cache server(s).
• The primary database must be in ARCHIVELOG mode to ship redo log files to the True Cache server(s). If you use Oracle DBCA then it will verify that the primary database is in ARCHIVELOG mode.
• Don’t set LOG_ARCHIVE_CONFIG and LOG_ARCHIVE_DEST_n on the primary database. True Cache automatically configures these for the primary database.

For demonstration purposes, I will configure the True Cache environment using the Database Configuration Assistant (DBCA) and set up the True Cache instance on the same server as the primary database. However, it’s important to note that in a production/real environment, deploying the True Cache instance on a separate server is recommended to ensure optimal performance and fault tolerance.

Command example:

Couple of things that makes everything easier:

You can copy password file and TDE wallet (if configured) from primary to the True Cache server or you can package everything into a configuration BLOB file using DBCA and just copy that file to the target server, it is simple and straightforward, so I will do that, also TDE is configured in my primary database.

$ORACLE_HOME/bin/dbca -configureDatabase -prepareTrueCacheConfigFile -sourceDB primary_db_sid_or_db_unique_name -trueCacheBlobLocation primary_db_config_blob_path -silent

On the True Cache node (in this case same server for me), run the -createTrueCache command to complete the True Cache configuration and start True Cache.

As I have run the previous command and generated the BLOB file so I will create my True Cache as follow:

$ORACLE_HOME/bin/dbca -createTrueCache -gdbName true_cache_global_name -sid true_cache_sid -sourceDBConnectionString primary_db_easy_connect_string -trueCacheBlobFromSourceDB true_cache_config_blob_path -sgaTargetInMB sga_memory_size -pgaAggregateTargetInMB pga_memory_size -tdeWalletLoginType AUTO_LOGIN -listeners listener_name -silent

Remember, you should provide primary easy connect string in the parameter -sourceDBConnectionString, format is: host:port/service_name if database is RAC then: SCAN:port/service_name

I have created my True Cache successfully!

Configuring True Cache Database Application Services

On primary database configure the remote listener, if your database is single instance with not grid then add REGISTRATION_INVITED_NODES_LISTENER parameter in the listener.ora file, for Oracle RAC primary databases, add the True Cache node to the invited node list for the SCAN listener by using the srvctl command line utility. (Don’t manually edit the listener.ora file as the grid owner user.)

Run the command as grid owner user and then verify:

srvctl modify scan_listener -invitednodes true_cache_host -endpoints TCP:port
srvctl config scan_listener

Now, create and start a primary database application service to use with the True Cache service, and then verify that the service is running.

For single instance primary databases, use the DBMS_SERVICE PL/SQL package connected to the PDB or executing the ALTER SESSION SET CONTAINER = PDBxxx to switch to the desire PDB and then run the PL/SQL.

BEGIN
   DBMS_SERVICE.CREATE_SERVICE('xx', 'xx');
   DBMS_SERVICE.START_SERVICE('xx');
END;
/

SELECT service_id, name, true_cache_service FROM v$active_services WHERE name='xx';

For RAC primary databases, use the srvctl command line utility to add the primary database service and start the service:

srvctl add service -db primary_db_unique_name -service primary_db_service_name -preferred primary_db_instance_list -pdb primary_pdb_name

In my case db is single but I have grid infrastructure so I will use srvctl add service, i don’t need to provide -preferred:

Now, I will run the dbca -configureDatabase command with the -configureTrueCacheInstanceService parameter. This configures the True Cache database application service on the primary database and then starts the True Cache service on True Cache instance.

Note: The primary database application service must already exist before you run this command. For a uniform True Cache configuration with multiple True Caches serving the same service, DBCA starts the service on the first True Cache only, so you will need to start the service on the other True Caches manually.

$ORACLE_HOME/bin/dbca -configureDatabase -configureTrueCacheInstanceService -sourceDB primary_db_sid_or_db_unique_name -trueCacheConnectString true_cache_easy_connect_string -trueCacheServiceName true_cache_service_name -serviceName primary_db_service_name -pdbName primary_pdb_name -silent

Verifying the True Cache Configuration

Now, that our config is done, let’s check if True Cache service has been register with the listener:

We can also take a look to the active services in the True Cache:

In our primary database we can see also which service are related with True Cache:

SELECT service_id, name, true_cache_service FROM v$active_services WHERE name='primary_db_service_name';

Great!

Monitoring True Cache!

So, how do we monitor True Cache? it is quite important right?

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.

SELECT * FROM v$true_cache;

You can also use AWR to gather statistics for True Cache. AWR snapshots are enabled by default and are captured hourly. You can also create snapshots manually connected to the True Cache as follow:

SELECT DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT FROM dual;

You can generate AWR snapshot report of the True Cache from primary database or connected directly to the True Cache, just be aware for each specify case what you need to do, example:

• On True Cache: @?/rdbms/admin/awrrpt.sql
• On the primary database: @?/rdbms/admin/awrrpti.sql
  • Here you will need to provide the(dbid) and instance number (inst_num) for True Cache. To get the dbid for True Cache, run the following query on the True Cache: SELECT DBMS_WORKLOAD_REPOSITORY.GET_AWR_ID() FROM dual;

Then provide the others parameters, snaps id to evaluate etc, as you will normally do when generating AWR reports.

This example was executed on True Cache:

Conclusion

In this post, we’ve explored the essential aspects of Oracle 23ai True Cache, highlighting its role as an in-memory, read-only cache that significantly enhances application performance. We discussed the core concepts of True Cache, including its configuration options, uniform and partitioned setups, and alignment with Oracle’s Maximum Availability Architecture (MAA).

By providing a detailed demonstration on creating an Oracle True Cache database and configuring application services, we aimed to equip you with practical steps to leverage this powerful feature in your database environments. Through effective monitoring and management strategies, such as utilizing the V$TRUE_CACHE view and AWR snapshots, you can ensure optimal performance and reliability of your True Cache instances.

True Cache not only optimizes data access but also enables seamless scaling and load balancing across multiple applications. Understanding and implementing True Cache can be an invaluable asset for database administrators seeking to enhance system performance and availability in their Oracle environments. As technology evolves, staying informed about such advancements will empower you to make the most out of your database infrastructure, driving efficiency and performance in your applications.