Setting STORE Attributes

The STORE attributes clause in the CREATE ACTIVE STANDBY PAIR, ALTER ACTIVE STANDBY PAIR, CREATE REPLICATION, and ALTER REPLICATION statements are used to set optional behavior for return services, compression, timeouts, durable commit behavior, and table definition checking.

For a classic replication scheme, you can also define conflict reporting at the table level.

Note:

See Using a Return Service in this book and CREATE ACTIVE STANDBY PAIR and CREATE REPLICATION in the Oracle TimesTen In-Memory Database SQL Reference.

When using classic replication schemes, the FAILTHRESHOLD and TIMEOUT attributes can be unique to a specific classic replication scheme definition. This means that these attribute settings can vary if you have applied different classic replication scheme definitions to your replicated databases. This is not true for any of the other attributes, which must be the same across all classic replication scheme definitions. For example, setting the PORT attribute for one classic replication scheme sets it for all classic replication schemes. For an example classic replication scheme that uses a STORE clause to set the FAILTHRESHOLD attribute, see Using a Return Service in a Classic Replication Scheme.

Note:

If you are using ALTER ACTIVE STANDBY PAIR to change any of the STORE attributes, you must follow the steps described in Making Other Changes to an Active Standby Pair.

The following sections describe some of the STORE attributes:

Column Definition Options for Replicated Tables

The definition for the columns of replicated tables participating in the replication scheme do not necessarily need to be identical.

If the TABLE DEFINITION CHECKING value is set to EXACT, the column definitions must be identical on the active and standby databases. This attribute enables replication of tables that are identical in their physical structure.
If the TABLE DEFINITION CHECKING value is set to RELAXED (the default), the column definitions of the replicated tables do not need to be identical. When using RELAXED, the replicated tables must have the same key definition, number of columns, column names, and column data types.

Table definition checking occurs on the standby database. Setting this attribute to RELAXED for both active and standby databases has the same effect as setting it for only the standby database.

Note:

See CREATE ACTIVE STANDBY PAIR or CREATE REPLICATION in the Oracle TimesTen In-Memory Database SQL Reference.

The TABLE DEFINITION CHECKING RELAXED attribute does not require that the physical structure of the table be identical on both master databases. For example, if tables have columns in a different order or have a different number of partitions, the data can still be replicated when using the RELAXED attribute. Thus, if you are altering your table by adding or dropping columns, you should use the RELAXED attribute. As noted in ALTER TABLE in the Oracle TimesTen In-Memory Database SQL Reference, adding columns when altering a table creates additional partitions. Dropping columns does not automatically free up the space. We recommend that any DML statement that alters the table should be run on the master and then replicated to any standby database and subscribers.

The RELAXED setting can result in slightly slower performance if it is compensating for a different physical structure. If the tables are identical in physical structure, then there is no performance impact. You can eliminate any performance issues (caused by a different physical structure, additional partitions, or extraneous space) by using the ttMigrate -r -relaxedUpgrade (only valid on databases where the table definition checking is set to RELAXED) to coalesce all additional partitions of a table into a single partition and eliminate extraneous space caused by dropped columns. If you perform this on all databases involved in the replication scheme, the resulting physical structure is identical resulting in the best performance potential.

Note:

See Check Partition Counts for the Tables in Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide.

For performance considerations of both the EXACT and RELAXED attributes for TABLE DEFINITION CHECKING, see Performance Considerations When Altering Tables That Are Replicated.

To ensure that table definition checking is set to RELAXED, stop the replication agent on the active or master database and then run an ALTER ACTIVE STANDBY PAIR or ALTER REPLICATION statement to set the table definition checking to RELAXED. Finally, use the ttRepAdmin -duplicate command to roll out these changes to the standby database and any subscribers. See ALTER ACTIVE STANDBY PAIR and ALTER REPLICATION in the Oracle TimesTen In-Memory Database SQL Reference.

The following sections provide examples for setting the table definition checking to relaxed:

Setting Table Definition Checking to Relaxed for an Active Standby Pair

This example demonstrates replicating tables that are identical in an active standby pair replication scheme. This example sets the TABLE DEFINITION CHECKING attribute to EXACT.

Create table t1 in master1 database:

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT);

