oracle stream参数,Oracle Stream概述与配置

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();