stream - table 单表流复制

源库和目标库都为归档
源库：
SQL> archive log list
数据库日志模式            存档模式
自动存档             启用
存档终点            /oradata/arch
最早的联机日志序列     49
下一个存档日志序列   51
当前日志序列           51
目标库：
SQL> archive log list
数据库日志模式            存档模式
自动存档             启用
存档终点            E:\app\administrator\oradata\arch
最早的联机日志序列     15
下一个存档日志序列   17
当前日志序列           17
SQL>
源库和目标库均需要设置以下参数
alter system set global_names=true scope =both;
alter system set aq_tm_processes=2 scope=both;
alter system set"_job_queue_interval"=1 scope=spfile;
alter system set aq_tm_processes=1;
alter system set streams_pool_size=200m scope=both;
在源库上启用追加日志
alter database add supplemental log data;
源库和目标库创建相同的表空间和用户并赋予权限
源库：
SQL> create tablespace stream_tbs datafile '/oradata/ora11204/ora11204/streams_tbs.dbf' size 100m autoextend on;
SQL> create user strmadmin identified by strmadmin default tablespace stream_tbs quota unlimited on stream_tbs;
SQL> grant connect,resource,dba to strmadmin; 
SQL> grant CREATE DATABASE LINK to strmadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('strmadmin');
目标库：
SQL> create tablespace stream_tbs datafile 'E:\app\Administrator\oradata\orcl\streams_tbs.dbf' size 100m autoextend on;
 
SQL> create user strmadmin identified by strmadmin default tablespace stream_tbs quota unlimited on stream_tbs;
Grant DBA TO STRMADMIN


SQL> grant connect,resource,dba to strmadmin; 
SQL> grant CREATE DATABASE LINK to strmadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('strmadmin');
同上操作


配置tns
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.94)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


ora11204 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.231)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11204)
    )
  )创建dblink
先用strmadmin登陆，在创建dblink
在源端建到目标库的db link
SQL> create database link orcl connect to strmadmin identified by strmadmin using 'orcl';
测试：
SQL> select * from global_name@orcl;
GLOBAL_NAME
----------------------------------------
ORCL
在目端建到源库的dblink
SQL> conn strmadmin/strmadmin
已连接。
SQL> create database link ora11204 connect to strmadmin identified by strmadmin using 'ora11204';
SQL> select * from global_name@'ora11204';
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL
SQL>


主SOURCE stream队列：
begin 
dbms_streams_adm.set_up_queue( 
queue_table => 'ORA11204_queue_table', 
queue_name => 'scott_queue'); 
end; 
/
删除队列
SQL> execdbms_streams_adm.remove_queue(queue_name => 'streams_queue',cascade =>true,drop_unused_queue_table => true);


主source capture捕获进程：
begin 
dbms_streams_adm.add_table_rules( 
table_name => 'scott.dept',
streams_type => 'capture', 
streams_name => 'capture_scott', 
queue_name => 'strmadmin.scott_queue', 
include_dml => true, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => null, 
inclusion_rule => true); 
end; 
/
SQL> select capture_name,queue_name,start_scn,status,capture_type, ERROR_MESSAGE from dba_capture;
SQL> select STREAMS_NAME,STREAMS_TYPE,TABLE_OWNER,TABLE_NAME,RULE_TYPE,DML_CONDITION,SOURCE_DATABASE,RULE_NAME from DBA_STREAMS_table_RULES where STREAMS_NAME='CAPTURE_SIP';
--capture管理命令
SQL> select capture_name,statusfrom dba_capture;
SQL> exec dbms_capture_adm.start_capture( 'tab_cap');
SQL> exec dbms_capture_adm.stop_capture( 'tab_cap');
SQL> exec dbms_capture_adm.drop_capture(capture_name =>'tab_cap',drop_unused_rule_sets => true);
   
主source propagation传播进程：
 
begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'scott.dept',
streams_name => 'propagation_scott',
source_queue_name => 'strmadmin.scott_queue',
destination_queue_name => 'strmadmin.scott_queue_target@ORCL',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false, 
source_database => 'ORA11204',
inclusion_rule => true,
queue_to_queue => true);
end;
/


select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status from dba_propagation;
--propagation管理命令
SQL> select propagation_name,statusfrom dba_propagation;
SQL> exec dbms_propagation_adm.start_propagation(propagation_name => 'tab_pg');
SQL> exec dbms_propagation_adm.stop_propagation(propagation_name => 'tab_pg');
SQL> exec dbms_propagation_adm.drop_propagation(propagation_name => 'tab_pg',drop_unused_rule_sets => true);


从slave receive 接收队列：
begin 
dbms_streams_adm.set_up_queue( 
queue_table => 'ORCL_queue_table', 
queue_name => 'scott_queue_target'); 
end; 
/


从slave APPLY进程：
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name=>'scott.dept',
streams_type=>'apply',
streams_name=>'apply_scott',
queue_name=>'strmadmin.scott_queue_target',
include_dml=>true,
include_ddl=>true,
include_tagged_lcr=>false,
source_database=>'ORA11204',
inclusion_rule=>true);
end;
/


