Using Cache Groups in TimesTen Scaleout

12 Using Cache Groups in TimesTen Scaleout

TimesTen Cache provides the ability to transfer data between an Oracle database and a TimesTen database through cache groups.

This chapter illustrates the creation and use of cache groups in TimesTen Scaleout.

Introduction of TimesTen Cache in TimesTen Scaleout

You can cache data from an Oracle database in a TimesTen database by creating cache groups, where each cache group can cache a single Oracle database table or a set of related Oracle database tables. The cached data from the Oracle database can consist of all the rows and columns or a subset of the rows and columns in the Oracle database tables.

TimesTen Scaleout supports static read-only cache groups with incremental autorefresh.

Applications can read from cache groups.
Data is initially loaded from the Oracle database into the cache group, and then refreshed manually or automatically.
Updates to the cached tables can be sent through to the Oracle database using a passthrough operation.

TimesTen Cache utilizes the scalability and data redistribution of TimesTen Scaleout. When you add or remove elements within replica sets, TimesTen Cache automatically:

Redistributes the data as specified with the distribution scheme on each cache table.
Redistributes the autorefresh processing across elements in the modified replica sets.

TimesTen Cache also utilizes the recovery of TimesTen Scaleout. TimesTen Cache in TimesTen Scaleout handles most occurrences of element failure, replica set failure, restarting cache agents, restarting the Oracle database, and communication failures with the Oracle database.

You can go to the following sections in the Oracle TimesTen In-Memory Database Cache Guide for concepts on static read-only cache groups and incremental autorefresh.

"Overview of cache groups" for an overall description of cache groups.
"Cache groups and cache tables" for an introduction on how to create cache groups and cache tables.
"Read-only cache group" for a description of read-only cache groups.
"AUTOREFRESH cache group attribute" for details on incremental autorefresh.

Setting up the Oracle database and TimesTen Scaleout systems

Before you can create a cache group, you must first install TimesTen Scaleout and then configure both the Oracle database and TimesTen Scaleout systems. See Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide for information about installing TimesTen Scaleout.

Note:

It is best to have the TimesTen and Oracle databases on separate systems, to avoid resource contention between them. TimesTen, being an in-memory database, uses a significant amount of memory. It may also use a significant amount of CPU time and generate a significant amount of I/O, depending on the workload.

You inform TimesTen Cache of the Oracle database with which to connect, which credentials to use when connecting to the Oracle database and which users own the tables in both TimesTen and Oracle databases.

Create users and tablespace in the Oracle database

You must create a default tablespace to be used for storing TimesTen Cache management objects that should not be shared with other applications. We strongly recommend that this tablespace be used solely for cache management.

Create the following users on the Oracle database:

One or more schema users that own the Oracle database tables to be cached in a TimesTen database. These may be existing users or new users.
A cache administration user creates and maintains Oracle database objects that store information used to manage the cache environment and enforce predefined behaviors of particular cache group types.

The following demonstrates how to create the default tablespace, the schema user, and the cache administration user:

Start SQL*Plus on the Oracle database system from an operating system shell or command prompt. Connect to the Oracle database as an Oracle database user with the privileges needed to create required roles and grant the necessary privileges to the cache administration user on the Oracle database.
```
% cd timesten_home/install/oraclescripts
% sqlplus sys as sysdba
Enter password: password
```
Note:

This example uses the sys as sysdba user since the SYS user is able to grant the required privileges. You can use any Oracle database user that has the appropriate privileges. See "Required privileges for the cache administration user and the cache manager user" in the Oracle TimesTen In-Memory Database Cache Guide for a list of the privileges needed.
Use SQL*Plus to create a default tablespace. In the following example, the name of the default tablespace is cachetblsp:
```
SQL> CREATE TABLESPACE cachetblsp DATAFILE 'tt_cache.f' SIZE 5G 
 SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.
```
Use SQL*Plus to create a schema user. Grant this user the minimum set of privileges required to create tables in the Oracle database to be cached in a TimesTen database. In the following example, the schema user is oratt:
```
SQL> CREATE USER oratt IDENTIFIED BY orapwd;
SQL> GRANT CREATE SESSION, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
 TO oratt;
```

Use SQL*Plus to perform the create a cache administration user:

Create a cache administration user and specify the default tablespace that you created for cache management objects.
Run the SQL*Plus script timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql to grant the cache administration user the minimum set of privileges required to perform cache group operations.
Pass the cache administration user name as arguments to the grantCacheAdminPrivileges.sql script. In the following example, the cache administration user name is cacheuser:

Note:

See the comments in the timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql script for the required privileges by the user who runs this script and the privileges that this user grants to the cache administration user.

SQL> CREATE USER cacheuser IDENTIFIED BY orapwd
    DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;
SQL> @grantCacheAdminPrivileges "cacheuser"
Please enter the administrator user id
The value chosen for administrator user id is cacheuser
 
***************** Creation of TT_CACHE_ADMIN_ROLE starts ******************
0. Creating TT_CACHE_ADMIN_ROLE role
** Creation of TT_CACHE_ADMIN_ROLE done successfully **
***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEUSER
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEUSER
2. Granting the DBMS_LOCK package privilege to CACHEUSER
3. Granting the DBMS_DDL package privilege to CACHEUSER
4. Granting the DBMS_FLASHBACK package privilege to CACHEUSER
5. Granting the CREATE SEQUENCE privilege to CACHEUSER
6. Granting the CREATE CLUSTER privilege to CACHEUSER
7. Granting the CREATE OPERATOR privilege to CACHEUSER
8. Granting the CREATE INDEXTYPE privilege to CACHEUSER
9. Granting the CREATE TABLE privilege to CACHEUSER
10. Granting the CREATE PROCEDURE  privilege to CACHEUSER
11. Granting the CREATE ANY TRIGGER  privilege to CACHEUSER
12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEUSER
13. Granting the DBMS_LOB package privilege to CACHEUSER
14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEUSER
15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEUSER
16. Checking if the cache administrator user has permissions on the default tablespace
Permission exists
18. Granting the CREATE TYPE privilege to CACHEUSER
19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEUSER
20. Granting the SELECT on SYS.GV$SESSION privilege  to CACHEUSER
21. Granting the SELECT on SYS.DBA_DATA_FILES privilege  to CACHEUSER
22. Granting the SELECT on SYS.USER_USERS privilege  to CACHEUSER
23. Granting the SELECT on SYS.USER_FREE_SPACE privilege  to CACHEUSER
24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege  to CACHEUSER
25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege  to CACHEUSER
26. Granting the SELECT on SYS.V$DATABASE privilege  to CACHEUSER (optional)
27. Granting the SELECT on SYS.V$SESSION privilege  to CACHEUSER (optional)
28. Granting the SELECT on SYS.V$PROCESS privilege  to CACHEUSER (optional)
29. Granting the SELECT ANY TRANSACTION privilege to CACHEUSER
********* Initialization for cache admin user done successfully *********
SQL> exit

