通用_11gr2 STREAMS部署(单向)

环境信息

	source	target
数据库	11gr2	11gr2
操作系统	Red Hat Enterprise Linux Server release 7.4 (Maipo)	Red Hat Enterprise Linux Server release 7.4 (Maipo)
dbname	orcl	orcl
global_name	orcl_soure	orcl_target

步骤

源库和目标库均修改为归档模式

1、检查 SQL> archive log list; 2、设置 SQL> alter database archivelog;

修改源库和目标库的参数

源库： SQL> alter system set aq_tm_processes=2 scope=both; SQL> alter system set global_names=true scope=both; SQL> alter system set job_queue_processes=10 scope=both; SQL> alter system set streams_pool_size=200M scope=both; SQL> alter system set open_links=4 scope=spfile; SQL> alter database rename GLOBAL_NAME to "orcl_source"; 目标库： SQL> alter system set aq_tm_processes=2 scope=both; SQL> alter system set global_names=true scope=both; SQL> alter system set job_queue_processes=10 scope=both; SQL> alter system set streams_pool_size=200M scope=both; SQL> alter system set open_links=4 scope=spfile; SQL> alter database rename GLOBAL_NAME to "orcl_target"; 【参数注释】 job_queue_processes 　　决定了job作业能够使用的总进程数 aq_tm_processes         该参数决定了数据库启动时Qnnn进程的数量，负责监视高级队列和负责队列传播（propagation） streams_pool_size　　    手动指定srteams池的大小 open_links              每个session最多允许的dblink数量 global_names            streams必须要设置该参数为true，当GLOBAL_NAMES参数设置为TRUE时，创建DBLINK的名称必须与被连接库的GLOBAL_NAME一致。

源库目标库创建表空间和用户

SQL> create tablespace TBS_CRM datafile '/u01/app/oracle/oradata/orcl/data/crm.dbf' size 512M reuse autoextend on maxsize 1G; SQL> create user crmuser identified by crmuser default tablespace TBS_CRM quota unlimited on TBS_CRM; SQL> grant dba to crmuser; SQL> exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('crmuser');

源库目标库TNS修改

orcl_source=   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.111)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   ) orcl_target=   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.112)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   )

在源库和目标库创建db link

源库： SQL> sqlplus /nolog SQL> conn crmuser/crmuser SQL> create database link orcl_target connect to crmuser identified by crmuser using ‘orcl_target’; Select * from global_name@orcl_target; 目标库： SQL> sqlplus /nolog SQL> conn crmuser/crmuser SQL> create database link orcl_source connect to crmuser identified by crmuser using ‘orcl_source’; Select * from global_name@orcl_source; 注意：database link名称必须是目标库的global_name名称

在源库和目标库启动追加日志

SQL> sqlplus / as sysdba SQL> alter database add supplemental log data;

源库上创建发送队列

SQL> sqlplus /nolog SQL> conn crmuser/crmuser SQL> BEGIN    DBMS_STREAMS_ADM.SET_UP_QUEUE(    queue_table => 'SOURCE_QUEUE_TABLE',    queue_name => 'SOURCE_QUEUE',    queue_user => 'crmuser');    END;    / 验证是否创建成功： SQL> select OWNER,QUEUE_TABLE,OBJECT_TYPE from dba_queue_tables where owner='CRMUSER'; 删除命令：exec dbms_streams_adm.remove_queue(queue_name=>'SOURCE_QUEUE');

在目标库上创建接收队列

SQL> sqlplus /nolog SQL> conn crmuser/crmuser SQL> BEGIN    DBMS_STREAMS_ADM.SET_UP_QUEUE(    queue_table => 'TARGET_QUEUE_TABLE',    queue_name => 'TARGET_QUEUE',    queue_user => ' crmuser ');    END;    / 验证是否创建成功： SQL> select OWNER,QUEUE_TABLE,OBJECT_TYPE from dba_queue_tables where owner='CRMUSER'; 删除命令：exec dbms_streams_adm.remove_queue(queue_name=>'SOURCE_QUEUE');

在源库上创建capture 进程

SQL> BEGIN    DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(    schema_name => 'cyd',    streams_type => 'capture',    streams_name => 'capture_stream',    queue_name => 'crmuser.SOURCE_QUEUE',    include_dml => true,    include_ddl => true,    source_database => 'orcl_source',    include_tagged_lcr => false,    inclusion_rule => true);    END;    / 验证是否创建成功： select CAPTURE_NAME,QUEUE_NAME,QUEUE_OWNER,CAPTURE_USER,START_SCN,STATUS,SOURCE_DATABASE, CAPTURE_TYPE,START_TIME from dba_capture; select * from dba_capture_prepared_schemas;

源库上创建传播进程（Propagation Process）

