CREATE REPLICATION

TimesTen SQL configuration for replication provides a programmable way to configure replication. The configuration can be embedded in C, C++ or Java code. Replication can be configured locally or from remote systems using client/server.

In addition, you need to use the ttRepAdmin utility to maintain operations not covered by the supported SQL statements. Use ttRepAdmin to change replication state, duplicate data stores, list the replication configuration and view replication status.

The CREATE REPLICATION statement:

• Defines a replication scheme at a participating data store.

• Installs the specified configuration in the executing data store's replication system tables.

• Typically consists of one or more replication ELEMENT specifications and zero or more STORE specifications.

Required privilege

ADMIN

Definitions

A replication element is an entity that TimesTen synchronizes between data stores. A replication element can be a whole table or a data store. A data store can include most types of tables and cache groups. It can include only specified tables and cache groups, or include all tables except specified tables and cache groups. It cannot include temporary tables or views, whether materialized and nonmaterialized.

A replication scheme is a set of replication elements, as well as the data stores that maintain copies of these elements.

When replicating cache groups:

• When replicating cache groups between data stores, both cache groups must be identical, with the exception of the settings for AUTOREFRESH and PROPAGATE.

• When replicating a cache group with AUTOREFRESH, the cache group on the subscriber must set the autorefresh STATE to OFF. In a bidirectional replication scheme, one of the cache groups must set the autorefresh STATE to OFF.

• If a master cache group specifies PROPAGATE, the subscriber cache group must set the autorefresh STATE to OFF.

For more detailed information on SQL configuration for replication, see Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

SQL syntax

CREATE REPLICATION [Owner.]ReplicationSchemeName
{ ELEMENT ElementName
  { DATASTORE | { TABLE [Owner.]TableName [CheckConflicts]} | 
       SEQUENCE [Owner.]SequenceName}
     { MASTER | PROPAGATOR } FullStoreName
     [TRANSMIT { NONDURABLE | DURABLE }]
     { SUBSCRIBER FullStoreName [,...]
        [ReturnServiceAttribute] } [, ...] }
     [...]
     [{INCLUDE | EXCLUDE}
          {TABLE [[Owner.]TableName[,...]] |
           CACHE GROUP [[Owner.]CacheGroupName[,...]] | 
           SEQUENCE [[Owner.]SequenceName[,...]} [,...]] 
[ STORE FullStoreName [StoreAttribute [... ]]] [...]
[ NetworkOperation[...]]

Syntax for CheckConflicts is described in "CHECK CONFLICTS".

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 ]
  [ CONFLICT REPORTING SUSPEND AT Value ]
  [ CONFLICT REPORTING RESUME AT Value ]
  [ TABLE DEFINITION CHECKING {RELAXED|EXACT}]

Syntax for NetworkOperation:

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

Parameters

The CREATE REPLICATION statement has the parameters:

---

CHECK CONFLICTS

Syntax

The syntax for CHECK CONFLICTS is:

{NO CHECK |
CHECK CONFLICTS BY ROW TIMESTAMP
      COLUMN ColumnName
      [ UPDATE BY { SYSTEM | USER } ]
      [ ON EXCEPTION { ROLLBACK [ WORK ] | NO ACTION } ]
      [ {REPORT TO 'FileName'
             [ FORMAT { XML | STANDARD } ] | NO REPORT
      } ]
}

Parameters

The CHECK CONFLICTS clause of the CREATE REPLICATION or ALTER REPLICATION statement has the parameters:

Description

• The names of all data stores on the same host must be unique for each replication scheme for each TimesTen instance.

• Replication elements can only be updated (by normal application transactions) through the MASTER data store. PROPAGATOR and SUBSCRIBER data stores are read-only.

• If you define a replication scheme that permits multiple data stores to update the same table, see "Conflict Resolution and Failure Recovery" inOracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for recommendations on how to avoid conflicts when updating rows.

