Skip Headers
Oracle® TimesTen In-Memory Database TimesTen to TimesTen Replication Guide
Release 11.2.1

Part Number E13072-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

10 Altering Replication

This chapter describes how to alter an existing replication system. Table 10-1 lists the tasks often performed on an existing replicated system.

Table 10-1 Tasks performed on an existing replicated system

Task What to do

Alter or drop a replication scheme

See"Altering a replication scheme" and "Dropping a replication scheme".

Alter a table used in a replication scheme

See "Altering a replicated table".

Truncate a table used in a replication scheme

See "Truncating a replicated table".

Change the replication state of a subscriber data store

See "Setting the replication state of subscribers".

Resolve update conflicts

See "Replication conflict detection and resolution".

Recover from failures

See "Managing data store failover and recovery".

Upgrade data store

Use the ttMigrate and ttRepAdmin utilities, as described in "Data Store Upgrades" in Oracle TimesTen In-Memory Database Installation Guide.


Altering a replication scheme

You can use ALTER REPLICATION to alter your replication scheme on the master and subscriber data stores. Any alterations on the master store must also be made on its subscribers.

Note:

You must have the ADMIN privilege to use the ALTER REPLICATION statement.

If you use ALTER REPLICATION to change a replication scheme that specifies a DATASTORE element, then:

Most ALTER REPLICATION operations are supported only when the replication agent is stopped (ttAdmin -repStop). However, it is possible to dynamically add a data store to a replication scheme while the replication agent is running, as described in "Creating and adding a subscriber data store".

The procedure for ALTER REPLICATION operations that require the replication agents to be stopped is:

  1. Use the ttRepStop procedure or ttAdmin -repStop to stop the replication agent for the master and subscriber data stores. While the replication agents are stopped, changes to the master data store are stored in the log.

  2. Issue the same ALTER REPLICATION statement on both master and subscriber data stores.

  3. Use the ttRepStart procedure or ttAdmin -repStart to restart the replication agent for the master and subscriber data stores. The changes stored in the master data store log are sent to the subscriber data store.

This section includes the following topics:

Adding a table or sequence to an existing replication scheme

There are two ways to add a table or sequence to an existing replication scheme:

  • When the element level of the replication scheme is TABLE or SEQUENCE, use the ALTER REPLICATION statement with the ADD ELEMENT clause to add a table or sequence. See Example 10-1.

  • When the element level of the replication scheme is DATASTORE, use the ALTER REPLICATION statement with the ALTER ELEMENT clause to include a table or sequence. See Example 10-2.

Example 10-1 Adding a sequence and a table to a replication scheme

This example uses the replication scheme r1, which was defined in Example 7-30. It alters replication scheme r1 to add sequence seq and table westleads, which will be updated on data store westds and replicated to data store eastds.

ALTER REPLICATION r1
  ADD ELEMENT elem_seq SEQUENCE seq
    MASTER westds ON "westcoast"
    SUBSCRIBER eastds ON "eastcoast"
  ADD ELEMENT elem_westleads TABLE westleads
    MASTER westds ON "westcoast"
    SUBSCRIBER eastds ON "eastcoast";

Example 10-2 Adding a sequence and a table to a DATASTORE element

Add the sequence my.seq and the table my.tab1 to the ds1 DATASTORE element in my.rep1 replication scheme.

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

Adding a DATASTORE element to an existing replication scheme

You can add a DATASTORE element to an existing replication scheme by using the ALTER REPLICATION statement with the ADD ELEMENT clause. All tables except temporary tables, materialized view, and nonmaterialized views are included in the data store if you do not use the INCLUDE or EXCLUDE clauses. Se"Including tables or sequences when you add a DATASTORE element" and "Excluding a table or sequence when you add a DATASTORE element".

Example 10-3 Adding a DATASTORE element to a replication scheme

Add a DATASTORE element to an existing replication scheme.

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

Including tables or sequences when you add a DATASTORE element

You can restrict replication to specific tables or sequences when you add a data store to an existing replication scheme. Use the ALTER REPLICATION statement with the ADD ELEMENT clause and the INCLUDE TABLE clause or INCLUDE SEQUENCE clause. You can have one INCLUDE clause for each table or sequence in the same ALTER REPLICATION statement.

