oracle streams相關啟_停_刪capture_apply_propagation

--check streams environment

show parameter  aq_tm_processes    --2
show parameter global_names   --true
show parameter job_queue_processes   --10
show parameter parallel_max_servers   --20
show parameter undo_retention    --3600
show parameter nls_date_format    --'YYYY-MM-DD HH24:MI:SS'
show parameter streams_pool_size   --25M
show parameter utl_file_dir    --'*'
show parameter  open_links    --4
-----------------------------------------------------------------------------------------------------------
select owner,object_name from dba_objects where object_type='DATABASE LINK';
select owner,object_name from dba_objects where object_type='DIRECTORY';
select capture_name ,queue_name, rule_set_name, negative_rule_set_name,captured_scn,applied_scn,status from dba_capture;
select apply_name ,apply_captured,error_number,error_message,status from dba_apply;
select * from dba_propagation;

select apply_name,error_number,error_message from dba_apply_error;

select name, queue_table from user_queues;
------------------------------------------------------------------
select owner,object_name from dba_objects where object_type='DATABASE LINK';
select owner,object_name from dba_objects where object_type='DIRECTORY';
select name, queue_table from user_queues;
select capture_name ,status from dba_capture;
select apply_name,status from dba_apply;
select propagation_name,status from dba_propagation;
--------------------------------------------------------------------------------------------

--clear and remove streams environment
conn
select capture_name,status from dba_capture;
select propagation_name,status from dba_propagation;
select apply_name,status from dba_apply;

exec dbms_capture_adm.stop_capture('WDB$CAP');
exec dbms_capture_adm.drop_capture('WDB$CAP');

exec dbms_propagation_adm.stop_propagation('PROPAGATION$_27');
exec dbms_propagation_adm.drop_propagation('PROPAGATION$_27');

exec dbms_apply_adm.stop_apply('APPLY$_ORA10G_18');
exec dbms_apply_adm.start_apply('APPLY$_ORA10G_18');
exec dbms_apply_adm.drop_apply('APPLY$_ORA10G_18');

exec dbms_streams_adm.remove_streams_configuration;

以下如果沒有報錯可以不用執行
begin
     dbms_streams_adm.recover_operation(
     script_id=>'6ECEAA16E37D6FBCE04400144F9F6BEA',
     operation_mode=>'PURGE');
end;

begin
 dbms_streams_adm.remove_streams_configuration();
 dbms_streams_adm.remove_queue(queue_name => 'WDB$CAPQ',cascade => true);
 dbms_streams_adm.remove_queue(queue_name => 'BDB$APPQ',cascade => true);
end;
/
conn
begin
 dbms_streams_adm.remove_streams_configuration();
 dbms_streams_adm.remove_queue(queue_name => 'hbdbc_queue',cascade => true);
 dbms_streams_adm.remove_queue(queue_name => 'hbdbr_queue',cascade => true);
end;
/

-------------------------------------------------
--restart streams 3 process:capture,propagation,apply
begin
dbms_capture_adm.stop_capture('HBDB$CAP');
dbms_propagation_adm.stop_propagation('PROPAGATION$_86');
dbms_apply_adm.stop_apply('APPLY$_BJDB_83');

dbms_apply_adm.start_apply('APPLY$_BJDB_83');
dbms_capture_adm.start_capture('HBDB$CAP');
dbms_propagation_adm.start_propagation('PROPAGATION$_86');

end;

-----------------------------------------