Skip Headers
Oracle® In-Memory Database Cache User's Guide
11g Release 2 (11.2.2)

E21634-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

2 Getting Started

This chapter describes how to create a cache grid. To illustrate the creation and use of cache groups, the chapter describes how to create an explicitly loaded read-only local cache group, and a dynamic updatable global cache group. The chapter also describes how to populate the 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.

This chapter includes the following topics:

Setting up the Oracle Database and TimesTen systems

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

Complete the following tasks:

  1. Create users in the Oracle database.

  2. Create a DSN for the TimesTen database.

  3. Create users in the TimesTen database.

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

Create users in the Oracle database

Before you can use Oracle In-Memory Database Cache, you must create the following users on the Oracle Database:

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

  • 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 cache grids 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_install_dir/oraclescripts
% sqlplus sys as sysdba
Enter password: password

Use SQL*Plus to run the SQL*Plus script TimesTen_install_dir/oraclescripts/initCacheGlobalSchema.sql to create the following elements:

  • The timesten user

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

  • The TT_CACHE_ADMIN_ROLE role that defines privileges on these Oracle Database tables

Use SQL*Plus to create a default tablespace to be used for storing Oracle In-Memory Database Cache management objects that should not be shared with other applications. While you may also store Oracle base tables that are cached in TimesTen, we strongly recommend that this tablespace be used solely by TimesTen for cache management.

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:

Note:

See the comments in the grantCacheAdminPrivileges.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
  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 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.

Create a DSN for the TimesTen database

SQL> SELECT value FROM nls_database_parameters WHERE 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 connection 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 connection 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 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".

Create users in the TimesTen database

In addition to the Oracle Database users, you must create the following 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 Database user that can access the cached Oracle Database tables. For example, the Oracle Database user must have privileges to select from and update the cached Oracle Database tables. The Oracle Database 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 Database user with the same name.

    The cache manager user creates and configures the cache grid and creates 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 autorefresh.

  • 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 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 is to 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 Database 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 "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 connection attribute. (In this example, the TimesTen cache manager user name is the same as the Oracle Database cache administration user name.) Specify the cache manager user's password in the PWD connection attribute and the cache administration user's password in the OraclePWD connection 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 Database 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.

Creating a cache grid

After you have created the Oracle Database users, the TimesTen database, and the TimesTen users, and set the Oracle Database cache administration user name and password in the TimesTen database, you need to create a cache grid to define a framework for TimesTen databases that cache tables from an Oracle database.

As the cache manager user, use the ttIsql utility to call the ttGridCreate built-in procedure to create a cache grid myGrid:

Command> call ttGridCreate('myGrid');

Then use ttIsql to call the ttGridNameSet built-in procedure to associate the TimesTen database with the myGrid cache grid:

Command> call ttGridNameSet('myGrid');

See "Configuring a cache grid" for more information about the contents and functionality of a cache grid.

Creating cache groups

After you have created a cache grid and associated the TimesTen database with the grid, you are ready to create cache groups. You create a read-only cache group as shown in Figure 2-1. Then, create an asynchronous writethrough (AWT) cache group as shown in Figure 2-2.

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

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

Figure 2-2 Single-table writethrough cache group

Description of Figure 2-2 follows
Description of "Figure 2-2 Single-table writethrough cache group"

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

  1. Create the Oracle Database tables to be cached.

  2. Start the cache agent.

  3. Create the cache groups.

  4. Start the replication agent for the AWT 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/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

Description of Figure 2-3 follows
Description of "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

Description of Figure 2-4 follows
Description of "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, '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 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 writethrough 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.

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 Database oratt.readtab table and a dynamic AWT global 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 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 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

Description of Figure 2-5 follows
Description of "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 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 Database tables.

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

Attaching the TimesTen database to the cache grid

If you are creating a local cache group, you do not need to attach the TimesTen database to the cache grid. Before you can perform operations on a global cache group or on its cache tables, you must attach the TimesTen database to the cache grid that it is associated with.

As the cache manager user, use the ttIsql utility to call the ttGridAttach built-in procedure to attach the TimesTen database to the myGrid cache grid:

Command> call ttGridAttach(1,'alone1','mysys',5001);

In this example, alone1 is a name that uniquely identifies the grid member, mysys is the host name of the TimesTen system, and 5001 is the TCP/IP port for the cache agent.Calling the ttGridAttach built-in procedure automatically starts the cache agent if it is not already running.

