oracle stream脚本

配置stream：

#!/usr/bin/sh

export ORACLE_SID=prod

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin

umask 022

 

 

exp userid=scott/tiger@prod tables=scott.t1 file=/home/oracle/exp/DATA$DAT.dmp

imp userid=system/oracle@dss file='/home/oracle/exp/DATA$DAT.dmp' ignore=y commit=y streams_instantiation=y fromuser=scott touser=scott;

 

DAT=`date +%Y%m`

 

sqlplus stream/oracle@prod <

begin

dbms_streams_adm.add_table_rules(

table_name=>'scott.DATA$DAT',

streams_type=>'capture',

streams_name=>'capture_prod',

queue_name=>'stream.prod_queue',

include_dml=>true,

include_ddl=>true);

end;

/

 

begin

dbms_streams_adm.add_table_propagation_rules(

table_name=>'scott.DATA$DAT',

streams_name=>'prod_to_DSS',

source_queue_name=>'stream.prod_queue',

destination_queue_name=>'stream.DSS_queue@dss',

include_dml=>true,

include_ddl=>true,

source_database=>'prod');

end;

/

 

DECLARE

  iscn  NUMBER;

BEGIN

  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER( );

  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@dss(

    source_object_name=>'scott.DATA$DAT',

    source_database_name=>'prod',

    instantiation_scn=>iscn);

end;

/

 

begin

dbms_capture_adm.start_capture(

capture_name=>'capture_prod');

end;

/

EOF

 

sqlplus stream/oracle@dss <

begin

dbms_streams_adm.add_table_rules(

table_name=>'scott.DATA$DAT',

streams_type=>'apply',

streams_name=>'apply_DSS',

queue_name=>'stream.DSS_queue',

include_dml=>true,

include_ddl=>true,

source_database=>'prod');

end;

/

 

begin

dbms_apply_adm.start_apply(

apply_name=>'apply_DSS');

end;

/

 

EOF

 

清除stream：

#!/usr/bin/sh

export ORACLE_BASE=/u01/oracle

export ORACLE_HOME=/u01/oracle/product/10.2.0

export ORACLE_SID=prod

export PATH=$PATH:$ORACLE_HOME/bin:/usr/bin:/etc:/usr/bin/X11:/usr/local/bin

 

 

sqlplus stream/oracle@prod <

begin

dbms_capture_adm.stop_capture(

capture_name=>'capture_prod');

end;

/

 

exec dbms_streams_adm.remove_streams_configuration();

 

EOF

 

sqlplus stream/oracle@dss <

begin

dbms_apply_adm.stop_apply(

apply_name=>'apply_DSS');

end;

/

 

exec dbms_streams_adm.remove_streams_configuration();

EOF