Example 10-4 Including a table and sequence in a DATASTORE element

Add the ds1 DATASTORE element to my.rep1 replication scheme. Include the table my.tab2 and the sequence my.seq in the DATASTORE element.

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

Excluding a table or sequence when you add a DATASTORE element

You can exclude tables or sequences when you add a DATASTORE element to an existing replication scheme. Use the ALTER REPLICATION statement with the ADD ELEMENT clause and the EXCLUDE TABLE clause or EXCLUDE SEQUENCE clause. You can have one EXCLUDE clause for each table or sequence in the same ALTER REPLICATION statement.

Example 10-5 Excluding a table or sequence from a DATASTORE element

Add the ds2 DATASTORE element to a replication scheme, but exclude the table my.tab1 and the sequence my.seq.

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

Dropping a table or sequence from a replication scheme

This section includes the following topics:

Dropping a table or sequence that is replicated as part of a DATASTORE element

To drop a table or sequence that is part of a replication scheme at the DATASTORE level, complete the following tasks:

  1. Stop the replication agent.

  2. Exclude the table or sequence from the DATASTORE element in the replication scheme.

  3. Drop the table or sequence.

If you have more than one DATASTORE element that contains the table or sequence, then you must exclude the table or sequence from each element before you drop it.

Example 10-6 Excluding a table from a DATASTORE element and then dropping the table

Exclude the table my.tab1 from the ds1 DATASTORE element in the my.rep1 replication scheme. Then drop the table.

ALTER REPLICATION my.rep1
  ALTER ELEMENT ds1 DATASTORE
    EXCLUDE TABLE my.tab1;
DROP TABLE my.tab1;

Dropping a table or sequence that is replicated as a TABLE or SEQUENCE element

To drop a table that is part of a replication scheme at the TABLE or SEQUENCE level, complete the following tasks:

  1. Stop the replication agent.

  2. Drop the element from the replication scheme.

  3. Drop the table or sequence.

Example 10-7 Dropping an element from a replication scheme and then dropping the sequence

Drop the SEQUENCE element elem_seq from the replication scheme r1. Then drop the sequence seq.

ALTER REPLICATION r1
  DROP ELEMENT elem_seq;
DROP SEQUENCE seq;

Creating and adding a subscriber data store

You can add a new subscriber data store while the replication agents are running. To add a data store to a replication scheme, do the following:

  1. Make sure the new data store does not exist.

  2. Apply the appropriate statements to all participating data stores:

    ALTER REPLICATION ...
      ALTER ELEMENT ...
        ADD SUBSCRIBER ...
    
  3. Run the ttRepAdmin -duplicate command to copy the contents of the master data store to the newly created subscriber. You can use the -setMasterRepStart option to ensure that any updates made to the master after the duplicate operation has started are also copied to the subscriber.

  4. Start the replication agent on the newly created data store (ttAdmin -repStart).

Example 10-8 Adding a subscriber to a replicated table

This example alters the r1 replication scheme to add a subscriber (backup3) to the westleads table (step 2 above):

ALTER REPLICATION r1
  ALTER ELEMENT elem_westleads
    ADD SUBSCRIBER backup3 ON "backupserver";

Dropping a subscriber data store

Stop the replication agent before you drop a subscriber data store.

This example alters the r1 replication scheme to drop the backup3 subscriber for the westleads table:

Example 10-9 Dropping a subscriber for a replicated table

ALTER REPLICATION r1
  ALTER ELEMENT elem_westleads
    DROP SUBSCRIBER backup3 ON "backupserver";

Changing a TABLE or SEQUENCE element name

Stop the replication agent before you change a TABLE or SEQUENCE element name.

Change the element name of the westleads table from elem_westleads to newelname:

Example 10-10 Changing a table name

ALTER REPLICATION r1
  ALTER ELEMENT Eelem_westleads
    SET NAME newelname;

Note:

You cannot use the SET NAME clause to change the name of a DATASTORE element.

Replacing a master data store

Stop the replication agent before you replace a master data store.

In this example, newwestds is made the new master for all elements currently configured for the master, westds:

Example 10-11 Replacing a master data store

ALTER REPLICATION r1
  ALTER ELEMENT * IN westds
    SET MASTER newwestds;

Eliminating conflict detection

