Note:

Implement Oracle True Cache Service with Oracle Database 23ai

Introduction

Oracle True Cache is an in-memory, consistent, and automatically managed SQL and key-value (object or JSON) cache for Oracle Database.

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

Modern applications often require a high number of connections and fast, low-latency access to the data. A popular approach is to place caches in front of the database because applications typically perform many more reads than updates, and they can read from the cache without affecting the database performance. For example, an airline reservation system reads data frequently as people shop for flights, and it is okay if the data is not the most current compared to what is in the database. The application only needs to access the most current data when someone reserves a flight.

Unlike conventional caches, Oracle True Cache automatically keeps the most frequently accessed data in the cache, and it keeps the cache consistent with the primary database, other objects in the same cache, and other caches. It caches all Oracle Database objects and data types, including JSON.

Because Oracle True Cache implements Oracle Database security policies, you can control access to the cache. This enables you to cache sensitive data, like private personal information, that you might not ordinarily cache.

Environment Details:

Objectives

Prerequisites

Task 1: Copy Password File and Transparent Data Encryption (TDE) Wallet

Copy the password file, and optionally the TDE wallet from the primary database to the Oracle True Cache node. You can use either of the following methods:

Task 2: Drop Unneeded Database on Oracle True Cache Node

On the Oracle True Cache node, drop existing database on the Oracle True Cache hosts if database already exists and is not required.

sqlplus / as sysdba
SQL> shut immediate
SQL> startup mount restrict
SQL> drop database;

Task 3: Test Primary Database Connection String from True Cache Host

Run the following command to test primary database connection string from Oracle True Cache host.

sqlplus sys/<sys_password>@<primary_database_hostname>:1521/<primary_database_service>  as sysdba

Task 4: Configure and Start Oracle True Cache

On the Oracle True Cache node, run the -createTrueCache command to complete the Oracle True Cache configuration and to start Oracle True Cache.

$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

Example Output:

[oracle@test1 ~]$ $ORACLE_HOME/bin/dbca -createTrueCache -gdbName test1 -sid test1 -sourceDBConnectionString proddb.sub12101216100.dbnet.oraclevcn.com.:1521/DB1218_xts_iad.sub12101216100.dbnet.oraclevcn.com -trueCacheBlobFromSourceDB /home/oracle/blob_2024-12-18_12-47-36PM.tar.gz -sgaTargetInMB 25000 -pgaAggregateTargetInMB 12000 -initParams db_create_file_dest=/u02/app/oracle/oradata -listeners LISTENER -silent
Enter Remote DB SYS user password:

Enter password for the source database TDE wallet:

Session ID of the current execution is: 1
Log file location: /u01/app/oracle/cfgtoollogs/dbca/test1/trace.log_2024-12-18_01-01-33PM_61716
-----------------
Running Extract_password_file_from_blob_file job
Completed Extract_password_file_from_blob_file job
25% complete
-----------------
Running Create_static_listener job
Skipping. Job is detected as not applicable.
38% complete
-----------------
Running Register_listener job
Completed Register_listener job
50% complete
-----------------
Running Extract_tde_wallet_from_blob_file job
Completed Extract_tde_wallet_from_blob_file job
54% complete
-----------------
Running Setup_required_directories job
Skipping. Job is detected as not applicable.
57% complete
-----------------
Running Create_pfile job
Completed Create_pfile job
61% complete
-----------------
Running Start_nomount_instance job
Completed Start_nomount_instance job
64% complete
-----------------
Running Create_TDE_wallet job
Completed Create_TDE_wallet job
68% complete
-----------------
Running Create_truecache_instance job

Completed Create_truecache_instance job
71% complete
-----------------
Running Add_oratab_entry job
Completed Add_oratab_entry job
75% complete
-----------------
Running Reopen_wallet job
Completed Reopen_wallet job
100% complete
---------- PLUGIN NOTES ----------
Successfully created True Cache.
In order to complete the operation,configure the True Cache database application services from the primary database.
---------- END OF PLUGIN NOTES ----------
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/test1/test1.log" for further details.
[oracle@test1 ~]$

Task 5: Create and Verify Pluggable Database (PDB) Level Application Service for Oracle True Cache

Create and start a primary database (at PDB level) application service to use with the Oracle True Cache service, and verify that the service is running.

For single instance primary databases, use the DBMS_SERVICE PL/SQL package. For services that are specific to a PDB, connect to the specific PDB, or set the correct PDB container in your session before starting the service using DBMS_SERVICE.

Example Output:

alter session set container=<PDB_NAME>;
show pdbs
BEGIN
DBMS_SERVICE.CREATE_SERVICE('SALES', 'SALES');
DBMS_SERVICE.START_SERVICE('SALES');
END;
/
PL/SQL procedure successfully completed.

Task 6: Configure Oracle True Cache on the Primary Database

Run the dbca -configureDatabase command with the -configureTrueCacheInstanceService parameter to configure Oracle True Cache on the primary database. This configures the Oracle True Cache database application service on the primary database and then starts the Oracle True Cache service on Oracle True Cache.

Run the following command for each Oracle True Cache database application service.

$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

Example Output:

