Oracle 10g配置单向stream流复制，完整记录

到了Oracle 10g,有了DBMS_STREAMS_ADM之后，Stream流复制已经很好配置了 下面的只是一个最简单的stream流复制，不涉及复杂的rule配置 环境： OS:Redhat Linux Oracle 10.2.0.2 source:stream1 destnation:stream2 ==准备== 1.归档日志配置： SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u01/oradata/stream1_arch Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u01/oradata/stream2_arch ---以下没有专门说明的都是sys用户--- 2.在源stream1上配置Supplemental loging： 在源上： SQL> alter database add supplemental log data; Database altered. 3.两台机器上修改global_name: stream1: SQL> alter system set global_names=true scope=both; SQL> alter database rename global_name to stream1.com; stream2: SQL> alter system set global_names=true scope=both; SQL> alter database rename global_name to stream2.com; 4.修改初始化参数： 参考官方文档确定需要来检查和设定参数 5.创建stream管理用户并表空间，配置权限，source和dest： 5.1 为strmadmin用户创建独立表空间 stream1: SQL> CREATE TABLESPACE streams_tbs DATAFILE '/u01/oradata/stream1/streams_tbs.dbf'  2    SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; Tablespace created. stream2: SQL> CREATE TABLESPACE streams_tbs DATAFILE '/u01/oradata/stream2/streams_tbs.dbf'  2    SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; Tablespace created. 5.2 source和dest创建相同用户和权限： SQL> CREATE USER strmadmin IDENTIFIED BY strmadminpw 2     DEFAULT TABLESPACE streams_tbs 3     QUOTA UNLIMITED ON streams_tbs; User created. SQL> GRANT DBA TO strmadmin; Grant succeeded. SQL> BEGIN 2    DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( 3      grantee          => 'strmadmin',     4      grant_privileges => true); 5 END; 6 / PL/SQL procedure successfully completed. 6.配置listener.ora和tnsnames.ora,闲话不多说，最后能保证： stream1上：sqlplus system/system@stream2 stream2上：sqlplus system/system@stream1 都没有问题就ok ---如果没有说明以下都是strmadmin/strmadminpw 用户--- 7.创建DB link: 单向复制之需要一个database link即source 到dest:stream1-->stream2 stream1上： sqlplus strmadmin/strmadminpw  SQL> CREATE DATABASE LINK stream2.com CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'stream2'; Database link created. 测试： SQL> select * from global_name@stream2.com; GLOBAL_NAME ----------------------------------------- STREAM2.COM ==开始stream的配置=== 8. source和dest 创建队列： stream1,stream2都要创建 SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); PL/SQL procedure successfully completed. 上面命令会创建一个队列缺省名：streams_queue，队列表缺省是：STREAMS_QUEUE_TABLE 队列存储的object类型是anaydata 可以用查询dba_queues,dba_queue_tables来检查： SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN'; OWNER           QUEUE_TABLE                     NAME ----------------   ----------------------------------   --------------------------------------------- STRMADMIN    STREAMS_QUEUE_TABLE    STREAMS_QUEUE STRMADMIN    STREAMS_QUEUE_TABLE    AQ$_STREAMS_QUEUE_TABLE_E SQL> select owner,queue_table,object_type from dba_queue_tables where owner='STRMADMIN'; OWNER            QUEUE_TABLE                        OBJECT_TYPE -----------------   ---------------------------------      ------------------------------- STRMADMIN    STREAMS_QUEUE_TABLE       SYS.ANYDATA 9. 在source: stream1上创建Stream propagation： SQL> BEGIN 1 DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( 2 schema_name                    => 'hr',  3 streams_name                   => 'stream1_to_stream2',  4 source_queue_name         => 'strmadmin.streams_queue', 5 destination_queue_name => 'strmadmin.streams_queue@stream2.com', 6 include_dml                        => true, 7 include_ddl                       => true, 8 source_database              => 'stream1.com', 9 inclusion_rule                    => true, 10 queue_to_queue              => true); 11 END; 12 / PL/SQL procedure successfully completed. 可以通过dba_propagations查看结果： SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation; PROPAGATION_NAME      SOURCE_QUEUE_NAME   DESTINATION_QUEUE_NAME   DESTINATION_DBL   STATUS -------------------------------- ------------------------------    --------------------------------- --------------- -------- STREAM1_TO_STREAM2 STREAMS_QUEUE            STREAMS_QUEUE              STREAM2.COM     ENABLED 9.在source: stream1上创建Capture进程： SQL> BEGIN 2    DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 3      schema_name     => 'hr',    4      streams_type      => 'capture', 5      streams_name     => 'capture_stream1', 6      queue_name       => 'strmadmin.streams_queue', 7      include_dml         => true, 8      include_ddl         => true, 9      inclusion_rule     => true); 10 END; 11 / PL/SQL procedure successfully completed. 可以通过dba_capture查看： SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture; CAPTURE_NAME            QUEUE_NAME            START_SCN   STATUS   CAPTURE_TY ----------------------------   -------------------------- --------------- ------------ ---------- CAPTURE_STREAM1     STREAMS_QUEUE      504733         DISABLED   LOCAL SQL> select * from ALL_CAPTURE_PREPARED_SCHEMAS;                                                                                                          SCHEMA_NAME   TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME -------------------- ---------------    -------------- -------------- -------------   -------- HR                      12-JUN-08      IMPLICIT       IMPLICIT     IMPLICIT     NO      10.将stream1上的hr schema数据导到stream2上： 在stream2上： sqlplus system/system SQL> create public database link stream1.com connect to system identified by system using 'stream1'; Database link created. SQL> select * from global_name@stream1.com; GLOBAL_NAME ------------------------------------------------------ STREAM1.COM 导数据： oracle@node2 ~]$ impdp system/system network_link=stream1.com schemas=hr Import: Release 10.2.0.2.0 - Production on Thursday, 12 June, 2008 17:47:59 Copyright (c) 2003, 2005, Oracle. All rights reserved. ..... ...... Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:50:32 11. 在stream1上设置stream2上hr schema的Instantiation SCN sqlplus strmadmin/strmadminpw  SQL> DECLARE 2    iscn NUMBER;          3    BEGIN 4    iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); 5    DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@stream2.com( 6     source_schema_name    => 'hr', 7    source_database_name => 'stream1.com', 8    instantiation_scn     => iscn, 9     recursive             => true); 10 END; 11 / PL/SQL procedure successfully completed. 12.在stream2上创建apply进程apply_stream2： SQL> BEGIN 2   DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 3      schema_name      => 'hr', 4      streams_type    => 'apply',  5      streams_name    => 'apply_stream2', 6      queue_name      => 'strmadmin.streams_queue', 7      include_dml     => true, 8      include_ddl     => true, 9      source_database => 'stream1.com', 10      inclusion_rule => true); 11 END; 12 / PL/SQL procedure successfully completed. 可以通过： dba_apply v$streams_apply_reader v$streams_apply_coordinator v$streams_apply_server 查看状态 SQL> select apply_name,queue_name,status from dba_apply; APPLY_NAME          QUEUE_NAME            STATUS ---------------------      ------------------------   -------- APPLY_STREAM2    STREAMS_QUEUE       DISABLED ==启动== 13.启动capture和apply: 13.1 Stream2上启动 Apply Process CONNECT strmadmin/strmadminpw SQL> BEGIN 2    DBMS_APPLY_ADM.SET_PARAMETER( 3      apply_name => 'apply_stream2',  4      parameter   => 'disable_on_error',  5      value       => 'n'); 6 END; 7 / PL/SQL procedure successfully completed. SQL> BEGIN 2    DBMS_APPLY_ADM.START_APPLY( 3      apply_name => 'apply_stream2'); 4 END; 5 / PL/SQL procedure successfully completed. SQL> select apply_name,queue_name,status from dba_apply; APPLY_NAME                   QUEUE_NAME               STATUS ------------------------------ --------------------------      -------- APPLY_STREAM2            STREAMS_QUEUE         ENABLED 这时候alert log有： Thu Jun 12 18:00:36 2008 Streams APPLY A001 started with pid=25, OS id=30819 Streams Apply Reader started P000 with pid=26 OS id=30821 Streams Apply Server started P001 with pid=27 OS id=30823 如果有问题，没有能启动就查dba_apply的error_messages列 13.2 stream1上启动capture process: sqlplus strmadmin/strmadminpw SQL> BEGIN 2    DBMS_CAPTURE_ADM.START_CAPTURE( 3      capture_name => 'capture_stream1'); 4 END; 5 / PL/SQL procedure successfully completed. SQL> select capture_name,status from dba_capture; CAPTURE_NAME              STATUS ------------------------------    ------------ CAPTURE_STREAM1       ENABLED alert 日志有： Thu Jun 12 18:04:46 2008 Streams CAPTURE C001 started with pid=27, OS id=11884 Thu Jun 12 18:04:49 2008 LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 1 LOGMINER: Memory Size = 10M, Checkpoint interval = 10M LOGMINER: session# = 1, builder process P001 started with pid=32 OS id=11966 LOGMINER: session# = 1, reader process P000 started with pid=29 OS id=11964 LOGMINER: session# = 1, preparer process P002 started with pid=33 OS id=11968 Thu Jun 12 18:04:53 2008 LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf Thu Jun 12 18:06:40 2008 Thread 1 advanced to log sequence 15 Current log# 2 seq# 15 mem# 0: /u01/oradata/stream1/redo02.log Thu Jun 12 18:06:53 2008 Thread 1 advanced to log sequence 16 Current log# 3 seq# 16 mem# 0: /u01/oradata/stream1/redo03.log ... LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf Thu Jun 12 18:07:34 2008 LOGMINER: End mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf Thu Jun 12 18:07:34 2008 LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_14_657197948.dbf 信息，说明已经开始mining logfile. ===测试=== 14.测试 在stream1上： SQL> conn hr/hr SQL> select employee_id,salary from employees where employee_id=197; EMPLOYEE_ID     SALARY --------------          ----------         197              3000 SQL> update employees set salary=salary+1 where employee_id=197; 1 row updated. SQL> commit; Commit complete. stream2上检查结果(可能有延迟)： SQL> conn hr/hr Connected. SQL> select employee_id,salary from employees where employee_id=197; EMPLOYEE_ID     SALARY -----------------       ----------         197              3001 这个时间间隔跟性能及其它情况有关，包括 capture mining logfile的速度 propagation的间隔(默认3秒)及传输时间 apply log的速度