In this example, conflict detection configured by the CHECK CONFLICTS clause in the scheme shown in Example 11-2 is eliminated for the elem_accounts_1 table:

Example 10-12 Eliminating conflict detection for a table

ALTER REPLICATION r1
  ALTER ELEMENT elem_accounts_1
    SET NO CHECK;

See "Replication conflict detection and resolution" for a detailed discussion on conflict checking.

Eliminating the return receipt service

In this example, the return receipt service is eliminated for the first subscriber in the scheme shown in Example 7-30:

Example 10-13 Eliminating return receipt service for a subscriber

ALTER REPLICATION r1
  ALTER ELEMENT elem_waccounts
    ALTER SUBSCRIBER eastds ON "eastcoast"
      SET NO RETURN;

Changing the port number

The port number is the TCP/IP port number on which a subscribing data store's replication agent accepts connection requests from its master replication agent. See "Port assignments" for details on how to assign port to the replication agents.

In this example, the r1 replication scheme is altered to change the eastds data store's port number to 22251:

Example 10-14 Changing a port number for a data store

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

Changing the replication route

If a replication host has multiple network interfaces, you may specify which interfaces are used for replication traffic using the ROUTE clause. If you need to change which interfaces are used by replication, you may do so by dropping and adding IP addresses from or to a ROUTE clause.

Example 10-15 Changing the replication route

In this example, the rep.r1 replication scheme is altered to change the priority 2 IP address for the master data store from 192.168.1.100 to 192.168.1.101:

ALTER REPLICATION r1
  DROP ROUTE MASTER eastds ON "eastcoast"
             SUBSCRIBER westds ON "westcoast"
             MASTERIP "192.168.1.100"
  ADD ROUTE MASTER eastds ON "eastcoast"
            SUBSCRIBER westds ON "westcoast"
            MASTERIP "192.168.1.101" PRIORITY 2;

Altering a replicated table

You can use ALTER TABLE to add or drop columns on the master data store. The ALTER TABLE operation is replicated to alter the subscriber data stores.

If you use ALTER TABLE on a data store configured for bidirectional replication, first stop updates to the table on all of the replicated data stores and confirm all replicated updates to the table have been received by the data stores before issuing the ALTER TABLE statement. Do not resume updates until the ALTER TABLE operation has been replicated to all data stores. This is necessary to ensure there will be no write operations in the pre-altered format after the table is altered on all data stores.

Note:

You can use the ttRepSubscriberWait procedure or monitoring tools described in "Monitoring Replication" on page 117 to confirm the updates have been received and committed on the data stores.

Also, if you are executing a number of successive ALTER TABLE operations on a data store, you should only proceed with the next ALTER TABLE after you have confirmed the previous ALTER TABLE has reached all of the subscribers.

Note:

You can use the ALTER TABLE statement to change default column values, but the ALTER TABLE statement is not replicated. Thus default column values need not be identical on all nodes.

Truncating a replicated table

You can use TRUNCATE TABLE to delete all of the rows of a table without dropping the table itself. Truncating a table is faster than using a DELETE FROM table statement.

Truncate operations on replicated tables are replicated and result in truncating the table on the subscriber data store. Unlike delete operations, however, the individual rows are not deleted. Even if the contents of the tables do not match at the time of the truncate operation, the rows on the subscriber data store are deleted anyway.

The TRUNCATE statement replicates to the subscriber, even when no rows are operated upon.

When tables are being replicated with timestamp conflict checking enabled, conflicts are not reported.

Dropping a replication scheme

You can use the DROP REPLICATION statement to remove a replication scheme from a data store. You cannot drop a replication scheme when master catchup is required unless it is the only replication scheme in the data store.

Note:

You must have the ADMIN privilege to use the DROP REPLICATION statement.

You must stop the replication agent before you drop a replication scheme.

Example 10-16 Dropping a replication scheme

To remove the repscheme replication scheme from a data store, enter the following:

DROP REPLICATION repscheme;

If you are dropping replicated tables, you must drop the replication scheme before dropping the replicated tables. Otherwise, you receive an error indicating that you have attempted to drop a replicated table or index.

Example 10-17 Removing a table and a replication from a data store

To remove the tab table and repscheme replication scheme from a data store, enter the following:

DROP REPLICATION repscheme;
DROP TABLE tab;