See "Create the Oracle database users" in the Oracle TimesTen In-Memory Database Cache Guide for more information about the schema users, the cache administration user, and the default tablespace.

Create a TimesTen database

Creating a TimesTen database includes the following tasks:

Create a database definition for the TimesTen database

When creating a database definition file for a TimesTen database that caches data from an Oracle database, pay special attention to the settings of the following connection attributes.

The OracleNetServiceName must be set to the net service name of the Oracle database instance.
PermSize specifies the allocated size of the database's permanent region in MB. Set PermSize to a size large enough to store all of the data in the cache groups, indexes, and so on. The PermSize value must be smaller than the physical RAM on the machine. The PermSize value could be from a few GB to several TB. The default is 32 MB.
DatabaseCharacterSet must match the Oracle database character set.

You can determine the Oracle database character set by executing the following query in SQL*Plus as any user:
```
SQL> SELECT value FROM nls_database_parameters 
       WHERE parameter='NLS_CHARACTERSET';
```

The following example shows the contents of a database definition file named database1.dbdef that defines:

The full path for the checkpoint files as /disk1/databases/database1
The directory for the log files as /disk2/logs
The database character set as AL32UTF8
32 GB for the permanent memory region of every element
4 GB for the temporary memory region of every element
1 GB for the internal transaction log buffer of every element
An upper limit of 3072 user-specified concurrent connections to the database
The OracleNetServiceName set to inst1.

DataStore=/disk1/databases/database1
LogDir=/disk2/logs
DatabaseCharacterSet=AL32UTF8
PermSize=32768
TempSize=4096
LogBufMB=1024
Connections=3072
OracleNetServiceName=inst1

See "Creating a database definition" in the Oracle TimesTen In-Memory Database Scaleout User's Guide for more information about creating a database definition.

Create and open the TimesTen database

Once the database definition is created to include connection attributes for TimesTen Cache, you can perform the rest of the tasks necessary to create and open the TimesTen database.

For full details on these steps, see "Creating a database" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Use the ttGridAdmin dbdefCreate command to create a database definition based on a database definition file. TimesTen Scaleout uses the name of the database definition file to name the database definition.
Create the database1 database definition based on the database1.dbdef file.
```
% ttGridAdmin dbdefCreate /mydir/database1.dbdef
Database Definition database1 created.
```

Add the database1 database definition to the current version of the model.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

Create a TimesTen database based on the attributes stored in a database definition.
The ttGridAdmin dbCreate command creates a database based on a database definition.

Create the database1 database based on the database1 database definition.
```
% ttGridAdmin dbCreate database1
Database database1 creation started
```

Wait until all data instances report that they have loaded their element of the database into memory before proceeding with the definition of the distribution map. You can verify the status of the database creation process with the ttGridAdmin dbStatus command.

% ttGridAdmin dbStatus database1 -element
Database database1 element level status as of Tue Dec 22 08:52:09 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1  1   loaded  stopped   2020-12-22 08:52:00         
host4 instance1  2   loaded  stopped   2020-12-22 08:52:04         
host5 instance1  3   loaded  stopped   2020-12-22 08:51:47         
host6 instance1  4   loaded  stopped   2020-12-22 08:51:58         
host7 instance1  5   loaded  stopped   2020-12-22 08:52:04         
host8 instance1  6   loaded  stopped   2020-12-22 08:52:04

Define the distribution map of the TimesTen database and add all the elements of the available data instances in the grid1 grid to the distribution map of the database1 database.
```
% ttGridAdmin dbDistribute database1 -add all -apply
Distribution map updated
```
Open the database1 database for user connections.
The ttGridAdmin dbOpen command opens a database for user connections.
```
% ttGridAdmin dbOpen database1
Database database1 open started
```

Verify that the database is open with the ttGridAdmin dbStatus command.

The example shows a status summary for the database1 database. Notice that the report shows all elements of the database as open.

% ttGridAdmin dbStatus database1 -element
Database database1 element level status as of Tue Dec 22 08:52:49 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened stopped 2020-12-22 08:52:44         
host4 instance1    2 opened stopped 2020-12-22 08:52:43         
host5 instance1    3 opened stopped 2020-12-22 08:52:43         
host6 instance1    4 opened stopped 2020-12-22 08:52:44         
host7 instance1    5 opened stopped 2020-12-22 08:52:44         
host8 instance1    6 opened stopped 2020-12-22 08:52:44

Add the Oracle Database net service name to the tnsnames.ora file

When you set up TimesTen Cache environment in TimesTen Scaleout, all instances in a database must have access to the Oracle database.

The Oracle Database tnsnames.ora file defines Oracle Net Services to which applications connect. Use the ttGridAdmin TNSNamesImport command to import the tnsnames.ora on all instances in the TimesTen database.

Note:

If you need to add SQLNet parameters, create and import a sqlnet.ora file with the ttGridAdmin SQLNetImport command.

Add the Oracle Database net service name to a tnsnames.ora file.

The following is an example of defining inst1 in a tnsnames.ora file:

inst1 =  
 (DESCRIPTION =   
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.com)
     (PORT = 1521))     
   (CONNECT_DATA =       
     (SERVICE_NAME = inst1.my.example.com)))

Import this file into the grid using the ttGridAdmin TNSNamesImport command.

% ttGridAdmin TNSNamesImport /tmp/tnsnames.ora
TNSNames configuration file /tmp/tnsnames.ora imported

Apply it to the model with the ttGridAdmin modelApply command. After the model apply completes, the tnsnames.ora file exists on all instances.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

See "Import TNS names (TNSNamesImport)" or "Import a sqlnet file (SQLNetImport)" in the Oracle TimesTen In-Memory Database Reference for more information.

Create users in the TimesTen database

In addition to the Oracle database users, you must create the following TimesTen users before you can use TimesTen Cache:

A cache manager user performs cache group operations. The TimesTen cache manager user must have the same name as a companion Oracle Database user that can access the cached Oracle database tables. The companion Oracle database user can be the cache administration user, a schema user, or some other existing user. For ease of use, making the cache administration user the companion Oracle database user of the cache manager user is preferable. The password of the cache manager user can be different than the password of the companion Oracle database user with the same name.

Note:

