2 Getting Started

Create users in the Oracle database

Before you can use Oracle In-Memory Database Cache, you must create some Oracle users:

• A user timesten owns Oracle tables that store information about cache grids.

• One or more schema users own the Oracle tables to be cached in a TimesTen database. These may be existing users or new users.

• A cache administration user creates and maintains Oracle objects that store information used to manage cache grids and enforce predefined behaviors of particular cache group types.

Start SQL*Plus on the Oracle system from an operating system shell or command prompt, and connect to the Oracle database instance as the sys user:

% cd TimesTen_install_dir/oraclescripts
% sqlplus sys as sysdba
Enter password: password

Use SQL*Plus to create a default tablespace that will be used by both the timesten user and the cache administration user. This tablespace should only be used to store objects for Oracle In-Memory Database Cache and should not be shared with other applications. Then run the SQL*Plus script TimesTen_install_dir/oraclescripts/initCacheGlobalSchema.sql to create the following elements:

• The timesten user

• The Oracle tables owned by the timesten user to store information about cache grids

• The TT_CACHE_ADMIN_ROLE role that defines privileges on these Oracle tables

Pass the default tablespace as an argument to the initCacheGlobalSchema.sql script. In the following example, the name of the default tablespace is cachetblsp:

SQL> CREATE TABLESPACE cachetblsp DATAFILE 'datfttuser.dbf' SIZE 100M;
SQL> @initCacheGlobalSchema "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_install_dir/oraclescripts/grantCacheAdminPrivileges.sql to grant the cache administration user the minimum set of privileges required to perform cache grid and cache group operations.

Pass the cache administration user name as an argument to the grantCacheAdminPrivileges.sql script. In the following example, the cache administration user name is cacheuser and the name of its default tablespace is cachetblsp:

SQL> CREATE USER cacheuser IDENTIFIED BY oracle
  2  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 will create and the operations that you will perform on the cache groups.

See "Create the Oracle users" for more information about the timesten user, the schema users, and the cache administration user.

Create a DSN for the TimesTen database

value FROM nls_database_parameters parameter='NLS_CHARACTERSET';

On UNIX, in the .odbc.ini file that resides in your home directory or the TimesTen_install_dir/info/sys.odbc.ini file, create a TimesTen DSN cachealone1 and set the following attributes:

[cachealone1]
DataStore=/users/OracleCache/alone1
PermSize=64
OracleNetServiceName=oracledb
DatabaseCharacterSet=AL32UTF8

On Windows, create a TimesTen user DSN or system DSN cachealone1 and set the following attributes:

• Data Store Path + Name: c:\temp\alone1

• Permanent Data Size: 64

• Oracle Net Service Name: oracledb

• Database Character Set: AL32UTF8

Use the default settings for all the other DSN attributes.

See "Define a DSN for the TimesTen database" for more information about defining a DSN for a TimesTen database that is used to cache data from an Oracle database.

See "Creating TimesTen Data Stores" in Oracle TimesTen In-Memory Database Operations Guide for more information about TimesTen DSNs.

Create users in the TimesTen database

In addition to the Oracle users, you must create some TimesTen users before you can use Oracle In-Memory Database Cache:

• A cache manager user performs cache grid and cache group operations. The TimesTen cache manager user must have the same name as an Oracle user that can access the cached Oracle tables. For example, the Oracle user must have privileges to select from and update the cached Oracle tables. The Oracle user can be the cache administration user, a schema user, or some other existing user. The password of the cache manager user can be different than the password of the Oracle user with the same name.

  The cache manager user will create and configure the cache grid, and create the cache groups. It may perform operations such as loading or refreshing a cache group although these operations can be performed by any TimesTen user that has sufficient privileges. The cache manager user can also monitor various aspects of the caching environment, such as the grid itself or asynchronous operations that are performed on cache groups such as automatic refresh.

• 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 schema user for each schema user who owns or will own Oracle tables to be cached in the TimesTen database. The password of a cache table user can be different than the password of the Oracle 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 table.

Start the ttIsql utility on the TimesTen system from an operating system shell or command prompt as the instance administrator, and connect to the cachealone1 DSN to create the TimesTen database that will be used to cache data from an Oracle database:

% ttIsql cachealone1

