[Oracle Streams Replication]如何使用Oracle流复制技术？

vongates · 发表于 2006-6-13 23:16

最初由 ocp8i 发布
[B]想问下streams能只刷update,insert 而忽略delete吗 [/B]

没看书呀。哈哈。

cc59 · 发表于 2006-6-14 02:00

Streams can capture DML and DDL changes made to database objects and replicate those changes to one or more other databases. A Streams capture process captures changes made to source database objects and formats them into LCRs, which can be propagated to destination databases and then applied by Streams apply processes.

The destination databases can allow DML and DDL changes to the same database objects, and these changes might or might not be propagated to the other databases in the environment. In other words, you can configure a Streams environment with one database that propagates changes, or you can configure an environment where changes are propagated between databases bidirectionally. Also, the tables for which data is shared do not need to be identical copies at all databases. Both the structure and the contents of these tables can differ at different databases, and the information in these tables can be shared between these databases.

vongates · 发表于 2006-6-14 09:47

最初由 ocp8i 发布
[B]想问下streams能只刷update,insert 而忽略delete吗 [/B]

是可以的。可以通过dbms_rule_adm 这个package来定义复杂的rule来实现

ocp8i · 发表于 2006-6-14 14:23

最初由 vongates 发布
[B]

是可以的。可以通过dbms_rule_adm 这个package来定义复杂的rule来实现 [/B]

找到了

yayagirl · 发表于 2006-6-14 15:12

你要实施stream?

vongates · 发表于 2006-6-14 20:09

最初由 yayagirl 发布
[B]你要实施stream? [/B]

工作需要

ocpmi · 发表于 2006-7-3 09:12

请问在你的工作中为什么需要实施Oracle® Streams Replication？用户需求是什么？

我问这个问题的目的是想知道Oracle® Streams Replication应该在哪些场合下使用。

谢谢！

最初由 vongates 发布
[B]

工作需要 [/B]

vongates · 发表于 2006-7-4 19:39

最初由 ocpmi 发布
[B]请问在你的工作中为什么需要实施Oracle® Streams Replication？用户需求是什么？

我问这个问题的目的是想知道Oracle® Streams Replication应该在哪些场合下使用。

谢谢！

[/B]

其实这个需求有点象dwh方面的

因为交易量比较大在生产系统中不可能保留所有的数据，要做data archiving(实际上就是做delete) ，可是这些数据对分析来说又是有用的。所以就需要我们把数据复制到另一DB中。
在主库做delete而从库则继续服务。
也就是说要过滤到delete相关的操作。
所以就优先考虑stream的特性

不知道大家有没有其它的方案可以提供参考

ocpmi · 发表于 2006-7-8 11:09

是不是用这个：

ADD_SUBSET_PROPAGATION_RULES Procedure
http://download-west.oracle.com/ ... strm_a.htm#i1007301

ADD_SUBSET_RULES Procedure
http://download-west.oracle.com/ ... strm_a.htm#i1027849

Usage Notes

Running this procedure generates three rules for the specified capture process, apply process, or messaging client: one for INSERT statements, one for UPDATE statements, and one for DELETE statements. For INSERT and DELETE statements, only DML changes that satisfy the condition specified for the dml_condition parameter are captured, applied, or dequeued. For UPDATE statements, the following variations are possible:

不知谁能举个例子说明如何使用？

谢谢！

最初由 vongates 发布
[B]

其实这个需求有点象dwh方面的

因为交易量比较大在生产系统中不可能保留所有的数据，要做data archiving(实际上就是做delete) ，可是这些数据对分析来说又是有用的。所以就需要我们把数据复制到另一DB中。
在主库做delete而从库则继续服务。
也就是说要过滤到delete相关的操作。
所以就优先考虑stream的特性

不知道大家有没有其它的方案可以提供参考 [/B]

vongates · 发表于 2006-7-9 08:28

[php]
-- 01 Create a rule set
BEGIN
  DBMS_RULE_ADM.CREATE_RULE_SET(rule_set_name    => 'STRMADMIN.VONGATES_RULES',
                              evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT');
END;

-- 02 Create a rule
BEGIN
  DBMS_RULE_ADM.CREATE_RULE(rule_name => 'STRMADMIN.DEPT_DML',
                        condition => '  :dml.get_object_owner() = ''SCOTT'' AND ' ||
                                       '  :dml.get_object_name()  = ''DEPT''  AND ' ||
                                       '  :dml.get_command_type()!= ''DELETE''  ');
END;
-- drop rule
BEGIN
  DBMS_RULE_ADM.drop_rule(rule_name => 'STRMADMIN.DEPT_DML');
END;

-- 3 Assign the rule to the rule set

BEGIN
  DBMS_RULE_ADM.ADD_RULE(rule_name    => 'STRMADMIN.DEPT_DML',
                     rule_set_name => 'STRMADMIN.VONGATES_RULES');
END;

--4 Associate the rule set with the capture process

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name    => 'SCOTT.DEPT',
                                 streams_type => 'CAPTURE',
                                 streams_name => 'capture_simp',
                                 queue_name    => 'strmadmin.streams_queue',
                                 include_dml    => TRUE,
                                 include_ddl    => TRUE,
                                 source_database => 'gates');
END;

BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE(capture_name  => 'capture_simp',
                                 rule_set_name => 'STRMADMIN.VONGATES_RULES');
END;

declare
  v_scn number;
begin
  v_scn := dbms_flashback.get_system_change_number();
  dbms_apply_adm.set_table_instantiation_scn@clone(source_object_name => 'scott.dept',
                                                source_database_name => 'gates',
                                                instantiation_scn => v_scn);
end;

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'capture_simp');
END;

01 Create a rule set
SQL> BEGIN
  2  DBMS_RULE_ADM.CREATE_RULE_SET(
  3    rule_set_name    => 'STRMADMIN.VONGATES_RULES',
  4    evaluation_context  => 'SYS.STREAMS$_EVALUATION_CONTEXT');
  5  END;
  6  /

PL/SQL procedure successfully completed

SQL>
SQL> BEGIN
  2 DBMS_RULE_ADM.CREATE_RULE(rule_name => 'STRMADMIN.DEPT_DML',
  3                            condition => '  :dml.get_object_owner() = "SCOTT" AND ' ||
  4                                        '  :dml.get_object_name()  = "DEPT"  AND ' ||
  5                                        '  :dml.get_command_type()!= "DELETE"  ');
  6  END;
  7  /

PL/SQL procedure successfully completed

SQL>
SQL> BEGIN
  2  DBMS_RULE_ADM.ADD_RULE(
  3    rule_name    => 'STRMADMIN.DEPT_DML',
  4    rule_set_name  => 'STRMADMIN.VONGATES_RULES');
  5  END;
  6  /

PL/SQL procedure successfully completed

SQL>
SQL> BEGIN
  2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name    => 'SCOTT.DEPT',
  3                                  streams_type => 'CAPTURE',
  4                                  streams_name => 'capture_simp',
  5                                  queue_name    => 'strmadmin.streams_queue',
  6                                  include_dml    => TRUE,
  7                                  include_ddl    => FALSE,
  8                                  source_database => 'gates');
  9  END;
10  /

PL/SQL procedure successfully completed

SQL>
SQL> BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE(capture_name  => 'capture_simp',
  2                                     rule_set_name => 'STRMADMIN.VONGATES_RULES');
  3  END;
  4  /

PL/SQL procedure successfully completed

SQL>

[/php]