See "Create the TimesTen Users" in the Oracle TimesTen In-Memory Database Cache Guide for more details on the cache manager user and its companion Oracle database user.
One or more cache users own the cache tables. You must create a TimesTen cache user with the same name as an Oracle database schema user for each schema user who owns or will own Oracle database tables to be cached in the TimesTen database. The password of a cache user can be different than the password of the Oracle database schema user with the same name.

The owner and name of a TimesTen cache table is the same as the owner and name of the corresponding cached Oracle database table.

On one of the data instances on the TimesTen database, connect using the direct connectable. Create a cache manager user and grant this user the minimum set of privileges required to create cache groups and to perform operations on the cache groups. In the following example, the cache manager user name is cacheuser, which is the same name as the cache administration user that was created earlier:

ttisql "DSN=database1"

Command> CREATE USER cacheuser IDENTIFIED BY ttpwd;
Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;

Then, create a cache user. In the following example, the cache user name is oratt, which is the same name as the Oracle database schema user that was created earlier:

Command> CREATE USER oratt IDENTIFIED BY ttpwd;

The privileges that the cache manager user requires depend on the operations that you perform on the cache groups. See "Create the TimesTen Users" in the Oracle TimesTen In-Memory Database Cache Guide for more information about the cache manager user and the cache users.

See "Managing Access Control" in the Oracle TimesTen In-Memory Database Security Guide for more information about TimesTen users and privileges.

Create a connectable for the TimesTen database

When creating a connectable for a TimesTen database that caches data from an Oracle database, pay special attention to the settings of the following connection attributes. All of these connection attributes can be set in a connectable or a connection string, unless otherwise stated.

UID specifies the name of a cache user, such as the cache manager user, that has the same name as a companion Oracle database user who can access the cached Oracle database tables. The UID connection attribute can be specified in a direct connectable, a client/server connectable, or a connection string.
PWD specifies the password of the TimesTen user specified in the UID connection attribute. The PWD connection attribute can be specified in a Direct DSN, a Client DSN, or a connection string.
OraclePWD specifies the password of the companion Oracle database user that has the same name as the TimesTen user specified in the UID connection attribute and can access the cached Oracle database tables.

Note:

See "Create users and tablespace in the Oracle database" and "Create users in the TimesTen database" for more details on the cache manager user and its companion Oracle database user.
PassThrough can be set to control whether statements are to be run in the TimesTen database or passed through to be run in the Oracle database. See "Using Passthrough".
LockLevel must be set to its default of 0 (row-level locking) because TimesTen Cache does not support database-level locking.

See "OracleNetServiceName" and "OraclePWD" in the Oracle TimesTen In-Memory Database Reference for more details on these connection attributes.

The following example shows how to create a connectable that uses TimesTen Cache.

Create a connectable file that sets the connection character set, OracleNetService Name, user ID for the connection.

This connectable file is named database1CS.connect. This file sets AL32UTF8 as the connection character set, cache administration user name (that is the same as the cache manager user name) as cacheuser, with the TimesTen password for cacheuser as ttpwd and the Oracle database password for cacheuser is oratt.
```
ConnectionCharacterSet=AL32UTF8
UID=cacheuser
PWD=ttpwd
OraclePWD=orapwd
```
The ttGridAdmin connectableCreate command creates a connectable based on a connectable file.

Create the database1CS connectable based on the database1CS.connect connectable file.
```
% ttGridAdmin connectableCreate -dbdef database1 -cs /mydir/database1CS.connect
Connectable database1CS created.
```

Apply the creation of the database1CS connectable to the current version of the model to make the connectable available for use.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

For full details on these steps, see "Creating a connectable" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Set the cache administration user name and password in the TimesTen database

TimesTen Cache must know which credentials to use when connecting to the Oracle database. All instances in the same database must use the same cache administration user id and password when connecting to the Oracle database.

Use the ttGridAdmin dbCacheCredentialSet command on the active management instance to set the cache administration user name and password. This user name and password are saved in an Oracle Wallet, which each instance in the database can access and use.

% ttGridAdmin dbCacheCredentialSet database1
Provide Oracle user id: cacheuser
Provide Oracle password: orapwd
Configuring cache.....................................................OK

When prompted, specify the cache administration user name as the Oracle database user id and the cache administration user password as the Oracle database password (created with the same name to be the companion user to the cache manager user).

The cache administration user name and password need to be set only once in a TimesTen database. See "Set the cache administration user name and password in the TimesTen database" in the Oracle TimesTen In-Memory Database Cache Guide for information on how to use this setting by the TimesTen database.

You can also use the ttGridAdmin dbCacheCredentialSet command to change the cache administration user name and password. The password can be changed at any time. The cache administration user name can only be changed when there are no cache groups on the TimesTen database. See "Changing cache user names and passwords" in the Oracle TimesTen In-Memory Database Cache Guide for full details.

Creating a static read-only cache group

This section creates a read-only cache group (as shown in Figure 12-1).

Figure 12-1 Single-table read-only cache group

Description of "Figure 12-1 Single-table read-only cache group"

Complete the following tasks to create a read-only cache group:

Create the Oracle database tables to be cached

Start SQL*Plus and connect to the Oracle database as the schema user:

% sqlplus oratt/orapwd

Use SQL*Plus to create a table readtab as shown in Figure 12-2:

SQL> CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));
Table created.

Figure 12-2 Creating an Oracle database table to be cached in a read-only cache group

Description of "Figure 12-2 Creating an Oracle database table to be cached in a read-only cache group"

Then, use SQL*Plus to insert some rows into the readtab table, and commit the changes:

SQL> INSERT INTO readtab VALUES (1, 'Hello');
1 row created.
SQL> INSERT INTO readtab VALUES (2, 'World');
1 row created.
SQL> COMMIT;
Commit complete.

Next use SQL*Plus to grant the SELECT privilege on the readtab table to the cache administration user:

SQL> GRANT SELECT ON readtab TO cacheuser;
Grant succeeded.

The SELECT privilege on the readtab table is required to create a read-only cache group that caches this table and to perform autorefresh operations from the cached Oracle database table to the TimesTen cache table.

See "Grant privileges to the Oracle database users" in the Oracle TimesTen In-Memory Database Cache Guide for more information about the privileges required for the cache administration user to create and perform operations on a read-only cache group.

Start a cache agent for TimesTen Scaleout

The cache agent performs cache operations, such as loading a cache group and managing autorefresh. TimesTen distributes cache tasks across different cache agents (each running on different data instances), where all work for a specific autorefresh interval is assigned to a single cache agent. A cache agent can manage multiple autorefresh intervals.

On the active management instance, use the ttGridAdmin dbCacheStart command to start cache agents on all data instances in the database. After which, use the ttGridAdmin dbStatus command to show when cache agents on all data instances are started.

