Classic Replication Scheme Syntax Examples
The examples in this section illustrate how to configure a variety of classic replication schemes.
Single Classic Subscriber Schemes
The following example replicates a single table within a classic replication scheme. The example shows a single master and subscriber unidirectional replication scheme.
The two databases are located on separate hosts, system1 and
system2. We use the RETURN RECEIPT service to confirm that all transactions committed on the
ttuser.tab table in the master database are received by the subscriber.
CREATE REPLICATION repscheme
ELEMENT e TABLE ttuser.tab
MASTER masterds ON "system1"
SUBSCRIBER subscriberds ON "system2"
RETURN RECEIPT;The following example replicates the entire databse. The scheme shown is a single master and subscriber unidirectional replication scheme. The two databases are located on separate hosts, server1 and server2. The master database, named masterds, replicates its entire contents to the subscriber database, named subscriberds.
CREATE REPLICATION repscheme
ELEMENT e DATASTORE
MASTER masterds ON "server1"
SUBSCRIBER subscriberds ON "server2";Multiple Subscriber Classic Replication Schemes
See Propagation Scheme for an example of a classic replication scheme with propagator databases.
This example replicates to two subscribers. This example establishes a master database, named masterds, that replicates the ttuser.tab table to two subscriber databases, subscriber1ds and subscriber2ds, located on server2 and server3, respectively. The name of the classic replication scheme is twosubscribers. The name of the replication element is e.
CREATE REPLICATION twosubscribers
ELEMENT e TABLE ttuser.tab
MASTER masterds ON "server1"
SUBSCRIBER subscriber1ds ON "server2",
subscriber2ds ON "server3";This example replicates to two subscribers using the RETURN RECEIPT attribute and STORE parameters.
RETURN RECEIPT enables the return receipt service for both
databases. The STORE parameter sets a FAILTHRESHOLD value
of 10 to establish the maximum number of transaction log files that can accumulate on
masterds for a subscriber before it assumes the subscriber has
failed.
CREATE REPLICATION twosubscribers
ELEMENT e TABLE ttuser.tab
MASTER masterds ON "server1"
SUBSCRIBER subscriber1ds ON "server2",
subscriber2ds ON "server3"
RETURN RECEIPT
STORE masterds FAILTHRESHOLD 10;This example enables RETURN RECEIPT for only one subscriber:
subscriber2ds. Note that there is no comma after the
subscriber1ds definition.
CREATE REPLICATION twosubscribers
ELEMENT e TABLE ttuser.tab
MASTER masterds ON "server1"
SUBSCRIBER subscriber1ds ON "server2"
SUBSCRIBER subscriber2ds ON "server3" RETURN RECEIPT
STORE masterds FAILTHRESHOLD 10;This example shows how to enable different return services for subscribers. This
example applies RETURN RECEIPT BY REQUEST to subscriber1ds
and RETURN RECEIPT to subscriber2ds. In this
classic replication scheme, applications accessing subscriber1ds must use
the ttRepSyncSet procedure to enable the return services for a
transaction, while subscriber2ds unconditionally provides return services
for all transactions.
CREATE REPLICATION twosubscribers
ELEMENT e TABLE ttuser.tab
MASTER masterds ON "server1"
SUBSCRIBER subscriberds1 ON "server2" RETURN RECEIPT BY REQUEST
SUBSCRIBER subscriber2ds ON "server3" RETURN RECEIPT
STORE masterds FAILTHRESHOLD 10;Replicating Tables to Different Subscribers
The classic replication scheme establishes a master database, named
centralds, which replicates four tables. ttuser.tab1 and
ttuser.tab2 are replicated to the subscriber backup1ds.
ttuser.tab3 and ttuser.tab4 are replicated to
backup2ds. The master database is located on the finance
server. Both subscribers are located on the backupsystem server.
CREATE REPLICATION twobackups ELEMENT a TABLE ttuser.tab1 MASTER centralds ON "finance" SUBSCRIBER backup1ds ON "backupsystem" ELEMENT b TABLE ttuser.tab2 MASTER centralds ON "finance" SUBSCRIBER backup1ds ON "backupsystem" ELEMENT d TABLE ttuser.tab3 MASTER centralds ON "finance" SUBSCRIBER backup2ds ON "backupsystem" ELEMENT d TABLE ttuser.tab4 MASTER centralds ON "finance" SUBSCRIBER backup2ds ON "backupsystem";
Propagation Scheme
The master database sends updates on a table to a propagator that forwards the changes to two subscribers.
The master database is centralds on the finance
host. The propagator database is propds on the nethandler host.
The subscribers are backup1ds on backupsystem1 and
backup2ds on backupsystem2.
The classic replication scheme has two elements. For element a, the changes to the tab table on centralds are replicated to the propds propagator database. For element b, the changes to the tab table received by propds are replicated to the two subscribers, backup1ds and backup2ds.
CREATE REPLICATION propagator
ELEMENT a TABLE ttuser.tab
MASTER centralds ON "finance"
SUBSCRIBER propds ON "nethandler"
ELEMENT b TABLE ttuser.tab
PROPAGATOR propds ON "nethandler"
SUBSCRIBER backup1ds ON "backupsystem1",
backup2ds ON "backupsystem2";Bidirectional Split Workload Schemes
There are two databases, westds on the westcoast
host and eastds on the eastcoast host. Customers are
represented in two tables: waccounts contains data for customers in the Western
region and eaccounts has data for customers from the Eastern region. The
westds database updates the waccounts table and replicates it
to the eastds database. The eaccounts table is owned by the
eastds database and is replicated to the westds database. The
RETURN RECEIPT attribute enables the return receipt service to guarantee that
transactions on either master table are received by their subscriber.
CREATE REPLICATION r1 ELEMENT elem_waccounts TABLE ttuser.waccounts MASTER westds ON "westcoast" SUBSCRIBER eastds ON "eastcoast" RETURN RECEIPT ELEMENT elem_eaccounts TABLE ttuser.eaccounts MASTER eastds ON "eastcoast" SUBSCRIBER westds ON "westcoast" RETURN RECEIPT;
Bidirectional Distributed Workload Scheme
A bidirectional general workload classic replication scheme in which the
ttuser.accounts table can be updated on either the eastds or
westds database. Each database is both a master and a subscriber for the
accounts table.
Note:
Do not use a bidirectional distributed workload replication scheme with return twosafe return service.
CREATE REPLICATION r1 ELEMENT elem_accounts_1 TABLE ttuser.accounts MASTER westds ON "westcoast" SUBSCRIBER eastds ON "eastcoast" ELEMENT elem_accounts_2 TABLE ttuser.accounts MASTER eastds ON "eastcoast" SUBSCRIBER westds ON "westcoast";
When elements are replicated in this manner, the applications should write to each
database in a coordinated manner to avoid simultaneous updates on the same data. To manage
update conflicts, include a timestamp column of type BINARY(8) in the
replicated table and enable timestamp comparison by including the CHECK
CONFLICTS clause in the CREATE REPLICATION statement. See Resolving Replication Conflicts.
The following example shows how to manage update conflicts. The
tstamp timestamp column is included in the ttuser.accounts
table. The CREATE REPLICATION statement has been modified to include the
CHECK CONFLICTS clause.
CREATE TABLE ttuser.accounts (custname VARCHAR2(30) NOT NULL,
address VARCHAR2(80),
curbalance DEC(15,2),
tstamp BINARY(8),
PRIMARY KEY (custname));
CREATE REPLICATION r1
ELEMENT elem_accounts_1 TABLE ttuser.accounts
CHECK CONFLICTS BY ROW TIMESTAMP
COLUMN tstamp
UPDATE BY SYSTEM
ON EXCEPTION ROLLBACK WORK
MASTER westds ON "westcoast"
SUBSCRIBER eastds ON "eastcoast"
ELEMENT elem_accounts_2 TABLE ttuser.accounts
CHECK CONFLICTS BY ROW TIMESTAMP
COLUMN tstamp
UPDATE BY SYSTEM
ON EXCEPTION ROLLBACK WORK
MASTER eastds ON "eastcoast"
SUBSCRIBER westds ON "westcoast";