This chapter illustrates the creation and use of cache groups by demonstrating how to create an explicitly loaded read-only local cache group and a dynamic updatable cache group. In addition, this chapter describes how to populate cache tables, and how to observe the transfer of updates between the cache tables in the TimesTen database and the cached tables in the Oracle database.
Performing operations on a dynamically updatable cache group
Cleaning up the TimesTen Classic and Oracle Database systems
Before you can create a cache group, you must first install TimesTen Classic and then configure the Oracle Database and TimesTen Classic systems. See Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide for information about installing TimesTen Classic.
Note:
It is best to have the TimesTen and Oracle databases on different 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.TimesTen Cache must know which Oracle database to connect to, which credentials to use when connecting to the Oracle database and which users own the tables in both TimesTen and Oracle databases.
Before you can use TimesTen Cache, you must create the following users on the Oracle Database:
A user timesten
owns Oracle Database tables that store information about the cache environment.
One or more schema users 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.
Start SQL*Plus on the Oracle Database system from an operating system shell or command prompt, and connect to the Oracle database instance as the sys
user:
% cd timesten_home/install/oraclescripts % sqlplus sys as sysdba Enter password: password
Use SQL*Plus to create a default tablespace to be used for storing TimesTen Cache management objects that should not be shared with other applications. While you may also store Oracle base tables that are cached in a TimesTen database, we strongly recommend that this tablespace be used solely by TimesTen Classic for cache management.
In the following example, the name of the default tablespace is cachetblsp
:
SQL> CREATE TABLESPACE cachetblsp;
Next, 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 oracle; SQL> GRANT CREATE SESSION, RESOURCE TO oratt;
Then use SQL*Plus to perform the following operations:
Create a cache administration user.
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 thegrantCacheAdminPrivileges.sql
script for the required privileges by the user who executes this script and the privileges that this user grants to the cache administration user.SQL> CREATE USER cacheuser IDENTIFIED BY oracle DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp; SQL> @grantCacheAdminPrivileges "cacheuser" SQL> exit
The privileges that the cache administration user requires depend on the types of cache groups you create and the operations that you perform on the cache groups.
See "Create the Oracle database users" for more information about the timesten
user, the schema users, and the cache administration user.
In the following data source name (DSN) examples, the net service name of the Oracle database instance is oracledb
and its database character set is AL32UTF8
. The TimesTen database character set 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';
On UNIX or Linux, in the .odbc.ini
file that resides in your home directory or the timesten_home
/conf/sys.odbc.ini
file, create a TimesTen DSN cache1
and set the following connection attributes:
[cache1] DataStore=/users/OracleCache/ttcache PermSize=64 OracleNetServiceName=oracledb DatabaseCharacterSet=AL32UTF8
On Windows, create a TimesTen user DSN or system DSN cache1
and set the following connection attributes:
Data Store Path + Name: c:\temp\ttcache
Permanent Data Size: 64
Oracle Net Service Name: oracledb
Database Character Set: AL32UTF8
Use the default settings for all the other connection attributes.
See "Define a DSN for the TimesTen database" for more information about defining a DSN for a TimesTen database that caches data from an Oracle database.
See "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide for more information about TimesTen DSNs.
Note:
The term "data store" is used interchangeably with "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" for more details on the cache manager user and its companion Oracle Database user.One or more cache table users own the cache tables. You must create a TimesTen cache table 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 table 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.
Start the ttIsql
utility on the TimesTen Classic system from an operating system shell or command prompt as the instance administrator, and connect to the cache1
DSN to create the TimesTen database that is to be used to cache data from an Oracle database:
% ttIsql cache1
Use ttIsql
to create a cache manager user. 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:
Command> CREATE USER cacheuser IDENTIFIED BY timesten; Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;
Then, use ttIsql
to create a cache table user. In the following example, the cache table 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 timesten; Command> exit
The privileges that the cache manager user requires depend on the types of cache groups you create and the operations that you perform on the cache groups. See "Create the TimesTen users" for more information about the cache manager user and the cache table users.
See "Authentication in TimesTen" and "Authorization in TimesTen" in Oracle TimesTen In-Memory Database Security Guide for more information about TimesTen users and privileges.
Start the ttIsql
utility and connect to the cache1
DSN as the cache manager user. In the connection string, specify the cache manager user name in the UID
connection attribute. Specify the cache manager user's password in the PWD
connection attribute. Specify the password of its companion Oracle user (created with the same name to be the companion user to the cache manager) in the OraclePWD
connection attribute within the connection string. In this example, the cache administration user is the companion user to the cache manager user and so its password is provided.
% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Use ttIsql
to call the ttCacheUidPwdSet
built-in procedure to set the cache administration user name and password:
Command> call ttCacheUidPwdSet('cacheuser','oracle');
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" for information on how to use this setting by the TimesTen database.
This section creates a read-only cache group (as shown in Figure 2-1) and an Asynchronous WriteThrough (AWT) cache group (as shown in Figure 2-2).
Figure 2-1 Single-table read-only cache group
Figure 2-2 Single-table WriteThrough cache group
Complete the following tasks to create a read-only cache group and an AWT cache group:
Start SQL*Plus and connect to the Oracle database as the schema user:
% sqlplus oratt/oracle
Use SQL*Plus to create a table readtab
as shown in Figure 2-3, and a table writetab
as shown in Figure 2-4:
SQL> CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32)); SQL> CREATE TABLE writetab (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));
Figure 2-3 Creating an Oracle Database table to be cached in a read-only cache group
Figure 2-4 Creating an Oracle Database table to be cached in an AWT cache group
Then use SQL*Plus to insert some rows into the readtab
and writetab
tables, and commit the changes:
SQL> INSERT INTO readtab VALUES (1, 'Hello'); SQL> INSERT INTO readtab VALUES (2, 'World'); SQL> INSERT INTO writetab VALUES (100, 'TimesTen'); SQL> INSERT INTO writetab VALUES (101, 'CACHE'); SQL> COMMIT;
Next use SQL*Plus to grant the SELECT
privilege on the readtab
table, and the SELECT
, INSERT
, UPDATE
and DELETE
privileges on the writetab
table to the cache administration user:
SQL> GRANT SELECT ON readtab TO cacheuser; SQL> GRANT SELECT ON writetab TO cacheuser; SQL> GRANT INSERT ON writetab TO cacheuser; SQL> GRANT UPDATE ON writetab TO cacheuser; SQL> GRANT DELETE ON writetab TO cacheuser;
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.
The SELECT
privilege on the writetab
table is required to create an AWT cache group that caches this table. The INSERT
, UPDATE
, and DELETE
privileges on the writetab
table are required to perform write through operations from the TimesTen cache table to the cached Oracle Database table.
See "Grant privileges to the Oracle database users" for more information about the privileges required for the cache administration user to create and perform operations on a read-only cache group and an AWT cache group.
As the cache manager user, use the ttIsql
utility to call the ttCacheStart
built-in procedure to start the cache agent on the TimesTen database:
Command> call ttCacheStart;
See "Managing the cache agent" for more information about starting the cache agent.
As the cache manager user, use the ttIsql
utility to create a read-only cache group readcache
that caches the Oracle Database oratt.readtab
table and a dynamic AWT cache group writecache
that caches the Oracle Database oratt.writetab
table:
Command> CREATE READONLY CACHE GROUP readcache AUTOREFRESH INTERVAL 5 SECONDS FROM oratt.readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32)); Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP writecache FROM oratt.writetab (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));
The cache groups readcache
and writecache
, and their respective cache tables oratt.readtab
and oratt.writetab
, whose owners and names are identical to the cached Oracle Database tables, are created in the TimesTen database. Figure 2-5 shows that the writecache
cache group caches the oratt.writetab
table.
Figure 2-5 Creating an Asynchronous WriteThrough cache group
Use the ttIsql
cachegroups
command to view the definition of the readcache
and writecache
cache groups:
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 Cache Group CACHEUSER.WRITECACHE: Cache Group Type: Asynchronous Writethrough (Dynamic) Autorefresh: No Aging: LRU on Root Table: ORATT.WRITETAB Table Type: Propagate 2 cache groups found.
See "Read-only cache group" for more information about read-only cache groups.
See "Asynchronous WriteThrough (AWT) cache group" for more information about AWT cache groups.
See "Dynamic cache groups" for more information about dynamic cache groups.
As the cache manager user, use the ttIsql
utility to call the ttRepStart
built-in procedure to start the replication agent on the TimesTen database:
Command> call ttRepStart;
The replication agent propagates committed updates on TimesTen cache tables in AWT cache groups to the cached Oracle Database tables.
See "Managing the replication agent" for more information about starting the replication agent.
This section shows how to manually load the read-only cache group. Then it shows the TimesTen cache table being automatically refreshed with committed updates on the cached Oracle Database table.
Complete the following tasks to perform operations on the read-only cache group:
As the cache manager user, use the ttIsql
utility to load the contents of the Oracle Database oratt.readtab
table into the TimesTen oratt.readtab
cache table in the readcache
cache group:
Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS; 2 cache instances affected. Command> exit
Figure 2-6 shows that the Oracle Database data is loaded into the oratt.readtab
cache table.
Figure 2-6 Loading a read-only cache group
Start the ttIsql
utility and connect to the cache1
DSN as the instance administrator. Use ttIsql
to 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.
% ttIsql cache1 Command> GRANT SELECT ON oratt.readtab TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cache1
DSN as the cache manager user, including the cache manager user password and the password of its companion Oracle user. Use ttIsql
to query the contents of oratt.readtab
cache table.
% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> SELECT * FROM oratt.readtab; < 1, Hello > < 2, World > 2 rows found.
See "Loading and refreshing a cache group" for more information about manually loading a cache group.
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'); SQL> DELETE FROM readtab WHERE keyval=2; SQL> UPDATE readtab SET str='Hi' WHERE keyval=1; SQL> COMMIT;
Since the read-only cache group was created specifying 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 updates on the cached Oracle Database oratt.readtab
table as shown in Figure 2-7.
Figure 2-7 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 updates on the cached Oracle Database table:
Command> SELECT * FROM oratt.readtab; < 1, Hi > < 3, Welcome > 2 rows found. Command> exit
See "AUTOREFRESH cache group attribute" for more information about automatically refreshing cache groups.
This section shows how to dynamically load an AWT cache group. Then it shows committed updates on the TimesTen cache table being automatically propagated to the cached Oracle Database table.
Complete the following tasks to perform operations on the AWT cache group:
Start the ttIsql
utility and connect to the cache1
DSN as the instance administrator. Use ttIsql
to grant the SELECT
privilege on the oratt.writetab
cache table to the cache manager user so that this user can issue a dynamic load SELECT
statement on this table.
% ttIsql cache1 Command> GRANT SELECT ON oratt.writetab TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cache1
DSN as the cache manager user, including the cache manager user password and the password of its companion Oracle user. Use ttIsql
to load a cache instance on demand from the Oracle Database oratt.writetab
table to the TimesTen oratt.writetab
cache table in the writecache
cache group.
% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> SELECT * FROM oratt.writetab WHERE pk=100; < 100, TimesTen > 1 row found. Command> exit
In a dynamic cache group, a cache instance can be loaded into its cache tables on demand with a dynamic load statement. A SELECT
, UPDATE
, DELETE
or INSERT
statement issued on a TimesTen cache table that uniquely identifies a cache instance results in the cache instance being automatically loaded from the cached Oracle Database table if the data is not found in the cache table. A dynamically loaded cache instance consists of a single row in the root table of the cache group, and all the related rows in the child tables.
See "Dynamically loading a cache instance" for more information about a dynamic load operation.
Data can also be manually loaded into the cache tables of a dynamic cache group using a LOAD CACHE GROUP
statement.
Start the ttIsql
utility and connect to the cache1
DSN as the instance administrator. Use ttIsql
to grant the INSERT
, DELETE
, and UPDATE
privileges on the oratt.writetab
cache table to the cache manager user so that this user can perform updates on this table.
% ttIsql cache1 Command> GRANT INSERT ON oratt.writetab TO cacheuser; Command> GRANT DELETE ON oratt.writetab TO cacheuser; Command> GRANT UPDATE ON oratt.writetab TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cache1
DSN as the cache manager user. Use ttIsql
to insert a new row, delete an existing row, and update an existing row in the oratt.writetab
cache table, and commit the changes.
% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> INSERT INTO oratt.writetab VALUES (102, 'Cache'); Command> DELETE FROM oratt.writetab WHERE pk=101; Command> UPDATE oratt.writetab SET attr='Oracle' WHERE pk=100; Command> COMMIT; Command> exit
The committed updates on the oratt.writetab
cache table in the writecache
cache group are automatically propagated to the Oracle Database oratt.writetab
table as shown in Figure 2-8.
Figure 2-8 Automatically propagate TimesTen cache table updates to Oracle Database
As the Oracle Database schema user, use SQL*Plus to query the contents of the writetab
table:
SQL> SELECT * FROM writetab; PK ATTR ---------- ------------------------------- 100 Oracle 102 Cache SQL> exit
Complete the following tasks to restore the TimesTen Classic and Oracle Database systems to their original state before creating cache groups:
As the cache manager user, use the ttIsql
utility to call the ttRepStop
built-in procedure to stop the replication agent on the TimesTen database:
Command> call ttRepStop; Command> exit
See "Managing the replication agent" for more information about stopping the replication agent.
Start the ttIsql
utility and connect to the cache1
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 cache1 Command> GRANT DROP ANY TABLE TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cache1
DSN as the cache manager user. Use ttIsql
to drop the readcache
read-only cache group and the writecache
AWT cache group.
% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> DROP CACHE GROUP readcache; Command> DROP CACHE GROUP writecache;
The cache groups readcache
and writecache
, and their respective cache tables oratt.readtab
and oratt.writetab
, are dropped from the TimesTen database.
See "Dropping a cache group" for more information about dropping cache groups.
As the cache manager user, use the ttIsql
utility to call the ttCacheStop
built-in procedure to stop the cache agent on the TimesTen database:
Command> call ttCacheStop; Command> exit
See "Managing the cache agent" for more information about stopping the cache agent.
Then use the ttDestroy
utility to connect to the cache1
DSN and destroy the TimesTen database:
% ttDestroy cache1
Start SQL*Plus and connect to the Oracle database as the sys
user. Use SQL*Plus to drop the timesten
user, the schema user oratt
, and the cache administration user cacheuser
.
% sqlplus sys as sysdba
Enter password: password
SQL> DROP USER timesten CASCADE;
SQL> DROP USER oratt CASCADE;
SQL> DROP USER cacheuser CASCADE;
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;
Then use SQL*Plus to drop the default tablespace cachetblsp
used by the timesten
user and cache administration user including the contents of the tablespace and its data file:
SQL> DROP TABLESPACE cachetblsp INCLUDING CONTENTS AND DATAFILES; SQL> exit