CREATE ACTIVE STANDBY PAIR

This statement creates an active standby pair. It includes an active master data store, a standby master data store, and may also include one or more read-only subscribers. The active master data store replicates updates to the standby master data store, which propagates the updates to the subscribers.

Required privilege

ADMIN

SQL syntax

CREATE ACTIVE STANDBY PAIR
  FullStoreName, FullStoreName [ReturnServiceAttribute]
    [SUBSCRIBER FullStoreName [,...]]
    [STORE FullStoreName [StoreAttribute [...]]]
    [NetworkOperation [...] ]
    [{ INCLUDE | EXCLUDE }{TABLE [[Owner.]TableName [,...]]|
         CACHE GROUP [[Owner.]CacheGroupName [,...]]|
         SEQUENCE [[Owner.]SequenceName [,...]]} [,...]]

The syntax for ReturnServiceAttribute is

{ RETURN RECEIPT [BY REQUEST] |
  RETURN TWOSAFE [BY REQUEST] |
  NO RETURN }

Syntax for StoreAttribute is:

[ DISABLE RETURN {SUBSCRIBER | ALL} NumFailures ]
  [ RETURN SERVICES {ON | OFF} WHEN [REPLICATION] STOPPED ]
  [ DURABLE COMMIT {ON | OFF}]
  [ RESUME RETURN MilliSeconds ]
  [ LOCAL COMMIT ACTION {NO ACTION | COMMIT} ]
  [ RETURN WAIT TIME Seconds ]
  [ COMPRESS TRAFFIC {ON | OFF}
  [ PORT PortNumber ]
  [ TIMEOUT Seconds ]
  [ FAILTHRESHOLD Value ]

Syntax for NetworkOperation:

ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName
  { { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost }
      PRIORITY Priority } [...]

Parameters

CREATE ACTIVE STANDBY PAIR has the parameters:

Parameter	Description
`FullStoreName`	The data store, specified as one of the following: SELF The prefix of the data store file name For example, if the data store path is `directory/subdirectory/data.ds0`, then `data` is the data store name that should be used. This is the data store file name specified in the `DataStore` attribute of the DSN description with optional host ID in the form: `DataStoreName` `[ON` `Host]` `Host` can be either an IP address or a literal host name assigned to one or more IP addresses, as described in "Configuring host IP addresses" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. Host names containing special characters must be surrounded by double quotes. For example: `"MyHost-500"`.
`RETURN RECEIPT [BY REQUEST]`	Enables the return receipt service, so that applications that commit a transaction to an active master data store are blocked until the transaction is received by the standby master data store. RETURN RECEIPT applies the service to all transactions. If you specify RETURN REQUEST BY REQUEST, you can use the `ttRepSyncSet` procedure to enable the return receipt service for selected transactions. For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.
`RETURN TWOSAFE [BY REQUEST]`	Enables the return twosafe service, so that applications that commit a transaction to an active master data store are blocked until the transaction is committed on the standby master data store. RETURN TWOSAFE applies the service to all transactions. If you specify RETURN TWOSAFE BY REQUEST, you can use the `ttRepSyncSet` procedure to enable the return receipt service for selected transactions. For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.
`DISABLE RETURN {SUBSCRIBER \| ALL}` `NumFailures`	Set the return service failure policy so that return service blocking is disabled after the number of timeouts specified by `NumFailures`. Specifying SUBSCRIBER is the same as specifying ALL. Both settings refer to the standby master data store. This failure policy can be specified for either the RETURN RECEIPT or RETURN TWOSAFE service. See "Managing return service timeout errors and replication state changes" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for details.
`RESUME RETURN` `Milliseconds`	If `DISABLE RETURN` has disabled return service blocking, this attribute sets the policy for when to re-enable the return service.
`NO RETURN`	Specifies that no return service is to be used. This is the default. For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.
`RETURN WAIT TIME` `Seconds`	Specifies the number of seconds to wait for return service acknowledgement. A value of 0 means that there is no waiting. The default value is 10 seconds. The application can override this timeout setting by using the `returnWait` parameter in the `ttRepSyncSet` built-in procedure.
`SUBSCRIBER` `FullStoreName` `[,...]]`	A data store that receives updates from a master data store. `FullStoreName` is the data store file name specified in the `DataStore` attribute of the DSN description.
`STORE` `FullStoreName` `[StoreAttribute` `[...]]`	Defines the attributes for the specified data store. Data store attributes include PORT, TIMEOUT and FAILTHRESHOLD. `FullStoreName` is the data store file name specified in the `DataStore` attribute of the DSN description.
`{INCLUDE \| EXCLUDE}` `{TABLE [[Owner.]TableName[,...]]\|` `CACHE GROUP` `[[Owner.]CacheGroupName` `[,...]]\|` `SEQUENCE` `[[Owner.]SequenceName` `[,...]]}` `[,...]`	An active standby pair replicates an entire data store by default. `INCLUDE` includes only the listed tables, sequences or cache groups to replication. Use one `INCLUDE` clause for each object type (table, sequence or cache group). `EXCLUDE` removes tables, sequences or cache groups from the replication scheme. Use one `EXCLUDE` clause for each object type (table, sequence or cache group).
`DURABLE COMMIT {ON \| OFF}`	Set to override the `DurableCommits` setting on a data store and enable durable commit when return service blocking has been disabled by DISABLE RETURN.
`FAILTHRESHOLD` `Value`	The number of log files that can accumulate for a subscriber data store. If this value is exceeded, the subscriber is set to the Failed state.The value 0 means "No Limit." This is the default. See "Setting the log failure threshold" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information.
`LOCAL COMMIT ACTION` `{NO ACTION \| COMMIT}`	Specifies the default action to be taken for a return twosafe transaction in the event of a timeout. Note: This attribute is valid only when the RETURN TWOSAFE or RETURN TWOSAFE BY REQUEST attribute is set in the SUBSCRIBER clause. `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 or rollback the call. This is the default. `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. This setting can be overridden for specific transactions by calling the `localAction` parameter in the `ttRepSyncSet` procedure.
`MASTER` `FullStoreName`	The data store on which applications update the specified ELEMENT. The MASTER data store sends updates to its SUBSCRIBER data stores. The `FullStoreName` must be the data store specified in the `DataStore` attribute of the DSN description.
`PORT` `PortNumber`	The TCP/IP port number on which the replication agent for the data store listens for connections. If not specified, the replication agent automatically allocates a port number. In an active standby pair, the standby master data store listens for updates from the active master data store. Read-only subscribers listen for updates from the standby master data store.
`ROUTE MASTER` `FullStoreName` `SUBSCRIBER` `FullStoreName`	Denotes the `NetworkOperation` clause. If specified, allows you to control the network interface that a master store uses for every outbound connection to each of its subscriber stores. In the context of the `ROUTE` clause, each master data store is a subscriber of the other master data store and each read-only subscriber is a subscriber of both master data stores. Can be specified more than once. For `FullStoreName`, `ON` `"host"` must be specified.
`MASTERIP` `MasterHost` `\|` `SUBSCRIBERIP` `SubscriberHost`	`MasterHost` and `SubscriberHost` are the IP addresses for the network interface on the master and subscriber stores. Specify in dot notation or canonical format or in colon notation for IPV6. Clause can be specified more than once.
`PRIORITY` `Priority`	Variable expressed as an integer from 1 to 99. Denotes the priority of the IP address. Lower integral values have higher priority. An error is returned if multiple addresses with the same priority are specified. Controls the order in which multiple IP addresses are used to establish peer connections. Required syntax of `NetworkOperation` clause. Follows `MASTERIP` `MasterHost` `\|` `SUBSCRIBERIP` `SubscriberHost` clause.
`TIMEOUT` `Seconds`	Set the maximum number of seconds a data store waits before re-sending a message to an unresponsive data store. In an active standby pair, the active master data store sends messages to the standby master data store. The standby master data store sends messages to the read-only subscribers.

Description

CREATE ACTIVE STANDBY PAIR is immediately followed by the names of the two master data stores. The master data stores are later designated as ACTIVE and STANDBY using the ttRepStateSet built-in procedure. See "Setting up an active standby pair with no cache groups" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.
The SUBSCRIBER clause lists one or more read-only subscriber data stores. You can designate up to 127 subscriber data stores.
Replication between the active master data store and the standby master data store can be RETURN TWOSAFE, RETURN RECEIPT, or asynchronous. RETURN TWOSAFE ensures no transaction loss.
Use the INCLUDE and EXCLUDE clauses to exclude the listed tables, sequences and cache groups from replication, or to include only the listed tables, sequences and cache groups, excluding all others.
If the active standby pair has the RETURN TWOSAFE attribute and replicates a cache group, a transaction may fail if:
- The transaction that is being replicated contains an ALTER TABLE statement or an ALTER CACHE GROUP statement
- The transaction contains an INSERT, UPDATE or DELETE statement on a replicated table, replicated cache group or an asynchronous writethrough cache group
Using an active standby pair to replicate read-only cache groups and asynchronous writethrough (AWT) cache groups is supported.
You cannot use an active standby pair to replicate synchronous writethrough (SWT) cache groups. If you are using an active standby pair to replicated a data store with SWT cache groups, you must either drop or exclude the SWT cache groups.

Examples

This example creates an active standby pair whose master data stores 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 data stores.

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

Specify NetworkOperation clause to control network interface:

CREATE ACTIVE STANDBY PAIR rep1,rep2
ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2"
MASTERIP "1.1.1.1" PRIORITY 1 SUBSCRIBERIP "2.2.2.2" PRIORITY 1;