The following sections describe the return service options, STORE
attributes, and network operations that can be configured for both active standby pairs and classic replication (involving master and subscribers). Any differences for one replication scheme over the other are detailed within each section.
Databases that replicate to each other must have the same DatabaseCharacterSet
data store attribute. TimesTen Classic does not perform any character set conversion between replicated databases.
You must make sure that the underlying data type for each replicated column is the same on each node when you replicate between databases.
See "Managing the transaction log on a replicated database" for recommendations for managing the replication log files.
If you want to configure parallel replication, see "Configuring parallel replication" for information about setting the ReplicationParallelism
and ReplicationApplyOrdering
data store attributes.
Databases must be hosted on systems that have two or more CPUs to take advantage of setting this attribute to 2.
By default, replication is performed with a single thread where the nodes in a replication scheme have one log reader, or transmitter thread, on the source database, and one applying thread, or receiving thread, on the target database. You can increase your performance by configuring parallel replication, which configures multiple threads for sending updates from the source database to the target database and for applying the updates on the target database. These threads act in parallel to replicate and apply transactional changes to nodes in a replication scheme. By default, parallel replication enforces transactional dependencies and applies changes in commit order; however, you can disable enforcement of the commit order.
Note:
If you enable parallel replication, you cannot execute both DDL and DML statements in the same transaction.Parallel replication options:
Automatic parallel replication: Parallel replication over multiple threads that automatically enforces transactional dependencies and all changes applied in commit order. This is the default.
Automatic parallel replication with disabled commit dependencies: Parallel replication over multiple threads that automatically enforces transactional dependencies, but does not enforce transactions to be committed in the same order on the subscriber database as on the master database.
These options are configured with the ReplicationApplyOrdering
and ReplicationParallelism
data store creation attributes, which must be set when the database is created.
Note:
All databases within the replication scheme that use parallel replication must be configured identically with the same type of parallel replication and the same number of threads or tracks.The only time you can have different values for parallel replication attributes is during an upgrade.
For details, see "Upgrades when using parallel replication" in the Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.
The following sections describe the options for parallel replication:
Automatic parallel replication enables you to configure multiple threads that act in parallel to replicate and apply transactional changes to nodes in either a classic or an active standby pair replication scheme. Automatic parallel replication enforces transactional dependencies and applies changes in commit order.
Enable automatic parallel replication by setting these data store attributes at database creation time:
Set ReplicationParallelism
to a number from 2 to 32. The number cannot exceed half the value of LogBufParallelism
. This number indicates the number of transmitter threads on the source database and the number of receiver threads on the target database. However, if you are using single-threaded replication, set ReplicationParallelism
to 1, which is also the default.
Note:
IfReplicationParallelism
is greater than 1, the LogBufParallelism
first connection attribute must be an integral multiple of ReplicationParallelism
.If the replication scheme is an active standby pair that replicates AWT cache groups, the settings for ReplicationApplyOrdering
, ReplicationParallelism
, and the CacheAWTParallelism
data store attributes determine how many threads are used to apply changes in the TimesTen cache tables to the corresponding Oracle database tables. See "Configuring parallel propagation to Oracle Database tables" in Oracle TimesTen Application-Tier Database Cache User's Guide for more information.
For more information on these data store attributes, see "ReplicationParallelism," "ReplicationApplyOrdering," and "LogBufParallelism" in the Oracle TimesTen In-Memory Database Reference.
In order to enforce transactional dependencies and ensure that changes are applied in commit order, automatic parallel replication normally tracks:
Begin dependencies: Operations that force one transaction to execute before another, such as an insert of a row followed by a delete of that same row.
Commit dependencies: Order in which transactions are committed so that they are committed on a subscriber in the same order as on the master database.
While parallel replication improves performance by utilizing multiple threads, you can further increase throughput performance when using automatic parallel replication if the transactions do not require enforcement of commit dependencies. That is, if your application has predictable transactional dependencies and does not require the commit order on the target database be the same as the order on the source database, you can relax the enforcement of the commit dependencies and still maintain transactional correctness. For example, if separate transactions are working on separate tables, you do not need to enforce commit dependencies.
By relaxing the requirement for tracking commit dependencies, you can improve the performance for automatic parallel replication. When you do not enforce commit dependencies, all non-DDL transactions may commit on the subscribers in a different order than how they were originally executed on the master. Begin dependencies are always enforced to prevent apply order anomalies.
You can only disable commit dependencies for automatic parallel replication for an active standby pair that uses asynchronous replication and does not contain cache groups. You can set the following data store attributes at database creation time:
Set the ReplicationApplyOrdering=2
before you create the TimesTen database.
Set ReplicationParallelism
to a number from 2 to 32. The number cannot exceed half the value of LogBufParallelism
. This number indicates the number of transmitter threads on the source database and the number of receiver threads on the target database. However, if you are using single-threaded replication, set ReplicationParallelism
to 1, which is also the default.
Note:
IfReplicationParallelism
is greater than 1, the LogBufParallelism
first connection attribute must be an integral multiple of ReplicationParallelism
.However, while your performance improves, this option requires the use of additional space in the temporary region of 16 MB plus an additional 16 MB for each configured replication track (ReplicationParallelism
connection attribute setting). For example, if the ReplicationParallism
connection attribute is set to 10, then this feature requires 16 MB + 160 MB = 176 MB of additional temporary region space.
When you use automatic parallel replication and disable commit dependencies, some of the tracks may move ahead of the others. This is known as a drift between the parallel replication tracks. You can limit the amount of drift between the replication tracks by setting the ParReplMaxDrift
configuration parameter within the ttDbConfig
built-in procedure.
Call ttDbConfig("ParReplMaxDrift", "30");
The example sets the allowed drift to 30 seconds between the replication tracks, after which TimesTen Classic causes all replication tracks to catch up to each other. For more details, see "ttDBConfig" in the Oracle TimesTen In-Memory Database Reference.
In general, automatic parallel replication decides over which thread (track) to replicate each transaction from the master to the subscriber. Thus, you do not have to manually decide how to divide work across different tracks.
However, with dependent transactions, you may achieve better performance by manually assigning them to the same track. Thus, the application has the option to specify which track a transaction belongs to when the transaction starts on the source database with either the ReplicationTrack
connection attribute or the ALTER SESSION SET REPLICATION_TRACK
statement. After which, all transactions for this connection use this track. The transactions for this track are applied in the order in which they are received on the target database, but commit order is not maintained for transactions across the different tracks. If you decide to specify the track for a transaction, then make sure that you distribute the workload evenly across the tracks.
Updates that should be applied in order on the receiving side should use the same track. You can spread operations on a table across separate tracks based on key values. For example, if you have a telecommunications billing application, you can use hash of the account number to set the track and send all transactions for each account on a separate track.
TimesTen Classic still computes and enforces dependencies to make sure that dependent transactions are applied in the correct order on the receiving side.
The application assigns transactions to tracks by one of these methods:
Set the ReplicationTrack
general connection attribute to a nonzero number. All transactions issued by the connection are assigned to this track. The value can be any number. TimesTen maps the ReplicationTrack
number for this connection to one of the available parallel replication threads. Thus, the application can use any number to group transactions that should be applied in order. See "ReplicationTrack" in Oracle TimesTen In-Memory Database Reference.
Use the ALTER SESSION
SQL statement to set the replication track number for the current connection. See "ALTER SESSION" in Oracle TimesTen In-Memory Database SQL Reference.
Use the TT_REPLICATION_TRACK
ODBC connection option for the SQLSetConnectOption
ODBC function. See "Features for use with replication" in Oracle TimesTen In-Memory Database C Developer's Guide
Use the setReplicationTrack()
method of the TimesTenConnection
JDBC class. See "Features for use with replication" in Oracle TimesTen In-Memory Database Java Developer's Guide
Use the ttConfiguration
built-in procedure to return the replication track number for the current connection. Select from the SYS.GV$LOG_HOLDS
or SYS.V$LOG_HOLDS
system views or call the ttLogHolds
built-in procedure to verify that multiple tracks are being used.
This section includes these topics:
A dedicated subdaemon thread writes the contents of the log buffer to the file system periodically. These write operations may be synchronous or buffered. The subdaemon thread ensures that the system I/O buffer never fills up with more transaction log data than the value of the LogFileSize
first connection attribute without being synchronized to the log buffer.
If the database is configured with LogFlushMethod=2
, then all write operations to the file system are synchronous write operations and the data is durably written to the file system before the write call returns. If the database is configured with LogFlushMethod=1
, then the write operations are buffered unless there is a specific request from an application for synchronous write operations.
In addition to the periodic write operations, an application can also trigger the subdaemon thread to write the buffer contents to the file system. The following are cases where the application triggers a synchronous write operation to the file system:
When a transaction that requested a durable commit is committed. A transaction can request a durable commit by calling the ttDurableCommit
built-in procedure or by having the DurableCommits
connection attribute set to 1.
When the replication agent sends a batch of transactions to a subscriber and the master has been configured for replication with the TRANSMIT DURABLE
attribute (the default).
When the replication agent periodically executes a durable commit, whether the master database is configured with TRANSMIT DURABLE
or not.
Transactions are also written to the file system durably when durable commits are configured as part of the return service failure policies and a failure has occurred.
The size of the log buffer has no influence on the ability of TimesTen to write data to the file system under any of the circumstances listed above.
In databases that do not use replication, Transaction Log API (XLA), cache groups or incremental backup, unneeded records in the log buffer and unneeded transaction log files are purged each time a checkpoint is initiated, either by the automatic background checkpointing thread or by an application's call to the ttCkpt
or ttCkptBlocking
built-in procedures. In a replicated database, transactions remain in the log buffer and transaction log files until the master replication agent confirms they have been fully processed by the subscriber. Only then can the master consider purging them from the log buffer and transaction log files.
A master database transaction log can grow much larger than it would on an unreplicated database if there are changes to its subscriber state. When the subscriber is in the start
state, the master can purge logged data after it receives confirmation that the information has been received by the subscriber. However, if a subscriber becomes unavailable or is in the pause
state, the log on the master database cannot be flushed and the space used for logging can be exhausted. When the log space is exhausted, subsequent updates on the master database are aborted. Select from the SYS.GV$LOG_HOLDS
or SYS.V$LOG_HOLDS
system views or call the ttLogHolds
built-in procedure to get information about replication log holds.
Note:
For more information about transaction log growth, see "Monitoring accumulation of transaction log files" in Oracle TimesTen In-Memory Database Operations Guide.For more information on log holds, see "Monitor replication from the replication log holds" in this book, "SYS.GV$LOG_HOLDS" or "SYS.V$LOG_HOLDS" in the Oracle TimesTen In-Memory Database SQL Reference or "ttLogHolds" in the Oracle TimesTen In-Memory Database Reference
LogBufMB
specifies the maximum size of the in-memory log buffer in megabytes. This buffer is flushed to a transaction log file when it becomes full. The minimum size for LogBufMB
is 8 times the value of LogBufParallelism
.
You need to establish enough space for the transaction log files. There are two settings that control the amount of space used by the log:
The LogFileSize
setting in the DSN specifies the maximum size of a transaction log file. If logging requirements exceed this value, additional transaction log files with the same maximum size are created. For best performance, set LogBufMB
and LogFileSize
to their maximum values.
The log failure threshold setting specifies the maximum number of transaction log files allowed to accumulate before the master assumes a subscriber has failed. The threshold value is the number of transaction log files between the most recently written to transaction log file and the earliest transaction log file being held for the subscriber. For example, if the last record successfully received by all subscribers was in Log File 1 and the last log record written to the file system is at the beginning of Log File 4, then replication is at least 2 transaction log files behind (the contents of Log Files 2 and 3). If the threshold value is 2, then the master sets the subscriber to the failed
state after detecting the threshold value had been exceeded. This may take up to 10 seconds. See "Setting the transaction log failure threshold" for more information.
Because transactions are logged to the file system, you can use bookmarks to detect the log record identifiers of the update records that have been replicated to subscribers and those that have been written to the file system. To view the location of the bookmarks for the subscribers associated with masterDSN
, use the ttBookmark
built-in procedure, as described in "Show replicated log records".
If a subscriber goes down and then comes back up before the threshold is reached, then replication automatically "catches up" as the committed transactions in the transaction log files following the bookmark are automatically transmitted. However, if the threshold is exceeded, the master sets the subscriber to the failed
state. A failed subscriber must use ttRepAdmin
-duplicate
to copy the master database and start over, as described in Chapter 14, "Managing Database Failover and Recovery".
See Oracle TimesTen In-Memory Database Reference for more information about TimesTen connection attributes, built-in procedures and utilities.
You can configure your replication scheme with a return service to ensure a higher level of confidence that your replicated data is consistent on the databases in your replication scheme.
Note:
This section assumes you understand return services. For an overview on return services, see "Copying updates between databases".This section describes how to configure and manage the return receipt and return twosafe services. You can specify a return service for table elements and database elements for any standby or subscriber defined in replication scheme with the CREATE ACTIVE STANDBY PAIR
, ALTER ACTIVE STANDBY PAIR
, CREATE REPLICATION
, or ALTER REPLICATION
statements. The default is the NO RETURN
service, which is asynchronous replication and the best performance option.
Note:
You can use thettRepXactStatus
procedure to check on the status of a return receipt or return twosafe transaction. See "Check the status of return service transactions" for details.The following sections describe the return services that can be used for your replication scheme:
In a classic replication scheme, you can specify a different return service for table elements and database elements for the subscribers listed in each SUBSCRIBER
clause in a CREATE REPLICATION
or ALTER REPLICATION
statement.
Example 4-1 shows separate SUBSCRIBER
clauses that can define different return service attributes for SubDatabase1
and SubDatabase2
.
Example 4-1 Different return services for each subscriber
CREATE REPLICATION Owner.SchemeName ELEMENT ElementNameElementType MASTER DatabaseName ON "HostName" SUBSCRIBER SubDatabase1 ON "HostName" ReturnServiceAttribute1 SUBSCRIBER SubDatabase2 ON "HostName" ReturnServiceAttribute2;
Alternatively, you can specify the same return service attribute for all of the subscribers defined in an element. Example 4-2 shows the use of a single SUBSCRIBER
clause that defines the same return service attributes for both SubDatabase1
and SubDatabase2
.
TimesTen Classic provides an optional return receipt service to loosely couple or synchronize your application with the replication mechanism.
In an active standby pair, you can specify the RETURN RECEIPT
clause to enable the return receipt service for the standby database. With return receipt enabled, when your application commits a transaction for an element on the active database, the application remains blocked until the standby acknowledges receipt of the transaction update.
In a classic replication scheme, you can specify the RETURN RECEIPT
clause to enable the return receipt service for the subscriber database. With return receipt enabled, when your application commits a transaction for an element on the master database, the application remains blocked until the subscriber acknowledges receipt of the transaction update. If the master is replicating the element to multiple subscribers, the application remains blocked until all of the subscribers have acknowledged receipt of the transaction update.
Note:
You can also configure the replication agent to disable the return receipt service after a specific number of timeouts. See "Setting the return service timeout period" for details on timeouts.If the standby or subscriber is unable to acknowledge receipt of the transaction within a configurable timeout period, your application receives a tt_ErrRepReturnFailed
(8170) warning on its commit request.
Example 4-3 Defining RETURN RECEIPT for an active standby pair
The following example creates an active standby pair where master1
is the active database, master2
is the standby database. The standby database is enabled with the return receipt service.
Command> CREATE ACTIVE STANDBY PAIR master1, master2 RETURN RECEIPT;
Example 4-4 Defining RETURN RECEIPT for a classic replication scheme
To confirm that all transactions committed on the tab
table in the master database (masterds
) are received by the subscriber (subscriberds
), the element description (e
) might look like the following:
NOTE: For more examples of classic replication schemes that use return receipt services, see Example 9-5 and Example 9-6.
ELEMENT e TABLE tab MASTER masterds ON "system1" SUBSCRIBER subscriberds ON "system2" RETURN RECEIPT
RETURN RECEIPT
enables notification of receipt for all transactions. You can use the RETURN RECEIPT
BY REQUEST
clause to enable an acknowledgement receipt notification only for specific transactions identified by your application.
If you specify RETURN RECEIPT BY REQUEST
, you must use the ttRepSyncSet
built-in procedure on the active or master database to enable the return receipt service for a transaction. The call to enable the return receipt service must be part of the transaction (autocommit
must be off).
If the standby or subscriber database is unable to acknowledge receipt of the transaction update within a configurable timeout period, the application receives a tt_ErrRepReturnFailed
(8170) warning on its commit request. See "Setting the return service timeout period" for more information on the return service timeout period.
Example 4-5 RETURN RECEIPT BY REQUEST for an active standby pair
The following example creates an active standby pair where master1
is the active database and master2
is the standby database. The standby database is enabled with the return receipt service.
Command> CREATE ACTIVE STANDBY PAIR master1, master2 RETURN RECEIPT BY REQUEST;
Example 4-6 RETURN RECEIPT BY REQUEST for a classic replication scheme
To enable confirmation that specific transactions committed on the tab
table in the master database (masterds
) are received by the subscriber (subscriberds
), the element description (e
) might look like:
ELEMENT e TABLE tab MASTER masterds ON "system1" SUBSCRIBER subscriberds ON "system2" RETURN RECEIPT BY REQUEST
Example 4-7 Using ttRepSyncSet to request the return services
Before committing a transaction that requires an acknowledgement return receipt, call ttRepSyncSet
. The following example sets the request for a return receipt with the first column set to 0x01
with a timeout value of 45 seconds in column two.
Command> autocommit off; Command> CALL ttRepSyncSet(0x01, 45, 1);
You can use ttRepSyncGet
to check if a return service is enabled and obtain the timeout value. The following demonstrates that the values that were previously set with the ttRepSyncSet
built-in procedure.
Command> CALL ttRepSyncGet; < 01, 45, 1 > 1 row found.
For more information, see "ttRepSyncSet" and "ttRepSyncGet" in the Oracle TimesTen In-Memory Database Reference.
TimesTen Classic provides a return twosafe service to fully synchronize your application with the replication mechanism. The return twosafe service ensures that each replicated transaction is committed on the standby database before it is committed on the active database. If replication is unable to verify the transaction has been committed on the standby or subscriber, it returns notification of the error. Upon receiving an error, the application can either take a unique action or fall back on preconfigured actions, depending on the type of failure.
To enable the return twosafe service for the subscriber, specify the RETURN TWOSAFE
attribute in the CREATE ACTIVE STANDBY PAIR
, ALTER ACTIVE STANDBY PAIR
, CREATE REPLICATION
, or ALTER REPLICATION
statements.
When using an active standby pair, a transaction that contains operations that are replicated with RETURN TWOSAFE
cannot have a PassThrough
setting greater than 0. If PassThrough
is greater than 0, an error is returned and the transaction must be rolled back.
When using a classic replication scheme, the return twosafe service is intended to be used in replication schemes where two databases must stay synchronized. One database has an active role, while the other database has a standby role but must be ready to assume an active role at any moment. Use return twosafe with a bidirectional replication scheme with exactly two databases.
When the application commits a transaction on the master database, the application remains blocked until the subscriber acknowledges it has successfully committed the transaction. Initiating identical updates or deletes on both databases can lead to deadlocks in commits that can be resolved only by stopping the processes.
If the standby or subscriber is unable to acknowledge commit of the transaction update within a configurable timeout period, the application receives a tt_ErrRepReturnFailed
(8170) warning on its commit request. See "Setting the return service timeout period" for more information on the return service timeout period.
Example 4-8 RETURN TWOSAFE with an active standby pair
The following example creates an active standby pair where master1
is the active database, master2
is the standby database. The standby database is enabled with the return twosafe service.
Command> CREATE ACTIVE STANDBY PAIR master1, master2 RETURN TWOSAFE;
Example 4-9 RETURN TWOSAFE with a classic replication scheme
To confirm all transactions committed on the master database (databaseA
) are also committed by the subscriber (databaseB
), the element description (a
) might look like the following:
ELEMENT a DATASTORE MASTER databaseA ON "system1" SUBSCRIBER databaseB ON "system2" RETURN TWOSAFE
The entire CREATE REPLICATION
statement that specifies both databaseA
and databaseB
in a bidirectional configuration with RETURN TWOSAFE
might look like the following:
CREATE REPLICATION bidirect ELEMENT a DATASTORE MASTER databaseA ON "system1" SUBSCRIBER databaseB ON "system2" RETURN TWOSAFE ELEMENT b DATASTORE MASTER databaseB ON "system2" SUBSCRIBER databaseA ON "system1" RETURN TWOSAFE;
RETURN TWOSAFE
enables notification of commit on the standby database for all transactions. You can use the RETURN TWOSAFE
BY REQUEST
clause to enable notification of a commit on the standby only for specific transactions identified by your application.
If you specify RETURN TWOSAFE BY REQUEST
for a standby or subscriber database, you must use the ttRepSyncSet
built-in procedure on the active or master database to enable the return twosafe service for a transaction. The call to enable the return twosafe service must be part of the transaction (autocommit
must be off).
When you use the ALTER TABLE
statement to alter a replicated table that is part of a RETURN TWOSAFE BY REQUEST
transaction, it ends up not being executed as a part of the TWOSAFE BY REQUEST
transaction. Instead, the ALTER TABLE
operation succeeds because a commit is performed before the ALTER TABLE
operation, resulting in the ALTER TABLE
operation executing in a new transaction which is not part of the RETURN TWOSAFE BY REQUEST
transaction.
Note:
See "Setting the return service timeout period" for more information on setting the return service timeout period.If the standby or subscriber is unable to acknowledge commit of the transaction within the timeout period, the application receives a tt_ErrRepReturnFailed
(8170) warning on its commit request. The application can then chose how to handle the timeout. See "Setting the return service timeout period".
When using an active standby pair, a transaction that contains operations that are replicated with RETURN TWOSAFE
cannot have a PassThrough
setting greater than 0. If PassThrough
is greater than 0, an error is returned and the transaction must be rolled back.
Example 4-10 RETURN TWOSAFE BY REQUST for an active standby pair
The following example creates an active standby pair where master1
is the active database, master2
is the standby database. The standby database is enabled with the return twosafe by request service.
Command> CREATE ACTIVE STANDBY PAIR master1, master2 RETURN TWOSAFE BY REQUEST;
Before calling commit for a transaction that requires confirmation of commit on the subscriber, call the ttRepSyncSet
built-in procedure to request the return service, set the timeout period to 45 seconds, and specify no action (1) in the event of a timeout error:
Command> CALL ttRepSyncSet(0x01, 45, 1);
You can use the ttRepSyncGet
built-in procedure to check if a return service is enabled and obtain the timeout value.
Command> CALL ttRepSyncGet(); < 01, 45, 1> 1 row found.
Example 4-11 RETURN TWOSAFE BY REQUEST for a classic replication scheme
To enable confirmation that specific transactions committed on the master database (databaseA
) are also committed by the subscriber (databaseB
), the element description (a
) might look like:
ELEMENT a DATASTORE MASTER databaseA ON "system1" SUBSCRIBER databaseB ON "system2" RETURN TWOSAFE BY REQUEST;
Before calling commit for a transaction that requires confirmation of commit on the subscriber, call the ttRepSyncSet
built-in procedure to request the return service, set the timeout period to 45 seconds, and specify no action (1) in the event of a timeout error:
Command> CALL ttRepSyncSet(0x01, 45, 1);
You can use the ttRepSyncGet
built-in procedure to check if a return service is enabled and obtain the timeout value.
Command> CALL ttRepSyncGet(); < 01, 45, 1> 1 row found.
You can use the NO RETURN
clause to explicitly disable either the return receipt or return twosafe services, depending on which one you have enabled. NO RETURN
is the default condition. This attribute is typically used only when altering a replication scheme to remove a previously defined return service in the ALTER ACTIVE STANDBY PAIR
or ALTER REPLICATION
statements. See Example 10-13.
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 "CREATE ACTIVE STANDBY PAIR" and "CREATE REPLICATION" in the Oracle TimesTen In-Memory Database SQL Reference for the full description and syntax for theSTORE
attributes.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 Example 9-5.
Note:
If you are usingALTER 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:
Setting wait timeout for response from remote replication agents
Suspending or resuming classic replication in response to conflicts
The following describes how a timeout can occur in a replication scheme configured with one of the return services described in "Using a return service".
In an active standby pair replication scheme, a timeout occurs if the standby database is unable to send an acknowledgement back to the active database within the time period specified by RETURN WAIT TIME
.
If the standby database is unable to acknowledge the transaction update from the active database within the timeout period, the application receives an errRepReturnFailed
warning on its commit request.
In a classic replication scheme, a timeout occurs if any of the subscribers are unable to send an acknowledgement back to the master within the time period specified by RETURN WAIT TIME
.
The replication state could be set to stop
by a user or by the master replication agent in the event of a subscriber failure. A subscriber may be unable to acknowledge a transaction that makes use of a return service and may time out with respect to the master.
If any of the subscribers are unable to acknowledge the transaction update within the timeout period, the application receives an errRepReturnFailed
warning on its commit request.
A return service may time out because of a replication failure or because replication is so far behind that the return service transaction times out before it is replicated. However, unless there is a simultaneous replication failure, failure to obtain a return service confirmation from the standby or subscriber does not necessarily mean the transaction has not been or will not be replicated.
The default return service timeout period is 10 seconds. You can specify a different return service timeout period by either:
Specifying the RETURN WAIT TIME
in the CREATE ACTIVE STANDBY PAIR
, ALTER ACTIVE STANDBY PAIR
, CREATE REPLICATION
, or ALTER REPLICATION
statements.
The RETURN WAIT TIME
attribute specifies the number of seconds to wait for a return service acknowledgement. A value of 0 means that there is no waiting.
The following example alters an active database (master1
) of an active standby pair to set a return service wait time of 25 seconds:
Command> ALTER ACTIVE STANDBY PAIR ALTER STORE master1 SET RETURN WAIT TIME 25;
Specifying a different return service timeout period programmatically by calling the ttRepSyncSet
built-in procedure on either the active database (in an active standby pair) or the master database (in a classic replication scheme) with a new timeout value for the returnWait
parameter.
The following example demonstrates how to set the return service wait time to 25 seconds using ttRepSyncSet
:
Command> CALL ttRepSyncSet (0x01, 25, 1);
Once the timeout is set, the timeout period applies to all subsequent return service transactions until you either reset the timeout period or terminate the application session. For a classic replication scheme, the timeout setting applies to all return services for all subscribers.
Note:
You can set otherSTORE
attributes to establish policies that automatically disable return service blocking in the event of excessive timeouts and re-enable return service blocking when conditions improve. See "Managing return service timeout errors and replication state changes".Example 4-12 Setting the timeout period for both databases in bidirectional (classic) replication scheme
To set the timeout period to 30 seconds for both bidirectionally replicated databases, databaseA
and databaseB
, in the bidirect
replication scheme, the CREATE REPLICATION
statement might look like the following:
CREATE REPLICATION bidirect ELEMENT a DATASTORE MASTER databaseA ON "system1" SUBSCRIBER databaseB ON "system2" RETURN TWOSAFE ELEMENT b DATASTORE MASTER databaseB ON "system2" SUBSCRIBER databaseA ON "system1" RETURN TWOSAFE STORE databaseA RETURN WAIT TIME 30 STORE databaseB RETURN WAIT TIME 30;
Example 4-13 Resetting the timeout period
Use the ttRepSyncSet
built-in procedure to reset the timeout period to 45 seconds. To avoid resetting the requestReturn
and localAction
values, specify NULL
:
Command> CALL ttRepSyncSet(NULL, 45, NULL);
The following sections describe how to detect and respond to timeouts on return service transactions:
Note:
One response to a timeout is to disable the return service. You can determine if the return service is enabled or disabled with either thettRepSyncSubscriberStatus
built-in procedure. For more information, see "Determine if return service is disabled".You may want to react if replication is stopped or return service timeout failures begin to adversely impact the performance of your replicated system. Your "tolerance threshold" for return service timeouts may depend on the historical frequency of timeouts and the performance/availability equation for your particular application, both of which should be factored into your response to the problem.
When using the return receipt service, you can manually respond by:
Using the ALTER ACTIVE STANDBY PAIR
or ALTER REPLICATION
statements to disable return receipt blocking. If you decide to disable return receipt blocking, your decision to re-enable it depends on your confidence level that the return receipt transaction is no longer likely to time out.
The following example uses the ALTER ACTIVE STANDBY PAIR
statement to disable return receipt after 10 failures:
Command> ALTER ACTIVE STANDBY PAIR ALTER STORE master1 SET DISABLE RETURN ALL 10;
Calling the ttDurableCommit
built-in procedure to durably commit transactions on the active or master database that you can no longer verify as being received by the standby or subscriber database.
An alternative to manually responding to return service timeout failures is to establish return service failure and recovery policies in the replication scheme. These policies direct the replication agents to detect changes to the replication state and to keep track of return service timeouts and then automatically respond in a predefined manner.
The following attributes in the CREATE ACTIVE STANDBY PAIR
, ALTER ACTIVE STANDBY PAIR
, CREATE REPLICATION
, or ALTER REPLICATION
statements set the failure and recovery policies when using a RETURN RECEIPT
or RETURN TWOSAFE
service:
The policies set by these attributes are applicable until changed. Except for DURABLE COMMIT
, the replication agent must be running to enforce these policies.
The RETURN SERVICES {ON | OFF} WHEN [REPLICATION] STOPPED
attribute determines whether a return receipt or return two safe service continues to be enabled or is disabled when replication is paused or stopped.
For an active standby pair, replication is considered stopped when either:
The active replication agent has failed or is explicitly stopped (for example, by ttAdmin
-repStop
active
).
A failed standby master that has exceeded the specified FAILTHRESHOLD
value stops replication. Note that even though replication is stopped when the standby master fails, TimesTen replicates directly from the active master to any subscribers, bypassing the standby master. All missing updates are propagated to the standby master if it recovers.
In a classic replication scheme, replication is considered stopped when either:
The master replication agent is explicitly stopped (for example, by ttAdmin
-repStop
master
).
The replication state of the subscriber database is set to pause
or stop
(for example, by ttRepAdmin
-state pause
subscriber
).
A failed subscriber that has exceeded the specified FAILTHRESHOLD
value stops replication.
Note:
A standby or subscriber database may become unavailable for a period of time that exceeds the timeout period specified byRETURN WAIT TIME
, yet may still be considered by the master replication agent to be in the start
state. Failure policies related to timeouts are set by the DISABLE RETURN
attribute.You can enable or disable the return service when replication is stopped with the following clause:
RETURN SERVICES OFF WHEN REPLICATION STOPPED
disables the return service when replication is stopped and is the default when using the RETURN RECEIPT
service.
RETURN SERVICES ON WHEN REPLICATION STOPPED
enables the return service to continue to be enabled when replication is stopped and is the default when using the RETURN TWOSAFE
service.
Example 4-14 RETURN SERVICES ON WHEN REPLICATION STOPPED for an active standby pair
The following example creates an active standby pair with RETURN TWOSAFE
return service and defines that the return service is to be disabled when replication is stopped (which is opposite of the default).
Command> CREATE ACTIVE STANDBY PAIR master1, master2 RETURN TWOSAFE STORE master2 RETURN SERVICES OFF WHEN REPLICATION STOPPED;
Example 4-15 RETURN SERVICES ON WHEN REPLICATION STOPPED for a classic replication scheme
Configure the CREATE REPLICATION
statement to replicate updates from the masterds
database to the subscriber1
database. The CREATE REPLICATION
statement specifies the use of RETURN RECEIPT
and RETURN SERVICES ON WHEN REPLICATION STOPPED
.
CREATE REPLICATION myscheme ELEMENT e TABLE tab MASTER masterds ON "server1" SUBSCRIBER subscriber1 ON "server2" RETURN RECEIPT STORE masterds ON "server1" RETURN SERVICES ON WHEN REPLICATION STOPPED;
While the application is committing updates to the master, you could use the ttRepAdmin
-state pause
to set subscriber1
to the pause
state:
ttRepAdmin -receiver -name subscriber1 -state pause masterds
At this point, the application would continue to wait for return receipt acknowledgements from subscriber1
until the replication state is reset to start
and it receives the acknowledgment:
ttRepAdmin -receiver -name subscriber1 -state start masterds
Note:
As described in "Set the replication state of subscribers", You should be cautious about setting the subscriber state tostop
, as this not only stops the replication to this subscriber, but also discards all of the updates. If you did set the subscriber to the stop
state, you would need to perform a duplicate to restore the subscriber.When a DISABLE RETURN
value is set, the database keeps track of the number of return receipt or return twosafe transactions that have exceeded the timeout period set by RETURN WAIT TIME
. If the number of timeouts exceeds the maximum value set by DISABLE RETURN
, the application reverts to a default replication cycle in which it no longer waits for the standby or subscriber to acknowledge the replicated updates.
When return service blocking is disabled, the applications on the active or master database no longer block execution while waiting to receive acknowledgements from the standby or subscribers that they received or committed the replicated updates. Transactions are still replicated to the standby or subscriber, whether the return service is enabled or disabled. When the return service is disabled, the transactions are sent in asynchronous mode; the active or master database continues to listen for an acknowledgement of each batch of replicated updates from standby or subscriber databases.
Configure DISABLE RETURN
as follows:
For an active standby pair, specifying SUBSCRIBER
is the same as specifying ALL
. Both settings refer to the standby database.
For a classic replication scheme, you can set DISABLE RETURN SUBSCRIBER
to establish a failure policy to disable return service blocking for only those subscribers that have timed out, or DISABLE RETURN ALL
to establish a policy to disable return service blocking for all subscribers.
Note:
You can use thettRepSyncSubscriberStatus
built-in procedure to determine whether the standby database or a particular subscriber has been disabled by the DISABLE RETURN
failure policy.The DISABLE RETURN
failure policy is only enabled when the replication agent is running. If DISABLE RETURN
is specified without RESUME RETURN
, the return services remain off until the replication agent for the database has been restarted.
For an active standby pair, you can cancel this failure policy by stopping the replication agent and specifying DISABLE RETURN
with a zero value for NumFailures
.
For a classic replication scheme, you can cancel this failure policy by stopping the replication agent and specifying either DISABLE RETURN SUBSCRIBER
or DISABLE RETURN ALL
with a zero value for NumFailures
.
DISABLE RETURN
maintains a cumulative timeout count for each subscriber. If there are multiple subscribers and you set DISABLE RETURN SUBSCRIBER
, the replication agent disables return service blocking for the first subscriber that reaches the timeout threshold. If one of the other subscribers later reaches the timeout threshold, the replication agent disables return service blocking for that subscriber also.
The count of timeouts to trigger the failure policy is reset either when you restart the replication agent, when you set the DISABLE RETURN
value to 0, or when return service blocking is re-enabled by RESUME RETURN
.
Example 4-16 DISABLE RETURN for an active standby pair
Configure the CREATE ACTIVE STANDBY PAIR
statement to replicate updates from the active database master1
to the standby database master2
. The CREATE ACTIVE STANDBY PAIR
statement specifies the use of RETURN RECEIPT
and DISABLE RETURN ALL
with a NumFailures
value of 5. The RETURN WAIT TIME
is set to 30 seconds.
CREATE ACTIVE STANDBY PAIR master1, master2 RETURN RECEIPT STORE master1 DISABLE RETURN ALL 5 RETURN WAIT TIME 30;
While the application is committing updates to the active database, the standby database (master2
) experiences problems and fails to acknowledge a replicated transaction update. The application is blocked for 30 seconds after which it commits its next update to the active database master1
. Over the course of the application session, this commit/timeout cycle repeats 4 more times until DISABLE RETURN
disables return receipt blocking for master2
.
For another example that sets the DISABLE RETURN
attribute for an active standby pair, see Example 4-18.
Example 4-17 DISABLE RETURN SUBSCRIBER for a classic replication scheme
Configure the CREATE REPLICATION
statement to replicate updates from the masterds
master database to the subscriber databases: subscriber1
and subscriber2
. The CREATE REPLICATION
statement specifies the use of RETURN RECEIPT
and DISABLE RETURN SUBSCRIBER
with a NumFailures
value of 5. The RETURN WAIT TIME
is set to 30 seconds.
CREATE REPLICATION myscheme ELEMENT e TABLE tab MASTER masterds ON "server1" SUBSCRIBER subscriber1 ON "server2", subscriber2 ON "server3" RETURN RECEIPT STORE masterds ON "server1" DISABLE RETURN SUBSCRIBER 5 RETURN WAIT TIME 30;
While the application is committing updates to the master, subscriber1
experiences problems and fails to acknowledge a replicated transaction update. The application is blocked for 30 seconds after which it commits its next update to the master database masterds
. Over the course of the application session, this commit/timeout cycle repeats 4 more times until DISABLE RETURN
disables return receipt blocking for subscriber1
. The application continues to wait for return-receipt acknowledgements from subscriber2
, but not from subscriber1
.
For another example that sets the DISABLE RETURN
attribute for a classic replication scheme, see Example 4-19.
If DISABLE RETURN
has disabled return service blocking, the RESUME RETURN
attribute sets the policy for re-enabling the return service. You can establish a return service recovery policy by setting the RESUME RETURN
attribute and specifying a resume latency value.
If return service blocking has been disabled for the standby or subscriber database and a latency time has been defined for RESUME RETURN
, the following occurs:
The applications on the active or master database no longer block execution while waiting to receive acknowledgements from the standby or subscribers. Transactions continue to be replicated to the standby or subscriber in asynchronous mode. The active or master databases continue to listen for an acknowledgement of each batch of replicated updates from standby or subscriber databases.
If the return service blocking is disabled, RESUME RETURN
evaluates the commit-to-acknowledge time for the last transaction to see if the latency is less than the latency limit configured by the RESUME RETURN
. If the commit-to-acknowledge time latency is less than the latency limit set by RESUME RETURN
, TimesTen re-enables the return receipt or return twosafe services.
Note:
The commit-to-acknowledge time latency is the time elapsed between when the application issues a commit and when the active or master database receives acknowledgement from the standby or subscriber.TimesTen evaluates the latency of the last acknowledged transaction before the current transaction is replicated to the standby or subscriber. The return service is re-enabled before the sending of the current transaction after evaluating the latency from the last transaction.
The RESUME RETURN
policy is enabled only when the replication agent is running. You can cancel a return receipt resume policy by stopping the replication agent and then using ALTER ACTIVE STANDBY PAIR
or ALTER REPLICATION
statements to set RESUME RETURN
to zero.
Example 4-18 RESUME RETURN for an active standby pair
If return receipt blocking has been disabled for master2
and if RESUME RETURN
is set to 8 milliseconds, then return receipt blocking is re-enabled for master2
the instant the active receives an acknowledgement of the update from the standby, as long as the acknowledgement is received within the specified latency 8 milliseconds from when it was committed by the application on the active database.
Command> CREATE ACTIVE STANDBY PAIR master1, master2 RETURN RECEIPT STORE master1 DISABLE RETURN ALL 5 RESUME RETURN 8;
Example 4-19 RESUME RETURN for a classic replication scheme
If return receipt blocking has been disabled for subscriber1
and if RESUME RETURN
is set to 8 milliseconds, then return receipt blocking is re-enabled for subscriber1
the instant the master receives an acknowledgement of the update from the subscriber, as long as the acknowledgement is received within the specified latency 8 milliseconds from when it was committed by the application on the master database.
CREATE REPLICATION myscheme ELEMENT e TABLE ttuser.tab MASTER masterds ON "server1" SUBSCRIBER subscriber1 ON "server2", subscriber2 ON "server3" RETURN RECEIPT STORE masterds ON "server1" DISABLE RETURN SUBSCRIBER 5 RESUME RETURN 8;
You can set the DURABLE COMMIT
attribute to specify the durable commit policy for applications that have return service blocking disabled by DISABLE RETURN
. When DURABLE COMMIT
is set to ON
, it overrides the DurableCommits
general connection attribute on the active or master database and forces durable commits for those transactions that have had return service blocking disabled.
In addition, when DURABLE COMMIT
is set to ON
, durable commits are issued when return service blocking is disabled regardless of whether the replication agent is running or stopped. They are also issued when the ttRepStateSave
built-in procedure has marked the standby or subscriber database as failed.
For a classic replication scheme, DURABLE COMMIT
is useful if you have only one subscriber. However, if you are replicating the same data to two subscribers and you disable return service blocking to one subscriber, then you achieve better performance if you rely on the other subscriber than you would if you enable durable commits.
Example 4-20 DURABLE COMMIT ON with an active standby pair
Set DURABLE COMMIT ON
when establishing a DISABLE RETURN ALL
policy to disable return-receipt blocking for all subscribers. If return-receipt blocking is disabled, commits are durably committed to the file system to provide redundancy.
Command> CREATE ACTIVE STANDBY PAIR master1, master2 RETURN RECEIPT STORE master1 DISABLE RETURN ALL 5 DURABLE COMMIT ON RESUME RETURN 8;
Example 4-21 DURABLE COMMIT ON with a classic replication scheme
Set DURABLE COMMIT ON
when establishing a DISABLE RETURN ALL
policy to disable return-receipt blocking for all subscribers. If return-receipt blocking is disabled, commits are durably committed to the file system to provide redundancy.
CREATE REPLICATION myscheme ELEMENT e TABLE tab MASTER masterds ON "server1" SUBSCRIBER subscriber ON "server2", subscriber2 ON "server3" RETURN RECEIPT STORE masterds ON "server1" DISABLE RETURN ALL 5 DURABLE COMMIT ON RESUME RETURN 8;
When you are using the return twosafe service, you can specify how the active or master replication agent responds to timeouts by setting LOCAL COMMIT ACTION
. You can override this setting for specific transactions with the localAction
parameter of the ttRepSyncSet
built-in procedure.
The possible actions upon receiving a timeout during replication of a twosafe transaction are:
COMMIT
- On timeout, the commit function attempts to perform a commit to end the transaction locally. No more operations are possible on the same transaction.
NO ACTION
- On timeout, the commit function returns to the application, leaving the transaction in the same state it was in when it entered the commit call, with the exception that the application is not able to update any replicated tables. The application can reissue the commit. This is the default.
If the call returns with an error, you can use the ttRepXactStatus
procedure described in "Check the status of return service transactions" to check the status of the transaction. Depending on the error, your application can choose to:
Reissue the commit call - This repeats the entire return twosafe replication cycle, so that the commit call returns when the success or failure of the replicated commit on the subscriber is known or if the timeout period expires.
Roll back the transaction - If the call returns with an error related to applying the transaction on the standby or subscriber, such as primary key lookup failure, you can roll back the transaction on the active or master database.
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:
For more details onTABLE DEFINITION CHECKING
, 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 executed 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 Example 4-18 and "Check partition counts for the tables" in Oracle TimesTen In-Memory Database Troubleshooting Guide for information on how to check the partitions in use for each table.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 execute 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. For more information, 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:
Examples for an active standby pair with table definition checking set to relaxed
Examples for classic replication scheme with table definition checking set to relaxed
The following examples demonstrate the effect of setting the TABLE DEFINITION CHECKING
attribute to either EXACT
or RELAXED
in an active standby pair replication scheme.
Example 4-22 Replicating tables that are identical in an active standby pair
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 Chapter 2, "Getting Started".
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.
Example 4-23 Altering the table definition checking to relaxed in an active standby pair
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 execution completes, use duplicate to roll out the changes to the standby database. Lastly, use duplicate to roll out the changes to any subscribers.
The following examples demonstrate the effect of setting the TABLE DEFINITION CHECKING
attribute to either EXACT
or RELAXED
in a classic replication scheme.
Example 4-24 Replicating tables that are identical in a classic replication scheme
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.
Example 4-25 Replicating tables with columns in different positions in a classic replication scheme
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.
Example 4-26 Replicating 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.
Example 4-27 Altering the table definition checking to relaxed in a classic replication scheme
You can alter the table definition checking for a classic replication scheme to be relaxed. First, stop the replication agent on the master database before altering the replication scheme on it. The following 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 execution completes, use duplicate to roll out the changes to the standby master. Lastly, use duplicate to roll out the changes to any subscribers.
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.
Example 4-28 Compressing traffic from an active database in an active standby pair
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;
Example 4-29 Compressing traffic from both active and standby databases
To compress the replicated traffic from the dsn1
and dsn2
databases, 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;
Example 4-30 Compressing traffic from one database in a classic replication scheme
To compress replicated traffic from database dsn1
and leave the replicated traffic from dsn2
uncompressed, the CREATE REPLICATION
statement looks like:
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;
Example 4-31 Compressing traffic between both databases in a classic replication scheme
To compress the replicated traffic between both the dsn1
and dsn2
databases, 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;
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 4-32 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 4-33 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;
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;
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, as follows:
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 Example 9-5.
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, "Database states".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".
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. For a full description, see Chapter 12, "Resolving Replication Conflicts".
The following sections describe some of the issues to consider when replicating TimesTen data over a network.
The network bandwidth required for TimesTen Classic replication depends on the bulk and frequency of the data being replicated. This discussion explores the types of transactions that characterize the high and low ends of the data range and the network bandwidth required to replicate the data between TimesTen databases.
Table 4-1 provides guidelines for calculating the size of replicated records.
Table 4-1 Replicated record sizes
Record Type | Size |
---|---|
Begin transaction |
48 bytes |
Update |
116 bytes + 18 bytes per column updated + size of old column values + size of new column values + size of the primary key or unique key |
Delete |
104 bytes + size of the primary key or unique key |
Insert |
104 bytes + size of the primary key or unique key + size of inserted row |
Transactions are sent between replicated databases in batches. A batch is created whenever there is no more data in the transaction log buffer in the master database, or when the current batch is roughly 256 KB. See "Copying updates between databases" for more information.
TimesTen Classic replication uses the TCP/IP protocol, which is not optimized for a WAN environment. You can improve replication performance over a WAN by installing a third-party "TCP stack" product. If replacing the TCP stack is not a feasible solution, you can reduce the amount of network traffic that the TCP/IP protocol has to deal with by setting the COMPRESS TRAFFIC
attribute in the CREATE ACTIVE STANDBY PAIR
or CREATE REPLICATION
statement. See "Compressing replicated traffic" for details.
See installation information for either the AIX or Linux platforms in "AIX prerequisites" or "Linux prerequisites" in the Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide for information about changing TCP/IP kernel parameters for better performance.
In a replication scheme, you need to identify the name of the host on which your database resides. The operating system translates this host name to one or more IP addresses.
When specifying the host for a database in a replication element, you should always use the name returned by the hostname
command, as replication uses the same host name to verify that the current host is involved in the replication scheme. Replication schemes may not be created that do not include the current host.
While you must specify the host name returned by the operating system's hostname
command when you specify the database name, you can configure replication to send or receive traffic over a different interface (other than the default) using the ROUTE
clause.
If a host contains multiple network interfaces (with different IP addresses), you should specify which interfaces are to be used by replication using the ROUTE
clause, unless you want replication to use the default interface. You must specify a priority for each interface. Replication tries to first connect using the address with the highest priority, and if a connection cannot be established, it tries the remaining addresses in order of priority until a connection is established. If a connection to a host fails while using one IP address, replication attempts to re-connect (or fall back) to another IP address, if more than one address has been specified in the ROUTE
clause.
The syntax of the ROUTE
clause is:
ROUTE MASTER FullDatabaseName SUBSCRIBER FullDatabaseName {{MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost} PRIORITY Priority} [...]
Note:
Addresses for theROUTE
clause may be specified as either host names or IP addresses. However, if your host has more than one IP address configured for a given host name, you should only configure the ROUTE
clause using the IP addresses, in order to ensure that replication uses only the IP addresses that you intend.When using the ROUTE
clause in an active standby pair, each master database is a subscriber of the other master database and each read-only subscriber is a subscriber of both master databases. This means that the CREATE ACTIVE STANDBY PAIR
statement should include ROUTE
clauses in multiples of two to specify a route in both directions.
When using the ROUTE
clause in a classic replication scheme that defines dual masters, each master database is a subscriber of the other master database. This means that the CREATE REPLICATION
statement should include ROUTE
clauses in multiples of two to specify a route in both directions.
Example 4-34 Configuring multiple network interfaces for an active standby pair
If host1
host is configured with a second interface accessible by the host1fast
host name, and host2
is configured with a second interface at IP address 192.168.1.100
, you may specify that the secondary interfaces are used with the replication scheme.
CREATE ACTIVE STANDBY PAIR dsn1, dsn2 ROUTE MASTER dsn1 ON "host1" SUBSCRIBER dsn2 ON "host2" MASTERIP "host1fast" PRIORITY 1 SUBSCRIBERIP "192.168.1.100" PRIORITY 1 ROUTE MASTER dsn2 ON "host2" SUBSCRIBER dsn1 ON "host1" MASTERIP "192.168.1.100" PRIORITY 1 SUBSCRIBERIP "host1fast" PRIORITY 1;
Example 4-35 Configuring multiple network interfaces for a classic replication scheme
If host1
host is configured with a second interface accessible by the host1fast
host name, and host2
is configured with a second interface at IP address 192.168.1.100
, you may specify that the secondary interfaces are used with the replication scheme.
CREATE REPLICATION repscheme ELEMENT e1 TABLE ttuser.tab MASTER dsn1 ON host1 SUBSCRIBER dsn2 ON host2 ELEMENT e2 TABLE ttuser.tab MASTER dsn2 ON host2 SUBSCRIBER dsn1 ON host1 ROUTE MASTER dsn1 ON host1 SUBSCRIBER dsn2 ON host2 MASTERIP host1fast PRIORITY 1 SUBSCRIBERIP "192.168.1.100" PRIORITY 1 ROUTE MASTER dsn2 ON host2 SUBSCRIBER dsn1 ON host1 MASTERIP "192.168.1.100" PRIORITY 1 SUBSCRIBERIP host1fast PRIORITY 1;
Alternately, on a replication host with more than one interface, you may want to configure replication to use one or more interfaces as backups, in case the primary interface fails or the connection from it to the receiving host is broken. You can use the ROUTE
clause to specify two or more interfaces for each master or subscriber that are used by replication in order of priority.
If replication on the master host is unable to bind to the MASTERIP
with the highest priority, it tries to connect using subsequent MASTERIP
addresses in order of priority immediately. However, if the connection to the subscriber fails for any other reason, replication tries to connect using each of the SUBSCRIBERIP
addresses in order of priority before it tries the MASTERIP
address with the next highest priority.
Example 4-36 Configuring network priority on an active standby pair
If the host1
host is configured with two network interfaces at IP addresses 192.168.1.100
and 192.168.1.101
, and the host2
host is configured with two interfaces at IP addresses 192.168.1.200
and 192.168.1.201
, you may specify that replication use IP addresses 192.168.1.100
and 192.168.200
to transmit and receive traffic first, and to try IP addresses 192.168.1.101
or 192.168.1.201
if the first connection fails.
CREATE ACTIVE STANDBY PAIR dsn1, dsn2 ROUTE MASTER dsn1 ON "host1" SUBSCRIBER dsn2 ON "host2" MASTERIP "192.168.1.100" PRIORITY 1 MASTERIP "192.168.1.101" PRIORITY 2 SUBSCRIBERIP "192.168.1.200" PRIORITY 1 SUBSCRIBERIP "192.168.1.201" PRIORITY 2;
Example 4-37 Configuring network priority for a classic replication scheme
If the host1
host is configured with two network interfaces at IP addresses 192.168.1.100
and 192.168.1.101
, and the host2
host is configured with two interfaces at IP addresses 192.168.1.200
and 192.168.1.201
, you may specify that replication use IP addresses 192.168.1.100
and 192.168.200
to transmit and receive traffic first, and to try IP addresses 192.168.1.101
or 192.168.1.201
if the first connection fails.
CREATE REPLICATION repscheme ELEMENT e TABLE ttuser.tab MASTER dsn1 ON host1 SUBSCRIBER dsn2 ON host2 ROUTE MASTER dsn1 ON host1 SUBSCRIBER dsn2 ON host2 MASTERIP "192.168.1.100" PRIORITY 1 MASTERIP "192.168.1.101" PRIORITY 2 SUBSCRIBERIP "192.168.1.200" PRIORITY 1 SUBSCRIBERIP "192.168.1.201" PRIORITY 2;
The following sections describe how to configure replication so that it uses the correct host names and IP addresses for each host when not using the ROUTE
clause.
Identifying database hosts on UNIX or Linux without using the ROUTE clause
User-specified addresses for TimesTen daemons and subdaemons
When possible, you should use the ROUTE
clause of a replication scheme to identify database hosts and the network interfaces to use for replication. However, if you have a replication scheme configuration that does not use the ROUTE
clause, this section explains how to configure operating system and DNS files for a replication host with multiple network interfaces.
If a host contains multiple network interfaces (with different IP addresses) and replication is not configured with a ROUTE
clause, TimesTen Classic replication tries to connect to the IP addresses in the same order as returned by the gethostbyname
call. It tries to connect using the first address; if a connection cannot be established, it tries the remaining addresses in order until a connection is established. TimesTen Classic replication uses this same sequence each time it establishes a new connection to a host. If a connection to a host fails on one IP address, TimesTen Classic replication attempts to re-connect (or fall back) to another IP address for the host in the same manner described above.
There are two basic ways you can configure a host to use multiple IP addresses on UNIX or Linux platforms: DNS or the /etc/hosts
file.
Note:
If you have multiple network interface cards (NICs), be sure that "multi on
" is specified in the /etc/host.conf
file. Otherwise, gethostbyname
cannot return multiple addresses.For example, if your machine has two NICs, use the following syntax for your /etc/hosts
file:
127.0.0.1 localhost IP_address_for_NIC_1 official_hostname optional_alias IP_address_for_NIC_2 official_hostname optional_alias
The host name official_hostname
is the name returned by the hostname
command.
When editing the /etc/hosts
file, keep in mind that:
You must log in as root
to change the /etc/hosts
file.
There should only be one line per IP address.
There can be multiple alias names on each line.
When there are multiple IP addresses for the same host name, they must be on consecutive lines.
The host name can be up to 30 characters long.
For example, the following entry in the /etc/hosts
file on a UNIX or Linux platform describes a server named Host1
with two IP addresses:
127.0.0.1 localhost 10.10.98.102 Host1 192.168.1.102 Host1
To specify the same configuration for DNS, your entry in the domain zone file would look like:
Host1 IN A 10.10.98.102 IN A 192.168.1.102
In either case, you only need to specify Host1
as the host name in your replication scheme and replication uses the first available IP address when establishing a connection.
In an environment in which multiple IP addresses are used, you can also assign multiple host names to a single IP address in order to restrict a replication connection to a specific IP address. For example, you might have an entry in your /etc/hosts
file that looks like:
127.0.0.1 localhost 10.10.98.102 Host1 192.168.1.102 Host1 RepHost1
or a DNS zone file that looks like:
Host1 IN A 10.10.98.102 IN A 192.168.1.102 RepHost1 IN A 192.168.1.102
If you want to restrict replication connections to IP address 192.168.1.102
for this host, you can specify RepHost1
as the host name in your replication scheme. Another option is to simply specify the IP address as the host name in either the CREATE ACTIVE STANDBY PAIR
or CREATE REPLICATION
statements used to configure your replication scheme.
If a replication configuration is specified using host names rather than IP addresses, replication must be able to translate host names of peers into IP addresses. For this to happen efficiently on Windows, make sure each Windows machine is set up to query either a valid WINS server or a valid DNS server that has correct information about the hosts on the network. In the absence of such servers, static host-to-IP entries can be entered in either:
%windir%\system32\drivers\etc\hosts
or
%windir%\system32\drivers\etc\lmhosts
Without any of these options, a Windows machine resorts to broadcasting to detect peer nodes, which is extremely slow.
You may also encounter extremely slow host name resolution if the Windows machine cannot communicate with the defined WINS servers or DNS servers, or if the host name resolution set up is incorrect on those servers. Use the ping
command to test whether a host can be efficiently located. The ping
command responds immediately if host name resolution is set up properly.
Note:
You must be consistent in identifying a database host in a replication scheme. Do not identify a host using its IP address for one database and then use its host name for the same or another database.By default, the TimesTen main daemon, all subdaemons, and all agents use any available address to listen on a socket for requests. You can modify the timesten.conf
file to specify an address for communication among the agents and daemons by including a listen_addr
option. See "Managing TimesTen daemon attributes" in Oracle TimesTen In-Memory Database Operations Guide for details.
Suppose that your machine has two NICs whose addresses are 10.10.10.100
and 10.10.11.200
. The loopback address is 127.0.0.1
. Then keep in mind the following as it applies to the replication agent:
If you do not set the listen_addr
option in the timesten.conf
file, then any process can talk to the daemons and agents.
If you set listen_addr
to 10.10.10.100
, then any process on the local host or the 10.10.10
net can talk to daemons and agents on 10.10.10.100
. No processes on the 10.10.11
net can talk to the daemons and agents on 10.10.10.100
.
If you set listen_addr
to 127.0.0.1
, then only processes on the local host can talk to the daemons and agents. No processes on other hosts can talk the daemons and agents.
Ordinarily, TimesTen Classic replication is able to identify the hosts involved in a replication configuration using normal operating system host name resolution methods. However, in some rare instances, if the host has an unusual host name configuration, TimesTen is unable to determine that the local host matches the host name as specified in the replication scheme. When this occurs, you receive error 8191, "This store is not involved in a replication scheme," when attempting to start replication using ttRepStart
or ttAdmin
-repStart
. The ttHostNameSet
built-in procedure may be used in this instance to explicitly indicate to TimesTen that the current database is in fact the database specified in the replication scheme. See "ttHostNameSet" in Oracle TimesTen In-Memory Database Reference for more information.