Setting Up a Cache Database

Before you can start caching Oracle tables, you need to create a TimesTen in-memory database. This database must include a user with either ADMIN or CACHE_MANAGER privilege. This user is known as the cache manager user, he is responsible for administrating the cache grid and cache groups. The Oracle user that owns the tables to be cached must also exist in the cache database, this user is known as the cache table user.

1. Create a Cache Database

1. Create a cache database by first defining a DSN, in this tutorial we will use cachedb1_1122, a pre-defined DSN shipped with the installation.

   Note: the DSN attribute OracleNetServiceName must contain a valid TNS service name pointing to the Oracle database, and the value of DatabaseCharacterSet must be identical to the database character set defined in the Oracle database. Please update these two attributes with the correct settings before proceeding. If necessary, refer to the section Creating a TimesTen Database for more information.

   In the example, we are using ttorcl as the OracleNetServiceName and AL32UTF8 as the DatabaseCharacterSet.




2. Create and connect to the database cachedb1_1122 in ttIsql as the instance administrator

   

2. Create a Cache Manager User

1. Create a cache manager user by granting either ADMIN or CACHE_MANAGER privilege to this user. This user is responsible for setting up and managing the cache grid, and creating and loading data into the cache groups.

   In the example, cacheadm is the cache manager user. This user has the same user name as the cache administration user in the Oracle database.

   create user cacheadm identified by cacheadm;
   grant admin to cacheadm;

3. Create a Cache Table User

1. In order to cache Oracle tables, the user who owns the Oracle tables to be cached must also exist in the TimesTen cache database.

   In our example, we are caching the tables owned by the HR user. Therefore the HR user needs to be created.

   create user hr identified by hr;
   grant create session to hr;

4. Associate the Oracle Cache Administration user with the Cache Database

1. Log in as the cache manager user, making sure to specify the password used by the cache administration user in the Oracle database. Use the built-in procedure ttCacheUidPwdSet to associate the Oracle cache administration user with the cache database. Verify that the Oracle cache administration user has been set by calling the procedure ttCacheUidGet.

   connect "dsn=cachedb1_1122;uid=cacheadm;oraclepwd=cacheadm";

   call ttcacheuidpwdset ('cacheadm','cacheadm');
   call ttcacheuidget;

   

5. Create a Cache Grid
     (required only for the first cache database)

1. Only the cache manager user can create and manage a cache grid. A cache database can only belong to one cache grid. Call the built-in procedure ttGridCreate to create a cache grid. Only the first grid member needs to perform this step.

   call ttgridcreate ('samplegrid');




2. Use the procedure ttGridInfo to verify that the cache grid has been created.

   call ttgridinfo;

   

6. Associate the Cache Database with the Cache Grid

1. Call the procedure ttGridNameSet to associate the current database with a cache grid.

   call ttgridnameset ('samplegrid');

For more information about Configuring a TimesTen database to cache Oracle Database data refer to the documetation here