个人建议还是尽量用手工配置,因为那些自动的脚本出现问题的时候,调试更困难.而且以后的维护也大多以手工为主.
- 全手工4节点
- Env
- Njzc--和orcl一个
- Strm1-和strm2一个
- Orcl是中心-->复制到njzc, orcl又发布到->strm1,strm2, 同时也有部分strm1和strm2的回到orcl(采集处)
- 硬盘
- Env
/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!