Asynchronous WriteThrough (AWT) Cache Group

An Asynchronous WriteThrough (AWT) cache group enforces a caching behavior where committed changes on the TimesTen cache tables are automatically and asynchronously propagated to the cached Oracle Database tables.

See Figure 4-6.

Only TimesTen Classic supports AWT cache groups.

Note:

You should avoid running DML statements on Oracle Database tables cached in an AWT cache group. This can result in an error condition. See Restrictions With AWT Cache Groups.

Figure 4-6 AWT Cache Group

Description of "Figure 4-6 AWT Cache Group"

The transaction commit on a TimesTen database occurs asynchronously from the commit on an Oracle database. This enables an application to continue issuing transactions on a TimesTen database without waiting for the Oracle Database transaction to complete. However, your application cannot ensure when the transactions are completed on an Oracle database.

You can update cache tables in an AWT cache group even if the Oracle database is unavailable. When the Oracle database returns to operation, updates that were committed on the cache tables while the Oracle database was unavailable are automatically propagated to the cached Oracle Database tables.

Note:

When TimesTen manages operations for AWT cache groups, it connects to the Oracle database using the Oracle cache administration user name and password set with the ttCacheUidPwdSet built-in procedure. For more details on ttCacheUidPwdSet, see Registering the Cache Administration User Name and Password.

Since an AWT cache group propagates data from the TimesTen database to the Oracle database, any data modified by the user in the cached tables on the Oracle database is not automatically uploaded from the Oracle database to the TimesTen database. In this case, you must manually run a REFRESH CACHE GROUP SQL statement to have any changes done to the Oracle database transmitted to the TimesTen database.

Processing of any REFRESH CACHE GROUP or UNLOAD CACHE GROUP statement for an AWT cache group waits until updates on any of the rows modified on the TimesTen database have been propagated to the Oracle database.

On the Oracle Database:

The following is an example of a definition of the Oracle database table that is to be cached in an AWT cache group. The Oracle database table is owned by the schema user sales.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

When the cached tables are a part of an AWT cache group, then the Oracle cache administration user must be granted the SELECT, INSERT, UPDATE, and DELETE privileges on any cached tables. In this example, the table is the sales.customer table.

See Required Privileges for Cache Administration User for Cache Operations for all required privileges for different activities.

On the Oracle database as an administrator, grant the following privileges:

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.customer TO cacheadmin;

On the TimesTen database:

Connect as the TimesTen cache administraiton user. Use the CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement to create an AWT cache group.

The following statement creates an AWT cache group new_customers that caches the sales.customer table:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM sales.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num));

The following sections describe configuration, behavior, and management for AWT cache groups:

Starting and Stopping the Replication Agent

Performing asynchronous writethrough operations requires that the replication agent be running on the TimesTen database that contains AWT cache groups.

Running a CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement creates a replication scheme that enables committed changes on the TimesTen cache tables to be asynchronously propagated to the cached Oracle Database tables.

After you have created AWT cache groups, start the replication agent on the TimesTen database by calling the ttRepStart built-in procedure as the cache administration user. This connects using an Oracle Wallet that contains the passwords for both cache administration users.

Connect as the TimesTen cache administration user and provide credentials in the Oracle Wallet.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> call ttRepStart;

It can also be started from a command line by running a ttAdmin -repStart utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -repStart cache1

The replication agent does not start unless there is at least one AWT cache group or replication scheme in the TimesTen database.

If the replication agent is running, it must be stopped before you can issue another CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement or a DROP CACHE GROUP statement on an AWT cache group.

You can stop the replication agent by calling the ttRepStop built-in procedure as the cache administration user:

Command> call ttRepStop;

You can also stop the replication agent from a command line with the ttAdmin -repStop utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -repStop cache1

Setting a Replication Agent Start Policy

Performing asynchronous writethrough operations requires that the replication agent be running on the TimesTen database that contains AWT cache groups. You can set a replication agent start policy to determine how and when the replication agent process starts on a TimesTen database.

