Oracle streams How to create a single source multi-directional replication environment

In this section, we build a slightly more complex replication environment, and this time we will no longer replicate between two databases, add a new database, and synchronize data between three databases.

I. Description of requirements

Set the conditions as follows:

Database Sid:jssweb, Jssstr and Jssbak

Copy the Jssweb database Schema:member under the Usr_user to the JSSSTR database under Member, and renamed to users;

Copy the DT_TBL1, DT_TBL2 and Dt_tmp tables in the Jssweb database to Jssbak in Schema:member database schema:member;

All Source Library local capture, one-way synchronization.

Synchronous DML/DDL Operation

Ii. preparatory work

Same as the previous chapters, no longer repeated.

Third, create queues and database chains

First in the Jssweb operation, create the queue table and the dblink connected to the JSSSTR database:

Sql> Conn Strmadmin/strmadmin

Connected.

Jssweb> set Sqlprompt "Jssweb>"

Jssweb> exec dbms_streams_adm.set_up_queue ();

Pl/sql procedure successfully completed.

Jssweb> CREATE DATABASE link Jssstr connect to strmadmin identified by Strmadmin Using¨jssstr_172.25.13.231¨;

Database Link created.

Jssweb> select Sysdate from Dual@jssstr;

Sysdate

--------------

2 April-March-09

JSSSTR-side operations, creating queue tables and Dblink connected to the TEST08 database:

Sql> Conn Strmadmin/strmadmin

Connected.

Jssstr> set Sqlprompt "Jssstr>"

Jssstr> exec dbms_streams_adm.set_up_queue ();

Pl/sql procedure successfully completed.

Jssstr> CREATE DATABASE link Jssbak connect to strmadmin identified by Strmadmin Using¨jssbak_172.25.13.227¨;

Database Link created.

Jssstr> select Sysdate from Dual@jssbak;

Sysdate

------------

24-mar-09

Finally, on the Jssbak side, create the queue table and the dblink connected to the Jssweb database (for later execution IMPDP)

Sql> set Sqlprompt "Jssbak>"

Jssbak> Conn Strmadmin/strmadmin

Connected.

jssbak> exec dbms_streams_adm.set_up_queue;

Pl/sql procedure successfully completed.

Jssbak> CREATE DATABASE link Jssweb connect to strmadmin identified by Strmadmin Using¨jssweb_172.25.13.229¨;

Database Link created.

Jssbak> select Sysdate from Dual@jssweb;

Sysdate

------------

24-mar-09

Because Jssbak is a new library, we create a new user and table space to hold the replicated data:

jssbak> Create tablespace webdata datafile¨/data/oracle/oradata/jss bak/webdata01.ora¨size 200m;

Tablespace created.

Jssbak> create user member identified by member default Tablespace webdata quota unlimited on webdata;

User created.

Jssbak> Grant Connect,resource to member;

Grant succeeded.

Jssbak> revoke unlimited tablespace from member;

Revoke succeeded.

Four, simple way to configure the replication environment

Generally there are two ways to configure, one is through Dbms_capture_adm, Dbms_propagation_adm, dbms_apply_adm several packages to create capture, propagation and application processes, relatively more flexible, but the operation is more complex, The other is to create directly from the DBMS_STREAMS_ADM package, which is relatively simple, as we demonstrate in this way.

In addition, the environment used in this chapter continues the previous chapters, the initialization environment section is not repeated, if you do not browse the other articles in this series, reading this article directly does not understand some of the data sources, it is recommended to first read the relevant sections in the previous section.

The following actions, if not specifically stated, are performed as Strmadmin.

1, Jssweb end configuration propagation process

Jssweb> BEGIN

2 Dbms_streams_adm. Add_schema_propagation_rules (

3 Schema_name =>¨member¨,

4 Streams_name =>¨web_to_str¨,

5 Source_queue_name =>¨strmadmin.streams_queue¨,

6 Destination_queue_name =>¨strmadmin.streams_queue@jssstr.jss.cn¨,

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/