6 Creating Other Cache Grid Members

Create and configure the active database

The following is the definition of the cacheactive DSN for the active 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 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 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 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 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 database, standby database and read-only subscriber database. sys3, sys4 and sys5 are the host names of the TimesTen systems where the active database, standby 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 database by calling the ttRepStart built-in procedure. Then declare the database as the active 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 database to the ttGrid cache grid that it is associated with by calling the ttGridAttach built-in procedure. The node number for an active database is 1.

In the following example:

• cacheact is a name that is used to uniquely identify the active database grid node

• cachestand is a name that is used to uniquely identify the standby database grid node

• sys3 is the host name of the TimesTen system where the active database resides

• sys4 is the host name of the TimesTen system where the standby database resides

• 5003 is the TCP/IP port for the active database's cache agent process

• 5004 is the TCP/IP port for the standby database's cache agent process

Command> CALL ttGridAttach(1,'cacheact','sys3',5003,'cachestand','sys4',5004);
Command> exit

Create and configure the standby database

The following is the definition of the cachestandby DSN for the standby 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 database as a duplicate of the active database by running a ttRepAdmin -duplicate utility command from the standby database system. The instance administrator user name of the active database's and standby database's instances must be identical.

Use the -keepCG option so that cache tables in the active database are duplicated as cache tables in the standby database because the standby database will have connectivity with the Oracle database.

In the following example:

• The -from option specifies the file name prefix of the active database's checkpoint and transaction log files

• The -host option specifies the host name of the TimesTen system where the active database resides

• The -uid and -pwd options specify a user name and password of a TimesTen internal user defined in the active 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 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 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 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 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 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 database to the ttGrid cache grid that it is associated with by calling the ttGridAttach built-in procedure. The node number for a standby database is 2. Use the same TCP/IP ports specified for the cache agent of the active and standby databases that were specified when configuring the active database.

In the following example:

• cacheact is a name that is used to uniquely identify the active database grid node

• cachestand is a name that is used to uniquely identify the standby database grid node

• sys3 is the host name of the TimesTen system where the active database resides

• sys4 is the host name of the TimesTen system where the standby database resides

• 5003 is the TCP/IP port for the active database's cache agent process

• 5004 is the TCP/IP port for the standby database's cache agent process

Command> CALL ttGridAttach(2,'cacheact','sys3',5003,'cachestand','sys4',5004);
Command> exit

Create and configure the read-only subscriber database

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 database by running a ttRepAdmin -duplicate utility command from the read-only subscriber database system. The instance administrator user name of the standby database instance and read-only subscriber database instance must be identical.

Use the -noKeepCG option so that cache tables in the standby 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 database's checkpoint and transaction log files

• The -host option specifies the host name of the TimesTen system where the standby database resides

• The -uid and -pwd options specify a user name and password of a TimesTen internal user defined in the standby 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

Performing global queries with local joins

You can execute a global query with a local join. This means that the SELECT statement is global (selects across grid members), but the join result is local (the join resides on the local node). You may find it useful to join fact and dimension tables, to join tables that are a similar size and whose data are distributed based on the join key or to join tables of a global cache group based on a primary key or foreign key relationship. Use the GlobalLocalJoin optimizer flag to enable a global query with local join.

Global queries with local joins can join cache tables, global cache tables, noncache tables with the same definition, views and materialized views. Global queries with local joins can include sequences.

A global query executed in serializable isolation belongs to the global transaction of the SELECT statement. A global query executed in read committed isolation is executed in its own transaction on the remote nodes.

These operations in a global query are executed locally in each grid member:

• Joins

• Derived tables

• Views

• GROUP BY, HAVING, ORDER BY and DISTINCT clauses in a subquery

These operations in the main query of a global query are executed globally:

• GROUP BY clause and aggregation

• ORDER BY clause

• DISTINCT clause

• HAVING clause. This clause cannot contain a join.

Synonyms are resolved on the node where the query originates.

Before executing a global query with local join, turn autocommit off and call the ttOptSetFlag built-in procedure to set the GlobalLocalJoin optimizer flag to 1:

autocommit off;
CALL ttOptSetFlag('GlobalLocalJoin', 1)

Global queries with local joins have these restrictions:

• The query cannot include the ROWNUM expression.

• The query cannot include a set operator.

• The query cannot include the WITH clause.

• The query cannot be used with GROUPING SETS, CUBE, ROLLUP, GROUPING, GROUPING_ID, or GROUP_ID.

• The query cannot include analytic SQL functions.

• The PassThrough connection attribute must be set to 0.

• The query cannot be performed on the standby database of an active standby grid member.

Obtaining information about the location of data in the cache grid

You may wish to execute a global query without changing the location of the data.You can use SQL functions to determine which grid node contains the information and then execute a query for the information from that node.

Use these SQL functions in a global query to obtain information about the location of data in the cache grid:

• TTGRIDMEMBERID() - Returns the node ID of the node on which the query is executed.

• TTGRIDNODENAME() - Returns the name of the node on which the query is executed.

• TTGRIDUSERASSIGNEDNAME() - Returns the user-assigned name of the node on which the query is executed. The user assigns the name when the ttGridAttach built-in procedure is called. If you are using Oracle Clusterware, you do not call ttGridAttach directly and the user-assigned name is generated by TimesTen.

These functions can be used in a SELECT statement and in these clauses of a SELECT statement:

• WHERE clause

• GROUP BY clause

• ORDER BY clause

Figure 6-1 shows a cache grid whose members have user-assigned names alone1, alone2, and an active standby pair on nodes cacheact and cachestand. Queries do not retrieve data from the standby database. The standby database has the same data as the active database.

Figure 6-1 Location of data in a cache grid

Description of "Figure 6-1 Location of data in a cache grid"

The following example shows a global query that retrieves employee_id, the user-assigned node name, and the member ID from the employee table from the grid members.

autocommit off;
CALL ttOptSetFlag('GlobalProcessing', 1);
SELECT employee_id, TTGRIDUSERASSIGNEDNAME(), TTGRIDMEMBERID() FROM employees;
COMMIT;
< 100, alone1, 1>
< 101, alone2, 2>
< 102, cacheact, 3>
< 103, alone1, 1>
< 104, cacheact, 3>
...

The rows that are returned show which grid node and member owns each row of the cache instance. Subsequent queries can access the appropriate node without changing the ownership of the data. For example, execute this query on grid member cacheact, including TTGRIDUSERASSIGNEDNAME() in the query to verify that cacheact is the grid where the query is executed:

SELECT employee_id, last_name, hire_date , TTGRIDUSERASSIGNEDNAME() 
 FROM employees
 WHERE employee_id=104;
< 104, Ernst, 1991-05-21 00:00:00, cacheact >

For more information about TTGRIDMEMBERID(), TTGRIDNODENAME() and TTGRIDUSERASSIGNEDNAME(), see "Cache grid functions" in Oracle TimesTen In-Memory Database SQL Reference.