Create an active standby pair replication scheme. Set TABLE DEFINITION CHECKING to EXACT for the master2 standby database.
```
Command> CREATE ACTIVE STANDBY PAIR master1, master2
        STORE master2 TABLE DEFINITION CHECKING EXACT;
```
Perform the rest of the steps to duplicate the active database to the standby database, start the replication agents on both databases, and set the state of the active database (as described in Getting Started.

The following verifies that the table definition checking is enabled to exact.

Insert a row into t1 on master1.

Command> INSERT INTO t1 VALUES (4,5,6);
1 row inserted.

Verify the results on t1 on master2.

Command> SELECT * FROM t1;
< 4, 5, 6>
1 row found.

You can alter the table definition checking for the active standby pair replication scheme to be relaxed. First, stop the replication agent on the active database before altering the active database. The following alters the dsn1 active database so the table definition checking is set to relaxed:

ALTER ACTIVE STANDBY PAIR
  ALTER STORE master1 SET TABLE DEFINITION CHECKING RELAXED;

After processing completes, use duplicate to roll out the changes to the standby database. Lastly, use duplicate to roll out the changes to any subscribers.

Examples for Classic Replication Scheme With Table Definition Checking Set to Relaxed

This provided example demonstrates replicating tables that are identical in a classic replication scheme. This example sets the TABLE DEFINITION CHECKING attribute to EXACT.

Create table t1 in dsn1 database:

CREATE TABLE ttuser.t1 (a INT PRIMARY KEY, b INT, c INT);

Create ttuser.t1 table in the dsn2 database exactly the same as in the dsn1 database.

Create replication scheme ttuser.rep1. Set TABLE DEFINITION CHECKING to EXACT for the subscriber, dsn2.

CREATE REPLICATION ttuser.rep1
       ELEMENT e1 TABLE ttuser.t1
       MASTER dsn1
       SUBSCRIBER dsn2
       STORE dsn2 TABLE DEFINITION CHECKING EXACT;

Start the replication agent for both databases. Insert a row into ttuser.t1 on dsn1.
```
Command> INSERT INTO ttuser.t1 VALUES (4,5,6);
1 row inserted.
```

Verify the results on ttuser.t1 on dsn2.

Command> SELECT * FROM ttuser.t1;
< 4, 5, 6>
1 row found.

The following example replicates tables with columns in different positions in a classic replication scheme. This example sets the TABLE DEFINITION CHECKING attribute to RELAXED.

Create table t1 in dsn1 database:

CREATE TABLE ttuser.t1 (a INT PRIMARY KEY, b INT, c INT);

Create table ttuser.t1 in dsn2 database with the columns in a different order than the columns in ttuser.t1 in dsn1 database. Note that the column names and data types are the same in both tables and a is the primary key in both tables.
```
CREATE TABLE ttuser.t1 (c INT, a INT PRIMARY KEY, b INT);
```

Create replication scheme ttuser.rep1. Set TABLE DEFINITION CHECKING to RELAXED for the subscriber, dsn2.

CREATE REPLICATION ttuser.rep1
       ELEMENT e1 TABLE ttuser.t1
       MASTER dsn1
       SUBSCRIBER dsn2
       STORE dsn2 TABLE DEFINITION CHECKING RELAXED;

Start the replication agent for both databases. Insert a row into ttuser.t1 on dsn1.
```
Command> INSERT INTO ttuser.t1 VALUES (4,5,6);
1 row inserted.
```

Verify the results on ttuser.t1 on dsn2.

Command> SELECT * FROM ttuser.t1;
< 5, 6, 4 >
1 row found.

The following example replicates tables with a different number of partitions in a classic replication scheme. When you alter a table to add columns, it increases the number of partitions in the table, even if you subsequently drop the new columns. You can use the RELAXED setting for TABLE DEFINITION CHECKING to replicate tables that have different number of partitions.

Create table ttuser.t3 on dsn1 with two columns.

CREATE TABLE ttuser.t3 (a INT PRIMARY KEY, b INT);

Create table ttuser.t3 on dsn2 with one column that is the primary key.
```
CREATE TABLE ttuser.t3 (a INT PRIMARY KEY);
```
Add a column to the table on dsn2. This increases the number of partitions to two, while the table on dsn1 has one partition.
```
ALTER TABLE ttuser.t3 ADD COLUMN b INT;
```

Create the replication scheme on both databases.

CREATE REPLICATION reppart
       ELEMENT e2 TABLE ttuser.t3
       MASTER dsn1
       SUBSCRIBER dsn2
       STORE dsn2 TABLE DEFINITION CHECKING RELAXED;

Start the replication agent for both databases. Insert a row into ttuser.t3 on dsn1.
```
Command> INSERT INTO ttuser.t3 VALUES (1,2);
1 row inserted.
```

Verify the results in ttuser.t3 on dsn2.

Command> SELECT * FROM ttuser.t3;
< 1, 2 >
1 row found.

You can alter the table definition checking for a classic replication scheme to relaxed. First, stop the replication agent on the master database before altering the replication scheme on it. The following example alters the dsn1 master database so the table definition checking is set to relaxed:

ALTER REPLICATION reppart
  ALTER STORE dsn1 SET TABLE DEFINITION CHECKING RELAXED;

After processing completes, use duplicate to roll out the changes to the standby master. Lastly, use duplicate to roll out the changes to any subscribers.

Compressing Replicated Traffic

If you are replicating over a low-bandwidth network, or if you are replicating massive amounts of data, you can set the COMPRESS TRAFFIC attribute to reduce the amount of bandwidth required for replication.

The COMPRESS TRAFFIC attribute compresses the replicated data from the database specified by the STORE parameter in the CREATE ACTIVE STANDBY PAIR, ALTER ACTIVE STANDBY PAIR, CREATE REPLICATION or ALTER REPLICATION statements. TimesTen does not compress traffic from other databases.

Though the compression algorithm is optimized for speed, enabling the COMPRESS TRAFFIC attribute affects replication throughput and latency.

For example, to compress replicated traffic from active database dsn1 and leave the replicated traffic from standby database dsn2 uncompressed, the CREATE ACTIVE STANDBY PAIR statement looks like:

CREATE ACTIVE STANDBY PAIR dsn1 ON "host1", dsn2 ON "host2"
  SUBSCRIBER dsn3 ON "host3"
  STORE dsn1 ON "host1" COMPRESS TRAFFIC ON;

To compress the replicated traffic from both the active database dsn1 and the standby database dsn2, use:

CREATE ACTIVE STANDBY PAIR dsn1 ON "host1", dsn2 ON "host2"
   SUBSCRIBER dsn3 ON "host3"
 STORE dsn1 ON "host1" COMPRESS TRAFFIC ON
 STORE dsn2 ON "host2" COMPRESS TRAFFIC ON;

You can compress replicated traffic from just one database dsn1 in a classic replication scheme while leaving the replicated traffic from other databases (such as dsn2) uncompressed. Use the CREATE REPLICATION statement as follows:

CREATE REPLICATION repscheme
 ELEMENT d1 DATASTORE
    MASTER dsn1 ON host1
    SUBSCRIBER dsn2 ON host2
 ELEMENT d2 DATASTORE
    MASTER dsn2 ON host2
    SUBSCRIBER dsn1 ON host1
 STORE dsn1 ON host1 COMPRESS TRAFFIC ON;

To compress the replicated traffic between both the dsn1 and dsn2 databases in a classic replicatoin scheme, use:

CREATE REPLICATION scheme
 ELEMENT d1 DATASTORE
    MASTER dsn1 ON host1
    SUBSCRIBER dsn2 ON host2
 ELEMENT d2 DATASTORE
    MASTER dsn2 ON host2
    SUBSCRIBER dsn1 ON host1
 STORE dsn1 ON host1 COMPRESS TRAFFIC ON
 STORE dsn2 ON host2 COMPRESS TRAFFIC ON;

Port Assignments

The PORT parameter for the STORE attribute of the CREATE ACTIVE STANDBY PAIR and CREATE REPLICATION statements set the port number used by a database to listen for updates from another database.

In an active standby pair, the standby database listens for updates from the active database. Read-only subscribers listen for updates from the standby database.
In a classic replication scheme, the subscribers listen for updates from the master database. Setting the PORT attribute for one classic replication scheme sets it for all classic replication schemes.

Static port assignments are recommended. If no PORT attribute is specified, the TimesTen daemon dynamically selects the port. When ports are assigned dynamically for the replication agents, then the ports of the TimesTen daemons have to match as well.

Note:

You must assign static ports if you want to do online upgrades.

When statically assigning ports, it is important to specify the full host name, DSN and port in the STORE attribute.

Example of assigning static ports for an active standby pair

CREATE ACTIVE STANDBY PAIR dsn1 ON "host1", dsn2 ON "host2"
   SUBSCRIBER dsn3 ON "host3"
 STORE dsn1 ON "host1" PORT 16080
 STORE dsn2 ON "host2" PORT 16083
 STORE dsn3 ON "host3" PORT 16084;

Example of assigning static ports for a classic replication scheme

CREATE REPLICATION repscheme
 ELEMENT el1 TABLE ttuser.tab
    MASTER dsn1 ON host1
    SUBSCRIBER dsn2 ON host2
 ELEMENT el2 TABLE ttuser.tab
    MASTER dsn2 ON host2
    SUBSCRIBER dsn1 ON host1
 STORE dsn1 ON host1 PORT 16080
 STORE dsn2 ON host2 PORT 16083;

Setting Wait Timeout for Response From Remote Replication Agents

The TIMEOUT store attribute sets the maximum number of seconds that the replication agent waits for a response from any remote replication agents.

We recommend that the default timeout (120 seconds) is used if you have any large transactions. The replication agent scales the timeout based on the size of the transaction in order to accommodate any large transactions that could potentially cause a delayed response from the remote replication agent. Automatic scaling by the replication agent is disabled if the user sets the TIMEOUT to less than or equal to 60 seconds.

Note:

If you experience repeated timeouts and the error log shows that multiple transmitter and receiver threads restart, then the transaction may be larger than can be scaled by the replication agent with the current timeout value. Continue to increase the timeout value until replication can progress for the transaction.

The following example creates an active standby pair whose master databases are rep1 and rep2. There is one subscriber, rep3. The type of replication is RETURN RECEIPT. The statement also sets PORT and TIMEOUT attributes for the master databases. The TIMEOUT attribute is set to 80 seconds for both the active and standby masters.

CREATE ACTIVE STANDBY PAIR rep1, rep2 RETURN RECEIPT 
   SUBSCRIBER rep3
 STORE rep1 PORT 21000 TIMEOUT 80
 STORE rep2 PORT 22000 TIMEOUT 80;

Setting the Transaction Log Failure Threshold

You can establish a threshold value that, when exceeded, sets an unavailable database to the failed state before the available transaction log space is exhausted.

In an active standby pair, if the transaction log threshold is exceeded, sets an unavailable standby database or a read-only subscriber to the failed state before the available transaction log space is exhausted. Set the transaction log threshold by specifying the STORE clause with a FAILTHRESHOLD value in the CREATE ACTIVE STANDBY PAIR or ALTER ACTIVE STANDBY PAIR statements.

If an active database sets the standby or read-only subscriber database to the failed state, it drops all of the data for the failed database from its transaction log and transmits a message to the failed database. If the active replication agent can communicate with the replication agent of the failed database, then the message is transmitted immediately. Otherwise, the message is transmitted when the connection is reestablished.
In a classic replication scheme, if the transaction log threshold is exceeded, sets an unavailable subscriber to the failed state before the available transaction log space is exhausted. Set the transaction log threshold by specifying the STORE clause with a FAILTHRESHOLD value in the CREATE REPLICATION or ALTER REPLICATION statements. For an example, see Using a Return Service in a Classic Replication Scheme.

If a master database sets the subscriber database to the failed state, it drops all of the data for the failed subscriber from its transaction log and transmits a message to the failed subscriber database. If the master replication agent can communicate with the subscriber replication agent, then the message is transmitted immediately. Otherwise, the message is transmitted when the connection is reestablished.

However, after receiving the message from the master, if the subscriber is configured for bidirectional replication or to propagate updates to other subscribers, it does not transmit any further updates, because its replication state has been compromised.

The default threshold value is 0, which means "no limit." See Setting Connection Attributes for Logging for details about transaction log failure threshold values.

Any application that connects to the failed database receives a tt_ErrReplicationInvalid (8025) warning indicating that the database has been marked failed by a replication peer. Once the database has been informed of its failed status, its state on the active or master database is changed from failed to stop.

Note:

For more information about database states, see Table 11-1.

An application can use the ODBC SQLGetInfo function to check if the database the application is connected to has been set to the failed state, as described in Subscriber Failures.

Suspending or Resuming Classic Replication in Response to Conflicts

With classic replication, you can specify the number of replication conflicts per second at the table level at which conflict reporting is suspended and the number of conflicts per second at which conflict reporting resumes with the CONFLICT REPORTING SUSPEND and CONFLICT REPORTING RESUME attributes.

See Resolving Replication Conflicts.