ALTER ACTIVE STANDBY PAIR

This statement is not supported in TimesTen Scaleout.

In TimesTen Classic:

You can change an active standby pair by:

  • Adding or dropping a subscriber database

  • Altering store attributes

    Only the PORT and TIMEOUT attributes can be set for subscribers.

  • Including tables, sequences or cache groups in the replication scheme

  • Excluding tables, sequences or cache groups from the replication scheme

See Making Other Changes to an Active Standby Pair in Oracle TimesTen In-Memory Database Replication Guide.

Required Privilege

ADMIN

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL Syntax

ALTER ACTIVE STANDBY PAIR { 
      SubscriberOperation | 
      StoreOperation | InclusionOperation |
      NetworkOperation } [...];

Syntax for SubscriberOperation: 

{ADD | DROP } SUBSCRIBER FullStoreName

Syntax for StoreOperation: 

ALTER STORE FullStoreName SET StoreAttribute

Syntax for InclusionOperation: 

    [{ INCLUDE | EXCLUDE }{TABLE [[Owner.]TableName [,...]]|
         CACHE GROUP [[Owner.]CacheGroupName [,...]]|
         SEQUENCE [[Owner.]SequenceName [,...]]} [,...]]

Syntax for NetworkOperation: 

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

ADD SUBSCRIBER FullStoreName

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

DROP SUBSCRIBER FullStoreName

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

ALTER STORE FullStoreName SET StoreAttribute

Indicates changes to the attributes of a database. Only the PORT and TIMEOUT attributes can be set for subscribers. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

See CREATE ACTIVE STANDBY PAIR For information on StoreAttribute clauses.

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

{INCLUDE|EXCLUDE}

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

CACHE GROUP

[[Owner.]CacheGroupName]|[,...]

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

[,...]

Includes in or excludes from replication the tables, sequences or cache groups listed.

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

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

You cannot use the EXCLUDE clause for AWT cache groups.

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. In the context of the ADD ROUTE clause, each master database is a subscriber of the other master database and each read-only subscriber is a subscriber of both master databases.

Can be specified more than once.

For FullStoreName, "ON host" must be specified.

DROP ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName

Drops NetworkOperation from 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

  • You must stop the replication agent before altering an active standby pair. The exceptions are for those objects and statements that are automatically replicated and included based on the values of the DDL_REPLICATION_LEVEL and DDL_REPLICATION_ACTION attributes. See ALTER SESSION for more information.

  • You can only alter the active standby pair replication scheme on the active database. See Making Other Changes to an Active Standby Pair in Oracle TimesTen In-Memory Database Replication Guide for more information.

  • Do not use ALTER ACTIVE STANDBY PAIR when using Oracle Clusterware with TimesTen. See Restricted Commands and SQL Statements in Oracle TimesTen In-Memory Database Replication Guide for more information.

    Instead, perform the tasks described in Changing the Schema section of the Oracle TimesTen In-Memory Database Replication Guide.

  • Use ADD SUBSCRIBER FullStoreName to add a subscriber to the replication scheme.

  • Use DROP SUBSCRIBER FullStoreName to drop a subscriber from the replication scheme.

  • Use the INCLUDE or EXCLUDE clause to include the listed tables, sequences or cache groups in the replication scheme or to exclude them from the replication scheme. Use one INCLUDE or EXCLUDE clause for each object type (table, sequence or cache group). The ALTER ACTIVE STANDBY statement is not necessary for those objects and statements that are automatically replicated and included based on the values of the DDL_REPLICATION_LEVEL and DDL_REPLICATION_ACTION attributes. See ALTER SESSION for more information. However, if DDL_REPLICATION_LEVEL is 2 or greater and DDL_REPLICATION_ACTION="EXCLUDE", use the INCLUDE clause to include replicated objects into the replication scheme.

  • Do not use the EXCLUDE clause for AWT cache groups.

  • When DDL_REPLICATION_LEVEL is 2 or greater, the INCLUDE clause can only be used with empty tables on the active database. The contents of the corresponding tables on the standby and any subscribers will be truncated before the table is added to the replication scheme.

Examples

Add a subscriber to the replication scheme.

ALTER ACTIVE STANDBY PAIR
    ADD SUBSCRIBER rep4;

Drop two subscribers from the replication scheme.

ALTER ACTIVE STANDBY PAIR
    DROP SUBCRIBER rep3
    DROP SUBSCRIBER rep4;

Alter the store attributes of the rep3 and rep4 databases. 

ALTER ACTIVE STANDBY PAIR
    ALTER STORE rep3 SET PORT 23000 TIMEOUT 180
    ALTER STORE rep4 SET PORT 23500 TIMEOUT 180;

Add a table, a sequence and two cache groups to the replication scheme.

ALTER ACTIVE STANDBY PAIR
    INCLUDE TABLE my.newtab
    INCLUDE SEQUENCE my.newseq
    INCLUDE CACHE GROUP my.newcg1, my.newcg2;

Add NetworkOperation clause to active standby pair: 

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

See Also