ALTER REPLICATION

The ALTER REPLICATION statement adds, alters, or drops replication elements and changes the replication attributes of participating data stores.

Most ALTER REPLICATION operations are supported only when the replication agent is stopped (ttAdmin -repStop). However, it is possible to dynamically add a subscriber data store to a replication scheme while the replication agent is running. See "Altering Replication" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information.

Required privilege

ADMIN

SQL syntax

The ALTER REPLICATION statement has the syntax:

ALTER REPLICATION [Owner.]ReplicationSchemeName
     ElementOperation [...] | StoreOperation |
     NetworkOperation [...]

Specify ElementOperation one or more times:

ADD ELEMENT ElementName
    {DATASTORE | {TABLE [Owner.]TableName [CheckConflicts]} | 
                 SEQUENCE [Owner.]SequenceName}
             { MASTER | PROPAGATOR } FullStoreName
             { SUBSCRIBER FullStoreName [, … ]
                [ReturnServiceAttribute] } [ … ] }
             { INCLUDE | EXCLUDE }{TABLE [[Owner.]TableName[,...]] | 
                    CACHE GROUP [[Owner.]CacheGroupName[,...]]| 
                    SEQUENCE [[Owner.]SequenceName[,...]]}[,...]
    ALTER ELEMENT { ElementName | * IN FullStoreName]
     ADD SUBSCRIBER FullStoreName [,...[ReturnServiceAttribute] |
            ALTER SUBSCRIBER FullStoreName [, …]   |
                SET [ReturnServiceAttribute]   |
            DROP SUBSCRIBER FullStoreName [, … ]
    ALTER ELEMENT * IN FullStoreName
             SET { MASTER | PROPAGATOR } FullStoreName
    ALTER ELEMENT ElementName
             {SET NAME NewElementName | SET CheckConflicts}
    ALTER ELEMENT ElementName 
             { INCLUDE | EXCLUDE }{TABLE [Owner.]TableName |
                    CACHE GROUP [Owner.]CacheGroupName | 
                    SEQUENCE [Owner.]SequenceName}[,...] 
    DROP ELEMENT { ElementName | * IN FullStoreName }

CheckConflicts can only be set when replicating TABLE elements. The syntax is described in "CHECK CONFLICTS".

Syntax for ReturnServiceAttribute is:

{ RETURN RECEIPT [BY REQUEST] | NO RETURN }

StoreOperation clauses:

ADD STORE FullStoreName [StoreAttribute [… ]]
    ALTER STORE FullStoreName SET StoreAttribute [… ]

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

Specify NetworkOperation one or more times:

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

Parameters

The ALTER REPLICATION statement has the parameters:

Description

• ALTER ELEMENT DROP SUBSCRIBER deletes a subscriber for a particular replication element.

• ALTER ELEMENT SET NAME may be used to change the name of a replication element when it conflicts with one already defined at another data store. SET NAME does not admit the use of * IN FullStoreName. The FullStoreName must the be data store's file base name. For example, if the data store file name is data.ds0, then data is the file base name.

• ALTER ELEMENT SET MASTER may be used to change the master data store for replication elements. The * IN FullStoreName option must be used for the MASTER operation. That is, a master data store must transfer ownership of all of its replication elements, thereby giving up its master role entirely. Typically, this option is used in ALTER REPLICATION statements requested at SUBSCRIBER data stores after the failure of a (common) MASTER.

To transfer ownership of the master elements to the subscriber:

• Manually drop the replicated elements by executing an ALTER REPLICATION DROP ELEMENT statement for each replicated table.

• Use ALTER REPLICATION ADD ELEMENT to add each table back to the replication scheme, with the newly designated MASTER / SUBSCRIBER roles.

• ALTER REPLICATION ALTER ELEMENT SET MASTER does not automatically retain the old master as a subscriber in the scheme. If this is desired, execute an ALTER REPLICATION ALTER ELEMENT ADD SUBSCRIBER statement.

  Note:

  There is no ALTER ELEMENT DROP MASTER. Each replication element must have exactly one MASTER data store, and the currently designated MASTER cannot be deleted from the replication scheme.

• Stop the replication agent before you use the NetworkOperation clause.

Examples

This example sets up replication for an additional table westleads that is updated on data store west and replicated to data store east.

ALTER REPLICATION r1
   ADD ELEMENT e3 TABLE westleads
     MASTER west ON "westcoast"
     SUBSCRIBER east ON "eastcoast";

This example adds an additional subscriber (backup) to table westleads.

ALTER REPLICATION r1
   ALTER ELEMENT e3
     ADD SUBSCRIBER backup ON "backupserver";

This example changes the element name of table westleads from e3 to newelementname.

ALTER REPLICATION r1
   ALTER ELEMENT e3
     SET NAME newelementname;

This example makes newwest the master for all elements for which west currently is the master.

ALTER REPLICATION r1
   ALTER ELEMENT * IN west
     SET MASTER newwest;

This element changes the port number for east.

ALTER REPLICATION r1
   ALTER STORE east ON "eastcoast" SET PORT 22251;

This example adds my.tab1 table to the ds1 data store element in my.rep1 replication scheme.

ALTER REPLICATION my.rep1
  ALTER ELEMENT ds1 DATASTORE
     INCLUDE TABLE my.tab1;

This example adds my.cg1 cache group to ds1 data store in my.rep1 replication scheme.

ALTER REPLICATION my.rep1
  ALTER ELEMENT ds1 DATASTORE
     INCLUDE CACHE GROUP my.cg1;

This example adds ds1 data store to my.rep1 replication scheme. Include my.tab2 table, my.cg2 cache group, and my.cg3 cache group in the data store.

ALTER REPLICATION my.rep1
  ADD ELEMENT ds1 DATASTORE
     MASTER rep2
     SUBSCRIBER rep1, rep3
     INCLUDE TABLE my.tab2
     INCLUDE CACHE GROUP my.cg2, my.cg3;

This example adds ds2 data store to a replication scheme but exclude my.tab1 table, my.cg0 cache group and my.cg1 cache group.

ALTER REPLICATION my.rep1
  ADD ELEMENT ds2 DATASTORE
     MASTER rep2
     SUBSCRIBER rep1
     EXCLUDE TABLE my.tab1
     EXCLUDE CACHE GROUP my.cg0, my.cg1;

Add NetworkOperation clause:

ALTER REPLICATION r
ADD ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2"
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;

Drop NetworkOperation clause:

ALTER REPLICATION r
DROP ROUTE MASTER repl ON "machine1" SUBSCRIBER rep2 ON "machine2"
MASTERIP "1.1.1.1" SUBSCRIBERIP "2.2.2.2"
MASTERIP "3.3.3.3" SUBSCRIBERIP "4.4.4.4";

See also

To drop a table from a data store, see "Altering a replicated table" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.