-- Propagation [ˌprɒpə'ɡeɪʃ(ə)n]

SQL> BEGIN    DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(    schema_name => 'cyd',    streams_name => 'source_to_target',    source_queue_name => 'crmuser.SOURCE_QUEUE',    destination_queue_name => 'crmuser.TARGET_QUEUE@orcl_target',    include_dml => true,    include_ddl => true,    source_database => 'orcl_source',    inclusion_rule => true,    queue_to_queue => true);    END;    / 修改propagation休眠时间为0，表示实时传播LCR SQL> BEGIN    dbms_aqadm.alter_propagation_schedule(    queue_name => 'SOURCE_QUEUE',    destination => 'orcl_target',    destination_queue => 'TARGET_QUEUE',    latency => 0);    END;    /

在目标库创建Apply进程

SQL> BEGIN    DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(    schema_name => 'cyd',    streams_type => 'apply',    streams_name => 'target_apply_stream',    queue_name => 'crmuser.TARGET_QUEUE',    include_dml => true,    include_ddl => true,    include_tagged_lcr => false,    source_database => 'orcl_source',    inclusion_rule => true);    END;    / 验证是否创建成功： select APPLY_NAME,QUEUE_NAME,QUEUE_OWNER,APPLY_CAPTURED,RULE_SET_OWNER,APPLY_USER,APPLY_DATABASE_LINK,STATUS from dba_apply;

目标库数据初始化

SQL> exp cyd/cyd1234@orcl_source file=/u01/app/oracle/cyd.dmp object_consistent=y rows=y; SQL> imp crmuser/crmuser@orcl_target file='/u01/app/oracle/cyd.dmp' ignore=y commit=y log='/u01/app/oracle/cyd.log' streams_instantiation=y fromuser=cyd touser=cyd;

目标库上启动apply进程

SQL> BEGIN    DBMS_APPLY_ADM.START_APPLY(    apply_name => 'target_apply_stream');    END; / 验证是否创建成功： SQL>select APPLY_NAME,QUEUE_NAME,QUEUE_OWNER,APPLY_CAPTURED,RULE_SET_OWNER,APPLY_USER ,APPLY_DATABASE_LINK,STATUS from dba_apply;

在源库上启动capture进程

SQL> BEGIN    DBMS_CAPTURE_ADM.START_CAPTURE(    capture_name => 'capture_stream');    END;    / 验证是否创建成功： SQL>select CAPTURE_NAME,QUEUE_NAME,QUEUE_OWNER,CAPTURE_USER,START_SCN,STATUS, SOURCE_DATABASE,CAPTURE_TYPE,START_TIME from dba_capture;

验证

在源库上创建表或增、删、改数据，验证目标库是否完全准确同步。

附1：排除规则

由于streams对于物化视图数据同步不支持，所以可按物化视图名称设定排除规则：

--添加过滤规则：排除掉物化视图 schema.mviewNm 的同步 set serveroutput on ; declare                    l_dml_rule_name varchar2(30);                    l_ddl_rule_name varchar2(30); begin          dbms_streams_adm.add_table_rules (                    table_name => 'schema.mviewNm',                    streams_type => 'CAPTURE',                    streams_name => 'CAPTURE_STREAM',                            queue_name => 'SOURCE_QUEUE',                    include_dml => true,                    include_ddl => true,                    inclusion_rule => false,                                --指定是 negative rule                    source_database => 'ORCL_SOURCE',                    dml_rule_name => l_dml_rule_name,                    ddl_rule_name => l_ddl_rule_name          );          dbms_output.put_line('DML Rule Name is: ' || l_dml_rule_name);          dbms_output.put_line('DDL Rule Name is: ' || l_ddl_rule_name); end; --查找创建的规则 select * from dba_rules; --根据规则名称删除规则 BEGIN DBMS_RULE_ADM.DROP_RULE ( rule_name => 'MVIEW_TB335', force  => TRUE ); END; /

附2：apply端问题排除

--查找apply状态 select * from dba_apply; --查找apply错误 select * from dba_apply_error order by error_creation_time desc; --查找apply错误详情 select * from dba_apply_error_messages where local_transaction_id='8.4.3091'; --重新执行某个错误 exec dbms_apply_adm.execute_error('10.25.2632');   --dba_apply_error.local_transaction_id --执行所有错误 exec dbms_apply_adm.execute_all_errors(); --删除/忽略某个错误 exec dbms_apply_adm.delete_error('8.4.3091');    --dba_apply_error.local_transaction_id --删除/忽略所有错误 exec dbms_apply_adm.delete_all_errors(); --启动apply exec dbms_apply_adm.start_apply('TARGET_APPLY_STREAM'); --停止apply exec dbms_apply_adm.stop_apply('TARGET_APPLY_STREAM');