oracle stream 详细配置

4rr

本文记录了整个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错误信息。