ORACLE10g Stream表級複製配置

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
myorcl           OPEN

SQL> alter system set aq_tm_processes=1 scope=spfile;    --佇列等待時間

SQL> alter system set job_queue_processes=2 scope=spfile;    --至少為2

SQL> alter system set global_names=true scope=spfile;   --用來控制database link同名

SQL> alter database rename global_name to myorcl.net;

SQL> alter system set streams_pool_size=50m scope=spfile;
重啟資料庫並檢視設定的global_name，如下：

SQL> shutdown immediate
SQL> startup
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
MYORCL.NET
在目標庫操作，如下：

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN

SQL> alter system set aq_tm_processes=1 scope=spfile;

SQL> alter system set job_queue_processes=2 scope=spfile;

SQL> alter system set global_names=true scope=spfile;

SQL> alter database rename global_name to orcl.net;

SQL> alter system set streams_pool_size=50m scope=spfile;
SQL> shutdown immediate
重啟資料庫並檢視設定的global_name，如下：

SQL> shutdown immediate
SQL> startup

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.NET

3.在源庫和目標庫配置tnsnames.ora，如下：

primary =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = myorcl)

    )

  )

standby =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

測試能否連通，如下：
首先關閉防火牆，service iptables stop
在源庫測試，如下：

[oracle@dayong ~]$ tnsping standby

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-APR-2014 11:47:41

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 msec)

在目標庫測試，如下：
[oracle@xiaoru ~]$ tnsping primary

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-APR-2014 11:53:24

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = myorcl)))
OK (0 msec)

4.源庫和目標庫複製管理員的建立
不能使用sys和system作為流管理員，流管理員不能使用system表空間作為預設表空間；
源庫操作如下：
SQL> create tablespace streamtbs datafile '/u01/app/oradata/myorcl/streamtbs.dbf' size 100m;

SQL> create user streamadmin identified by oracle default tablespace streamtbs quota unlimited on streamtbs;

SQL> grant connect,resource,dba to streamadmin;

目標庫操作如下：
SQL> create tablespace streamtbs datafile '/u01/app/oradata/orcl/streamtbs.dbf' size 100m;

SQL> create user streamadmin identified by oracle default tablespace streamtbs quota unlimited on streamtbs;

SQL>  grant connect,resource,dba to streamadmin;

5.源庫和目標庫建立互連的database link
源庫操作如下：
SQL> conn
SQL> create database link orcl.net connect to streamadmin identified by oracle using 'standby';

SQL> select * from ;      --測試

D
-
X

檢視建立的DB_LINK，如下：
SQL> conn / as sysdba
SQL> desc dba_db_links;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DB_LINK                                   NOT NULL VARCHAR2(128)
 USERNAME                                           VARCHAR2(30)
 HOST                                               VARCHAR2(2000)
 CREATED                                   NOT NULL DATE

SQL> col owner for a15;
SQL> col db_link for a15;
SQL> col username for a15;
SQL> col host for a15;
SQL> select owner,db_link,username,host from dba_db_links;

OWNER           DB_LINK         USERNAME        HOST
--------------- --------------- --------------- ---------------
STREAMADMIN     ORCL.NET        STREAMADMIN     standby

目標庫操作如下：
SQL> connect streamadmin/oracle@standby

SQL> create database link myorcl.net connect to streamadmin identified by oracle using 'primary';

Database link created.

SQL> select * from dual@myorcl.net;

D

-

X

6.檢視源庫和目標資料庫是否處於歸檔模式，如果不是歸檔模式，必須設定處於歸檔模式
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/my_arch
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
7.建立測試用的表空間和表
在源庫上操作如下：
SQL> create user huyong identified by huyong default tablespace streamtbs quota unlimited on streamtbs;

SQL> grant connect,resource to huyong;

SQL>CONN HUYONG/HUYONG;

SQL>create table test01(id number,name varchar2(20));

SQL>create table test02(id number,name varchar2(20));

SQL>create table test03(id number,name varchar2(20));

在目標資料庫操作如下：

SQL>create user huyong identified by huyong default tablespace streamtbs quota unlimited on streamtbs;

SQL>grant connect,resource to huyong;

8.在源庫上執行maintain_tables過程
SQL> connect
Connected.
SQL> declare
  2  v_tables DBMS_UTILITY.UNCL_ARRAY;
  3  begin
  4  v_tables(1) := 'huyong.test01';
  5  v_tables(2) := 'huyong.test02';
  6  v_tables(3) := 'huyong.test03';
  7  dbms_streams_adm.maintain_tables(
  8  table_names => v_tables,
  9  source_directory_object => null,
 10  destination_directory_object => null,
 11  source_database => 'myorcl.net',
 12  destination_database => 'orcl.net',
 13  perform_actions => true,
 14  bi_directional => true,
 15  include_ddl => true,
 16  instantiation => dbms_streams_adm.instantiation_table_network);
 17  end;
 18  /

PL/SQL procedure successfully completed.

批註：此操作過程中開啟另一個終端檢視tail -f /u01/app/admin/myorcl/bdump/alert_myorcl.log 日誌，觀察是否有錯誤資訊
9.驗證
檢視目標端資料庫的huyong使用者下是否實現表的同步
SQL> conn huyong/huyong
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST01                         TABLE
TEST02                         TABLE
TEST03                         TABLE

在源端資料庫的huyong使用者下插入資料，如下：

SQL> conn huyong/huyong;

Connected.

SQL> insert into test01 values (1,'huyong');

SQL> insert into test02 values (1,'huyong');

SQL> insert into test03 values (1,'huyong');

SQL> commit;

在目標端資料庫驗證，如下：

SQL> conn huyong/huyong;

Connected.

SQL> select * from test01;

        ID NAME

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

         1 huyong

SQL> select * from test02;

        ID NAME

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

         1 huyong

SQL> select * from test03;

        ID NAME

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

         1 huyong

繼續驗證，在目標端插入資料，如下：

SQL> insert into test01 values (2,'xiaoru');

SQL> commit;

在源端進行驗證，如下：

SQL> select * from test01;

        ID NAME

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

         1 huyong

         2 xiaoru

可知已經完成了資料的同步。
補充：
現由於業務需要，增加新的表並實現資料庫之間表級同步，操作如下：
在源庫操作如下：
SQL> conn huyong/huyong;
SQL> create table test04 as select * from test01; 
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST02                         TABLE
TEST01                         TABLE
TEST03                         TABLE
TEST04                         TABLE

SQL> conn
Enter password:
Connected.
SQL> declare
  2  v_tables DBMS_UTILITY.UNCL_ARRAY;
  3  begin
  4  v_tables(1) := 'huyong.test04';
  5  dbms_streams_adm.maintain_tables(
  6  table_names => v_tables,
  7  source_directory_object => null,
  8  destination_directory_object => null,
  9  source_database => 'myorcl.net',
 10  destination_database => 'orcl.net',
 11  perform_actions => true,
 12  bi_directional => true,
 13  include_ddl => true,
 14  instantiation => dbms_streams_adm.instantiation_table_network);
 15  end;
 16  /

PL/SQL procedure successfully completed.

在目標庫檢視是否完成表的同步：
SQL> conn huyong/huyong;
Connected.
SQL> select * from tab;        

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST04                         TABLE
TEST01                         TABLE
TEST02                         TABLE
TEST03                         TABLE