SQL> select APPLY_NAME, QUEUE_NAME, QUEUE_OWNER,  APPLY_USER, APPLY_DATABASE_LINK,DDL_HANDLER, STATUS, ERROR_MESSAGE from dba_apply;


SQL> select APPLY_NAME,LOCAL_TRANSACTION_ID,SOURCE_COMMIT_SCN,MESSAGE_NUMBER,ERROR_MESSAGE from dba_apply_error
--apply管理命令
SQL> select apply_name,statusfrom dba_apply;
SQL> exec dbms_apply_adm.start_apply(apply_name => 'tab_app');
SQL> exec dbms_apply_adm.stop_apply(apply_name => 'tab_app');
SQL> exec dbms_apply_adm.drop_apply(apply_name =>'tab_app',drop_unused_rule_sets => true);


实例化数据
SQL> conn strmadmin/strmadmin@ora11204
Connected.
SQL> set serveroutput on
  DECLARE
   iscn NUMBER;
   BEGIN
           iscn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
           DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: ' || iscn);
   END;
   /
Instantiation SCN is:12919865 –设置set serveroutput on才会返回到屏幕


PL/SQL procedure successfully completed.
SQL> connect strmadmin/strmadmin@orcl
Connected.
 
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
       source_object_name=> 'scott.dept',
       source_database_name => 'ORA11204',
       instantiation_scn => 12919865);
END;
/
 
 
主source 
修改propagation休眠时间为0，表示实时传播LCR
BEGIN
dbms_aqadm.alter_propagation_schedule( 
       queue_name=> 'scott_queue', 
       destination=> 'ORCL', 
       destination_queue => 'strmadmin.scott_queue_target',  
       latency=> 0); 
end; 
/ 






从slave start：
begin 
dbms_apply_adm.start_apply( 
apply_name => 'apply_scott'); 
end; 
/
主source start：
begin 
dbms_capture_adm.start_capture( 
capture_name => 'capture_scott'); 
end; 
/






--添加表
begin 
dbms_streams_adm.add_table_rules( 
table_name => 'scott.emp',
streams_type => 'capture', 
streams_name => 'capture_scott', 
queue_name => 'strmadmin.scott_queue', 
include_dml => true, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => null, 
inclusion_rule => true); 
end; 
/


begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'scott.emp',
streams_name => 'propagation_scott',
source_queue_name => 'strmadmin.scott_queue',
destination_queue_name => 'strmadmin.scott_queue_target@ORCL',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false, 
source_database => 'ORA11204',
inclusion_rule => true,
queue_to_queue => true);
end;
/


begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name=>'scott.emp',
streams_type=>'apply',
streams_name=>'apply_scott',
queue_name=>'strmadmin.scott_queue_target',
include_dml=>true,
include_ddl=>true,
include_tagged_lcr=>false,
source_database=>'ORA11204',
inclusion_rule=>true);
end;
/


SQL> conn strmadmin/strmadmin@ora11204
Connected.
SQL> set serveroutput on
  DECLARE
   iscn NUMBER;
   BEGIN
           iscn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
           DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: ' || iscn);
   END;
   /
Instantiation SCN is:12919865 –设置set serveroutput on才会返回到屏幕


PL/SQL procedure successfully completed.
SQL> connect strmadmin/strmadmin@orcl
Connected.
 
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
       source_object_name=> 'scott.emp',
       source_database_name => 'ORA11204',
       instantiation_scn => 12921030);
END;
/




 exec dbms_apply_adm.start_apply(apply_name => 'apply_scott');  
 
 
 
--添加表（支持ddl不支持dml）
begin 
dbms_streams_adm.add_table_rules( 
table_name => 'scott.BONUS',
streams_type => 'capture', 
streams_name => 'capture_scott', 
queue_name => 'strmadmin.scott_queue', 
include_dml => false, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => null, 
inclusion_rule => true); 
end; 
/


begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'scott.BONUS',
streams_name => 'propagation_scott',
source_queue_name => 'strmadmin.scott_queue',
destination_queue_name => 'strmadmin.scott_queue_target@ORCL',
include_dml => false,
include_ddl => true,
include_tagged_lcr => false, 
source_database => 'ORA11204',
inclusion_rule => true,
queue_to_queue => true);
end;
/


begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name=>'scott.BONUS',
streams_type=>'apply',
streams_name=>'apply_scott',
queue_name=>'strmadmin.scott_queue_target',
include_dml=>false,
include_ddl=>true,
include_tagged_lcr=>false,
source_database=>'ORA11204',
inclusion_rule=>true);
end;
/


SQL> conn strmadmin/strmadmin@ora11204
Connected.
SQL> set serveroutput on
  DECLARE
   iscn NUMBER;
   BEGIN
           iscn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
           DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: ' || iscn);
   END;
   /
Instantiation SCN is:12919865 –设置set serveroutput on才会返回到屏幕


PL/SQL procedure successfully completed.
SQL> connect strmadmin/strmadmin@orcl
Connected.
 
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
       source_object_name=> 'scott.BONUS',
       source_database_name => 'ORA11204',
       instantiation_scn => 12921030);
END;
/




 exec dbms_apply_adm.start_apply(apply_name => 'apply_scott');