% ttGridAdmin dbCacheStart database1
Database database1 : Starting cache agents.

% ttGridAdmin dbStatus -element
Database database1 element level status as of Thu Dec 24 09:59:14 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened started 2020-11-23 08:37:35         
host4 instance1    2 opened started 2020-11-23 08:37:35         
host5 instance1    3 opened started 2020-11-23 08:37:35         
host6 instance1    4 opened started 2020-11-23 08:37:35         
host7 instance1    5 opened started 2020-11-23 08:37:35         
host8 instance1    6 opened started 2020-11-23 08:37:35

You can start the cache agent for a specific data instance if you specify the -instance option.

% ttGridAdmin dbCacheStart database1 -instance host4.instance1
Database database1 : Starting cache agents. 

% ttGridadmin dbStatus database1 -element
 
Database database1 element level status as of Mon Dec  7 14:52:51 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened stopped 2020-11-23 08:37:35         
host4 instance1    2 opened started 2020-11-23 08:37:35         
host5 instance1    3 opened stopped 2020-11-23 08:37:35         
host6 instance1    4 opened stopped 2020-11-23 08:37:35         
host7 instance1    5 opened stopped 2020-11-23 08:37:35         
host8 instance1    6 opened stopped 2020-11-23 08:37:35

See "Stopping the cache agents for TimesTen Scaleout" for how to stop the cache agent on all data instances or a single data instance. See "Limiting cache agent connections to the Oracle database" for performance considerations.

Create the cache groups

In TimesTen Scaleout, you can create static read-only cache groups with incremental autorefresh. Read-only cache groups provide for committed changes on tables in the Oracle database to be automatically refreshed to the cache tables in the TimesTen database. Read-only cache groups are described fully in "Read-only cache group" in the Oracle TimesTen In-Memory Database Cache Guide.

You specify incremental autorefresh with AUTOREFRESH INTERVAL when you create the cache group. See "Automatically refresh updates on the cached Oracle database table" for more details.

The main difference for creating cache groups on TimesTen Scaleout is that you specify how the data is distributed across the elements of the database. The distribution scheme is specified in the DISTRIBUTE BY clause of the CREATE CACHE GROUP statement. See Distribution schemes for TimesTen Cache in TimesTen Scaleout for more details.

As the cache manager user, the following example creates a static read-only cache group readcache that caches the Oracle database oratt.readtab table using an incremental autorefresh with an autorefresh interval of 5 seconds. The distribution scheme is specified as the HASH distribution scheme. The HASH distribution scheme is the default (and so would not normally be necessary to include in the SQL statement).

To connect as the cache manager user, start the ttIsql utility and connect to the database1 TimesTen database including the cache manager user, the cache manager user password and the password of its companion Oracle user.

ttIsql "DSN=database1;UID=cacheuser;PWD=ttpwd;OraclePWD=orapwd"
 
Copyright (c) 1996, 2021, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=database1;UID=cacheuser;PWD=********;OraclePWD=********";
Connection successful: DSN=database1;UID=cacheuser;
DataStore=/timesten/datastores/database1;DatabaseCharacterSet=WE8ISO8859P1;
ConnectionCharacterSet=AL32UTF8;PermSize=512;TempSize=512;Connections=100;
OracleNetServiceName=inst1;EpochInterval=1;
(Default setting AutoCommit=1)

Command> CREATE READONLY CACHE GROUP readcache
 AUTOREFRESH INTERVAL 5 SECONDS
 FROM oratt.readtab
 (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32))
 DISTRIBUTE BY HASH;
Command>

The readcache cache group and its respective oratt.readtab cache table, whose owners and names are identical to the cached Oracle database tables, are created in the TimesTen database.

Figure 12-3 shows that the readcache cache group caches the oratt.readtab table.

Figure 12-3 Creating a read-only cache group

Description of "Figure 12-3 Creating a read-only cache group"

Use the ttIsql cachegroups command to view the definition of the readcache cache group:

Command> cachegroups;

Cache Group CACHEUSER.READCACHE:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined

  Root Table: ORATT.READTAB
  Table Type: Read Only

1 cache group found.

See "Read-only cache group" in the Oracle TimesTen In-Memory Database Cache Guide for more information about read-only cache groups.

Distribution schemes for TimesTen Cache in TimesTen Scaleout

Distribution schemes for cache groups are specified with the DISTRIBUTE BY clause. A best practice is for all child tables to be distributed by reference.

The following are the default distribution schemes for cache groups defined within TimesTen Scaleout:

Single table cache groups default to using a HASH distribution scheme.
If a column is not specified in the DISTRIBUTE BY HASH clause, TimesTen Scaleout selects the primary key columns as the key columns of the distribution scheme. If a primary key is not defined, TimesTen Scaleout creates a hidden column as the hash key.
In multiple table cache groups, the parent cache group table can only use the HASH or DUPLICATE distribution schemes. If you define the parent cache table to use a DUPLICATE distribution scheme, the child table can only use HASH or DUPLICATE.
When you have multiple table cache groups, the parent cache table defaults to using the HASH distribution scheme and all child cache tables default to using the REFERENCE distribution scheme. When you have multiple table cache groups and you set the parent cache table to using the DUPLICATE distribution scheme and do not set the distribution scheme of the child tables, then all child cache tables in this case default to using the HASH distribution scheme.
For child tables, if the foreign key is identical to the parent table primary key, the distribution scheme is changed to the HASH distribution scheme as an optimization.

The following example shows a multiple table cache group where the parent table is distributed using a hash distribution scheme and the child table uses a reference distribution scheme.

Define the customers and accounts tables on the Oracle database as follows:

SQL> CREATE TABLE customers
(
    cust_id            NUMBER(10,0) NOT NULL,
    first_name         VARCHAR2(30) NOT NULL,
    last_name          VARCHAR2(30) NOT NULL,
    addr1              VARCHAR2(64),
    addr2              VARCHAR2(64),
    zipcode            VARCHAR2(5),
    member_since       DATE NOT NULL,
 PRIMARY KEY (cust_id));

Table created.

SQL> CREATE TABLE accounts
(
    account_id         NUMBER(10,0) NOT NULL PRIMARY KEY,
    phone              VARCHAR2(16) NOT NULL,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2,0) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL,
    CONSTRAINT fk_customer
        FOREIGN KEY (cust_id)
            REFERENCES customers(cust_id)); 
 
Table created.

The following defines a multiple table cache group with the oratt.customer parent table and the accounts child table.

