本文记录了整个stream的配置过程,请根据环境的不同修改schema,以及相关路径.
–1)设定初始化参数
–在线、备份数据库分别执行如下的语句：
sqlplus ‘/ as sysdba‘ alter system set aq_tm_processes=4 scope=both; alter system set global_names=true scope=both; alter system set job_queue_processes=10 scope=both; alter system set parallel_max_servers=20 scope=both; alter system set undo_retention=3600 scope=both; alter system set nls_date_format=‘YYYY-MM-DD HH24:MI:SS‘ scope=spfile; alter system set streams_pool_size=500M scope=spfile; alter system set utl_file_dir=‘*‘ scope=spfile; alter system set open_links=6 scope=spfile; alter database add supplemental log data;
–2)在线、备份库设置为归档模式
– 修改实际log_archive_dest_1路径
sqlplus ‘/ as sysdba‘ alter system set log_archive_dest_1=‘location=/oraarch/archdir‘ scope=spfile; alter system set log_archive_start=TRUE scope=spfile; --重启数据库 shutdown immediate; startup mount; alter database archivelog; alter database open;
–3)创建STRMADMIN用户
– 修改create_strmamin.sql中的表空间信息
–在线、备份
sqlplus ‘/ as sysdba‘ -- 创建LOGMNRTS表空间 DROP TABLESPACE LOGMNRTS INCLUDING CONTENTS AND DATAFILES; CREATE TABLESPACE LOGMNRTS DATAFILE ‘/oradata/stream/logmnrts01.dbf‘ SIZE 500M AUTOEXTEND ON NEXT 8K MAXSIZE 2048M LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; -- 创建STRMADMIN用户 DROP USER STRMADMIN CASCADE; CREATE USER STRMADMIN IDENTIFIED BY STRMADMIN DEFAULT TABLESPACE LOGMNRTS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 4 Roles for STRMADMIN GRANT RESOURCE TO STRMADMIN; GRANT CONNECT TO STRMADMIN; GRANT AQ_ADMINISTRATOR_ROLE TO STRMADMIN; GRANT DBA TO STRMADMIN; ALTER USER STRMADMIN DEFAULT ROLE ALL; -- 16 System Privileges for STRMADMIN BEGIN SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE ( PRIVILEGE => ‘ENQUEUE_ANY‘, GRANTEE => ‘STRMADMIN‘, ADMIN_OPTION => TRUE); END; / BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_RULE, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / BEGIN SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE ( PRIVILEGE => ‘DEQUEUE_ANY‘, GRANTEE => ‘STRMADMIN‘, ADMIN_OPTION => TRUE); END; / GRANT UNLIMITED TABLESPACE TO STRMADMIN; BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / GRANT SELECT ANY DICTIONARY TO STRMADMIN; BEGIN SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE ( PRIVILEGE => ‘MANAGE_ANY‘, GRANTEE => ‘STRMADMIN‘, ADMIN_OPTION => TRUE); END; / BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE_SET, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_RULE_SET, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / GRANT RESTRICTED SESSION TO STRMADMIN; BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_RULE_OBJ, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT, GRANTEE => ‘STRMADMIN‘, GRANT_OPTION => TRUE); END; / -- 88 Object Privileges for STRMADMIN GRANT SELECT ON SYS.AQ$INTERNET_USERS TO STRMADMIN; GRANT SELECT ON SYS.AQ$_PROPAGATION_STATUS TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_CONFLICT_COLUMNS TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_DML_HANDLERS TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_ENQUEUE TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_ERROR TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_EXECUTE TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_INSTANTIATED_GLOBAL TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_INSTANTIATED_OBJECTS TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_INSTANTIATED_SCHEMAS TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_KEY_COLUMNS TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_PARAMETERS TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_PROGRESS TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_SPILL_TXN TO STRMADMIN; GRANT SELECT ON SYS.DBA_APPLY_TABLE_COLUMNS TO STRMADMIN; GRANT SELECT ON SYS.DBA_AQ_AGENTS TO STRMADMIN; GRANT SELECT ON SYS.DBA_AQ_AGENT_PRIVS TO STRMADMIN; GRANT SELECT ON SYS.DBA_CAPTURE TO STRMADMIN; GRANT SELECT ON SYS.DBA_CAPTURE_EXTRA_ATTRIBUTES TO STRMADMIN; GRANT SELECT ON SYS.DBA_CAPTURE_PARAMETERS TO STRMADMIN; GRANT SELECT ON SYS.DBA_CAPTURE_PREPARED_DATABASE TO STRMADMIN; GRANT SELECT ON SYS.DBA_CAPTURE_PREPARED_SCHEMAS TO STRMADMIN; GRANT SELECT ON SYS.DBA_CAPTURE_PREPARED_TABLES TO STRMADMIN; GRANT SELECT ON SYS.DBA_EVALUATION_CONTEXTS TO STRMADMIN; GRANT SELECT ON SYS.DBA_EVALUATION_CONTEXT_TABLES TO STRMADMIN; GRANT SELECT ON SYS.DBA_EVALUATION_CONTEXT_VARS TO STRMADMIN; GRANT SELECT ON SYS.DBA_PROPAGATION TO STRMADMIN; GRANT SELECT ON SYS.DBA_QUEUES TO STRMADMIN; GRANT SELECT ON SYS.DBA_QUEUE_PUBLISHERS TO STRMADMIN; GRANT SELECT ON SYS.DBA_QUEUE_SCHEDULES TO STRMADMIN; GRANT SELECT ON SYS.DBA_QUEUE_SUBSCRIBERS TO STRMADMIN; GRANT SELECT ON SYS.DBA_QUEUE_TABLES TO STRMADMIN; GRANT SELECT ON SYS.DBA_REGISTERED_ARCHIVED_LOG TO STRMADMIN; GRANT SELECT ON SYS.DBA_RULES TO STRMADMIN; GRANT SELECT ON SYS.DBA_RULESETS TO STRMADMIN; GRANT SELECT ON SYS.DBA_RULE_SETS TO STRMADMIN; GRANT SELECT ON SYS.DBA_RULE_SET_RULES TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_ADD_COLUMN TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_ADMINISTRATOR TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_DELETE_COLUMN TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_GLOBAL_RULES TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_MESSAGE_CONSUMERS TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_MESSAGE_RULES TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_NEWLY_SUPPORTED TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_RENAME_COLUMN TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_RENAME_SCHEMA TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_RENAME_TABLE TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_RULES TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_SCHEMA_RULES TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_TABLE_RULES TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_TRANSFORMATIONS TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_TRANSFORM_FUNCTION TO STRMADMIN; GRANT SELECT ON SYS.DBA_STREAMS_UNSUPPORTED TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_APPLY_ADM TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_AQ TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_AQADM TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_AQELM TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_AQIN TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_AQ_BQVIEW TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_CAPTURE_ADM TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_PROPAGATION_ADM TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_RULE_ADM TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_STREAMS_ADM TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_STREAMS_MESSAGING TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_STREAMS_RPC TO STRMADMIN; GRANT EXECUTE ON SYS.DBMS_TRANSFORM TO STRMADMIN; GRANT SELECT ON SYS.GV_$AQ TO STRMADMIN; GRANT SELECT ON SYS.GV_$BUFFERED_PUBLISHERS TO STRMADMIN; GRANT SELECT ON SYS.GV_$BUFFERED_QUEUES TO STRMADMIN; GRANT SELECT ON SYS.GV_$BUFFERED_SUBSCRIBERS TO STRMADMIN; GRANT SELECT ON SYS.GV_$STREAMS_APPLY_COORDINATOR TO STRMADMIN; GRANT SELECT ON SYS.GV_$STREAMS_APPLY_READER TO STRMADMIN; GRANT SELECT ON SYS.GV_$STREAMS_APPLY_SERVER TO STRMADMIN; GRANT SELECT ON SYS.GV_$STREAMS_CAPTURE TO STRMADMIN; GRANT SELECT ON SYS.GV_$STREAMS_TRANSACTION TO STRMADMIN; GRANT SELECT ON SYS.V_$AQ TO STRMADMIN; GRANT SELECT ON SYS.V_$BUFFERED_PUBLISHERS TO STRMADMIN; GRANT SELECT ON SYS.V_$BUFFERED_QUEUES TO STRMADMIN; GRANT SELECT ON SYS.V_$BUFFERED_SUBSCRIBERS TO STRMADMIN; GRANT SELECT ON SYS.V_$STREAMS_APPLY_COORDINATOR TO STRMADMIN; GRANT SELECT ON SYS.V_$STREAMS_APPLY_READER TO STRMADMIN; GRANT SELECT ON SYS.V_$STREAMS_APPLY_SERVER TO STRMADMIN; GRANT SELECT ON SYS.V_$STREAMS_CAPTURE TO STRMADMIN; GRANT SELECT ON SYS.V_$STREAMS_TRANSACTION TO STRMADMIN; execute dbms_logmnr_d.set_tablespace(‘LOGMNRTS‘);
–4)创建DBLINK
– 在线库和备份库分别配置tnsnames.ora，分别要包含在线库和备份库
– DBLink的名称保持与GLOBAL_NAME一致 本例中在线库GLOBAL_NAME:MC1
– 备份库 GLOBAL_NAME:MC2
–在线
–DBLink 在线->备份
–查看global_name
select global_name from global_name; sqlplus STRMADMIN/STRMADMIN CREATE DATABASE LINK MC2 CONNECT TO STRMADMIN identified by STRMADMIN using ‘MC2‘; --备份 --DBLink 备份->在线 sqlplus STRMADMIN/STRMADMIN CREATE DATABASE LINK MC1 CONNECT TO STRMADMIN identified by STRMADMIN using ‘ MC1 ‘;
–5)创建stream队列
–备份
sqlplus STRMADMIN/STRMADMIN BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => ‘STREAMS_QUEUE_TABLE‘, queue_name => ‘STREAMS_QUEUE‘, queue_user => ‘STRMADMIN‘); END; /
--在线
sqlplus STRMADMIN/STRMADMIN BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE ( queue_name => ‘STREAMS_QUEUE‘, queue_table =>‘STREAMS_QUEUE_TABLE‘, queue_user => ‘STRMADMIN‘); END; /
–6)创建捕获进程
– schema_name修改为实际需要同步的schema_name
– source_database修改为实际源数据库库
–在线
sqlplus STRMADMIN/STRMADMIN BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => ‘test‘, streams_type => ‘CAPTURE‘, streams_name => ‘STREAMS_CAPTURE‘, queue_name => ‘STREAMS_QUEUE‘, include_dml => true, include_ddl => true, include_tagged_lcr => false, inclusion_rule => true, source_database => null ); END;
– schema_name修改为实际需要同步的schema_name
– destination_queue_name修改为实际目标库
– source_database修改为实际源数据库库
BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => ‘test‘, streams_name => ‘STREAMS_PROPAGATE‘, source_queue_name => ‘STREAMS_QUEUE‘, destination_queue_name => ‘STREAMS_QUEUE@MC2‘, include_dml => true, include_ddl => true, include_tagged_lcr => false, source_database => ‘MC1‘, inclusion_rule => true ); END; / BEGIN DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE( queue_name => ‘STREAMS_QUEUE‘, destination => ‘MC2‘, latency=> 0 ); END; /
–7)创建应用进程
–备份
– schema_name修改为实际需要同步的schema_name
– source_database修改为实际源数据库库
sqlplus STRMADMIN/STRMADMIN BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => ‘test‘, streams_type => ‘APPLY‘, streams_name => ‘STREAMS_APPLY‘, queue_name => ‘STRMADMIN.STREAMS_QUEUE‘, include_dml => true, include_ddl => true, source_database => ‘MC1‘); END; /
–8)stream参数设置
–在线
BEGIN DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name => ‘STREAMS_CAPTURE‘, parameter => ‘disable_on_limit‘, value => ‘N‘); END; / BEGIN DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name => ‘STREAMS_CAPTURE‘, parameter => ‘parallelism‘, value => ‘4‘); END; /
– 备份
– apply_user修改为实际用户
sqlplus STRMADMIN/STRMADMIN BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => ‘STREAMS_APPLY‘, apply_user => ‘test‘); END; / BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => ‘STREAMS_APPLY‘, parameter => ‘DISABLE_ON_ERROR‘, value => ‘N‘ ); END; / BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => ‘STREAMS_APPLY‘, parameter => ‘allow_duplicate_rows‘, value => ‘Y‘ ); END; / BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => ‘STREAMS_APPLY‘, parameter => ‘parallelism‘, value => ‘2‘ ); END; /
–9)同步schema:test 的数据,具体操作省略
–10)stream启动
–备份 启动
sqlplus STRMADMIN/STRMADMIN BEGIN DBMS_APPLY_ADM.START_APPLY(apply_name => ‘STREAMS_APPLY‘); END; /
--在线 启动
sqlplus STRMADMIN/STRMADMIN exec dbms_capture_adm.START_CAPTURE (capture_name => ‘STREAMS_CAPTURE‘); exec dbms_propagation_adm.START_PROPAGATION (PROPAGATION_NAME => ‘STREAMS_PROPAGATE‘);
状态查看:
1.登陆在线库
SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME,NEGATIVE_RULE_SET_NAME,STATUS FROM DBA_CAPTURE;
如果STATUS状态是ENABLED，表示Capture进程运行正常;
如果STATUS状态是DISABLED，表示Capture进程处于停止状态，只需重新启动即可;
如果STATUS状态是ABORTED，表示Capture进程非正常停止，查询相应的ERROR_NUMBER、ERROR_MESSAGE列可以得到详细的信息;同时，Oracle会在跟踪文件中记录该信息。
SELECT CAPTURE_NAME, QUEUE_NAME, STATUS, CAPTURED_SCN, APPLIED_SCN FROM DBA_CAPTURE;
如果APPLIED_SCN小于CAPTURED_SCN，则表示在主数据库一端，要么LCR没有被dequeue，要么Propagation进程尚未传播到从数据库一端。
2.登陆备份库
SELECT apply_name, apply_captured, status FROM dba_apply;
如果STATUS状态是ENABLED，表示Apply进程运行正常;
如果STATUS状态是DISABLED，表示Apply进程处于停止状态，只需重新启动即可;
如果STATUS状态是ABORTED，表示Apply进程非正常停止，查询相应的ERROR_NUMBER、ERROR_MESSAGE列可以得到详细的信息;同时，可以查询DBA_APPLY_ERROR视图，了解详细的Apply错误信息。
本文出自 “mcshell学习博客” 博客，请务必保留此出处http://mcshell.blog.51cto.com/803455/1394295