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