CREATE ACTIVE STANDBY PAIR

This statement is not supported in TimesTen Scaleout.

In TimesTen Classic:

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

Required Privilege

ADMIN

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

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 [,...]]} [,...]]

Syntax for ReturnServiceAttribute: 

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

Syntax for StoreAttribute: 

  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 
  TABLE DEFINITION CHECKING {RELAXED|EXACT}

Syntax for NetworkOperation: 

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

Parameters

Parameter Description

FullStoreName

The database, specified as one of the following:

  • SELF

  • The prefix of the database file name

For example, if the database path is directory/subdirectory/data.ds0, then data is the database name that should be used.

This is the database 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 the Network in Oracle TimesTen In-Memory Database 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 database are blocked until the transaction is received by the standby master database.

Specifying RETURN RECEIPT applies the service to all transactions. If you specify RETURN RECEIPT 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 Replication Guide.

RETURN TWOSAFE [BY REQUEST]

Enables the return twosafe service, so that applications that commit a transaction to an active master database are blocked until the transaction is committed on the standby master database.

Specifying 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 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 database.

This failure policy can be specified for either the RETURN RECEIPT or RETURN TWOSAFE service.

RETURN SERVICES {ON | OFF} WHEN [REPLICATION] STOPPED

Sets return services on or off when replication is disabled (stopped or paused state).

OFF disables return services when replication is disabled and is the default for RETURN RECEIPT service. ON allows return services to continue to be enabled when replication is disabled and is the default for RETURN TWOSAFE service.

See Establishing Return Service Failure and Recovery Policies in Oracle TimesTen In-Memory Database Replication Guide.

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 Replication Guide.

RETURN WAIT TIME Seconds

Specifies the number of seconds to wait for return service acknowledgment. A value of 0 (zero) 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 database that receives updates from a master database. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

STORE FullStoreName [StoreAttribute [...]]

Defines the attributes for the specified database. Attributes include PORT, TIMEOUT and FAILTHRESHOLD. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

TABLE DEFINITION CHECKING {EXACT|RELAXED}

StoreAttribute clause.

Specifies type of table definition checking that occurs on the subscriber:

  • EXACT - The tables must be identical on master and subscriber.

  • RELAXED - The tables must have the same key definition, number of columns and column data types.

The default is RELAXED.

Note: If you use TABLE DEFINITION CHECKING EXACT, use ttMigrate -exactUpgrade if you migrate the database. If you use TABLE DEFINITION CHECKING RELAXED, use ttMigrate -relaxedUpgrade if you migrate the database.

{INCLUDE | EXCLUDE}

{TABLE [[Owner.]TableName[,...]]|

CACHE GROUP

[[Owner.]CacheGroupName

[,...]]|

SEQUENCE

[[Owner.]SequenceName

[,...]]}

[,...]

An active standby pair replicates an entire database by default.

INCLUDE includes only the listed tables, sequences or cache groups for the replication scheme. Use one INCLUDE clause for each object type (table, sequence or cache group).

EXCLUDE removes tables or sequences or cache groups from the replication scheme. Use one EXCLUDE clause for each object type (table, sequence or cache group).

Do not use the EXCLUDE clause for AWT cache groups.

COMPRESS TRAFFIC {ON | OFF}

Compress replicated traffic to reduce the amount of network bandwidth. ON specifies that all replicated traffic for the database defined by STORE be compressed. OFF (the default) specifies no compression. See Compressing Replicated Traffic in Oracle TimesTen In-Memory Database Replication Guide for details.

DURABLE COMMIT {ON | OFF}

Overrides the DurableCommits general connection attribute setting. DURABLE COMMIT ON enables durable commits regardless of whether the replication agent is running or stopped. It also enables durable commits when the ttRepStateSave built-in procedure has marked the standby database as failed.

FAILTHRESHOLD Value

The number of log files that can accumulate for a subscriber database. 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 Transaction Log Failure Threshold in Oracle TimesTen In-Memory Database 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 only reissue the commit. The transaction may not be rolled back. 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 database on which applications update the specified element. The MASTER database sends updates to its SUBSCRIBER databases. The FullStoreName must be the database specified in the DataStore attribute of the DSN description.

PORT PortNumber

The TCP/IP port number on which the replication agent for the database listens for connections. If not specified, the replication agent automatically allocates a port number.

In an active standby pair, the standby master database listens for updates from the active master database. Read-only subscribers listen for updates from the standby master database.

ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName

Denotes the NetworkOperation clause. If specified, enables 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, you can define the following:

  • A route for the active database to the standby database and for the standby database to the active database for when failover occurs

  • A route for a read-only subscriber to the active and standby databases

When using active standby pairs, ROUTE should be specified at least twice for an active standby pair with no read only subscribers. Then, ROUTE should be specified twice more for each read only subscriber on the active standby pair.

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

The maximum number of seconds the replication agent waits for a response from remote replication agents. The default is 120 seconds.

In an active standby pair, the active master database sends messages to the standby master database. The standby master database sends messages to the read-only subscribers.

Note: For large transactions that may cause a delayed response from the remote replication agent, the agent scales the timeout based on the size of the transaction. This scaling is disabled if you set TIMEOUT to less than or equal to 60 seconds. Also see Setting Wait Timeout for Response from Remote Replication Agents in Oracle TimesTen In-Memory Database Replication Guide.

Description

  • After you create an active standby pair, make one of your databases the active database. To accomplish this, call ttRepStateSet ('ACTIVE'). Then use ttRepAdmin to duplicate the active database to the second database. When the operation is successful, the second database becomes the standby database. For more information, see Setting Up an Active Standby Pair with No Cache Groups in Oracle TimesTen In-Memory Database Replication Guide.

  • The SUBSCRIBER clause lists one or more read-only subscriber databases. You can designate up to 127 subscriber databases.

  • Replication between the active master database and the standby master database 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.

  • You can use an active standby pair to replicate read-only cache groups and asynchronous writethrough (AWT) cache groups. You cannot use an active standby pair to replicate synchronous writethrough (SWT) cache groups or user managed cache groups.

  • You cannot use the EXCLUDE clause for AWT cache groups.

  • You cannot execute the CREATE ACTIVE STANDBY PAIR statement when Oracle Clusterware is used with TimesTen.

Examples

This 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. 

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
ROUTE MASTER rep2 ON "machine2" SUBSCRIBER rep1 ON "machine1"
MASTERIP "2.2.2.2" PRIORITY 1 SUBSCRIBERIP "1.1.1.1" PRIORITY 1;

See Also