Connect> CREATE READONLY CACHE GROUP customer_orders
FROM oratt.customer
 ( cust_id            NUMBER(10,0) NOT NULL,
   first_name         VARCHAR2(30) NOT NULL,
   last_name          VARCHAR2(30) NOT NULL,
   addr1              VARCHAR2(64),
   addr2              VARCHAR2(64),
   zipcode            VARCHAR2(5),
   member_since       DATE NOT NULL,
  PRIMARY KEY(cust_id))
 DISTRIBUTE BY HASH
  WHERE (oratt.customer.cust_id < 100),
oratt.accounts
   (account_id         NUMBER(10,0) NOT NULL,
    phone              VARCHAR2(16) NOT NULL,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2,0) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL,
  PRIMARY KEY(account_id),
  FOREIGN KEY(cust_id) REFERENCES oratt.customer(cust_id))
 DISTRIBUTE BY REFERENCE;

Once created, you cannot alter the distribution scheme of any cache table. Instead, you must drop and recreate the cache group to change the distribution scheme of a cache table.

See Defining table distribution schemes for details on distribution schemes in TimesTen Scaleout. See "CREATE CACHE GROUP" in the Oracle TimesTen In-Memory Database SQL Reference for full syntax.

Creating an index on a cache table

You should pause autorefresh on your cache group before creating an index on a cache table within the cache group.

This eliminates any potential contention for resources and avoids a lock condition.

See "Managing the autorefresh state" for details on how to change the autorefresh state for your cache group.

Performing operations on the read-only cache group

This section shows how to manually load or automatically refresh the read-only cache group with committed changes on the cached Oracle database table.

Automatically refresh updates on the cached Oracle database table

You specify incremental autorefresh with the AUTOREFRESH INTERVAL cache group attribute when creating a read-only cache group using a CREATE CACHE GROUP statement. By default, autorefresh is defined on read-only cache groups.

This example shows an autorefresh interval of 5 seconds defined for the readcache cache group. The default mode is INCREMENTAL and the default state is PAUSED.

Command> CREATE READONLY CACHE GROUP readcache
        AUTOREFRESH INTERVAL 5 SECONDS
        FROM oratt.readtab
        (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));

Autorefresh considerations:

Autorefresh state: Since the autorefresh state is set to PAUSED by default, you can perform a manual load as the initial load of the cache group. This is the recommended method. See "Manually load the cache group" for details.

However, if you set the autorefresh state to ON when creating the cache group or anytime after cache group creation but before a manual load, then a full autorefresh is automatically requested to perform the initial load of the cache group. There can be risks of performing a full autorefresh for large cache groups. See "Disabling full autorefresh" for more information.
Autorefresh mode and interval: With AUTOREFRESH MODE INCREMENTAL INTERVAL defined, committed changes on cached Oracle database tables are automatically refreshed to the TimesTen cache tables based on the autorefresh interval of the cache group. Incremental autorefresh uses Oracle database objects to track committed changes on cached Oracle database tables. Transactional consistency is maintained for cache groups belonging to the same autorefresh interval.

The autorefresh interval determines how often autorefresh operations occur in minutes, seconds or milliseconds. You can only set the autorefresh interval during cache group creation. Cache groups with the same autorefresh interval are refreshed within the same transaction and are managed by a single cache agent. You can improve the performance of your cache groups by placing them in separate autorefresh intervals, which achieves parallelism.

You can specify continuous autorefresh with an autorefresh interval of 0 milliseconds. With continuous autorefresh, the next autorefresh cycle is scheduled as soon as possible after the last autorefresh cycle has ended.

The following are the default settings of the autorefresh attributes:

The autorefresh mode is incremental.
The autorefresh interval is 5 minutes.
The autorefresh state is PAUSED.

See Managing the autorefresh state for how to change the autorefresh interval and state.

The following example demonstrates how changes to the Oracle database cache tables are automatically refreshed to the cache groups on TimesTen.

Use SQL*Plus, as the Oracle database schema user, to insert a new row, delete an existing row, and update an existing row in the Oracle Database readtab table, and commit the changes:

SQL> INSERT INTO readtab VALUES (3, 'Welcome');
1 row created.
SQL> DELETE FROM readtab WHERE keyval=2;
1 row deleted.
SQL> UPDATE readtab SET str='Hi' WHERE keyval=1;
1 row updated.
SQL> COMMIT;
Commit complete.

Since the read-only cache group was created specifying incremental autorefresh with an interval of 5 seconds, the oratt.readtab cache table in the readcache cache group is automatically refreshed after 5 seconds with the committed changes on the cached Oracle database oratt.readtab table as shown in Figure 12-4.

Figure 12-4 Automatically refresh the TimesTen cache table with Oracle Database updates

Description of "Figure 12-4 Automatically refresh the TimesTen cache table with Oracle Database updates"

As the cache manager user, use the ttIsql utility to query the contents of the oratt.readtab cache table after the readcache cache group has been automatically refreshed with the committed changes on the cached Oracle database table:

Command> SELECT * FROM oratt.readtab;
< 1, Hi >
< 3, Welcome >
2 rows found.

See "Automatically refreshing a cache group" in the Oracle TimesTen In-Memory Database Cache Guide for more information about automatically refreshing cache groups.

Managing the autorefresh state

The autorefresh state can be set to ON, OFF, or PAUSED.

ON: Autorefresh operations are scheduled by TimesTen when the cache group's autorefresh state is ON.
OFF: When the cache group's autorefresh state is OFF, committed changes on the cached Oracle database tables are not tracked.
PAUSED: When the cache group's autorefresh state is PAUSED, committed changes on the cached Oracle database tables are tracked in the Oracle database, but are not automatically refreshed to the TimesTen cache tables until the state is changed to ON.

You can set the autorefresh state when creating or altering the cache group. The following example modifies the autorefresh state with the ALTER CACHE GROUP statement.

Command> ALTER CACHE GROUP readcache SET AUTOREFRESH STATE PAUSED;
Command> cachegroups;
 
Cache Group CACHEUSER.READCACHE:
 
  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined
 
  Root Table: ORATT.READTAB
  Table Type: Read Only
 
1 cache group found.

Command> ALTER CACHE GROUP readcache SET AUTOREFRESH STATE ON;
Command> cachegroups;
 
Cache Group CACHEUSER.READCACHE:
 
  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined
 
  Root Table: ORATT.READTAB
  Table Type: Read Only
 
1 cache group found.

See "Automatically refreshing a cache group" in the Oracle TimesTen In-Memory Database Cache Guide for more information about automatically refreshing cache groups.

Disabling full autorefresh

There can be risks of performing a full autorefresh for large cache groups as loading large amounts of data can overwhelm either temporary space or the cache administration user tablespace.

TimesTen requests a full autorefresh:

