Oracle 10g schema级别本地捕获stream配置

################################################################
#source database 192.168.6.2
#target database 192.168.6.3
################################################################

source database执行
1>为stream管理用户创建表空间
CREATE TABLESPACE streams_tbs DATAFILE '/data/oracle/oradata/source/streamtbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

2>创建stream管理用户，别授予权限
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;

GRANT DBA TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

把logminer字典移出系统表空间
execute dbms_logmnr_d.set_tablespace('streams_tbs');
commit;

 target database执行
3>为stream管理用户创建表空间
CREATE TABLESPACE streams_tbs DATAFILE '/data/oracle/oradata/target/streamtbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 创建stream管理用户，别授予权限
4> CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
   GRANT DBA TO strmadmin;

BEGIN
 DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
 grantee => 'strmadmin',
 grant_privileges => true);
END;
/

把logminer字典移出系统表空间
execute dbms_logmnr_d.set_tablespace('streams_tbs');

在source database和target database数据库执行
5>修改初始化参数
alter system set GLOBAL_NAMES=true scope=spfile;
alter system set JOB_QUEUE_PROCESSES=40 scope=spfile;
alter system set OPEN_LINKS=10 scope=spfile;
alter system set PARALLEL_MAX_SERVERS=20 scope=spfile;

6>创建dblink
source database：
CREATE DATABASE LINK target CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'target';

target database：
CREATE DATABASE LINK source CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'source';

注意LINK名字必须和数据库global_name相同,可以运行select * from global_name;查询

7>初始化数据可以使用 IMP/EXP 或者是RMAN，注意指定SCN 这个根据需求掌握
例子:
源数据库：
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
exp newweb/newweb wner=newweb CONSISTENT=y  flashback_scn=iscn-1  file=/data/newweb.dmp log=/data/newweb.log

目标数据库：
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
imp newweb/newweb fromuser=newweb touser=newweb STREAMS_INSTANTIATION=y file=/data/newweb.dmp log=/data/newweb.log

 

8>在目标库和源库创建anydata队列

source database:

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.queue_c',
queue_name => 'strmadmin.queue_c',
queue_user => 'strmadmin');
END;
/

target database：
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.queue_a',
queue_name => 'strmadmin.queue_a',
queue_user => 'strmadmin');
END;
/

source database执行
9>创建capture进程
BEGIN
 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
  SCHEMA_NAME =>'a',
  streams_type => 'capture',
  streams_name => 'capture_test',
  queue_name => 'strmadmin.queue_c',
  include_dml => true,
  include_ddl => true,
  include_tagged_lcr => false,
  source_database => 'source',
  inclusion_rule => true);
 END;
/

---propagation进程
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'a',
streams_name => 'propagation_test',
source_queue_name => 'strmadmin.queue_c',
destination_queue_name => 'strmadmin.queue_a@target',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'source',
inclusion_rule => true,
queue_to_queue => true);
END;
/

10>在target database执行

创建apply进程
BEGIN
 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
 schema_name => 'a',
 streams_type => 'APPLY',
 streams_name => 'apply_test',
 queue_name => 'strmadmin.queue_a',
 include_dml => true,
 include_ddl => true,
 source_database => 'source');
END;
/

 

初始化APPLY进程
----set install scn
set serveroutput on
DECLARE
  iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@source;
dbms_output.put_line(iscn);
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
  source_schema_name => 'a',
  source_database_name => 'source',
  instantiation_scn => iscn);
END;
/

11>在source database启动capture进程
exec dbms_capture_adm.start_capture('capture_test');

12>在target database启动apply进程
exec dbms_apply_adm.start_apply('apply_test');

 

配置完成！！

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7314177/viewspace-630433/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7314177/viewspace-630433/