全手工4节点stream的试验

个人建议还是尽量用手工配置,因为那些自动的脚本出现问题的时候,调试更困难.而且以后的维护也大多以手工为主.

 

 

全手工4节点 Env Njzc--和orcl一个Strm1-和strm2一个Orcl是中心-->复制到njzc, orcl又发布到->strm1,strm2, 同时也有部分strm1和strm2的回到orcl(采集处) 硬盘

/dev/sdc -10g, fdisk, mkfs,

mount /dev/sdc1 /u02

Chown -R oracle:oinstall /u02

Vi /etc/fstab

/dev/sdc1               /u02     ext2    defaults        0       2

建库 Root: Xhost+Su - oracle, Dbca归档,flash区使用大小, 30%内存运行多个oracle实例后top的内存使用率下降了. Pre configuration

Sqlpuls

.bash_profile

SQLPATH=/home/oracle;export SQLPATH

Login.sql:

set termout off

define loginname=idle

column global_name new_value loginname

select USER||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name

from (select global_name, instr(global_name,'.') dot from global_name);

set sqlprompt '&loginname>'

set termout on

 

Normal:

alter system set global_names=true;

alter system set db_domain=net scope=spfile; shutdown immediate, startup

Compatible 10.2.0.3 and 10.2.0.4

Alter system set job_queue_processes=10;-->改了4个

Alter system set aq_tm_processes=10;-->改了4个

Sga_target小点看能否运行

 

Strmadmin user:

(njzc)Create tablespace strm_tbs datafile '/u02/oradata/njzc/strms01.dbf' size 500m;

(strm1)Create tablespace strm_tbs datafile '/u02/oradata/strm1/strms01.dbf' size 500m;

(njzc,strm1)Create user strmadmin identified by strmadmin default tablespace strm_tbs quota unlimited on strm_tbs;

(njzc,strm1)

grant dba to strmadmin;

begin

dbms_streams_auth.grant_admin_privilege(

grantee => 'strmadmin',

grant_privileges => true);

end;

/

 

Network:

网络参数--4台都加上.

测试不通过-因为自动注册后,改domain name后 注册的服务名变成.domainame后缀了,--加入静态注册ok

 

DB link:

(strm1)alter database rename global_name to strm1.net;

(njzc)alter database rename global_name to njzc.net;

Connect strmadmin/strmadmin

(orcl)Create database link strm1.net connect to strmadmin identified by strmadmin using 'STRM1';

(ocrl)Create database link njzc.net connect to strmadmin identified by strmadmin using 'NJZC';

(orcl)strm2-已经有

(strm1)Create database link orcl.net connect to strmadmin identified by strmadmin using 'ORCL';

(strm2)Create database link orcl.net connect to strmadmin identified by strmadmin using 'ORCL';

select * from global_name@xxx 都测试通过.

 

Directory:

Orcl:

(strmadmin)create directory dir_orcl as '/u01/app/oracle/flash_recovery_area';

Strm2:

(strmadmin)create directory dir_strm2 as '/u01/app/oracle/flash_recovery_area';

Strm1:

(strmadmin)create directory dir_strm1 as '/u01/app/oracle/flash_recovery_area';

Njzc

(strmadmin)create directory dir_njzc as '/u01/app/oracle/flash_recovery_area';

 

建表

(strm1,strm2)Create table hr.t2(id number,site varchar2(10),name varchar2(10));alter table hr.t2 add primary key(id,site);

(strm1)insert into hr.t2 values(1,'strm1',null);commit;

(strm2)insert into hr.t2 values(1,'strm2',null);commit;

建queue

建立queue(orcl & strm2已经创建)

(strm1)

begin

dbms_streams_adm.set_up_queue(

queue_table => 'strm1_cap_queue_table',

queue_name => 'strm1_cap_queue');

end;

/

begin

dbms_streams_adm.set_up_queue(

queue_table => 'apply_orcl_queue_table',

queue_name => 'apply_orcl_queue');

end;

/

(orcl)

begin

dbms_streams_adm.set_up_queue(

queue_table => 'apply_strm1_queue_table',

queue_name => 'apply_strm1_queue');

end;

/

--->orcl上把strm1的两个queue也建立-copy/paste错误!!

(orcl)

begin

dbms_streams_adm.set_up_queue(

queue_table => 'orcl_cap_que_4strm1_tab',

queue_name => 'orcl_cap_4strm1_queue');

end;

/

begin

dbms_streams_adm.set_up_queue(

queue_table => 'orcl_cap_que_4strm2_tab',

queue_name => 'orcl_cap_4strm2_queue');

end;

/

begin

dbms_streams_adm.set_up_queue(

queue_table => 'orcl_cap_que_4njzc_tab',

queue_name => 'orcl_cap_4njzc_queue');

end;

/

(njzc)

begin

dbms_streams_adm.set_up_queue(

queue_table => 'apply_orcl_queue_table',

queue_name => 'apply_orcl_queue');

end;

/

 

Rule (strm1-->orcl)

加rule

(Strm1):

begin

dbms_streams_adm.add_table_rules(

table_name => 'hr.t2',

streams_type => 'capture',

streams_name => 'strm1_capturer',

queue_name => 'strmadmin.strm1_cap_queue',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => null,

inclusion_rule => true);

end;

/

 

expdp system/sys@strm1 tables=(hr.t2) directory=dir_strm1 dumpfile=hr_t2.dmp

 

scp /u01/app/oracle/flash_recovery_area/hr_t2.dmp rh1:/u01/app/oracle/flash_recovery_area/hr_t2.dmp

 

impdp system/sys@orcl dumpfile='hr_t2.dmp' STREAMS_CONFIGURATION=y directory=dir_orcl

 

