其中schema_name => 'c4', 为配置流复制的数据对象，也就是oracle user。以下配置中的schema_name都是同含义。
可以通过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.HOME ENABLED
STATUS为ENABLED则表示创建成功
6.2. 在stream1上创建Capture进程
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'hr',
streams_type => 'capture',
streams_name => 'capture_stream1',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
可以通过dba_capture查看：
SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;
查询到CAPTURE_NAME为CAPTURE_STREAM1 的记录则表明创建成功，此进程还未启动，所以STATUS会是DISABLED。
6.3. 在stream1进行与stream2同步SCN
DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@stream2.home(
source_schema_name => 'c4',
source_database_name => 'stream1.home',
instantiation_scn => v_scn,
recursive => true);
END;
6.4. stream2上创建apply进程
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'c4',
streams_type => 'apply',
streams_name => 'apply_stream2',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'stream1.home',
inclusion_rule => true);
END;
通过查询语句 select * from dba_apply;
可以查询到APPLY_NAME为APPLY_STREAM2的记录。
7. stream2 -> stream1 配置
使用strmadmin用户登录
7.1. 在stream2上创建propagation
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'c4',
streams_name => 'stream2_to_stream1',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@stream1.home',
include_dml => true,
include_ddl => true,
source_database => 'stream2.home',
inclusion_rule => true,
queue_to_queue => true);
END;
其中schema_name => 'c4', 为配置流复制的数据对象，也就是oracle user。以下配置中的schema_name都是同含义。
可以通过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
-------------------------------- ------------------------------ --------------------------------- --------------- --------
STREAM2_TO_STREAM1 STREAMS_QUEUE STREAMS_QUEUE STREAM1.HOME ENABLED
STATUS为ENABLED则表示创建成功
7.2. 在stream2上创建Capture进程
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'hr',
streams_type => 'capture',
streams_name => 'capture_stream2',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
可以通过dba_capture查看：
SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;
查询到CAPTURE_NAME为CAPTURE_STREAM2 的记录则表明创建成功，此进程还未启动，所以STATUS会是DISABLED。
7.3. 在stream2进行与stream1同步SCN
DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@stream1.home(
source_schema_name => 'c4',
source_database_name => 'stream2.home',
instantiation_scn => v_scn,
recursive => true);
END;
7.4. stream1上创建apply进程
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'c4',
streams_type => 'apply',
streams_name => 'apply_stream1',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'stream2.home',
inclusion_rule => true);
END;
通过查询语句 select * from dba_apply;
可以查询到APPLY_NAME为APPLY_STREAM1的记录。
8. 启动
8.1. stream1-> stream2
均使用strmadmin用户登录
在stream2上启动apply：
SQL>
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_stream2',
parameter => 'disable_on_error',
value => 'n');
END;
结果：PL/SQL procedure successfully completed.
SQL>
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_stream2');
END;
结果：PL/SQL procedure successfully completed.
通过如下sql查询状态：
SQL> select apply_name,queue_name,status from dba_apply;
APPLY_NAME QUEUE_NAME STATUS
------------------------------ -------------------------- --------
APPLY_STREAM2 STREAMS_QUEUE ENABLED
在stream1上启动capture：
SQL>
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_stream1');
END;
通过如下sql查询启动状态：
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
------------------------------ ------------
CAPTURE_STREAM1 ENABLED
8.2. stream2 -> stream1
在stream1上启动apply:
SQL>
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_stream1',
parameter => 'disable_on_error',
value => 'n');
END;
PL/SQL procedure successfully completed.
SQL>
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_stream1');
END;
PL/SQL procedure successfully completed.
SQL> select apply_name,queue_name,status from dba_apply;
APPLY_NAME QUEUE_NAME STATUS
------------------------------ -------------------------- --------
APPLY_STREAM1 STREAMS_QUEUE ENABLED
在stream2上启动capture:
SQL>
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_stream2');
END;
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
------------------------------ ------------
CAPTURE_STREAM2 ENABLED
启动完成后，通过对两个库进行DDL，DML操作均可实时同步，验证配置成功。