oracle 流複製

測試環境:

oracle linux 虛擬機器

192.168.1.4,

192.168.1.5

oracle 10.2.0.3

alter system set global_names=true scope=both;

show parameter COMPATIBLE
show parameter job

alter system set streams_pool_size=15 scope=memory;

[@more@]

歸檔日誌模式

GRANT DBA TO strmadmin IDENTIFIED BY strmadmin;

EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

到目的伺服器上的連線
CREATE DATABASE LINK db10rac CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'ora10g_5';

在源端配置使用者傳送的資訊

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

schema_name => 'zjd',

streams_name => 'str1_to_str2',

source_queue_name => 'strmadmin.streams_queue',

destination_queue_name => ,

include_dml => true,

include_ddl => true,

source_database => 'orcl',

inclusion_rule => true,

queue_to_queue => true);

end;

/

在源端配置日誌抓取程式
BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'zjd',
streams_type => 'capture',
streams_name => 'capture_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

在源端例項化目標端hr使用者所有物件的SCN，這時要保證源端對hr中的物件無DDL操作或commit DML操作

DECLARE

iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
(
source_schema_name => 'zjd',
source_database_name => 'orcl',
instantiation_scn => iscn,
recursive => true);
END;
/

/---------------------------------------------------------------------------------------------------------------------------/
在目標端配置應用程式
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'zjd',
streams_type => 'apply',
streams_name => 'apply_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'orcl',
inclusion_rule => true);
END;
/

在目標端啟動應用程式
BEGIN

DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_simp',
parameter => 'disable_on_error',
value => 'n');
END;
/

BEGIN DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_simp');
END;
/

在目標端啟動抓取程式
BEGIN DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_simp');
END;
/

/-----------------------------------------------------------------------------------------------------------------------/
停止程式
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_simp');
end;
/
begin
dbms_apply_adm.stop_apply(
apply_name => 'apply_simp');
end;
/