• SELF is intended for replication schemes where all participating data stores are local. Do not use SELF for a distributed replication scheme in a production environment, where spelling out the hostname for each data store in a script allows it to be used at each participating data store.

• Each attribute for a given STORE may be specified only once, or not at all.

• Specifying the PORT of a data store for one replication scheme specifies it for all replication schemes. All other data store attributes are specific to the replication scheme specified in the command.

• For replication schemes, DataStoreName is always the prefix of the TimesTen data store checkpoint file names. These are the files with the.ds0 and.ds1 suffixes that are saved on disk by checkpoint operations.

• If a row with a default NOT INLINE VARCHAR value is replicated, the receiver creates a copy of this value for each row instead of pointing to the default value if and only if the default value of the receiving node is different from the sending node.

• To use timestamp comparison on replicated tables, you must specify a nullable column of type BINARY(8) to hold the timestamp value. Define the timestamp column when you create the table. You cannot add the timestamp column with the ALTER TABLE statement. In addition, the timestamp column cannot be part of a primary key or index.

• If you specify the XML report format, two XML documents are generated:

  • FileName.xml: This file contains the DTD for the report and the root node for the report. It includes the document definition and the include directive.

  • FileName.include: This file is included in FileName.xml and contains all the actual conflicts.

  • The FileName.include file can be truncated. Do not truncate the FileName.xml file.

  • For a complete description of the XML format, including examples of each conflict, see "Reporting conflicts to an XML file" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

• If you specify a report format for an element and then drop the element, the corresponding report files are not deleted.

• Use the CONFLICT REPORTING SUSPEND AT clause to specify a high water mark threshold at which the reporting of conflict resolution is suspended. When the number of conflicts per second exceeds the specified high water mark threshold, conflict resolution reporting (if configured and reported by the report file) and SNMP are suspended and an SNMP trap is emitted to indicate that it has been suspended.

• Use the CONFLICT REPORTING RESUME AT clause to specify a low water mark threshold where the reporting of conflict resolution is resumed. When the rate of conflict falls below the low water mark threshold, conflict resolution reporting is resumed. A SNMP trap is emitted to indicate the resumption of conflict resolution. This trap provides the number of unreported conflicts during the time when conflict resolution was suspended.

• The state of whether conflict reporting is suspended or not by a replication agent does not persist across the local replication agent and the peer agent stop and restart.

• Do not use the CREATE REPLICATION statement to replicate dynamic read-only cache groups asynchronously. Use the CREATE ACTIVE STANDBY PAIR statement.

Examples

Replicate the contents of repl.tab from masterds to two subscribers, subscriber1ds and subscriber2ds.

CREATE REPLICATION repl.twosubscribers
       ELEMENT e TABLE repl.tab
         MASTER masterds ON "server1"
         SUBSCRIBER subscriber1ds ON "server2",
                    subscriber2ds ON "server3";

Replicate the entire masterds data store to the subscriber, subscriber1ds. The FAILTHRESHOLD specifies that a maximum of 10 log files can accumulate on masterds before it assumes subscriber1ds has failed.

CREATE REPLICATION repl.wholestore
  ELEMENT e DATASTORE
     MASTER masterds ON "server1"
     SUBSCRIBER subscriber1ds ON "server2"
  STORE masterds FAILTHRESHOLD 10;

Bidirectionally replicate the entire westds and eastds data stores and enable the RETURN TWOSAFE service.

CREATE REPLICATION repl.biwholestore
  ELEMENT e1 DATASTORE
     MASTER westds ON "westcoast"
     SUBSCRIBER eastds ON "eastcoast"
        RETURN TWOSAFE
  ELEMENT e2 DATASTORE
     MASTER eastds ON "eastcoast"
     SUBSCRIBER westds ON "westcoast"
        RETURN TWOSAFE;

Enable the return receipt service for select transaction updates to the subscriber1ds subscriber.

