Oracle® TimesTen Application-Tier Database Cache User's Guide 11g Release 2 (11.2.2) E21634-12 |
|
|
View PDF |
The following sections provide a quick reference on the steps for creating a cache environment as well as the privileges required to do so:
The following section provides a quick reference on the steps necessary when setting up an environment that caches Oracle Database data in a TimesTen in-memory database. For a detailed explanation and examples for each step, see Chapter 2, "Getting Started", Chapter 3, "Setting Up a Caching Infrastructure", and Chapter 4, "Defining Cache Groups".
Perform the following on the Oracle database:
Create a default tablespace to be used for storing TimesTen Application-Tier Database Cache management objects.
Create the timesten
user, Oracle Database tables owned by the timesten
user, and the TT_CACHE_ADMIN_ROLE
role that defines privileges on these Oracle Database tables by running the SQL*Plus script TimesTen_install_dir
/oraclescripts/initCacheGlobalSchema.sql
as the sys
user.
As the sys
user, create one or more schema users to own the cached Oracle Database tables (may be existing users).
As the sys
user, create the cache administration user that creates, owns, and maintains Oracle Database objects that store information used to manage a specific cache grid (if one is defined) and enforce predefined behaviors of particular cache group types. In the CREATE USER
statement for the cache administration user, designate the tablespace that was created for the timesten user as the default tablespace for the cache administration user.
See "Create the Oracle database users" for more information about the Oracle Database users.
As the sys
user, run the TimesTen_install_dir
/oraclescripts/grantCacheAdminPrivileges.sql
script to grant the cache administration user the privileges required to perform any cache grid operations (if one is defined), create the desired types of cache groups, and perform operations on the cache groups. Alternatively, you can manually create each Oracle Database object.
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 "Required privileges for the cache administration user and the cache manager user" for more information about the privileges that must be granted to the cache administration user to perform particular cache operations.
Perform the following on the TimesTen database:
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.
Create the following users in the TimesTen database:
Cache manager user
This 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. 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 "Required privileges for the cache administration user and the cache manager user" for more information about the privileges that must be granted to the cache manager user to perform particular cache operations.
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.
If you are going to use a cache grid for global cache groups, then perform the following:
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.
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.
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.
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.
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.
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.
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.
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.
If using a cache grid, you can add subsequent standalone TimesTen databases can as members to an existing cache 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.
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.
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, 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, 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, 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, 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.
The privileges that the Oracle Database users require depends on the types of cache groups you create and the operations that you perform on the cache groups. The privileges required for the cache administration user are listed in the first column and the privileges required for the TimesTen cache manager user for each cache operation are listed in the second column in Table A-1.
Table A-1 Oracle Database and TimesTen user privileges required for cache operations
Cache operation | Privileges required for Oracle Database cache administration userFoot 1 | Privileges required for TimesTen cache manager userFoot 2 |
---|---|---|
Initialize the cache administration user. The |
Permissions for the default tablespace |
|
Set the cache administration user name and password with either:
|
|
|
Get the cache administration user name with either:
|
None |
|
Create a cache grid:
|
|
|
Associate a TimesTen database with a cache grid:
|
|
|
Attach a TimesTen database to a cache grid:
|
|
|
Detach a TimesTen database from a cache grid:
|
|
|
Detach a list of nodes from a cache grid:
|
|
|
Destroy a cache grid:
|
|
|
Start the cache agent with either:
|
|
|
Stop the cache agent
|
None |
|
Set a cache agent start policy with either:
|
|
|
Return the cache agent start policy setting:
|
|
None |
Start the replication agent with either:
|
None |
|
Stop the replication agent with either:
|
None |
|
Set a replication agent start policy
|
None |
|
when the cache group created is an AWT cache group |
|
|
Duplicate the database with |
|
|
|
||
|
|
|
|
||
|
|
|
(see variants in following rows) |
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
None |
|
|
|
|
Synchronous writethrough or propagate |
|
|
Asynchronous writethrough |
|
|
Asynchronous writethrough when the |
Note: This privilege is an addition to the privileges needed for any asynchronous writethrough cache group. |
No additional privileges |
Asynchronous writethrough cache for Oracle Database |
Note: This privilege is an addition to the privileges needed for any asynchronous writethrough cache group. |
No additional privileges |
Incremental autorefresh |
|
None |
Full autorefresh |
|
None |
Dynamic load |
|
|
Aging |
None |
|
Set the LRU aging attributes
|
None |
|
Generate Oracle Database SQL statements to manually install or uninstall Oracle Database objects
|
|
|
Disable or enable propagation of committed cache table updates to the Oracle database
|
None |
|
Configure cache agent timeout and recovery method for autorefresh cache groups
|
|
|
Set the AWT transaction log file threshold
|
None |
|
Enable or disable monitoring of AWT cache groups
|
None |
|
Enable or disable tracking of DDL statements issued on cached Oracle Database tables
|
|
|
Return information about cache grids
|
|
|
Return information about cache grid nodes
|
|
|
Footnote 1 At minimum, the cache administration user must have the CREATE TYPE
privilege.
Footnote 2 At minimum, the cache manager user must have the CREATE SESSION
privilege.
Footnote 3 If the cache administration user will not create autorefresh cache groups, then you can grant the CREATE TRIGGER
privilege instead of the CREATE ANY TRIGGER
privilege.
Footnote 4 Not required if the Oracle Database objects used to manage the caching of Oracle Database data are manually created with the initCacheAdminSchema.sql
script.
Footnote 5 Required if the cache agent start policy is being set to always
or norestart
.
Footnote 6 Required on all Oracle Database tables cached in the TimesTen cache group except for tables owned by the cache administration user.
Footnote 7 The CACHE_MANAGER
privilege includes the CREATE [ANY] CACHE GROUP
privilege. ANY
is required if the cache manager user creates cache groups owned by a user other than itself.
Footnote 8 ANY
is required if any of the cache tables are owned by a user other than the cache manager user.
Footnote 9 Required if the cache group's autorefresh mode is incremental and initial autorefresh state is OFF
, and the Oracle Database objects used to manage the caching of Oracle Database data are automatically created.
Footnote 10 Required if the TimesTen user accessing the cache group does not own the cache group.
Footnote 11 Required if the cache group's autorefresh mode is incremental.
Footnote 12 Required if the TimesTen user accessing the cache group does not own all its cache tables.
Footnote 13 The privilege must be granted to the Oracle Database user with the same name as the TimesTen cache manager user if the Oracle Database user is not the cache administration user.
Footnote 14 Required if the TimesTen user accessing the cache table does not own the table.