This chapter describes the tasks for creating a second standalone TimesTen database and an active standby pair, and attaching these members to the cache grid that was created in Chapter 3, "Setting Up a Caching Infrastructure". It includes the following topics:
The following is the definition of the cachealone2 DSN for the second standalone TimesTen database that will become a member of the ttGrid cache grid:
[cachealone2] DataStore=/users/OracleCache/alone2 PermSize=64 OracleNetServiceName=orcl DatabaseCharacterSet=WE8ISO8859P1
Start the ttIsql utility and connect to the cachealone2 DSN as the instance administrator to create the database. Then create the cache manager user cacheuser whose name, in this example, is the same as the Oracle cache administration user. Then create a cache table user oratt whose name is the same as the Oracle schema user who will own the Oracle tables to be cached in the TimesTen database.
% ttIsql cachealone2 Command> CREATE USER cacheuser IDENTIFIED BY timesten; Command> CREATE USER oratt IDENTIFIED BY timesten;
As the instance administrator, use the ttIsql utility to grant the cache manager user cacheuser the privileges required to perform the operations listed in Example 3-8:
Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser; Command> exit
Start the ttIsql utility and connect to the cachealone2 DSN as the cache manager user. Set the cache administration user name and password by calling the ttCacheUidPwdSet built-in procedure.
% ttIsql "DSN=cachealone2;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheUidPwdSet('cacheuser','oracle');
Associate the second standalone database to the ttGrid cache grid by calling the ttGridNameSet built-in procedure as the cache manager user:
Command> call ttGridNameSet('ttGrid');
The ttGrid cache grid was created from the first standalone TimesTen database. Since the grid already exists, it does not need to be created again.
If desired, you can test the connectivity between the second standalone TimesTen database and the Oracle database using the instructions stated in "Testing the connectivity between the TimesTen and Oracle databases".
Start the cache agent on the second standalone database by calling the ttCacheStart built-in procedure as the cache manager user:
Command> call ttCacheStart;
Then create cache groups in the database as the cache manager user. For example, the following statement creates a dynamic AWT global cache group subscriber_accounts that caches the oratt.subscriber table:
CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP subscriber_accounts FROM oratt.subscriber (subscriberid NUMBER(10) NOT NULL PRIMARY KEY, name VARCHAR2(100) NOT NULL, minutes_balance NUMBER(5) NOT NULL, last_call_duration NUMBER(4) NOT NULL)
The definition of the oratt.subscriber cached Oracle table is shown in "Global cache groups".
If any AWT cache groups were created, start the replication agent on the TimesTen database by calling the ttRepStart built-in procedure as the cache manager user:
Command> call ttRepStart;
If any global cache groups were created, the database must attach to the cache grid that it is associated with in order to update the cache tables of the global cache groups. Attaching the database to the grid allows the database to become a member of the grid so that cache instances in the cache tables of the global cache groups can maintain consistency among the databases within the grid.
As the cache manager user, attach the second standalone database to the ttGrid cache grid that it is associated with by calling the ttGridAttach built-in procedure. The node number for a standalone TimesTen database is 1.
In the following example, alone2 is a name that is used to uniquely identify the grid member, sys2 is the host name of the TimesTen system where the second standalone database resides, and 5002 is the TCP/IP port for the second standalone database's cache agent process:
Command> call ttGridAttach(1,'alone2','sys2',5002); Command> exit
To achieve high availability, configure an active standby pair replication scheme for cache tables in a read-only cache group or an AWT cache group.
An active standby pair that replicates cache tables from one of these cache group types can automatically change the role of a TimesTen database as part of failover and recovery with minimal chance of data loss. Cache groups themselves provide resilience from Oracle database outages, further strengthening system availability. See "Administering an Active Standby Pair with Cache Groups" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information.
An active standby pair replication scheme provides for high availability of a TimesTen database. Multiple grid members provide for high availability of a TimesTen cache grid. Oracle Real Application Clusters (Oracle RAC) provides for high availability of an Oracle database. For more information about using Oracle In-Memory Database Cache in an Oracle RAC environment, see "Using Oracle In-Memory Database Cache in an Oracle RAC Environment".
Perform the following tasks to configure an active standby pair for TimesTen databases that cache Oracle tables:
The following is the definition of the cacheactive DSN for the active master database of the active standby pair that will become a member of the ttGrid cache grid:
[cacheactive] DataStore=/users/OracleCache/cacheact PermSize=64 OracleNetServiceName=orcl DatabaseCharacterSet=WE8ISO8859P1
Start the ttIsql utility and connect to the cacheactive DSN as the instance administrator to create the database. Then create the cache manager user cacheuser whose name, in this example, is the same as the Oracle cache administration user. Then create a cache table user oratt whose name is the same as the Oracle schema user who will own the Oracle tables to be cached in the TimesTen database.
% ttIsql cacheactive Command> CREATE USER cacheuser IDENTIFIED BY timesten; Command> CREATE USER oratt IDENTIFIED BY timesten;
As the instance administrator, use the ttIsql utility to grant the cache manager user cacheuser the privileges required to perform the operations listed in Example 3-8 as well as create an active standby pair replication scheme which requires the ADMIN privilege:
Command> GRANT CREATE SESSION, CACHE_MANAGER,
> CREATE ANY TABLE, ADMIN TO cacheuser;
Command> exit
Start the ttIsql utility and connect to the cacheactive DSN as the cache manager user. Set the cache administration user name and password by calling the ttCacheUidPwdSet built-in procedure.
% ttIsql "DSN=cacheactive;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheUidPwdSet('cacheuser','oracle');
Associate the active master database to the ttGrid cache grid by calling the ttGridNameSet built-in procedure as the cache manager user:
Command> call ttGridNameSet('ttGrid');
The ttGrid cache grid was created from the first standalone TimesTen database. Since the grid already exists, it does not need to be created again.
If desired, you can test the connectivity between the active master database and the Oracle database using the instructions stated in "Testing the connectivity between the TimesTen and Oracle databases".
Start the cache agent on the active master database by calling the ttCacheStart built-in procedure as the cache manager user:
Command> call ttCacheStart;
Then create cache groups in the database as the cache manager user. For example, the following statement creates a dynamic AWT global cache group subscriber_accounts that caches the oratt.subscriber table:
CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP subscriber_accounts FROM oratt.subscriber (subscriberid NUMBER(10) NOT NULL PRIMARY KEY, name VARCHAR2(100) NOT NULL, minutes_balance NUMBER(5) NOT NULL, last_call_duration NUMBER(4) NOT NULL)
The definition of the oratt.subscriber cached Oracle table is shown in "Global cache groups".
As the cache manager user, create an active standby pair replication scheme in the active master database using a CREATE ACTIVE STANDBY PAIR statement.
In the following example, cacheact, cachestand and subscr are the file name prefixes of the checkpoint and transaction log files of the active master database, standby master database and read-only subscriber database. sys3, sys4 and sys5 are the host names of the TimesTen systems where the active master database, standby master database and read-only subscriber database reside, respectively.
Command> CREATE ACTIVE STANDBY PAIR cacheact ON "sys3", cachestand ON "sys4" > SUBSCRIBER subscr ON "sys5";
As the cache manager user, start the replication agent on the active master database by calling the ttRepStart built-in procedure. Then declare the database as the active master by calling the ttRepStateSet built-in procedure.
Command> call ttRepStart;
Command> call ttRepStateSet('active');
If any global cache groups were created, the database must attach to the cache grid that it is associated with in order to update the cache tables of the global cache groups. Attaching the database to the grid allows the database to become a member of the grid so that cache instances in the cache tables of the global cache groups can maintain consistency among the databases within the grid.
As the cache manager user, attach the active master database to the ttGrid cache grid that it is associated with by calling the ttGridAttach built-in procedure. The node number for an active master database is 1.
In the following example:
cacheact is a name that is used to uniquely identify the active master database grid node
cachestand is a name that is used to uniquely identify the standby master database grid node
sys3 is the host name of the TimesTen system where the active master database resides
sys4 is the host name of the TimesTen system where the standby master database resides
5003 is the TCP/IP port for the active master database's cache agent process
5004 is the TCP/IP port for the standby master database's cache agent process
Command> call ttGridAttach(1,'cacheact','sys3',5003,'cachestand','sys4',5004); Command> exit
The following is the definition of the cachestandby DSN for the standby master database of the active standby pair that will become a member of the ttGrid cache grid:
[cachestandby] DataStore=/users/OracleCache/cachestand PermSize=64 OracleNetServiceName=orcl DatabaseCharacterSet=WE8ISO8859P1
As the instance administrator, create the standby master database as a duplicate of the active master database by running a ttRepAdmin -duplicate utility command from the standby master database's system. The instance administrator user name of the active master database's and standby master database's instances must be identical.
Use the -keepCG option so that cache tables in the active master database are duplicated as cache tables in the standby master database because the standby master database will have connectivity with the Oracle database.
In the following example:
The -from option specifies the file name prefix of the active master database's checkpoint and transaction log files
The -host option specifies the host name of the TimesTen system where the active master database resides
The -uid and -pwd options specify a user name and password of a TimesTen internal user defined in the active master database that has been granted the ADMIN privilege
The -cacheuid and -cachepwd options specify the Oracle cache administration user name and password
cachestandby is the DSN of the standby master database
% ttRepAdmin -duplicate -from cacheact -host "sys3" -uid cacheuser -pwd timesten -cacheuid cacheuser -cachepwd oracle -keepCG cachestandby
Start the ttIsql utility and connect to the cachestandby DSN as the cache manager user. Set the cache administration user name and password by calling the ttCacheUidPwdSet built-in procedure.
% ttIsql "DSN=cachestandby;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheUidPwdSet('cacheuser','oracle');
The ttGrid cache grid was created from the first standalone TimesTen database. Since the grid already exists, it does not need to be created again.
The ttRepAdmin -duplicate -keepCG utility command associated the standby master database to the ttGrid cache grid so this association does not need to be done explicitly.
If desired, you can test the connectivity between the standby master database and the Oracle database using the instructions stated in "Testing the connectivity between the TimesTen and Oracle databases".
Start the cache agent on the standby master database by calling the ttCacheStart built-in procedure as the cache manager user:
Command> call ttCacheStart;
As the cache manager user, start the replication agent on the standby master database by calling the ttRepStart built-in procedure.
Command> call ttRepStart;
If any global cache groups were created, the database must attach to the cache grid that it is associated with in order to update the cache tables of the global cache groups. Attaching the database to the grid allows the database to become a member of the grid so that cache instances in the cache tables of the global cache groups can maintain consistency among the databases within the grid.
As the cache manager user, attach the standby master database to the ttGrid cache grid that it is associated with by calling the ttGridAttach built-in procedure. The node number for a standby master database is 2. Use the same TCP/IP ports specified for the cache agent of the active master and standby master databases that were specified when configuring the active master database.
In the following example:
cacheact is a name that is used to uniquely identify the active master database grid node
cachestand is a name that is used to uniquely identify the standby master database grid node
sys3 is the host name of the TimesTen system where the active master database resides
sys4 is the host name of the TimesTen system where the standby master database resides
5003 is the TCP/IP port for the active master database's cache agent process
5004 is the TCP/IP port for the standby master database's cache agent process
Command> call ttGridAttach(2,'cacheact','sys3',5003,'cachestand','sys4',5004); Command> exit
The following is the definition of the rosubscriber DSN for the read-only subscriber database of the active standby pair:
[rosubscriber] DataStore=/users/OracleCache/subscr PermSize=64 DatabaseCharacterSet=WE8ISO8859P1
As the instance administrator, create the read-only subscriber database as a duplicate of the standby master database by running a ttRepAdmin -duplicate utility command from the read-only subscriber database system. The instance administrator user name of the standby master database instance and read-only subscriber database instance must be identical.
Use the -noKeepCG option so that cache tables in the standby master database are duplicated as regular tables in the read-only subscriber database because the read-only subscriber database will have no connectivity with the Oracle database. As a result, the read-only subscriber database will not be associated with a cache grid.
In the following example:
The -from option specifies the file name prefix of the standby master database's checkpoint and transaction log files
The -host option specifies the host name of the TimesTen system where the standby master database resides
The -uid and -pwd options specify a user name and password of a TimesTen internal user defined in the standby master database that has been granted the ADMIN privilege
rosubscriber is the DSN of the read-only subscriber database
% ttRepAdmin -duplicate -from cachestand -host "sys4" -uid cacheuser -pwd timesten -noKeepCG rosubscriber
As the cache manager user, start the replication agent on the read-only subscriber database by calling the ttRepStart built-in procedure.
% ttIsql "DSN=rosubscriber;UID=cacheuser;PWD=timesten" Command> call ttRepStart; Command> exit
The definition of the oratt.subscriber cached Oracle table is shown in "Global cache groups".
The following is the data in the oratt.subscriber cached Oracle table.
SUBSCRIBERID NAME MINUTES_BALANCE LAST_CALL_DURATION
------------ ---------------- --------------- ------------------
1001 Jane Anderson 75 15
1004 Robert Phillips 60 20
1005 William Ackerman 40 10
1009 Sandy Little 90 30
The oratt.subscriber TimesTen cache table in the subscriber_accounts global cache group is initially empty in all five TimesTen databases (cachealone1, cachealone2, cacheactive, cachestandby, rosubscriber):
Command> SELECT * FROM oratt.subscriber; 0 rows found.
Issue the following SELECT statement on the cachealone1 TimesTen database to dynamically load one cache instance from the cached Oracle table into the TimesTen cache table:
Command> SELECT * FROM oratt.subscriber WHERE subscriberid = 1004; < 1004, Robert Phillips, 60, 20 >
As a result, the cachealone1 standalone database grid member has ownership of the cache instance with subscriber ID 1004. This cache instance does not exist in any of the other grid members.
Next issue the following SELECT statement on the cachealone2 TimesTen database to dynamically load one cache instance from the cached Oracle table into the TimesTen cache table:
Command> SELECT * FROM oratt.subscriber WHERE subscriberid = 1004; < 1004, Robert Phillips, 60, 20 >
As a result, the cachealone2 standalone database grid member has taken ownership of the cache instance with subscriber ID 1004 from the cachealone1 grid member. This cache instance no longer exists in cachealone1 and does not exist in any of the other grid members.
Next issue the following INSERT statement on the cacheactive TimesTen database to insert a new cache instance into the TimesTen cache table:
Command> INSERT INTO oratt.subscriber VALUES (1012, 'Charles Hill', 80, 16);
As a result, the cacheactive active master database grid node has ownership of the cache instance with subscriber ID 1012. The cache instance is replicated to the cachestandby standby master database and the rosubscriber read-only subscriber database. The cache instance does not exist in any of the other grid members. The insert operation is also automatically propagated to the oratt.subscriber cached Oracle table.
A standby master database or a read-only subscriber database cannot directly take ownership of a cache instance. A dynamic or manual load operation is prohibited including SELECT statements that result in a dynamic load because these databases are read-only.
No data sharing occurs with cache tables in local cache groups among the grid members. Each grid member can have a different number of local cache groups. If two grid members have a local cache group with the same definition, the data in the cache table within one grid member can overlap with the data in the cache table within the other grid member. There is no concept of cache instance ownership for cache tables in local cache groups.
If you want to access data on all the nodes of a cache grid, perform a global query. For example, consider this statement:
SELECT MAX(salary) FROM employees;
When global query processing is not enabled, the statement returns the maximum salary for the rows that exist on the local node. When global query processing is enabled, it returns the maximum salary across all employee records in the cache grid without changing ownership of the cache instance where the data is found.
A global query can reference a cache table or a noncache table in all attached grid members. The referenced tables can be any combination of local tables, cache tables, views, materialized views and table synonyms. The tables need to have the same definition for columns affected by the global query.
Enable global query processing by setting an optimizer flag. Before executing a global query, turn autocommit off and call the ttOptSetFlag built-in procedure to set the GlobalProcessing optimizer flag to 1:
CALL ttOptSetFlag('GlobalProcessing', 1);
Global queries have these restrictions:
The query must reference exactly one table.
The query cannot reference a global temporary table.
The query cannot include a self join, a derived table or subqueries.
ROWNUM and GROUP BY clauses cannot be used in the same query.
The query cannot be performed on the standby database of an active standby grid member.
If a database that contains a global cache group is attached to a cache grid, a subsequent database can attach to the same grid and become a grid member only if it contains a global cache group with the same definition as the global cache group in the database that is attached to the grid. The subsequent database cannot attach to the same grid if it contains more or fewer global cache groups than the database that is attached to the grid. Each database can contain a different number of local cache groups with non-matching definitions between the databases.
Before you can create a new dynamic AWT global cache group in a TimesTen database that is attached to a cache grid, stop the replication agent on the database. Then restart the replication agent after creating the global cache group. The new global cache group cannot be manually or dynamically loaded, and its cache tables cannot be updated until the cache group has been created with the same definition in all the grid members. In the standalone databases and the active master database, create the new global cache group manually. For the standby master database and the read-only subscriber databases, use the ttDestroy utility to drop the databases and a ttRepAdmin -duplicate utility command to re-create the databases so that they contain the new global cache group.