If you set the autorefresh state to ON when creating the cache group or anytime after cache group creation but before a manual load, the first load of the cache group is a full autorefresh.
TimesTen automatically performs a full autorefresh when recovery is requested.

However, if performance is a concern, you can disallow full autorefresh requests for all cache groups defined with incremental autorefresh by setting the DisableFullAutorefresh cache configuration parameter to 1. If you do so, both the initial load and any recovery requires a manual load.

Command> call ttCacheConfig('DisableFullAutorefresh',,,'1');
< DisableFullAutorefresh, <NULL>, <NULL>, 1 >
1 row found.

See "Disabling full autorefresh for cache groups" in the Oracle TimesTen In-Memory Database Cache Guide for more information.

Manually load the cache group

As the cache manager user, you perform the initial load of the cache group with the LOAD CACHE GROUP statement. The LOAD CACHE GROUP statement only loads committed inserts on the cached Oracle database tables into the TimesTen cache tables. New cache instances are loaded into the cache tables, but cache instances that already exist in the cache tables are not updated or deleted even if the corresponding rows in the cached Oracle database tables have been updated or deleted. A manual load operation is primarily used to initially populate a cache group.

A manual load on a read-only cache group with autorefresh can only occur when the autorefresh state is PAUSED. Once the manual load completes, the autorefresh state automatically changes from PAUSED to ON. After which, incremental autorefresh starts.

If you know that there is a large amount of data to load, you can portion the data by specifying the COMMIT EVERY n ROWS clause and request parallel loading across several threads by specifying the PARALLEL clause.

The following example loads the contents of the Oracle database oratt.readtab table into the TimesTen oratt.readtab cache table in the readcache cache group. The example commits every 256 rows and specifies 3 threads to run concurrently.

Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS PARALLEL 3;
2 cache instances affected.

Figure 12-5 shows that the Oracle Database data is loaded into the oratt.readtab cache table.

Figure 12-5 Loading a read-only cache group

Description of "Figure 12-5 Loading a read-only cache group"

Grant the SELECT privilege on the oratt.readtab cache table to the cache manager user so that this user can issue a SELECT query on this table.

Command> GRANT SELECT ON oratt.readtab TO cacheuser;

Query the contents of oratt.readtab cache table.

Command> SELECT * FROM oratt.readtab;
< 1, Hello >
< 2, World >
2 rows found.

See "Loading and refreshing a cache group" in the Oracle TimesTen In-Memory Database Cache Guide for more information about manually loading a cache group.

Manually refresh the read-only cache group

You can also manually refresh the read-only cache group using the REFRESH CACHE GROUP SQL statement. REFRESH CACHE GROUP replaces cache instances in the TimesTen cache tables with the most current data from the cached Oracle database tables including cache instances that are already exist in the cache tables. A refresh operation is primarily used to update the contents of a cache group with committed changes on the cached Oracle database tables after the cache group has been initially populated.

For a static cache group, a refresh operation is equivalent to issuing an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement on the cache group. In effect, all committed inserts, updates and deletes on the cached Oracle database tables are refreshed into the cache tables. New cache instances may be loaded into the cache tables. Cache instances that already exist in the cache tables are updated or deleted if the corresponding rows in the cached Oracle database tables have been updated or deleted.

The following example refreshes cache instances in the TimesTen cache tables within the readcache cache group from the cached Oracle database tables:

Command> REFRESH CACHE GROUP readcache COMMIT EVERY 256 ROWS;
2 cache instances affected.

See "Loading and refreshing a cache group" in the Oracle TimesTen In-Memory Database Cache Guide for more information about manually loading a cache group. See Unloading the cache group for more information about the UNLOAD CACHE GROUP statement.

Unloading the cache group

You can delete some or all cache instances from the cache tables in a cache group with the UNLOAD CACHE GROUP statement. Unlike the DROP CACHE GROUP statement, the cache tables themselves are not dropped when a cache group is unloaded.

The following example unloads all cache instances from all cache tables in the readcache cache group. A commit frequency is specified, so the operations is performed over several transactions by committing every 256 rows:

Command> UNLOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
2 cache instances affected.

See "Unloading a cache group" in the Oracle TimesTen In-Memory Database Cache Guide for more information.

Managing the TimesTen Cache environment

The following sections describe how to manage the TimesTen Cache environment within TimesTen Scaleout:

Monitoring the status of the cache agent processes

You can use the ttGridAdmin dbStatus -all command to check which of the TimesTen cache agent processes are running.

The following example shows that one of the cache agents of the database1 database is stopped.

% ttGridAdmin dbStatus database1 -all

Database database1 summary status as of Mon Dec  7 14:54:57 PST 2020
created,loaded-complete,open
 
Completely created elements: 6 (of 6)
Completely loaded elements: 6 (of 6)
Completely created replica sets: 3 (of 3)
Completely loaded replica sets: 3 (of 3)

Cache agents running: 5 (of 6)
Open elements: 6 (of 6)

Then, you can use the ttGridAdmin dbStatus -element command to verify which cache agents are running on each data instance of the database. All data instances must have access to the Oracle database.

The following example shows that a cache agent is not running on the host4 instance of the database1 database.

% ttGridadmin dbStatus database1 -element
 
Database database1 element level status as of Mon Dec  7 14:52:51 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened started   2020-11-23 08:37:35         
host4 instance1    2 opened stopped   2020-11-23 08:37:35         
host5 instance1    3 opened started   2020-11-23 08:37:35         
host6 instance1    4 opened started   2020-11-23 08:37:35         
host7 instance1    5 opened started   2020-11-23 08:37:35         
host8 instance1    6 opened started   2020-11-23 08:37:35

You can restart the cache agent for the host4.instance1 instance as follows:

% ttGridAdmin dbCacheStart database1 -instance host4.instance1]
Database database1 : Starting cache agents. 0 cache agents started.

For more information on the ttGridAdmin dbStatus and ttGridAdmin dbCachestart commands, see "Monitor the status of a database (dbStatus)" and "Start a cache agent (dbCacheStart)", respectively, in the Oracle TimesTen In-Memory Database Reference.

Displaying information about cache groups

You can display information about cache groups in a TimesTen database using the ttIsql utility cachegroups command.

% ttIsql "DSN=database1;UID=cacheuser;PWD=ttpwd;OraclePWD=orapwd"
Command> cachegroups;

Cache Group CACHEUSER.READCACHE:
 
  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined
 
  Root Table: ORATT.READTAB
  Table Type: Read Only
 
1 cache group found.

For more information about the ttIsql utility cachegroups command, see "ttIsql" in Oracle TimesTen In-Memory Database Reference.

Changing TimesTen Cache user names and passwords

