ALTER REPLICATION

This statement is not supported in TimesTen Scaleout.

In TimesTen Classic:

The ALTER REPLICATION statement adds, alters, or drops replication elements and changes the replication attributes of participating databases involved in a classic replication scheme.

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

Required Privilege

ADMIN

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

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

DROP ELEMENT { ElementName | * IN FullStoreName }

CheckConflicts can only be set when replicating TABLE elements. See CHECK CONFLICTS for syntax requirements.

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

Parameter Description

[Owner.]ReplicationSchemeName

Name assigned to the classic replication scheme.

ADD ELEMENT ElementName

Adds a new element to the existing classic replication scheme. ElementName is an identifier of up to 30 characters. With DATASTORE elements, the ElementName must be unique with respect to other DATASTORE element names within the first 20 characters.

If the element is a DATASTORE, all tables are included in the database. SEQUENCE elements that are part of the database do not have their return services modified by this statement.

ADD ELEMENT ElementName DATASTORE

{INCLUDE | EXCLUDE}

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

SEQUENCE [[Owner.]SequenceName[,...]]} [,...]

Adds a new DATASTORE element to the existing classic replication scheme. ElementName is an identifier of up to 30 characters. With DATASTORE elements, the ElementName must be unique with respect to other DATASTORE element names within the first 20 characters.

INCLUDE includes in the database only the tables and sequences listed. Use one INCLUDE clause for each object type (table or sequence).

EXCLUDE includes in the database all tables and sequences except the tables and sequences listed. Use one EXCLUDE clause for each object type (table or sequence).

If the element is a sequence, RETURN attributes are not applied, no conflict checking is supported and sequences that cycle return an error.

ADD SUBSCRIBER FullStoreName

Indicates an additional subscriber database. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

ALTER ELEMENT * IN FullStoreName

SET { MASTER | PROPAGATOR } FullStoreName

Makes a change to all elements for which FullStoreName is the MASTER or PROPAGATOR. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

This syntax can be used on a set of element names to:

  • Add, alter, or drop subscribers.

  • Set the MASTER or PROPAGATOR status of the element set.

SEQUENCE elements that are part of the database being altered do not have their return services modified by this statement.

ALTER ELEMENT ElementName

Name of the element to which a subscriber is to be added or dropped.

ALTER ELEMENT

ElementName1

SET NAME ElementName2

Renames ElementName1 with the name ElementName2. You can only rename elements of type TABLE.

ALTER ELEMENT ElementName

{INCLUDE|EXCLUDE}

{TABLE [Owner.]TableName |

SEQUENCE [Owner.]SequenceName} [,...]

ElementName is the name of the element to be altered.

INCLUDE adds to the database the tables and sequences listed. Use one INCLUDE clause for each object type (table or sequence).

EXCLUDE removes from the database the tables and sequences listed. Use one EXCLUDE clause for each object type (table or sequence).

If the element is a sequence, RETURN attributes are not applied, no conflict checking is supported and sequences that cycle return an error.

ALTER SUBSCRIBER FullStoreName

SET RETURN RECEIPT

[BY REQUEST]|NO RETURN

Indicates an alteration to a subscriber database to enable, disable, or change the return receipt service. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

CheckConflicts

Check for replication conflicts when simultaneously writing to bidirectionally replicating TABLE elements between databases. You cannot check for conflicts when replicating elements of type DATASTORE. See CHECK CONFLICTS.

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.

CONFLICT REPORTING SUSPEND AT Value

Suspends conflict resolution reporting.

Value is a non-negative integer. Conflict reporting is suspended when the rate of conflict exceeds Value. The default is 0, which means that the conflict reporting is always on and will not be suspended.

This clause is valid for table level replication.

CONFLICT REPORTING RESUME AT Value

Resumes conflict resolution reporting.

Value is a non-negative integer. Conflict reporting is resumed when the rate of conflict falls below Value. The default is 1.

This clause is valid for table level replication.

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. Selecting SUBSCRIBER applies this policy only to the subscriber that fails to acknowledge replicated updates within the set timeout period. ALL applies this policy to all subscribers should any of the subscribers fail to respond. This failure policy can be specified for either the RETURN RECEIPT or RETURN TWOSAFE service.

If DISABLE RETURN is specified but RESUME RETURN is not specified, the return services remain off until the replication agent for the database has been restarted.

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.

DROP ELEMENT * IN FullStoreName

