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 orttAdmin
-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 orttAdmin
-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 theDATASTORE
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
-
Adding a PL/SQL Object to an Existing Classic Replication Scheme
-
Adding a DATASTORE Element to an Existing Classic Replication Scheme
-
Dropping a Table or Sequence From a Classic Replication Scheme
-
Creating and Adding a Subscriber Database to a Classic Replication Scheme
-
Dropping a Subscriber Database From a Classic Replication Scheme
-
Changing a TABLE or SEQUENCE Element Name in a Classic Replication Scheme
-
Eliminating Conflict Detection in a Classic Replication Scheme
-
Eliminating the Return Receipt Service in a Classic Replication Scheme
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
orSEQUENCE
, use theALTER REPLICATION
statement with theADD ELEMENT
clause to add a table or sequence.. -
When the element level of the classic replication scheme is
DATASTORE
, use theALTER REPLICATION
statement with theALTER 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:
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;
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.