The default start policy is manual which means the replication agent must be started manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command. To manually stop a running replication agent process, call the ttRepStop built-in procedure or run a ttAdmin -repStop utility command.

The start policy can be set to always so that the replication agent starts automatically when the TimesTen main daemon process starts. With the always start policy, the replication agent cannot be stopped when the main daemon is running unless the start policy is changed to either manual or norestart and then a manual stop is issued by calling the ttRepStop built-in procedure or running a ttAdmin -repStop utility command.

With the manual and always start policies, the replication agent automatically restarts after a failure such as a database invalidation.

The start policy can be set to norestart which means the replication agent must be started manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command, and stopped manually by calling the ttRepStop built-in procedure or running a ttAdmin -repStop utility command.

With the norestart start policy, the replication agent does not automatically restart after a failure such as a database invalidation. You must restart the replication agent manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command.

Perform the following to set the replication agent start policy:

Before you set a replication agent start policy, grant the ADMIN privilege to the TimesTen cache administration user as the instance administrator.
```
% ttIsql cache1
Command> GRANT ADMIN TO cacheadmin;
Command> exit
```
Set the replication agent start policy by calling the ttRepPolicySet built-in procedure as the TimesTen cache administration user:
```
% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> call ttRepPolicySet('manual');
Command> exit
```
Alternately, set the replication agent start policy from a command line by running a ttAdmin -repPolicy utility command as a TimesTen external user with the ADMIN privilege:
```
% ttAdmin -repPolicy always cache1
```

Monitoring Propagation of Transactions to the Oracle Database

Since the AWT cache group uses the replication agent to asynchronously propagate transactions to the Oracle database, these transactions remain in the transaction log buffer and transaction log files until the replication agent confirms they have been fully processed by the Oracle database.

You can monitor the propagation for these transactions with the ttLogholds built-in procedure.

When you call the ttLogHolds built-in procedure, the description field contains _ORACLE to identify the transaction log hold for the AWT cache group propagation.

Command> call ttLogHolds();
< 0, 18958336, Checkpoint                    , cache1.ds0 >
< 0, 19048448, Checkpoint                    , cache1.ds1 >
< 0, 19050904, Replication                   , ADC6160529:_ORACLE >
3 rows found.

See the Show Replicated Log Records section in the Oracle TimesTen In-Memory Database Replication Guide.

You can also improve performance by configuring parallel propagation to the Oracle Database. See Improving AWT Throughput With Parallel Propagation to the Oracle Database.

Disabling Propagation of Committed Changes

If there are updates from DML statements that you do not want propagated to the Oracle database, then you can disable propagation of committed changes (as a result of running DML statements) within the current transaction to the Oracle database by setting the flag in the ttCachePropagateFlagSet built-in procedure to zero.

After the flag is set to zero, the effects of running any DML statements are never propagated to the back-end Oracle database. Thus, these updates exist only on the TimesTen database. You can then re-enable propagation by resetting the flag to one with the ttCachePropagateFlagSet built-in procedure. After the flag is set back to one, propagation of all committed changes to the Oracle database resumes. The propagation flag automatically resets to one after the transaction is committed or rolled back. See ttCachePropagateFlagSet in the Oracle TimesTen In-Memory Database Reference.

Configuring Parallel Propagation to the Oracle Database

To improve throughput for an AWT cache group, you can configure multiple threads that act in parallel to propagate and apply transactional changes to the Oracle database.

Parallel propagation enforces transactional dependencies and applies changes in AWT cache tables to Oracle Database tables in commit order. See Improving AWT Throughput With Parallel Propagation to the Oracle Database.

What an AWT Cache Group Does and Does Not Guarantee

An AWT cache group comes with some guarantees.

An AWT cache group can guarantee that:

No transactions are lost because of communication failures between the TimesTen and Oracle databases.
If the replication agent is not running or loses its connection to the Oracle database, automatic propagation of committed changes on the TimesTen cache tables to the cached Oracle Database tables resumes after the agent restarts or reconnects to the Oracle database.
Transactions are committed in the Oracle database in the same order they were committed in the TimesTen database.

