源库和目标库都为归档
源库:
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');
源库:
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');