[oracle@proddb ~]$ $ORACLE_HOME/bin/dbca -configureDatabase -configureTrueCacheInstanceService -sourceDB DB1218 -trueCacheConnectString test1.sub12101216100.dbnet.oraclevcn.com:1521/test1.sub12101216100.dbnet.oraclevcn.com -trueCacheServiceName sales_tc1 -serviceName sales -pdbName DB1218_PDB1 -silent
Session ID of the current execution is: 7
Log file location: /u01/app/oracle/cfgtoollogs/dbca/DB1218/trace.log_2024-12-18_02-39-50PM_60536
-----------------
Running Initialization job
Enter SYS user password:

Completed Initialization job
33% complete
-----------------
Running Validate_true_cache_instance_connection job
Completed Validate_true_cache_instance_connection job
37% complete
-----------------
Running Validate_dataguard job
Skipping. Job is detected as not applicable.
41% complete
-----------------
Running Validate_db_version job
Completed Validate_db_version job
44% complete
-----------------
Running Validate_true_cache_instance job
Completed Validate_true_cache_instance job
48% complete
-----------------
Running Validate_archive_log_mode job
Completed Validate_archive_log_mode job
52% complete
-----------------
Running Validate_pdb job
Completed Validate_pdb job
56% complete
-----------------
Running Validate_primary_db_service job
Completed Validate_primary_db_service job
59% complete
-----------------
Running Validate_true_cache_db_service job
Completed Validate_true_cache_db_service job
63% complete
-----------------
Running Validate_true_cache_instance_open_mode job
Completed Validate_true_cache_instance_open_mode job
67% complete
-----------------
Running Create_truecache_service job
Completed Create_truecache_service job
73% complete
-----------------
Running Add_network_entry job
Completed Add_network_entry job
80% complete
-----------------
Running Modify_primary_service job
Completed Modify_primary_service job
87% complete
-----------------
Running Start_truecache_service job
Completed Start_truecache_service job
93% complete
-----------------
Running Enable_service_registration job
Completed Enable_service_registration job
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/DB1218/DB12183.log" for further details.
[oracle@proddb ~]$

Task 7: Start Service on Oracle True Cache Node

Run the following command to start the service on Oracle True Cache instance node.

alter session set container=<PDB_NAME>;
show pdbs
BEGIN
DBMS_SERVICE.START_SERVICE('sales_tc1');
END;
/

Task 8: Update Parameters in the Primary Database

  1. Update the following parameters in the primary database listener.ora file.

    VALID_NODE_CHECKING_REGISTRATION_LISTENER = OFF
REGISTRATION_INVITED_NODES_LISTENER=(<true_cache_instance_ip>)

  2. Restart or reload the listener on the primary database server.

Task 9: Verify Oracle True Cache Services in the Primary Database Listener

Run the following command to verify that the Oracle True Cache (sales_tc) services are running under the primary database listener.

SQL> select name,open_mode,database_role from gv$database;

NAME                           OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
DB1218                         READ WRITE           PRIMARY

SQL> select * from v$true_cache;

MY_DG_ID REMOTE_DG_ID    DEST_ID TRUE_CACHE PRIMARY_NAME         STATUS     REMOTE_VERSION         CON_ID
---------- ------------ ---------- ---------- -------------------- ---------- ------------------ ----------
4079357444    576841986          2 test1      DB1218_xts_iad       HEALTHY    0.0.0.0.0                   0
4079357444   2149296628          3 test2      DB1218_xts_iad       HEALTHY    0.0.0.0.0                   0

SQL> SELECT name, true_cache_service FROM DBA_SERVICES WHERE name='sales';

NAME                           TRUE_CACHE_SERVICE
------------------------------ ------------------------------
sales                          sales_tc2

[oracle@proddb ~]$ lsnrctl status LISTENER|grep -i sales_tc1
Service "sales_tc1.sub12101216100.dbnet.oraclevcn.com" has 1 instance(s).
[oracle@proddb ~]$

Task 10: Verify True Cache Services on Oracle True Cache Instance Node

Run the following command to verify that the Oracle True Cache (sales_tc) services are running on the Oracle True cache instance node.

SQL> select name,open_mode,database_role from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DB1218    READ ONLY WITH APPLY TRUE CACHE

SQL> select * from v$true_cache;

MY_DG_ID REMOTE_DG_ID    DEST_ID TRUE_CACHE PRIMARY_NAME         STATUS     REMOTE_VERSION         CON_ID
---------- ------------ ---------- ---------- -------------------- ---------- ------------------ ----------
576841986   4079357444          0 test1      DB1218_xts_iad       HEALTHY    23.0.0.0.0                  0

[oracle@test1 ~]$ lsnrctl status LISTENER|grep sales_tc
Service "sales_tc1.sub12101216100.dbnet.oraclevcn.com" has 1 instance(s).
[oracle@test1 ~]$

Task 11: Test Oracle True Cache Service

Add the following Transparent Network Substrate (TNS) entry to the application.

TEST_TC =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = test1.sub12101216100.dbnet.oraclevcn.com)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sales_tc1.sub12101216100.dbnet.oraclevcn.com)
   )
   )

sqlplus sys/<sys_password>@TEST_TC as sysdba
select name,open_mode,database_role from v$database;
show pdbs

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.