An AWT cache group cannot guarantee that:

All transactions committed successfully in the TimesTen database are successfully propagated to and committed in the Oracle database. Execution errors on the Oracle database cause the transaction in the Oracle database to be rolled back. For example, an update on the Oracle database may fail because of a unique constraint violation. Transactions that contain execution errors are not retried.

Execution errors are considered permanent errors and are reported to the TimesTenDatabaseFileName.awterrs file that resides in the same directory as the TimesTen database's checkpoint files. See Reporting Oracle Database Permanent Errors for AWT Cache Groups.
The absolute order of Oracle Database updates is preserved because TimesTen does not resolve update conflicts. The following are some examples:
- In two separate TimesTen databases (DB1 and DB2), different AWT cache groups cache the same Oracle Database table. An update is committed on the cache table in DB1. An update is then committed on the cache table in DB2. The two cache tables reside in different TimesTen databases and cache the same Oracle Database table. Because the writethrough operations are asynchronous, the update from DB2 may get propagated to the Oracle database before the update from DB1, resulting in the update from DB1 overwriting the update from DB2.
- An update is committed on a cache table in an AWT cache group. The same update is committed on the cached Oracle Database table using a passthrough operation. The cache table update, which is automatically and asynchronously propagated to the Oracle database, may overwrite the passed through update that was processed directly on the cached Oracle Database table depending on when the propagated update and the passed through update is processed on the Oracle database. For this and other potential error conditions, TimesTen recommends that you do not run DML statements directly against Oracle Database tables cached in an AWT cache group. For more information, see Restrictions With AWT Cache Groups.

Restrictions With AWT Cache Groups

Certain restrictions apply when using an AWT cache group.

The following restrictions apply when using an AWT cache group:

Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.

See ON DELETE CASCADE Cache Table Attribute.

See Creating a Hash Index on the Primary Key Columns of the Cache Table.
A FLUSH CACHE GROUP statement cannot be issued on the cache group.

See Flushing a User Managed Cache Group.
The cache table definitions cannot contain a WHERE clause.

See Using a WHERE Clause.
A TRUNCATE TABLE statement cannot be issued on the cache tables.
AWT cache groups cannot cache Oracle Database views or materialized views.

The replication agent must be stopped before creating or dropping an AWT cache group.

See Starting and Stopping the Replication Agent.
Committed changes on the TimesTen cache tables are not propagated to the cached Oracle Database tables unless the replication agent is running.
To create an AWT cache group, the length of the absolute path name of the TimesTen database cannot exceed 248 characters.
You should avoid running DML statements on Oracle Database tables cached in an AWT cache group. This could result in an error condition. Any insert, update, or delete operation on the cached Oracle Database table can negatively affect the operations performed on TimesTen for the affected rows. TimesTen does not detect or resolve update conflicts that occur on the Oracle database. Committed changes made directly on a cached Oracle Database table may be overwritten by a committed update made on the TimesTen cache table when the cache table update is propagated to the Oracle database. In addition, deleting rows on the cached Oracle Database table could cause an empty update if TimesTen tries to update a row that no longer exists.

To ensure that not all data is restricted from DML statements on Oracle Database, you can partition the data on Oracle Database to separate the data that is to be included in the AWT cache group from the data to be excluded from the AWT cache group.
TimesTen performs deferred checking when determining whether a single SQL statement causes a constraint violation with a unique index.

For example, suppose there is a unique index on a cached Oracle Database table's NUMBER column, and a unique index on the same NUMBER column on the TimesTen cache table. There are five rows in the cached Oracle Database table and the same five rows in the cache table. The values in the NUMBER column range from 1 to 5.

An UPDATE statement is issued on the cache table to increment the value in the NUMBER column by 1 for all rows. The operation succeeds on the cache table but fails when it is propagated to the cached Oracle Database table.

This occurs because TimesTen performs the unique index constraint check at the end of the statement's processing after all the rows have been updated. The Oracle database, however, performs the constraint check each time after a row has been updated.

