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 toEXACT
, 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 toRELAXED
(the default), the column definitions of the replicated tables do not need to be identical. When usingRELAXED
, 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
inmaster1
database:CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT);
-
Create an active standby pair replication scheme. Set
TABLE DEFINITION CHECKING
toEXACT
for themaster2
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.
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
indsn1
database:CREATE TABLE ttuser.t1 (a INT PRIMARY KEY, b INT, c INT);
-
Create
ttuser.t1
table in thedsn2
database exactly the same as in thedsn1
database. -
Create replication scheme
ttuser.rep1
. SetTABLE DEFINITION CHECKING
toEXACT
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
ondsn1
.Command> INSERT INTO ttuser.t1 VALUES (4,5,6); 1 row inserted.
-
Verify the results on
ttuser.t1
ondsn2
.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
indsn1
database:CREATE TABLE ttuser.t1 (a INT PRIMARY KEY, b INT, c INT);
-
Create table
ttuser.t1
indsn2
database with the columns in a different order than the columns inttuser.t1
indsn1
database. Note that the column names and data types are the same in both tables anda
is the primary key in both tables.CREATE TABLE ttuser.t1 (c INT, a INT PRIMARY KEY, b INT);
-
Create replication scheme
ttuser.rep1
. SetTABLE DEFINITION CHECKING
toRELAXED
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
ondsn1
.Command> INSERT INTO ttuser.t1 VALUES (4,5,6); 1 row inserted.
-
Verify the results on
ttuser.t1
ondsn2
.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.
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 theSTORE
clause with aFAILTHRESHOLD
value in theCREATE ACTIVE STANDBY PAIR
orALTER 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 theSTORE
clause with aFAILTHRESHOLD
value in theCREATE REPLICATION
orALTER 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.