Although the example in this chapter contains only one standalone TimesTen database as the sole grid member, it can be extended to include additional grid members such as active standby pairs and other standalone TimesTen databases. See Chapter 6, "Creating Other Cache Grid Members", for details on how to create and add other members to an existing cache grid, and how data in a global cache group is shared among the grid members.

Performing operations on the read-only cache group

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:

  1. Manually load the cache group.

  2. Update the cached Oracle Database table.

Manually load the 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

Description of Figure 2-6 follows
Description of "Figure 2-6 Loading a read-only 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.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 Database table

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;

After 5 seconds, the oratt.readtab cache table in the readcache cache group is automatically refreshed 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

Description of Figure 2-7 follows
Description of "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.

Performing operations on the dynamic updatable global cache group

This section shows how to dynamically load the 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:

  1. Dynamically load the cache group.

  2. Update the TimesTen cache table.

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

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 Database oratt.writetab table as shown in Figure 2-8.

Figure 2-8 Automatically propagate TimesTen cache table updates to Oracle Database

Description of Figure 2-8 follows
Description of "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

Cleaning up the TimesTen and Oracle Database systems

Complete the following tasks to restore the TimesTen and Oracle Database systems to their original state before creating a cache grid and cache groups:

  1. Detach the TimesTen database from the cache grid.

  2. Stop the replication agent.

  3. Drop the cache groups.

  4. Destroy the cache grid.

  5. Stop the cache agent and destroy the TimesTen database.

  6. Drop the Oracle Database users and their objects.

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

Procedure for caching Oracle Database data in TimesTen

Table 2-1 Instructions for caching Oracle Database data in a TimesTen database

Task number Task

1

Create the following users in the Oracle database:

  • timesten user

    Create the user by running the SQL*Plus script TimesTen_install_dir/oraclescripts/initCacheGlobalSchema.sql as the sys user.

  • One or more schema users who own the cached Oracle Database tables (may be existing users)

  • Cache administration user

    A default tablespace must be created to be used for storing Oracle In-Memory Database Cache management objects that should not be shared with other applications. While you may also store Oracle base tables that are cached in TimesTen, we strongly recommend that this tablespace be used solely by TimesTen for cache management.

Execute CREATE USER statements as the sys user.

See "Create the Oracle database users" for more information about the Oracle Database users.

Grant the cache administration user the privileges required to perform the cache grid operations, create the desired types of cache groups, and perform operations on the cache groups. Privileges are granted by running either the TimesTen_install_dir/oraclescripts/grantCacheAdminPrivileges.sql or the TimesTen_install_dir/oraclescripts/initCacheAdminSchema.sql script as the sys user.

See "Automatically create Oracle Database objects used to manage data caching" or "Manually create Oracle Database objects used to manage data caching" to determine the appropriate script to run.

If you are manually creating the Oracle Database objects, you also need to run the TimesTen_install_dir/oraclescripts/initCacheGridSchema.sql script to create the Oracle Database tables used to store information about TimesTen databases that are associated with a particular cache grid.

Some privileges cannot be granted until the cached Oracle Database tables have been created. To grant these privileges, execute GRANT statements as the sys user.

See "Grant privileges to the Oracle database users" for more information about the privileges that must be granted to the cache administration user to perform particular cache operations.

2

Define a DSN that references the TimesTen database that is to be used to cache data from an Oracle database.

Set the OracleNetServiceName connection attribute to the Oracle Net service name that references the Oracle database instance.

Set the DatabaseCharacterSet connection attribute to the Oracle database character set. The TimesTen database character set must match the Oracle database character set.

Then, connect to the DSN to create the database if this is a standalone database or is to be an active database of an active standby pair.

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

3

Create the following users in the TimesTen database:

  • Cache manager user

    This user must have the same name as an Oracle Database user that can access the cached Oracle Database tables. The Oracle Database user can be the cache administration user, a schema user, or some other existing user. The password of the cache manager user and the Oracle Database user with the same name can be different.

  • One or more cache table users who own the TimesTen cache tables

    These users must have the same name as the Oracle Database schema users who own the cached Oracle Database tables. The password of a cache table user and the Oracle Database user with the same name can be different.

Execute CREATE USER statements as the instance administrator.

See "Create the TimesTen users" for more information about the TimesTen users.

Grant the cache manager user the privileges required to perform the cache grid operations, create the desired types of cache groups, and perform operations on the cache groups. Execute GRANT statements as the instance administrator.

See "Grant privileges to the TimesTen users" for more information about the privileges that must be granted to the cache manager user to perform particular cache operations.

4