Therefore, when the row in the cache table with value 1 in the NUMBER column is changed to 2 and the update is propagated to the Oracle database, it causes a unique constraint violation with the row that has the value 2 in the NUMBER column of the cached Oracle Database table.

Reporting Oracle Database Permanent Errors for AWT Cache Groups

If transactions are not successfully propagated to and committed in the Oracle database, then the permanent errors cause the transaction in the Oracle database to be rolled back.

For example, an update on the Oracle database may fail because of a unique constraint violation. Transactions that contain permanent errors are not retried.

Permanent errors are always reported to the TimesTenDatabaseFileName.awterrs text file that resides in the same directory as the TimesTen database checkpoint files. See Oracle Database Errors Reported by TimesTen for AWT in the Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide for information about the contents of this file.

You can configure TimesTen to report these errors in both ASCII and XML formats with the ttCacheConfig built-in procedure.

Note:

Do not pass in any values to the tblOwner and tblName parameters for ttCacheConfig as they are not applicable to setting the format for the errors file.

To configure TimesTen to report permanent errors to only the TimesTenDatabaseFileName.awterrs text file, call the ttCacheConfig built-in procedure with the ASCII parameter. This is the default.
```
Command> call ttCacheConfig('AwtErrorXmlOutput',,,'ASCII');
```
To configure TimesTen to report permanent errors to both the TimesTenDatabaseFileName.awterrs text file as well as to an XML file named TimesTenDatabaseFileName.awterrs.xml, call the ttCacheConfig built-in procedure with the XML parameter.
```
Command> call ttCacheConfig('AwtErrorXmlOutput',,,'XML');
```

Note:

Before calling ttCacheConfig to direct permanent errors to the XML file, you must first stop the replication agent. Then, restart the replication agent after the built-in procedure completes.

See ttCacheConfig in the Oracle TimesTen In-Memory Database Reference.

When you configure error reporting to be reported in XML format, the following two files are generated when Oracle Database permanent errors occur:

TimesTenDatabaseFileName.awterrs.xml contains the Oracle Database permanent error messages in XML format.

TimesTenDatabaseFileName.awterrs.dtd is the file that contains the XML Document Type Definition (DTD), which is used when parsing the TimesTenDatabaseFileName.awterrs.xml file.

The XML DTD, which is based on the XML 1.0 specification, is a set of markup declarations that describes the elements and structure of a valid XML file containing a log of errors. The XML file is encoded using UTF-8. The following are the elements for the XML format.

Note:

For more information on reading and understanding XML Document Type Definitions, see http://www.w3.org/TR/REC-xml/.

<!ELEMENT ttawterrorreport (awterrentry*) >
<!ELEMENT awterrentry(header, (failedop)?, failedtxn) >
<!ELEMENT header (time, datastore, oracleid, transmittingagent, errorstr,
 (ctn)?, (batchid)?, (depbatchid)?) >
<!ELEMENT failedop (sql) >
<!ELEMENT failedtxn ((sql)+) >
<!ELEMENT time (hour, min, sec, year, month, day) >
<!ELEMENT hour (#PCDATA) >
<!ELEMENT min (#PCDATA) >
<!ELEMENT sec (#PCDATA) >
<!ELEMENT year (#PCDATA) >
<!ELEMENT month (#PCDATA) >
<!ELEMENT day (#PCDATA) >
<!ELEMENT datastore (#PCDATA) >
<!ELEMENT oracleid (#PCDATA) >
<!ELEMENT transmittingagent (transmitingname, pid, threadid) >
<!ELEMENT pid (#PCDATA) >
<!ELEMENT threadid (#PCDATA) >
<!ELEMENT transmittingname (#PCDATA) >
<!ELEMENT errorstr (#PCDATA) >
<!ELEMENT ctn (timestamp, seqnum) >
<!ELEMENT timestamp(#PCDATA) >
<!ELEMENT seqnum(#PCDATA) >
<!ELEMENT batchid(#PCDATA) >
<!ELEMENT depbatchid(#PCDATA) >
<!ELEMENT sql(#PCDATA) >