Oracle Stream概述与配置

介绍一下Streams和goldengate的比较

1.异构复制

streams 仅支持oracle-oracle

GG 支持不同平台的复制

2.capture方式

streams 通过logmnr的方式抽取redo和archivelog中的数据，效率相比更低一些

GG 通过GG独有的方式抽取内存、redo、archivelog中数据，效率相比更高一些

3.收集数据

streams Oracle专有后台进程

GG 外部进程(extract/replicat)

4.内存分配

Streams SGA(stream pool)

GG OS memory

5.事务 – 数据收集阶段(源数据库)

Streams 所有的事务(commit,uncommited)

GG 提交的事务

6.数据应用阶段(目标数据库)

Sreams 过滤掉uncommited数据

GG 收到的数据都是提交过的,直接应用。

7.事务的顺序

Streams SCN的顺序

GG commit先后顺序

下面进行1个DEMO演示Stream配置部署：

step1. 参数配置

Source-Target:

archive log listalter system set aq_tm_processes=2 scope=both; alter system set streams_pool_size=200M scope=spfile; alter database add supplemental log data;alter system set "_job_queue_interval"=1 scope=spfile;startup force;

step2. 创建Streams管理用户

Source-Target:

create tablespace tbs_stream datafile size 100M;create user strmadmin identified by strmadmin default tablespace tbs_stream;grant dba to strmadmin;

step3. 创建业务用户

Source-Target:

create tablespace tianbaobao datafile size 100M;create user tianbaobao identified by tianbaobao default tablespace tbs_stream;grant dba to tianbaobao;

step4. 配置环境tnsnames.ora

Source-Target:

SOURCEDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = source.ht8888.ht3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sourcedb) ) )

TARGETDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = target.ht8888.ht3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = targetdb) ) )

step5.创建DBlink(如果不配置双向复制，无需配置目标到源的DBlink)

create database link strm_link connect to strmadmin identified by strmadmin using 'targetdb';

step6.创建Streams Queue

Source-Target:

sqlplus strmadmin/strmadmin@sourcedbbegin dbms_streams_adm.set_up_queue( queue_table => 'source_queue_table', queue_name => 'source_queue'); end; /

sqlplus strmadmin/strmadmin@targetdbbegin dbms_streams_adm.set_up_queue( queue_table => 'target_queue_table', queue_name => 'target_queue'); end; /

step7.源创建捕获进程

connect strmadmin/strmadmin@sourcedbbegin dbms_streams_adm.add_schema_rules(schema_name => 'tianbaobao', streams_type => 'capture', streams_name => 'capture_source', queue_name => 'strmadmin.source_queue', include_dml => true, include_ddl => true, inclusion_rule => true); end; /

step8.源创建传播进程

connect strmadmin/strmadmin@sourcedbbegin dbms_streams_adm.add_schema_propagation_rules( schema_name => 'tianbaobao', streams_name => 'source_to_target', source_queue_name => 'strmadmin.source_queue', destination_queue_name => 'strmadmin.target_queue@strm_link', include_dml => true, include_ddl => true, source_database => 'sourcedb', inclusion_rule => true,queue_to_queue => true); end; /

#修改propagation休眠时间为0，表示实时传播LCR。

connect strmadmin/strmadmin@sourcedbbegin dbms_aqadm.alter_propagation_schedule( queue_name => 'source_queue', destination => 'strm_link', destination_queue => 'target_queue',latency => 0); end; /

step9.目标创建应用进程

connect strmadmin/strmadmin@sourcedbbegin dbms_streams_adm.add_schema_rules( schema_name => 'tianbaobao', streams_type => 'apply', streams_name => 'apply_target', queue_name => 'strmadmin.target_queue', include_dml => true, include_ddl => true, source_database => 'sourcedb', inclusion_rule => true); end; /

step10.数据库直接设置SCN的方式进行实例化

---获取源库互置用户的SCN

connect strmadmin/strmadmin@sourcedb set serveroutput on DECLARE iscn NUMBER;

-- Variable to hold instantiation SCN valueBEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);END; /

---设置为目标库互置用户的SCN

connect streamadmin/streamadmin@targetdbBEGIN DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN( source_schema_name => 'TIANBAOBAO', source_database_name => 'SOURCEDB', instantiation_scn => &iscn);END; /

step11.启动捕获和应用进程

connect strmadmin/strmadmin@sourcedbexec dbms_capture_adm.start_capture(capture_name => 'capture_source');

connect strmadmin/strmadmin@targetexec dbms_apply_adm.start_apply(apply_name => 'apply_target');

step12.创建测试表进程测试

Source：sqlplus tianbaobao/tianbaobao@sourcedbSQL> create table tb30(x int);

Table created.

SQL> insert into tb30 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table tb30 add y int ;

Table altered.

SQL> insert into tb30 values (2,2);

1 row created.

SQL> commit;

Commit complete.

SQL>

Traget：sqlplus tianbaobao/tianbaobao@targetdbSQL> select * from tb30;

X Y---------- ---------- 1 2 2

SQL>

step13 .常用命令

select CAPTURE_NAME,QUEUE_NAME,STATUS,CAPTURED_SCN,APPLIED_SCN from dba_capture;

select PROPAGATION_NAME,SOURCE_QUEUE_OWNER,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation;

select APPLY_NAME,ERROR_NUMBER,STATUS from dba_apply;

select APPLY_NAME,QUEUE_NAME,MESSAGE_NUMBER,ERROR_NUMBER,ERROR_MESSAGE from dba_apply_error;

exec dbms_capture_adm.stop_capture(capture_name => 'capture_source');

exec dbms_apply_adm.stop_apply(apply_name => 'apply_target');

exec DBMS_STREAMS_ADM.remove_streams_configuration();

ITPub博客

Oracle Stream概述与配置

最新文章