You can change any of the user names or passwords for the TimesTen cache manager user, its companion Oracle user, or the cache administration user. See "Changing cache user names and passwords" in the Oracle TimesTen In-Memory Database Cache Guide for more information.

Changing the Oracle database schema

If you need to make changes to the Oracle database schema, you must drop the affected cache groups and stop all cache agents before you modify the Oracle database schema. See "Impact on cache groups when modifying the Oracle database schema" in the Oracle TimesTen In-Memory Database Cache Guide for more information.

Monitoring autorefresh operations on cache groups

The support log contains messages that show the progress of autorefresh. The support log shows when autorefresh starts, the autorefresh interval, any message number (if applicable), number of rows updated, and if the autorefresh completes successfully.

See "Understanding messages about autorefresh in the support log" in Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide for more details.

Managing the change log tables and triggers in the Oracle database

For a cache group with autorefresh, TimesTen creates a change log table and trigger in the Oracle database for each cache table in the cache group. The trigger is fired for each committed insert, update, or delete operation on the cached Oracle database table. The trigger records the primary key of the updated rows in the change log table. The cache agent periodically scans the change log table for updated keys and then joins this table with the cached Oracle database table to get a snapshot of the latest updates. See "Managing a cache environment with Oracle Database objects" in the Oracle TimesTen In-Memory Database Cache Guide for more information on the change log tables and triggers.

The following sections describe how to gather information from the change log table and how to remove the change log tables and triggers when necessary:

Gathering information from the change log table

TimesTen provides the cacheInfo SQL script that gathers information from the change log table that exists on the Oracle database for autorefresh cache groups.

The following example is run within SQL*Plus on the Oracle database:

SQL> @$TIMESTEN_HOME/install/oraclescripts/cacheInfo.sql
***************** Database Information	*********************
Database name: DATABASE1
Unique database name: database1
Primary database name:
Database Role: PRIMARY
Database Open Mode: READ WRITE
Database Protection Mode: MAXIMUM PERFORMANCE
Database Protection Level: UNPROTECTED
Database Flashback On: NO
Database Current SCN: 21512609
*************************************************************
*************Autorefresh Objects Information  ***************
Grid name: grid1 (7D03C680-BD93-4233-A4CF-B0EDB0064F3F)
Timesten database name: database1
Cache table name: ORATT.READTAB
Change log table name: tt_06_96977_L
Number of rows in change log table: 4
Maximum logseq on the change log table: 1
Timesten has autorefreshed updates upto logseq: 1
Number of updates waiting to be autorefreshed: 0
Number of updates that has not been marked with a valid logseq: 0
****************************
*************No DDL Tracking objects are found*************
 
PL/SQL procedure successfully completed.

See "Displaying information from the change log tables on the Oracle database" in Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide for more details on the cacheInfo SQL script.

Dropping Oracle database objects used for caching

If a TimesTen database that contains cache groups with autorefresh becomes unavailable, Oracle database objects such as change log tables and triggers used to implement autorefresh operations continue to exist in the Oracle database. Oracle database objects used to implement autorefresh operations also continue to exist in the Oracle database when a TimesTen database is no longer being used but still contains cache groups with autorefresh. Rows can continue to accumulate in the change log tables. In this case, you can drop the Oracle database objects used to implement autorefresh operations. See "Dropping Oracle database objects used by cache groups with autorefresh" in the Oracle TimesTen In-Memory Database Cache Guide for more information.

Restoring the TimesTen and Oracle database systems

Complete the following tasks to restore the TimesTen and Oracle database systems to their original state:

Dropping cache groups

Start the ttIsql utility and connect to the database1 DSN as the instance administrator. Use ttIsql to grant the DROP ANY TABLE privilege to the cache manager user so that this user can drop the underlying cache tables when dropping the cache groups.

% ttIsql "DSN=database1"
Command> GRANT DROP ANY TABLE TO cacheuser;
Command> exit

In order to drop the cache group, you should first pause the autorefresh operations to avoid any contention. Start the ttIsql utility and connect to the database1 DSN as the cache manager user.

Use an ALTER CACHE GROUP statement to set the cache group's autorefresh state to PAUSED.
Use DROP CACHE GROUP statement to drop the readcache read-only cache group.

% ttIsql "DSN=database1;UID=cacheuser;PWD=ttpwd;OraclePWD=orapwd"
Command> ALTER CACHE GROUP readcache SET AUTOREFRESH STATE PAUSED;
Command> DROP CACHE GROUP readcache;

The readcache cache group and its cache table oratt.readtab are dropped from the TimesTen database.

See "Dropping a cache group" in the Oracle TimesTen In-Memory Database Cache Guide for more information about dropping cache groups.

Stopping the cache agents for TimesTen Scaleout

On the active management instance, use ttGridAdmin dbCacheStop command to stop the cache agent on all data instances within the database:

% ttGridAdmin dbCacheStop database1
Database database1 : Stopping cache agents. 

Database database1 element level status as of Mon Dec  7 14:52:51 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened stopped   2020-11-23 08:37:35         
host4 instance1    2 opened stopped   2020-11-23 08:37:35         
host5 instance1    3 opened stopped   2020-11-23 08:37:35         
host6 instance1    4 opened stopped   2020-11-23 08:37:35         
host7 instance1    5 opened stopped   2020-11-23 08:37:35         
host8 instance1    6 opened stopped   2020-11-23 08:37:35

You can stop the cache agent for a specific data instance if you specify the -instance option.

% ttGridAdmin dbCacheStop database1 -instance host4.instance1
Database database1 : Stopping cache agents. 

% ttGridadmin dbStatus database1 -element
 
Database database1 element level status as of Mon Dec  7 14:52:51 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened started   2020-11-23 08:37:35         
host4 instance1    2 opened stopped   2020-11-23 08:37:35         
host5 instance1    3 opened started   2020-11-23 08:37:35         
host6 instance1    4 opened started   2020-11-23 08:37:35         
host7 instance1    5 opened started   2020-11-23 08:37:35         
host8 instance1    6 opened started   2020-11-23 08:37:35

If there is only one cache agent running, do not stop the last cache agent immediately after you have dropped or altered a cache group with autorefresh. Instead, wait for at least two minutes to enable the cache agent to clean up Oracle database objects such as change log tables and triggers that were created and used to manage the cache group. This is not an issue when you have more than one cache agent running.

See "Start a cache agent for TimesTen Scaleout" for details how to start the cache agent on all data instances or a single data instance.

Dropping the Oracle database users and their objects

Start SQL*Plus and connect to the Oracle database as the sys user. Use SQL*Plus to drop the schema user oratt and the cache administration user cacheuser.

