发表于:2008.02.23 01:07
分类: oracle管理
出处：http://zhouwf0726.itpub.net/post/9689/455634
---------------------------------------------------------------
FMS oracle stream configuration
On destination database,open Apply process:
1) Create stream user:repadmin
2) connect repadmin
3) run the following scripts:
4) APPLY_REP_QUEUE has been used by other source DB,we will share one apply queue as other sources
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name =>'FMS_PROD_USER.SHIPMENT_REQUESTS',
streams_type =>'APPLY',
streams_name =>'STRM_FMS_APPLY',
queue_name =>'REPADMIN.APPLY_REP_QUEUE',
include_dml =>true,
include_ddl =>true,
source_database =>'FMS1CN.xxxxxx'
);
end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name =>'FMS_PROD_USER.SHIPMENT_REQUEST_ITEMS',
streams_type =>'APPLY',
streams_name =>'STRM_FMS_APPLY',
queue_name =>'REPADMIN.APPLY_REP_QUEUE',
include_dml =>true,
include_ddl =>true,
source_database =>'FMS1CN.xxxxxx'
);
end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name =>'FMS_PROD_USER.SHIPMENT_REQUEST_STATES',
streams_type =>'APPLY',
streams_name =>'STRM_FMS_APPLY',
queue_name =>'REPADMIN.APPLY_REP_QUEUE',
include_dml =>true,
include_ddl =>true,
source_database =>'FMS1CN.xxxxxx'
);
end;
/
begin
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name =>'STRM_FMS_APPLY',
apply_user =>'REPADMIN'
);
end;
/
begin
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name =>'STRM_FMS_APPLY',
parameter =>'DISABLE_ON_ERROR',
value =>'N'
);
end;
/
begin
DBMS_APPLY_ADM.START_APPLY(apply_name =>'STRM_FMS_APPLY');
end;
/
on original database,open capture process
1) Create stream user:repadmin
2) connect repadmin
3) run the following scripts:
--execute DBMS_STREAMS_ADM.REMOVE_QUEUE('CAPTURE_REP_QUEUE',true);
begin
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table =>'CAPTURE_REP_QUEUE_TABLE',
queue_name =>'CAPTURE_REP_QUEUE',
queue_user =>'REPADMIN'
);
end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'FMS_PROD_USER.SHIPMENT_REQUESTS',
streams_type => 'CAPTURE',
streams_name => 'STRM_CAPTURE',
queue_name => 'REPADMIN.CAPTURE_REP_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'FMS1CN.xxxxxx'
);
end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'FMS_PROD_USER.SHIPMENT_REQUEST_ITEMS',
streams_type => 'CAPTURE',
streams_name => 'STRM_CAPTURE',
queue_name => 'REPADMIN.CAPTURE_REP_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'FMS1CN.xxxxxx'
);
end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'FMS_PROD_USER.SHIPMENT_REQUEST_STATES',
streams_type => 'CAPTURE',
streams_name => 'STRM_CAPTURE',
queue_name => 'REPADMIN.CAPTURE_REP_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'FMS1CN.xxxxxx'
);
end;
/
--execute DBMS_PROPAGATION_ADM.DROP_PROPAGATION('STRM_PROPAGATE',true);
--or begin DBMS_PROPAGATION_ADM.DROP_PROPAGATION(
propagation_name =>'STRM_PROPAGATE',
drop_unused_rule_sets =>true);end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name =>'FMS_PROD_USER.SHIPMENT_REQUESTS',
streams_name =>'STRM_PROPAGATE',
source_queue_name =>'REPADMIN.CAPTURE_REP_QUEUE',
destination_queue_name =>'REPADMIN.APPLY_REP_QUEUE@FCDW1CN.xxxxxx',
include_dml => true,
include_ddl => true,
source_database =>'FMS1CN.xxxxxx'
);
end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name =>'FMS_PROD_USER.SHIPMENT_REQUEST_ITEMS',
streams_name =>'STRM_PROPAGATE',
source_queue_name =>'REPADMIN.CAPTURE_REP_QUEUE',
destination_queue_name =>'REPADMIN.APPLY_REP_QUEUE@FCDW1CN.xxxxxx',
include_dml => true,
include_ddl => true,
source_database =>'FMS1CN.xxxxxx'
);
end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name =>'FMS_PROD_USER.SHIPMENT_REQUEST_STATES',
streams_name =>'STRM_PROPAGATE',
source_queue_name =>'REPADMIN.CAPTURE_REP_QUEUE',
destination_queue_name =>'REPADMIN.APPLY_REP_QUEUE@FCDW1CN.xxxxxx',
include_dml => true,
include_ddl => true,
source_database =>'FMS1CN.xxxxxx'
);
end;
/
DBMS_CAPTURE_ADM.SET_PARAMETER('STRM_CAPTURE','parallelism','2');
apply_name =>'STRMADMIN_APPLY',
parameter =>'DISABLE_ON_ERROR',
value =>'N'
);
on orignial database: sqlplus "/ as sysdba"
alter table FMS_PROD_USER.SHIPMENT_REQUEST_STATES add supplemental log group SHIPMENT_REQUEST_STATES_pk(SHIPMENT_REQUEST_STATE_CD) always;
alter table FMS_PROD_USER.SHIPMENT_REQUESTS add supplemental log group SHIPMENT_REQUESTS_pk(SHIPMENT_REQUEST_ID) always;
:$
alter table FMS_PROD_USER.SHIPMENT_REQUEST_ITEMS add supplemental log group SHIPMENT_REQUEST_ITEMS_pk (SHIPMENT_REQUEST_ITEM_ID) always;
on orignial database,export data from database to disk
cd ~oracle
exp userid=fms_prod_user/fms_prod_user owner=fms_prod_user file=fmsdate.dmp log=fms-exp.log object_consistent=y statistics=none
on destination database,imp data from disk to database
Import into the Destination Database:
Specify STREAMS_INSTANTIATION=Y clause in the import command.
By doing this, the streams metadata is updated with the appropriate
information in the destination database corresponding to the SCN that
is recorded in the export file.
imp USERID=fms_prod_user/fms_prod_user FULL=Y CONSTRAINTS=Y FILE=~oracle/fmsdate.dmp IGNORE=Y
COMMIT=Y LOG=fms-imp.log STREAMS_INSTANTIATION=Y
on original database,start capture process
begin
DBMS_CAPTURE_ADM.start_capture(
capture_name =>'STRM_CAPTURE'
);
end;
/
on orignial database,check capture process
@stream-capture-process-event-enqueue-latency.sql
on destination database,check apply process
@stream-apply-event-latency-dynamic.sql
other testing configuration commands,maybe will be usefull:
begin
DBMS_CAPTURE_ADM.ALTER_CAPTURE(
CAPTURE_NAME=>'STRM_CAPTURE',
first_scn=>579634988
);
end;
/
begin
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name =>'STRM_FMS_APPLY',
apply_user =>'REPADMIN'
);
end;
/
begin
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name =>'REPADMIN.CAPTURE_REP_QUEUE',
capture_name =>'STRM_CAPTURE',
rule_set_name =>NULL,
start_scn =>NULL,
source_database =>'FMS1CN.xxxxxx',
use_database_link =>false,
first_scn =>NULL,
logfile_assignment =>'implicit');
end;
/
begin
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name =>'FMS_PROD_USER',
streams_name =>'STRM_PROPAGATE',
source_queue_name =>'REPADMIN.CAPTURE_REP_QUEUE',
destination_queue_name =>'REPADMIN.APPLY_FMS_QUEUE@FCDW1CN.xxxxxx',
include_dml => true,
include_ddl => true,
source_database =>'FMS1CN.xxxxxx'
);
end;
/
begin
dbms_apply_adm.drop_apply(
apply_name =>'STRM_FMS_APPLY',
drop_unused_rule_sets =>true
);
end;
/
exec dbms_apply_adm.DELETE_ALL_ERRORS('STRM_FMS_APPLY');
exec dbms_apply_adm.EXECUTE_ALL_ERRORS('STRM_FMS_APPLY');
set serveroutput on size 100000
declare until_scn number;
begin
until_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
DBMS_OUTPUT.PUT_LINE('until scn:'||until_scn);
end;
/
1)before export scn:936919117
2)after export scn:936960401
3)after imp scn:937016933
936837098
937038122
re: ORACLE STREAM复制 (二)