1. Cache Guide
  2. Using GoldenGate as an Alternative to Native Read-Only Cache Groups
  3. Configuring GoldenGate to Provide Cache Refresh Functionality for TimesTen Workflow
  4. Creating TimesTen Database Users and Tables

Creating TimesTen Database Users and Tables

Perform steps to create the TimesTen database users and tables.

  1. While connected to the TimesTen database as the TimesTen instance administrator user, create a dedicated GoldenGate apply database user for the GoldenGate apply processes. This user owns all of the cached tables in TimesTen. Make sure that the dedicated GoldenGate apply database user has all necessary privileges on the cached tables.

    This example creates a dedicated GoldenGate apply database user called cacheadmin. 

    CREATE USER cacheadmin IDENTIFIED BY ttpwd;
GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheadmin;

  2. Applications should connect to the database as different users from the GoldenGate apply database user. As always, application users should be granted the minimum set of privileges consistent with the operations needed to perform. This example creates two application users named oratt1 and oratt2: 

    CREATE USER oratt1 IDENTIFIED BY ttpwd;
GRANT CREATE SESSION, CREATE ANY TABLE TO oratt1;
CREATE USER oratt2 IDENTIFIED BY ttpwd;
GRANT CREATE SESSION, CREATE ANY TABLE TO oratt2;

  3. GoldenGate for Oracle TimesTen supports delivery of data to user tables, instead of cache groups. Since GoldenGate uses regular tables instead of cache groups, create your cache tables in TimesTen as regular tables (using the CREATE TABLE statement) and not as cache groups (using the CREATE READONLY CACHE GROUP statement).

    This step creates the required cache tables in TimesTen. Make sure that the table definitions are compatible with the corresponding tables in the source database. You should create the tables owned by the oratt database user (you need to be connected either as cacheadmin or as some other user with ADMIN privileges): 

    CREATE TABLE oratt1.cachetab1 ( … );
CREATE TABLE oratt2.cachetab2 ( … );
…

  4. When using GoldenGate as the cache refresh mechanism, any read-only cached tables in TimesTen are not truly read-only. Applications are not prevented from modifying data in the tables (provided that they have suitable access privileges on the tables). However, any such modifications can be overwritten if GoldenGate refreshes newly modified data to the table from the back-end database. You can mitigate this by ensuring that application users only have read access to the cache tables. These tables must be owned by a user other than the application users, such as the dedicated GoldenGate apply database user to ensure that the GoldenGate apply process to write to these same tables.

    Grant SELECT (read) privileges on the cache tables to the application users: 

    GRANT SELECT ON oratt1.cachetab1 TO cacheadmin1, cacheadmin2, …;
GRANT SELECT ON oratt2.cachetab2 TO cacheadmin1, cacheadmin2, …;