Oracle 10g Stream表級複製配置

1.實驗環境配置
 本次實驗在vmware9.0環境下進行，安裝的作業系統CentOS-6.4-x86_64,資料庫版本是Oracle10g10.2.0.1，源庫和目標庫都安裝了資料庫，源庫ORACLE_SID=myorcl，目標庫ORACLE_SID=orcl

Oracle Streams技術介紹&搭建

單一實例到單一實例Oracle Stream搭建

Oracle 10gR2 Streams刪除所有配置

Oracle 單表流複製 Stream 

Oracle簡單Stream一個使用者單向複製配置

 2.在源庫與目標庫初始化參數設定
 在源庫操作如下：
 
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;

 

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