Read-Only Cache Group

A read-only cache group enforces a caching behavior where the TimesTen cache tables cannot be updated directly, and committed changes on the cached Oracle Database tables are automatically refreshed to the cache tables.

See Figure 4-5.

Figure 4-5 Read-Only Cache Group

Description of Figure 4-5 follows
Description of "Figure 4-5 Read-Only Cache Group"

If the TimesTen database is unavailable for whatever reason, you can still update the Oracle Database tables that are cached in a read-only cache group. When the TimesTen database returns to operation, updates that were committed on the cached Oracle Database tables while the TimesTen database was unavailable are automatically refreshed to the TimesTen cache tables.

TimesTen supports both static and dynamic read-only cache groups.

Note:

When TimesTen manages operations for read only cache groups, it connects to the Oracle database using the Oracle cache administration user name and password. For more details, see Registering the Cache Administration User Name and Password.

On the Oracle Database:

The following is an example of a definition of the Oracle Database tables that are to be cached in read-only cache groups. The Oracle Database tables are owned by the schema user sales. 

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

CREATE TABLE orders
(ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
 cust_num     NUMBER(6) NOT NULL,
 order_det    JSON,
 when_placed  DATE NOT NULL,
 when_shipped DATE NOT NULL);

For cached tables that are going to be included in read-only cache groups, the Oracle cache administration user must be granted the SELECT privilege on these cached tables. In this example, these tables are sales.customer and sales.orders tables.

See Required Privileges for Cache Administration User for Cache Operations for all required privileges for different activities.

On the Oracle database, connect as an administrator and grant the following privileges:

SQL> GRANT SELECT ON sales.customer TO cacheadmin;
SQL> GRANT SELECT ON sales.orders TO cacheadmin;

On the TimesTen database:

Connect as the TimesTen cache administration user. Use the CREATE READONLY CACHE GROUP statement to create a read-only cache group.

The following statement creates a read-only cache group customer_orders that caches the tables sales.customer (root table) and sales.orders (child table): 

CREATE READONLY CACHE GROUP customer_orders
FROM sales.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num)),
sales.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  order_det    JSON,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES sales.customer(cust_num));

By default, all read-only cache groups are defined with incremental autorefresh paused with the default interval value. Perform a LOAD CACHE GROUP statement for the first load of the read-only cache group since the cache tables are empty. The autorefresh state changes from PAUSED to ON after the LOAD CACHE GROUP statement completes.

The cache tables in a read-only cache group cannot be updated directly. However, you can set the passthrough level to 2 to allow committed update operations issued on a TimesTen cache table to be passed through and processed on the cached Oracle Database table, and then have the updates be automatically refreshed into the cache table. See Setting a Passthrough Level.

When you’re working with trigger-based cache groups in TimesTen, it’s important to understand that the effects of a passed through statement (such as an update, insert, or delete) on the cache tables do not occur within the same transaction in which the update operation was issued on the backend database. Instead, they are seen after the passed through update operation has been committed on the Oracle database and the next automatic refresh of the cache group has occurred. The Oracle cache administration user must be granted the INSERT, UPDATE and DELETE privileges on the Oracle database tables that are cached in the read-only cache group in order for the passed through update operations to be processed on the cached Oracle database tables.

Log-based cache groups in Oracle TimesTen, are primarily designed for both static and dynamic cache groups, where data is continuously refreshed and synchronized from Oracle using redo logs. It enables automatic refresh and asynchronous data propagation from Oracle to TimesTen by reading changes from the Oracle logs. See Configuring GoldenGate for Log-Based Cache Autorefresh on TimesTen.

If you manually created the Oracle database objects used to enforce the predefined behaviors of a cache group with autorefresh as described in The initCacheAdminSchema.sql Script, you need to set the autorefresh state to OFF when creating the cache group.

Then you need to run the ttIsql utility's cachesqlget command to generate a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle Database table that is cached in the read-only cache group. See Manually Creating Oracle Database Objects for Cache Groups with Autorefresh for how to create these objects.