% sqlplus sys as sysdba
Enter password: password
SQL> DROP USER oratt CASCADE;
User dropped.
SQL> DROP USER cacheuser CASCADE;
User dropped.

Specifying CASCADE in a DROP USER statement drops all objects such as tables and triggers owned by the user before dropping the user itself.

Next use SQL*Plus to drop the TT_CACHE_ADMIN_ROLE role:

SQL> DROP ROLE TT_CACHE_ADMIN_ROLE;
Role dropped.

Then use SQL*Plus to drop the default tablespace cachetblsp used by the cache administration user including the contents of the tablespace and its data file:

SQL> DROP TABLESPACE cachetblsp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> exit

Supported TimesTen Cache Features in TimesTen Scaleout

The following features are supported for TimesTen Cache in TimesTen Scaleout:

Using Passthrough

When an application issues statements on a TimesTen connection, the statement runs in the TimesTen database or passes through to run in the Oracle database. Whether the statement runs in the TimesTen or Oracle database depends on the composition of the statement and the setting of the PassThrough connection attribute. You can set the PassThrough connection attribute to control which statements run locally in TimesTen and which are to be redirected to run in the Oracle database.

See "Setting a passthrough level" in the Oracle TimesTen In-Memory Database Cache Guide for full details.

Using Oracle RAC

Oracle RAC enables multiple Oracle database instances to access one Oracle database with shared resources, including all data files, control files, PFILEs and redo log files that reside on cluster-aware shared disks. Oracle RAC handles read/write consistency and load balancing while providing high availability.

See "Using TimesTen Cache in an Oracle RAC environment" in the Oracle TimesTen In-Memory Database Cache Guide for full details.

Limiting cache agent connections to the Oracle database

TimesTen Cache starts a set number of connections to the Oracle Database for each cache agent for cache management. If you have a large grid, then you may want to manage the number of required cache agent connections.

Every cache agent in TimesTen Scaleout starts 11 active cache management threads with connections to the Oracle database. In addition, 2 more connections are added for each autorefresh interval for each cache agent. For example, if you have cache groups defined with 4 different autorefresh intervals, then (2*4)=8 additional connections for each cache agent to manage autorefresh intervals are created to the Oracle database. Thus, the total number of connections created to the Oracle database when using TimesTen Cache in TimesTen Scaleout is:

total_number_connections = num_cache_agents * (11 + (2 * num_interval))

The following example uses a grid where:

k=3.
A database in that grid is defined with 10 replica sets.
The user specifies cache groups with four autorefresh intervals.

In this example, you would create 30 hosts, which are labeled host01, host02, … host30. If each data instance on these hosts in this example runs a cache agent, then the number of connections to the Oracle database for servicing the cache agents would be:

30 * (11 + (2*4)) = 570

If you have a large grid with one cache agent started for each instance, this creates a scenario where there can be redundant open cache management connections to the Oracle database. Instead, we recommend that for a large grid, you start only a fraction of the cache agents. The minimum number of cache agents that you can start is one cache agent for each autorefresh interval plus one cache agent for each k-factor.

num_cache_agents = (num_interval + k)

Thus, in our previous example where there are 4 autorefresh intervals with a k-factor of 3, then you would start (4+3)=7 cache agents using the ttGridAdmin dbCacheStart -instance command.

Each cache agent has threads connected to the Oracle database to perform cache management tasks, as well as refresher threads performing autorefresh operations. The autorefresh intervals are rebalanced among active cache agents so that each cache agent autorefreshes the mean interval for each cache agent.

In our example, we would start 7 cache agents.

% ttGridAdmin dbCacheStart -instance host01 database1
Database database1 : Starting cache agents. 
% ttGridAdmin dbCacheStart -instance host02 database1
Database database1 : Starting cache agents. 
% ttGridAdmin dbCacheStart -instance host03 database1
Database database1 : Starting cache agents.
% ttGridAdmin dbCacheStart -instance host04 database1
Database database1 : Starting cache agents. 
% ttGridAdmin dbCacheStart -instance host05 database1
Database database1 : Starting cache agents. 
% ttGridAdmin dbCacheStart -instance host06 database1
Database database1 : Starting cache agents. 
% ttGridAdmin dbCacheStart -instance host07 database1
Database database1 : Starting cache agents.

With seven cache agents started, each servicing 4 autorefresh intervals, there are (7 * (11 + (2*4))= 133 connections to the Oracle database just for cache management. Autorefresh operations and cache management tasks are load balanced over seven data instances. Depending on availability requirements, users can choose to limit the number of connections further by reducing the number of autorefresh intervals required in the cache groups.

Since cache agents are known to be on the first seven data instances, all manual cache operations that require a cache agent must be executed on one of these seven data instances. If you need to run a manual cache operation from a data instance that does not have a cache agent running, you can perform the following:

On the active management instance, start the cache agent for the data instance where you want to perform the cache operation (for example host19) by executing the ttGridAdmin dbCacheStart command.
```
% ttGridAdmin dbCacheStart -instance host19 database1
Database database1 : Starting cache agents. 
```
Execute the cache operation on the data instance on host19.
Once the manual cache operation is completed, stop the cache agent for that data instance by executing the ttGridAdmin dbCacheStop command on the active management instance.
```
% ttGridAdmin dbCacheStop -instance host19 database1
Database database1 : Stopping cache agents. 
```

Compatibility issues between the TimesTen and Oracle databases

There are some compatibility issues between the TimesTen and Oracle databases. For example:

TimesTen and Oracle database metadata are stored differently.
TimesTen and Oracle databases have different transaction isolation models.
TimesTen and Oracle databases have different connection and statement properties.
Sequences are not cached and synchronized between the TimesTen database and the corresponding Oracle database.
Side effects of Oracle database triggers and stored procedures are not reflected in the TimesTen database until after an automatic or manual refresh operation.

See "Compatibility between TimesTen and Oracle databases" in the Oracle TimesTen In-Memory Database Cache Guide for full details.

Restrictions for TimesTen Cache on TimesTen Scaleout

Full autorefresh mode is not supported for cache groups. However, TimesTen Scaleout may still initiate a full autorefresh as the initial load of a cache group or to recover from certain error conditions.
Aging is not supported.
Materialized views on cache group tables are not supported.
Global indexes on cache group tables are not supported.
LOAD CACHE GROUP WITH ID, REFRESH CACHE GROUP WITH ID, and UNLOAD CACHE GROUP WITH ID SQL statements are not supported.
Data Guard is not supported for TimesTen Cache in TimesTen Scaleout.
Disaster Recovery is not supported for TimesTen Cache in TimesTen Scaleout.