CREATE REPLICATION repl.twosubscribers
  ELEMENT e TABLE repl.tab
     MASTER masterds ON "server1"
     SUBSCRIBER subscriber1ds ON "server2"
        RETURN RECEIPT BY REQUEST
     SUBSCRIBER subscriber2ds ON "server3";

Replicate the contents of the customerswest table from the west data store to the ROUNDUP data store and the customerseast table from the east data store. Enable the return receipt service for all transactions.

CREATE REPLICATION r
       ELEMENT west TABLE customerswest
         MASTER west ON "serverwest"
         SUBSCRIBER roundup ON "serverroundup"
            RETURN RECEIPT
       ELEMENT east TABLE customerseast
         MASTER east ON "servereast"
         SUBSCRIBER roundup ON "serverroundup"
            RETURN RECEIPT;

Replicate the contents of the repl.tab table from the centralds data store to the propds data store, which propagates the changes to the backup1ds and backup2ds data stores.

CREATE REPLICATION repl.propagator
       ELEMENT a TABLE repl.tab
         MASTER centralds ON "finance"
         SUBSCRIBER proprds ON "nethandler"
       ELEMENT b TABLE repl.tab
         PROPAGATOR proprds ON "nethandler"
         SUBSCRIBER backup1ds ON "backupsystem1"
                    bakcup2ds ON "backupsystem2";

Bidirectionally replicate the contents of the repl.accounts table between the eastds and westds data stores. Each data store is both a master and a subscriber for the repl.accounts table.

Because the repl.accounts table can be updated on either the eastds or westds data store, it includes a timestamp column (tstamp). The CHECK CONFLICTS clause establishes automatic timestamp comparison to detect any update conflicts between the two data stores. In the event of a comparison failure, the entire transaction that includes an update with the older timestamp is rolled back (discarded).

CREATE REPLICATION repl.r1
ELEMENT elem_accounts_1 TABLE repl.accounts
   CHECK CONFLICTS BY ROW TIMESTAMP
      COLUMN tstamp
      UPDATE BY SYSTEM
      ON EXCEPTION ROLLBACK
   MASTER westds ON "westcoast"
   SUBSCRIBER eastds ON "eastcoast"
ELEMENT elem_accounts_2 TABLE repl.accounts
   CHECK CONFLICTS BY ROW TIMESTAMP
      COLUMN tstamp
      UPDATE BY SYSTEM
      ON EXCEPTION ROLLBACK
   MASTER eastds ON "eastcoast"
   SUBSCRIBER westds ON "westcoast";

Replicate the contents of the repl.accounts table from the activeds data store to the backupds data store, using the return twosafe service, and using TCP/IP port 40000 on activeds and TCP/IP port 40001 on backupds. The transactions on activeds need to be committed whenever possible, so configure replication so that the transaction is committed even after a replication timeout using LOCAL COMMIT ACTION, and so that the return twosafe service is disabled when replication is stopped. To avoid significant delays in the application if the connection to the backupds data store is interrupted, configure the return service to be disabled after five transactions have timed out, but also configure the return service to be re-enabled when the backupds data store's replication agent responds in under 100 milliseconds. Finally, the bandwidth between data stores is limited, so configure replication to compress the data when it is replicated from the activeds data store.

CREATE REPLICATION repl.r
ELEMENT elem_accounts_1 TABLE repl.accounts
   MASTER activeds ON "active"
   SUBSCRIBER backupds ON "backup"
      RETURN TWOSAFE
ELEMENT elem_accounts_2 TABLE repl.accounts
   MASTER activeds ON "active"
   SUBSCRIBER backupds ON "backup"
      RETURN TWOSAFE
STORE activeds ON "active"
   PORT 40000
   LOCAL COMMIT ACTION COMMIT
   RETURN SERVICES OFF WHEN REPLICATION STOPPED
   DISABLE RETURN SUBSCRIBER 5
   RESUME RETURN 100
   COMPRESS TRAFFIC ON