Use ttIsql to create a cache manager user. Grant this user the minimum set of privileges required to create a cache grid and cache groups, and perform operations on the cache groups. In the following example, the cache manager user name is cacheuser, which is the same name as the Oracle 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 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 will create and the operations that you will perform on the cache groups. See "Create the TimesTen users" for more information about the cache manager user and the cache table users.

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

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

Start the ttIsql utility and connect to the cachealone1 DSN as the cache manager user. In the connection string, specify the cache manager user name in the UID attribute. (In this example, the TimesTen cache manager user name is the same as the Oracle cache administration user name.) Specify the cache manager user's password in the PWD attribute and the cache administration user's password in the OraclePWD attribute within the connection string.

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"

Use ttIsql to call the ttCacheUidPwdSet built-in procedure to set the Oracle 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 about how this setting is used by the TimesTen database.

Create the Oracle tables to be cached

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));

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, 'IMDB');
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 automatic refresh operations from the cached Oracle 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 writethrough operations from the TimesTen cache table to the cached Oracle table.

See "Grant privileges to the Oracle 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.

Start the cache agent

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.

Create the cache groups

As the cache manager user, use the ttIsql utility to create a read-only cache group readcache that caches the Oracle oratt.readtab table as shown in Figure 2-5 and a dynamic AWT global cache group writecache that caches the Oracle oratt.writetab table as shown in Figure 2-6:

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 GLOBAL 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 tables, are created in the TimesTen database.

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 global (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.

See "Global cache groups" for more information about global cache groups.

Start the replication agent for the AWT cache group

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 tables.

See "Managing the replication agent" for more information about starting the replication agent.

Manually load the cache group

As the cache manager user, use the ttIsql utility to load the contents of the Oracle 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

Start the ttIsql utility and connect to the cachealone1 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 cachealone1
Command> GRANT SELECT ON oratt.readtab TO cacheuser;
Command> exit

Start the ttIsql utility and connect to the cachealone1 DSN as the cache manager user. Use ttIsql to query the contents of oratt.readtab cache table.

% ttIsql "DSN=cachealone1;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.

Update the cached Oracle table

Use SQL*Plus, as the Oracle schema user, to insert a new row, delete an existing row, and update an existing row in the Oracle 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;

After 5 seconds, the oratt.readtab cache table in the readcache cache group will be automatically refreshed with the committed updates on the cached Oracle oratt.readtab table as shown in Figure 2-7.

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 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.

Dynamically load the cache group

Start the ttIsql utility and connect to the cachealone1 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 cachealone1
Command> GRANT SELECT ON oratt.writetab TO cacheuser;
Command> exit

Start the ttIsql utility and connect to the cachealone1 DSN as the cache manager user. Use ttIsql to load a cache instance on demand from the Oracle oratt.writetab table to the TimesTen oratt.writetab cache table in the writecache cache group.

% ttIsql "DSN=cachealone1;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 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 group" 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.

Update the TimesTen cache table

Start the ttIsql utility and connect to the cachealone1 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 cachealone1
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 cachealone1 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=cachealone1;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 oratt.writetab table as shown in Figure 2-8.

As the Oracle 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

Detach the TimesTen database from the cache grid

Start the ttIsql utility and connect to the cachealone1 DSN as the cache manager user. Use ttIsql to call the ttGridDetach built-in procedure to detach the TimesTen database from the myGrid cache grid.

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttGridDetach;

See "Detaching a TimesTen database from a cache grid" for information about the effects of detaching a TimesTen database from a cache grid.

Stop the replication agent

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.

Drop the cache groups

Start the ttIsql utility and connect to the cachealone1 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 cachealone1
Command> GRANT DROP ANY TABLE TO cacheuser;
Command> exit

Start the ttIsql utility and connect to the cachealone1 DSN as the cache manager user. Use ttIsql to drop the readcache read-only cache group and the writecache AWT cache group.

% ttIsql "DSN=cachealone1;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.

Destroy the cache grid

As the cache manager user, use the ttIsql utility to call the ttGridDestroy built-in procedure to destroy the myGrid cache grid:

Command> call ttGridDestroy('myGrid');

See "Destroying a cache grid" for more information about destroying a cache grid.

Stop the cache agent and destroy the TimesTen database

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 cachealone1 DSN and destroy the TimesTen database:

% ttDestroy cachealone1

Drop the Oracle users and their objects

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