實戰11g stream replication之table replication

一.環境描述:(S=>source,D=>destination)
                DB                OS                        DB_NAME                DB_DOMAIN                        IP_ADRESS
S         11.2.0.3        redhat5                     tldb                             op.com                               10.1.101.161
D         11.2.0.3        redhat5                    wilson                       bbk.com                               10.1.101.160

二.準備工作
1.Configure a Streams Administrator on All Databases
1.1 create a tablespace for the Streams administrator
create tablespace streams_tbs datafile '+DATA' size 25M reuse autoextend on maxsize unlimited;

1.2 Create a new user to act as the Streams administrator
create user strmadmin identified by strmadminpw default tablespace streams_tbs quota unlimited on streams_tbs;

1.3 Grant the Streams administrator DBA role
grant dba to strmadmin;

2.Create One or More Database Links(tldb.op.com(source),wilson.bbk.com(destination))
2.1 configure network service name on all database
TLDB.OP.COM =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.101.161)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = opdb.op.com)
    )
  )

WILSON.BBK.COM =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.101.160)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = wilson1.bbk.com)
    )
  )

2.2 create database link on sourcedb
conn
CREATE DATABASE LINK WILSON.BBK.COM CONNECT TO strmadmin IDENTIFIED BY strmadminpw  USING 'WILSON.BBK.COM';

2.3 create database link on targetdb
conn
CREATE DATABASE LINK TLDB.OP.COM CONNECT TO strmadmin IDENTIFIED BY strmadminpw  USING 'TLDB.OP.COM';

3.Create the Required Directory Objects
3.1 A source directory object is required if you decided to perform. a Data Pump export dump file instantiation
CONNECT strmadmin/strmadminpw
CREATE DIRECTORY streams_dir AS '/tmp/streams';

4.Make Sure Each Source Database Is In ARCHIVELOG Mode
conn / as sysdba
archive log list

5.Make Sure the Initialization Parameters Are Set Properly on all database
alter system set GLOBAL_NAMES=true;
alter system set STREAMS_POOL_SIZE=50M;

三.配置表複製
1.Configuring Table Replication Using the DBMS_STREAMS_ADM Package
1.1 Complete the required tasks before running the MAINTAIN_SCHEMAS procedure
1.1.1 Configure a Streams administrator at both databases(configured)
1.1.2 Create a database link from the source database TLDB.OP.COM to the destination database WILSON.BBK.COM(configured)
1.1.3 Because the MAINTAIN_TABLES procedure will perform. a Data Pump network import instantiation,
create a database link from the destination database WILSON.BBK.COM to the source database TLDB.OP.COM(configured)
1.1.4 Create a script. directory object at the source database. This example assumes that this directory object is SCRIPT_DIRECTORY
CONNECT
create directory SCRIPT_DIRECTORY as '/tmp/script';
1.1.5 Make sure the source database stm1.net is in ARCHIVELOG mode(configured)
1.1.6 Make sure the initialization parameters are set properly at both databases(configured)

1.2 Connect to the source database as the Streams administrator and run the MAINTAIN_TABLES procedure
CONNECT
DECLARE
  tables DBMS_UTILITY.UNCL_ARRAY;
  BEGIN
    tables(1) := 'scott.d';
    tables(2) := 'scott.e';
    DBMS_STREAMS_ADM.MAINTAIN_TABLES(
      table_names                  => tables,
      source_directory_object      => NULL,
      destination_directory_object => NULL,
      source_database              => 'TLDB.OP.COM',
      destination_database         => 'WILSON.BBK.COM',
      perform_actions              => false,
      script_name                  => 'configure_rep.sql',
      script_directory_object      => 'SCRIPT_DIRECTORY',
      bi_directional               => false,
      include_ddl                  => false,
      instantiation      => DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
END;
/

四.簡單測試一下dml複製
1.查詢sourcedb的scott.d表
SQL> select * from d where deptno=40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

2.查詢targetdb的scott.d表
SQL> select * from d where deptno=40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

3.更新sourcedb的scott.d表並提交
SQL> update d set LOC='changsha' where deptno=40;
1 row updated.

SQL> commit;
Commit complete.

4.再次查詢targetdb的scott.d表
SQL> select * from d where deptno=40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     changsha

    至此一個簡單的流複製之表複製環境就搭建成功了！