STORE backupds ON "backup"
   PORT 40001;

Illustrate conflict reporting suspend and conflict reporting resume clauses for table level replication. Use these clauses for table level replication not data store replication. Issue repschemes command to show that replication scheme is created.

Command> CREATE TABLE repl.accounts (tstamp BINARY (8) NOT NULL 
PRIMARY KEY, tstamp1 BINARY (8));
Command> CREATE REPLICATION repl.r2
> ELEMENT elem_accounts_1 TABLE repl.accounts
> CHECK CONFLICTS BY ROW TIMESTAMP
> COLUMN tstamp1
> UPDATE BY SYSTEM
> ON EXCEPTION ROLLBACK WORK
> MASTER westds ON "west1"
> SUBSCRIBER eastds ON "east1"
> ELEMENT elem_accounts_2 TABLE repl.accounts
> CHECK CONFLICTS BY ROW TIMESTAMP 
> COLUMN tstamp1
> UPDATE BY SYSTEM 
> ON EXCEPTION ROLLBACK WORK 
> MASTER eastds ON "east1"
> SUBSCRIBER westds ON "west1"
> STORE westds
> CONFLICT REPORTING SUSPEND AT 20
> CONFLICT REPORTING RESUME AT 10;
Command> REPSCHEMES;

Replication Scheme REPL.R2:

  Element: ELEM_ACCOUNTS_1
  Type: Table REPL.ACCOUNTS
  Conflict Check Column: TSTAMP1
  Conflict Exception Action: Rollback Work
  Conflict Timestamp Update: System
  Conflict Report File: (none)
  Master Store: WESTDS on WEST1 Transmit Durable
  Subscriber Store: EASTDS on EAST1

  Element: ELEM_ACCOUNTS_2
  Type: Table REPL.ACCOUNTS
  Conflict Check Column: TSTAMP1
  Conflict Exception Action: Rollback Work
  Conflict Timestamp Update: System
  Conflict Report File: (none)
  Master Store: EASTDS on EAST1 Transmit Durable
  Subscriber Store: WESTDS on WEST1

  Store: EASTDS on EAST1
    Port: (auto)
    Log Fail Threshold: (none)
    Retry Timeout: 120 seconds
    Compress Traffic: Disabled

  Store: WESTDS on WEST1
    Port: (auto)
    Log Fail Threshold: (none)
    Retry Timeout: 120 seconds
    Compress Traffic: Disabled
    Conflict Reporting Suspend: 20
    Conflict Reporting Resume: 10

1 replication scheme found.

Example of NetworkOperation clause with 2 MASTERIP and SUBSCRIBERIP clauses:

CREATE REPLICATION r ELEMENT e DATASTORE
MASTER rep1 SUBSCRIBER rep2 RETURN RECEIPT
MASTERIP "1.1.1.1" PRIORITY 1 SUBSCRIBERIP "2.2.2.2"
    PRIORITY 1
MASTERIP "3.3.3.3" PRIORITY 2 SUBSCRIBERIP "4.4.4.4"
    PRIORITY 2;

Example of NetworkOperation clause. Use the default sending interface but a specific receiving network:

CREATE REPLICATION r
ELEMENT e DATASTORE
MASTER rep1 SUBSCRIBER rep2
ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2"
SUBSCRIBERIP "rep2nic2" PRIORITY 1;

Example of using the NetworkOperation clause with multiple subscribers:

CREATE REPLICATION r ELEMENT e DATASTORE
MASTER rep1 SUBSCRIBER rep2,rep3
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 Rep1 ON "machine1" SUBSCRIBER Rep3 ON "machine2"
MASTERIP "3.3.3.3" PRIORITY 2 SUBSCRIBERIP "4.4.4.4";

See also

ALTER ACTIVE STANDBY PAIR
ALTER REPLICATION
CREATE ACTIVE STANDBY PAIR
DROP ACTIVE STANDBY PAIR
DROP REPLICATION