Deletes the replication description of all elements for which FullStoreName is the MASTER. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

DROP ELEMENT ElementName

Deletes the replication description of ElementName.

DROP SUBSCRIBER FullStoreName

Indicates that updates should no longer be sent to the specified subscriber database. This operation fails if the classic replication scheme has only one subscriber. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

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.

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.

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

LOCAL COMMIT ACTION {NO ACTION | COMMIT}

Specifies the default action to be taken for a RETURN TWOSAFE transaction in the event of a timeout.

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 ttRepSyncSet procedure with the localAction parameter.

MASTER FullStoreName

The database on which applications update the specified element. The MASTER database sends updates to its SUBSCRIBER databases. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

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.

PORT PortNumber

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

All TimesTen databases that replicate to each other must use the same port number.

PROPAGATOR FullStoreName

The database that receives replicated updates and passes them on to other databases.

RESUME RETURN Milliseconds

If return service blocking has been disabled by DISABLE RETURN, this attribute sets the policy on when to re-enable return service blocking. Return service blocking is re-enabled as soon as the failed subscriber acknowledges the replicated update in a period of time that is less than the specified Milliseconds.

If DISABLE RETURN is specified but RESUME RETURN is not specified, the return services remain off until the replication agent for the database has been restarted.

RETURN RECEIPT [BY REQUEST]

Enables the return receipt service, so that applications that commit a transaction to a master database are blocked until the transaction is received by all subscribers.

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

RETURN TWOSAFE [BY REQUEST]

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

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.

RETURN WAIT TIME Seconds

Specifies the number of seconds to wait for return service acknowledgment. The default value is 10 seconds. A value of 0 (zero) means there is no timeout. Your application can override this timeout setting by calling the ttRepSyncSet procedure with the returnWait parameter.

SET {MASTER | PROPAGATOR} FullStoreName

Sets the given database to be the MASTER or PROPAGATOR of the given elements. The FullStoreName must the be database's file base name.

SUBSCRIBER FullStoreName

A database that receives updates from the MASTER databases. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

TABLE DEFINITION CHECKING {EXACT|RELAXED}

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.

TIMEOUT Seconds

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

Note: For large transactions that may cause a delayed response from the remote replication agent, the agent scales the timeout to increasingly larger values, as needed, based on the size of the transaction. This scaling will not occur, and the agent may time out waiting for responses, if you set TIMEOUT to less than or equal to 60 seconds for large transactions. Also see Setting Wait Timeout for Response from Remote Replication Agents in Oracle TimesTen In-Memory Database Replication Guide.

ADD ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName

Adds NetworkOperation to replication scheme. Enables you to control the network interface that a master store uses for every outbound connection to each of its subscriber stores.

Can be specified more than once.

For FullStoreName, ON "host" must be specified.

DROP ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName

Drops NetworkOperation from the classic replication scheme.

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. Valid for both ADD and DROP ROUTE MASTER.

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.

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 database. SET NAME does not admit the use of * IN FullStoreName. The FullStoreName must be the database's file base name. For example, if the database file name is data.ds0, then data is the file base name.

  • ALTER ELEMENT SET MASTER may be used to change the master database for replication elements. The * IN FullStoreName option must be used for the MASTER operation. That is, a master database 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 databases after the failure of a (common) MASTER.

  • To transfer ownership of the master elements to the subscriber:

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

    2. 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 database, and the currently designated MASTER cannot be deleted from the replication scheme.

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

  • You cannot alter the following replication schemes with the ALTER REPLICATION statement:

    • Any active standby pair. Instead, use ALTER ACTIVE STANDBY PAIR.

    • A Clusterware-managed active standby pair. Instead, perform the tasks described in Changing the Schema section of the Oracle TimesTen In-Memory Database Replication Guide.

Examples

This example sets up a classic replication scheme for an additional table westleads that is updated on database west and replicated to database 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 database element in my.rep1 replication scheme. 

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

This example adds ds1 database to my.rep1 replication scheme. Include my.tab2 table in the database. 

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

This example adds ds2 database to a replication scheme but excludes my.tab1 table. 

ALTER REPLICATION my.rep1
  ADD ELEMENT ds2 DATASTORE
     MASTER rep2
     SUBSCRIBER rep1
     EXCLUDE TABLE my.tab1;

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 database, see Altering a Replicated Table in a Classic Replication Scheme in Oracle TimesTen In-Memory Database Replication Guide.