Propagation:

(strm1)

begin

dbms_streams_adm.add_table_propagation_rules(

table_name => 'hr.t2',

streams_name => 'strm1_propagator',

source_queue_name => 'strmadmin.strm1_cap_queue',

destination_queue_name => 'strmadmin.apply_strm1_queue@orcl.net',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => 'orcl.net',

inclusion_rule => true);

end;

 

Apply:

(orcl)

Begin

dbms_streams_adm.add_table_rules(

table_name => 'hr.t2',

streams_type => 'apply',

streams_name => 'apply_strm1',

queue_name => 'strmadmin.apply_strm1_queue',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => null,

inclusion_rule => true);

end;

/

 

*propogate错误, source, strm1.net

修改,T23,

Begin

Dbms_streams_adm.remove_rule(

Rule_name=>'T23',

streams_type=>'propagation',

streams_name=>'strm1_propagator');

End;

删除完毕了查询为什么还在????--dba_rules

 

重新加入

Propagation:

(strm1)

begin

dbms_streams_adm.add_table_propagation_rules(

table_name => 'hr.t2',

streams_name => 'strm1_propagator',

source_queue_name => 'strmadmin.strm1_cap_queue',

destination_queue_name => 'strmadmin.apply_strm1_queue@orcl.net',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => 'strm1.net',

inclusion_rule => true);

end;

 

启用capture和apply(propagation自动enable)

(orcl)Exec dbms_apply_adm.start_apply('APPLY_STRM1');

(strm1)Exec dbms_capture_adm.start_capture('STRM1_CAPTURER');

 

Works!

 

 

Rule (strm2-->orcl)

加rule

(Strm2):

begin

dbms_streams_adm.add_table_rules(

table_name => 'hr.t2',

streams_type => 'capture',

streams_name => 'strm2_capturer',

queue_name => 'strmadmin.strm2_cap_queue',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => null,

inclusion_rule => true);

end;

/

 

expdp system/sys@strm2 tables=(hr.t2) directory=dir_strm2 dumpfile=hr_t2_strm2.dmp

 

scp /u01/app/oracle/flash_recovery_area/hr_t2_strm2.dmp rh1:/u01/app/oracle/flash_recovery_area/hr_t2_strm2.dmp

 

impdp system/sys@orcl dumpfile='hr_t2_strm2.dmp' TABLE_EXISTS_ACTION=APPEND STREAMS_CONFIGURATION=y directory=dir_orcl

 

ORA-39152: Table "HR"."T2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

 

Propagation:

(strm2)

begin

dbms_streams_adm.add_table_propagation_rules(

table_name => 'hr.t2',

streams_name => 'strm1_propagator',

source_queue_name => 'strmadmin.strm2_cap_queue',

destination_queue_name => 'strmadmin.apply_strm2_queue@orcl.net',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => 'strm2.net',

inclusion_rule => true);

end;

 

Apply:

(orcl)

Begin

dbms_streams_adm.add_table_rules(

table_name => 'hr.t2',

streams_type => 'apply',

streams_name => 'apply_strm2',

queue_name => 'strmadmin.apply_strm2_queue',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => null,

inclusion_rule => true);

end;

/

 

启用capture和apply(propagation自动enable)

(orcl)Exec dbms_apply_adm.start_apply('APPLY_STRM2');

(strm1)Exec dbms_capture_adm.start_capture('STRM2_CAPTURER');

 

select * from dba_rules where rule_owner='STRMADMIN' and rule_name like 'T2%'

 

表的第二个同步源是不是什么也不需要做???

 

Works!

 

Rule(orcl-->njzc)

加rule

(orcl):

begin

dbms_streams_adm.add_table_rules(

table_name => 'hr.t2',

streams_type => 'capture',

streams_name => 'orcl_capturer',

queue_name => 'strmadmin.orcl_cap_queue',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => null,

inclusion_rule => true);

end;

/

 

expdp system/sys@orcl tables=(hr.t2) directory=dir_orcl dumpfile=hr_t2_orcl.dmp

 

#scp /u01/app/oracle/flash_recovery_area/hr_t2.dmp rh1:/u01/app/oracle/flash_recovery_area/hr_t2.dmp

 

impdp system/sys@njzc dumpfile='hr_t2_orcl.dmp' STREAMS_CONFIGURATION=y directory=dir_njzc

 

Propagation:(第一次dest queue 错误apply_orcl

(orcl)

begin

dbms_streams_adm.add_table_propagation_rules(

table_name => 'hr.t2',

streams_name => 'orcl_propagator_njzc',

source_queue_name => 'strmadmin.orcl_cap_queue',

destination_queue_name => 'strmadmin.apply_orcl_queue@njzc.net',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => 'orcl.net',

inclusion_rule => true);

end;

 

ORA-24010: QUEUE "STRMADMIN"."ORCL_CAP_QUEUE" does not exist

改变progate名字,建立第二个,---->需要吗?

 

Apply:

(njzc)

Begin

dbms_streams_adm.add_table_rules(

table_name => 'hr.t2',

streams_type => 'apply',

streams_name => 'apply_orcl',

queue_name => 'strmadmin.apply_orcl_queue',

include_dml => true,

include_ddl => false,

include_tagged_lcr => false,

source_database => null,

inclusion_rule => true);

end;

/

 

#启用capture和apply(propagation自动enable)

(njzc)Exec dbms_apply_adm.start_apply('APPLY_ORCL');

(orcl)Exec dbms_capture_adm.start_capture('ORCL_CAPTURER');

 

ddl的使用-多源主肯定有冲突!

 

Works!