oracle文schema配置,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 => ,

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_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');

配置完成！！