源库和目标库都为归档
源库:
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 /oradata/arch
最早的联机日志序列 49
下一个存档日志序列 51
当前日志序列 51
目标库:
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 E:\app\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 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;
SQL> grant connect,resource 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> 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 => 'ORA11204_queue');
end;
/
主source capture捕获进程:
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'STREAM',
streams_type => 'capture',
streams_name => 'capture_ORA11204',
queue_name => 'strmadmin.ORA11204_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;
主source propagation传播进程:
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'STREAM',
streams_name => 'ORA11204_tab_ORCL',
source_queue_name => 'strmadmin.ORA11204_queue',
destination_queue_name => 'strmadmin.ORCL_queue@ORCL',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ORA11204',
inclusion_rule => true);
end;
/
SQL> select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status,ERROR_MESSAGE from dba_propagation;
从slave receive 接收队列:
begin
dbms_streams_adm.set_up_queue(
queue_table => 'ORCL_queue_table',
queue_name => 'ORCL_queue');
end;
/
从slave APPLY进程:
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'STREAM',
streams_type => 'apply',
streams_name => 'apply_ORCL',
queue_name => 'strmadmin.ORCL_queue',
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> conn strmadmin/strmadmin@ora11204
Connected.
SQL> setserveroutput on
DECLARE
iscn NUMBER;
BEGIN
iscn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: ' || iscn);
END;
/
Instantiation SCN is:1750929 –设置set serveroutput on才会返回到屏幕
PL/SQL procedure successfully completed.
SQL> connectstrmadmin/strmadmin@orcl
Connected.
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name=> 'STREAM',
source_database_name=> 'ORA11204',
instantiation_scn =>12800919);
END;
/
Enter value for iscn:1750929
old 5: instantiation_scn =>&iscn);
new 5: instantiation_scn =>1750929);
BEGIN
dbms_aqadm.alter_propagation_schedule(
queue_name=> 'ORA11204_queue',
destination=> 'ORCL',
destination_queue => 'strmadmin.ORCL_queue',
latency=> 0);
end;
/
从slave start:
begin
dbms_apply_adm.start_apply(
apply_name => 'apply_ORCL');
end;
/
主source start:
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_ORA11204');
end;
/
源库:
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 /oradata/arch
最早的联机日志序列 49
下一个存档日志序列 51
当前日志序列 51
目标库:
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 E:\app\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 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;
SQL> grant connect,resource 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> 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 => 'ORA11204_queue');
end;
/
主source capture捕获进程:
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'STREAM',
streams_type => 'capture',
streams_name => 'capture_ORA11204',
queue_name => 'strmadmin.ORA11204_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;
主source propagation传播进程:
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'STREAM',
streams_name => 'ORA11204_tab_ORCL',
source_queue_name => 'strmadmin.ORA11204_queue',
destination_queue_name => 'strmadmin.ORCL_queue@ORCL',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ORA11204',
inclusion_rule => true);
end;
/
SQL> select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status,ERROR_MESSAGE from dba_propagation;
从slave receive 接收队列:
begin
dbms_streams_adm.set_up_queue(
queue_table => 'ORCL_queue_table',
queue_name => 'ORCL_queue');
end;
/
从slave APPLY进程:
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'STREAM',
streams_type => 'apply',
streams_name => 'apply_ORCL',
queue_name => 'strmadmin.ORCL_queue',
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> conn strmadmin/strmadmin@ora11204
Connected.
SQL> setserveroutput on
DECLARE
iscn NUMBER;
BEGIN
iscn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: ' || iscn);
END;
/
Instantiation SCN is:1750929 –设置set serveroutput on才会返回到屏幕
PL/SQL procedure successfully completed.
SQL> connectstrmadmin/strmadmin@orcl
Connected.
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name=> 'STREAM',
source_database_name=> 'ORA11204',
instantiation_scn =>12800919);
END;
/
Enter value for iscn:1750929
old 5: instantiation_scn =>&iscn);
new 5: instantiation_scn =>1750929);
BEGIN
dbms_aqadm.alter_propagation_schedule(
queue_name=> 'ORA11204_queue',
destination=> 'ORCL',
destination_queue => 'strmadmin.ORCL_queue',
latency=> 0);
end;
/
从slave start:
begin
dbms_apply_adm.start_apply(
apply_name => 'apply_ORCL');
end;
/
主source start:
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_ORA11204');
end;
/