Oracle 10g Stream使用者級複製配置

1.之前做了Oracle 10g Stream表級複製的配置()，在已經存在複製環境的情況下，再配置其他類型的複製前需要清除已經存在的複製環境，否則複製會導致失敗。
 使用stream管理使用者，源庫操作如下：
 
SQL> conn streamadmin/oracle@primary
 Connected.
 SQL> begin
  2  for cur_pro in (select propagation_name from dba_propagation) loop
  3  dbms_propagation_adm.drop_propagation(cur_pro.propagation_name);
  4  end loop;
  5  dbms_streams_adm.REMOVE_STREAMS_CONFIGURATION;
  6  end;
  7  /

PL/SQL procedure successfully completed.
 查看記錄檔資訊，部分如下：
 Thu Apr  3 16:52:25 2014
 Streams CAPTURE C001 with pid=24, OS id=2291 stopped
 Thu Apr  3 16:52:33 2014
 ARC1: STARTING ARCH PROCESSES
 ARC2: Archival started
 ARC1: STARTING ARCH PROCESSES COMPLETE
 ARC2 started with pid=24, OS id=3305
 Streams Apply Server P001 pid=27 OS id=2297 stopped
 Streams Apply Reader P000 pid=26 OS id=2295 stopped
 Streams Apply Server P000 pid=26 OS id=2295 stopped
 Streams Apply Server P001 pid=27 OS id=2297 stopped
 Thu Apr  3 16:52:37 2014
 Streams APPLY A001 with pid=25, OS id=2293 stopped
 Thu Apr  3 16:53:31 2014
 Shutting down archive processes
 Thu Apr  3 16:53:36 2014
 ARCH shutting down
 ARC2: Archival stopped
 查看stream相關的表的隊列資訊，如下：
 SQL> select apply_name,queue_name,queue_owner,status from dba_apply;

no rows selected

SQL> select CAPTURE_NAME,QUEUE_OWNER,STATUS,CAPTURE_USER from dba_capture;

no rows selected

使用stream管理員，目標庫操作如下：
 SQL> conn streamadmin/oracle@standby;
 Connected.
 SQL> begin
  2  for cur_pro in (select propagation_name from dba_propagation) loop
  3  dbms_propagation_adm.drop_propagation(cur_pro.propagation_name);
  4  end loop;
  5  dbms_streams_adm.REMOVE_STREAMS_CONFIGURATION;
  6  end;
  7  /

PL/SQL procedure successfully completed.

查看記錄檔資訊，部分如下：
 Thu Apr  3 17:08:46 2014
 Streams CAPTURE C001 with pid=25, OS id=2454 stopped
 Thu Apr  3 17:08:53 2014
 ARC1: STARTING ARCH PROCESSES
 ARC2: Archival started
 ARC1: STARTING ARCH PROCESSES COMPLETE
 ARC2 started with pid=25, OS id=3342
 Streams Apply Server P001 pid=28 OS id=2460 stopped
 Streams Apply Reader P000 pid=27 OS id=2458 stopped
 Streams Apply Server P001 pid=28 OS id=2460 stopped
 Streams Apply Server P000 pid=27 OS id=2458 stopped
 Thu Apr  3 17:08:57 2014
 Streams APPLY A001 with pid=26, OS id=2456 stopped
 Thu Apr  3 17:09:36 2014
 Shutting down archive processes
 Thu Apr  3 17:09:41 2014
 ARCH shutting down
 ARC2: Archival stopped
 2.源庫和目標庫初始化參數設定
 在源庫：
 alter system set aq_tm_processes=1 scope=spfile; 
alter system set job_queue_processes=2 scope=spfile;
 alter system set global_names=true scope=spfile;     
alter database rename global_name to myorcl.net;   
alter system set streams_pool_size=52m scope=spfile;
 在目標資料庫：
 alter system set aq_tm_processes=1 scope=spfile;
 alter system set job_queue_processes=2 scope=spfile;
alter system set global_names=true scope=spfile;
 alter database rename global_name to orcl.net;
 alter system set streams_pool_size=50m scope=spfile;
 由於之前做了表級複製，現在只需驗證配置資訊是否正確。
 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)
    )
  )
 4.源庫和目標庫複製管理員的建立
 不能使用sys和system作為流管理員，流管理員不能使用system資料表空間作為預設資料表空間；
 在源庫驗證操作如下：
 SQL> select username from dba_users where username like '%STREAM%';    --之前做表級複製時建立的stream管理員
 
USERNAME
 ------------------------------
 STREAMADMIN

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME like '%STREAM%';
 
TABLESPACE_NAME                STATUS
 ------------------------------ ---------
 STREAMTBS                      ONLINE

在目標庫驗證操作如下：
 SQL> select username from dba_users where username like '%STREAM%';

USERNAME
 ------------------------------
 STREAMADMIN

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME like '%STREAM%';
 
TABLESPACE_NAME                STATUS
 ------------------------------ ---------
 STREAMTBS                      ONLINE

5.源庫和目標庫建立互連的資料庫連接
 在源庫驗證操作如下：
 SQL> conn streamadmin/oracle@primary
 Connected.
 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> select * from dual@orcl.net;

D
 -
 X
 在目標庫驗證操作如下：
 SQL> conn streamadmin/oracle@standby;
 Connected.
 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    MYORCL.NET      STREAMADMIN    primary

SQL> select * from dual@myorcl.net;

D
 -
 X

6.查看源庫和目標庫是否處于歸檔模式
 SQL> conn / as sysdba
 Connected.
 SQL> archive log list;
 Database log mode              Archive Mode
 Automatic archival            Enabled
 Archive destination            /u01/app/my_arch
 Oldest online log sequence    6
 Next log sequence to archive  8
 Current log sequence          8

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

Oracle Streams技術介紹&搭建

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

Oracle 10gR2 Streams刪除所有配置

Oracle 單表流複製 Stream

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

1 2 下一頁