Set the cache administration user name and password in the TimesTen database either by calling the ttCacheUidPwdSet built-in procedure as the cache manager user or running a ttAdmin -cacheUidPwdSet utility command as a TimesTen external user with the CACHE_MANAGER privilege.

See "Set the cache administration user name and password" for more information about setting the cache administration user name and password in a TimesTen database.

5

Create a cache grid by calling the ttGridCreate built-in procedure in the TimesTen database as the cache manager user.

See "Create a cache grid" for more information about creating a cache grid.

6

Associate the TimesTen database with the cache grid by calling the ttGridNameSet built-in procedure in the TimesTen database as the cache manager user.

See "Associate a TimesTen database with a cache grid" for more information about associating a TimesTen database with a cache grid.

7

Start the cache agent on the TimesTen database either by calling the ttCacheStart built-in procedure as the cache manager user or running a ttAdmin -cacheStart utility command as a TimesTen external user with the CACHE_MANAGER privilege.

See "Managing the cache agent" for more information about starting a cache agent on a TimesTen database.

8

Design the schema for the cache groups by determining which Oracle Database tables to cache and within those tables, which columns and rows to cache. For multiple table cache groups, determine the relationship between the tables by defining which table is the root table, which tables are direct child tables of the root table, and which tables are the child tables of other child tables. For each cached column, determine the TimesTen data type to which the Oracle Database data type should be mapped.

See "Mappings between Oracle Database and TimesTen data types" for a list of valid data type mappings between the Oracle and TimesTen databases.

For each cache group, determine what type to create (read-only, SWT, AWT, user managed) based on the application requirements and objectives. Also, determine whether each cache group is to be explicitly loaded or dynamic, and local or global.

Then create the cache groups.

See "Creating a cache group" for more information about creating a cache group.

9

If this TimesTen database is intended to be an active database of an active standby pair, create an active standby pair replication scheme in the database.

10

If the TimesTen database contains an active standby pair replication scheme or at least one AWT cache group, start the replication agent on the database either by calling the ttRepStart built-in procedure as the cache manager user or running a ttAdmin -repStart utility command as a TimesTen external user with the CACHE_MANAGER privilege.

See "Managing the replication agent" for more information about starting a replication agent on a TimesTen database.

11

If the TimesTen database contains at least one global cache group, attach the TimesTen database to the cache grid that the database associated with by calling the ttGridAttach built-in procedure as the cache manager user.

See "Attach a TimesTen database to a cache grid" for more information about attaching a TimesTen database to a cache grid.

12

Manually load the cache tables in explicitly loaded cache groups using LOAD CACHE GROUP statements, and load the cache tables in dynamic cache groups using proper SELECT, UPDATE or INSERT statements.

See "Loading and refreshing a cache group" for more information about manually loading cache tables in a cache group.

See "Dynamically loading a cache instance" for more information about dynamically loading cache tables in a dynamic cache group.

13

Subsequent standalone TimesTen databases can be added as members to an existing cache grid.

To create a standalone database, perform task 2. Then perform tasks 3 to 4, 6 to 8, and 10 to 11 to configure the database and add it as a member to the grid.

See "Creating and configuring a subsequent standalone TimesTen database" for details about creating another standalone TimesTen database and adding that database to an existing cache grid.

14

An active standby pair can be added as a member to an existing cache grid to achieve high availability by replicating the cache tables to another TimesTen database.

To create the active database perform task 2. Then perform tasks 3 to 4, and 6 to 11 to configure the database and add it as a member to the grid.

See "Create and configure the active database" for details about creating an active database and adding the database to an existing cache grid.

To create the standby database from the active database, perform task 2 to create a DSN for the standby database, and then run a ttRepAdmin -duplicate utility command on the standby database system as a TimesTen external user with the ADMIN privilege. For the command to succeed, the cache manager user in the active database must be granted the ADMIN privilege. Then perform tasks 4, 7, 10 and 11 to configure the database and add it as a member to the grid.

See "Create and configure the standby database" for details about creating a standby database and adding the database to an existing cache grid.

To create an optional read-only subscriber database from the standby database, perform task 2 to create a DSN for the subscriber database. Then run a ttRepAdmin -duplicate utility command on the subscriber database system as a TimesTen external user with the ADMIN privilege. For the command to succeed, the cache manager user in the standby database must be granted the ADMIN privilege. Then perform task 10 to start the replication agent on the database.

See "Create and configure the read-only subscriber database" for details about creating a read-only subscriber database for an active standby pair.