Altering a Classic Replication Scheme

You can perform the following tasks without stopping the replication agent:

Create, alter or drop a user. These statements are replicated.
Grant or revoke privileges from a user. These statements are replicated.
Add a subscriber to the replication scheme. See Creating and Adding a Subscriber Database to a Classic Replication Scheme.
Add a PL/SQL object to the master database and implement its replication on subscribers. See Adding a PL/SQL Object to an Existing Classic Replication Scheme.

Use ALTER REPLICATION to alter the classic replication scheme on the master and subscriber databases. Any alterations on the master database must also be made on its subscribers.

Note:

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

Most ALTER REPLICATION operations are supported only when the replication agent is stopped (ttAdmin -repStop). The procedure for ALTER REPLICATION operations that require the replication agents to be stopped is:

Use the ttRepStop built-in procedure or ttAdmin -repStop to stop the replication agent for the master and subscriber databases. While the replication agents are stopped, changes to the master database are stored in the log.
Issue the same ALTER REPLICATION statement on both master and subscriber databases.
Use the ttRepStart built-in procedure or ttAdmin -repStart to restart the replication agent for the master and subscriber databases. The changes stored in the master database log are sent to the subscriber database.

For more information, see Starting and Stopping the Replication Agents.

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

You cannot use SET NAME to change the name of the DATASTORE element.
You cannot use SET CHECK CONFLICTS to enable conflict resolution.

This section includes the following topics:

Adding a Table or Sequence to an Existing Classic Replication Scheme

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

When the element level of the classic replication scheme is TABLE or SEQUENCE, use the ALTER REPLICATION statement with the ADD ELEMENT clause to add a table or sequence..
When the element level of the classic replication scheme is DATASTORE, use the ALTER REPLICATION statement with the ALTER ELEMENT clause to include a table or sequence. .

This example alters the classic replication scheme r1 to add sequence seq and table westleads, which are updated on database westds and replicated to database 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";

This example adds 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
    INCLUDE SEQUENCE my.seq
  ALTER ELEMENT ds1
    INCLUDE TABLE my.tab1;

Adding a PL/SQL Object to an Existing Classic Replication Scheme

You can add a new PL/SQL procedure, package, package body or function to an existing replication scheme.

Create the PL/SQL object on a master database. The CREATE statement is not replicated to subscribers.
Create the PL/SQL object on the subscribers
Grant privileges to the new PL/SQL object on the master database. The GRANT statement is replicated to the subscribers.

Adding a DATASTORE Element to an Existing Classic Replication Scheme

You can add a DATASTORE element to an existing classic replication scheme by using the ALTER REPLICATION statement with the ADD ELEMENT clause.

All tables except temporary tables, materialized views, and non-materialized views are included in the replication scheme if you do not use the INCLUDE or EXCLUDE clauses. See Including Tables or Sequences When You Add a DATASTORE Element and Excluding a Table or Sequence When You Add a DATASTORE Element.

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 database to an existing classic 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.

The following example adds the ds1 DATASTORE element to my.rep1 replication scheme. Then, it includes 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 classic 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.

The following example adds the ds2 DATASTORE element to a replication scheme, but excludes 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 Classic Replication Scheme

You can drop a table or sequence that is replicated as part of a DATASTORE, TABLE or SEQUENCE element.

This section includes the following topics:

Dropping a Table or Sequence That Is Replicated As Part of a DATASTORE Element

You can drop a table or sequence that is part of a classic replication scheme at the DATASTORE level.

Stop the replication agent.
Exclude the table or sequence from the DATASTORE element in the classic replication scheme.
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.

This example excludes the table my.tab1 from the ds1 DATASTORE element in the my.rep1 replication scheme. Then drops the table.

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

Dropping a Table or Sequence That Is Replicated As a TABLE or SEQUENCE Element

You can drop a table that is part of a classic replication scheme at the TABLE or SEQUENCE level.

Stop the replication agent.
Drop the element from the classic replication scheme.
Drop the table or sequence.

This example drops the SEQUENCE element elem_seq from the classic replication scheme r1. Then, it drops the sequence seq.

ALTER REPLICATION r1
  DROP ELEMENT elem_seq;
DROP SEQUENCE seq;

Creating and Adding a Subscriber Database to a Classic Replication Scheme

You can add a new subscriber database while the replication agents are running.

To add a database to a classic replication scheme, do the following:

Make sure the new subscriber database does not exist.

Apply the appropriate statements to all participating databases:

ALTER REPLICATION ...
  ALTER ELEMENT ...
    ADD SUBSCRIBER ...

On the source database (the master), create a user and grant the ADMIN privilege to the user:
```
CREATE USER ttuser IDENTIFIED BY ttuser;
User created.

GRANT admin TO ttuser;
```
Logged in as the instance administrator, run the ttRepAdmin -duplicate command to copy the contents of the master database to the newly created subscriber. By default, any updates made to the master after the duplicate operation has started are also copied to the subscriber. Use the -noSetMasterRepStart option if you do not want to copy updates to the subscriber.
Start the replication agent on the newly created database (ttAdmin -repStart).

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 Database from a Classic Replication Scheme

Stop the replication agent before you drop a subscriber database.

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

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

Changing a TABLE or SEQUENCE Element Name in a Classic Replication Scheme

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

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

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 Database in a Classic Replication Scheme

Stop the replication agent before you replace a master database in a classic replication scheme.

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

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

Eliminating Conflict Detection in a Classic Replication Scheme

In this example, conflict detection configured by the CHECK CONFLICTS clause in the classic replication scheme shown is eliminated for the elem_accounts_1 table.

ALTER REPLICATION r1
  ALTER ELEMENT elem_accounts_1
    SET NO CHECK;

See Resolving Replication Conflicts.

Eliminating the Return Receipt Service in a Classic Replication Scheme

In this example, the return receipt service is eliminated for the first subscriber in the classic replication scheme.

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

Changing the Port Number for a Classic Replication Scheme

The port number is the TCP/IP port number on which the replication agent of a subscriber database accepts connection requests from the master replication agent.

See Port Assignments for details on how to assign ports to the replication agents.

In this example, the r1 replication scheme is altered to change the port number of the eastds to 22251:

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. See Configuring Network Interfaces with the ROUTE Clause.

Changing the Log Failure Threshold

Use the FAILTHRESHOLD attribute of the STORE parameter to reset the log failure threshold.

Stop the replication agents before using ALTER REPLICATION to define a new threshold value, and then restart the replication agents.

See Setting the Transaction Log Failure Threshold.