如何配置oracle流复制

最新推荐文章于 2024-09-18 17:56:19 发布

lhao77

最新推荐文章于 2024-09-18 17:56:19 发布

阅读量596

点赞数

文章标签： oracle instantiation database include schema table

本文链接：https://blog.csdn.net/lhao77/article/details/5897837

版权

前提条件

建好主库以及从库。设置为归档模式。

1.创建管理员用户以及表空间

create tablespace streams_tbs datafile 'D:/database/oradata/master/streams_tbs.dbf' size 500m REUSE autoextend off;

drop user strmadmin cascade;
create user strmadmin identified by strmadmin_123 default tablespace streams_tbs temporary tablespace temp;
grant connect,resource,dba,aq_administrator_role to strmadmin;
exec dbms_streams_auth.grant_admin_privilege(grantee => 'strmadmin',grant_privileges => true);

create tablespace streams_tbs datafile 'D:/oracle/product/10.2.0/oradata/slave/streams_tbs.dbf' size 500m REUSE autoextend off;

检查strmadmin（这部不是必做，用来检查下）
select name,supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui force_logging from v/$database;
select u.username, u.default_tablespace, d.file_name from dba_users u, dba_data_files d where u.username=upper('strmadmin') and d.tablespace_name=u.default_tablespace;

2.创建交换用户以及表空间

create tablespace reptable_tbs datafile 'D:/database/oradata/master/reptable_tbs.dbf' size 500m;

drop user reptable cascade;
create user reptable identified by reptable default tablespace reptable_tbs temporary tablespace temp;
grant connect,resource,dba,aq_administrator_role to reptable;
exec dbms_streams_auth.grant_admin_privilege(grantee => 'reptable',grant_privileges => true);

create tablespace reptable_tbs datafile 'D:/oracle/product/10.2.0/oradata/slave/reptable_tbs.dbf' size 500m;

3.创建数据库链接

conn strmadmin/strmadmin_123@master_10.175.172.194;
drop database link slave.zxin;
create database link slave.zxin connect to strmadmin identified by strmadmin_123 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.175.172.147)(PORT = 1521)))(CONNECT_DATA =(SID = slave)(SERVER = DEDICATED)))';

conn strmadmin/strmadmin_123@slave_10.175.172.147;
drop database link master.zxin;
create database link master.zxin connect to strmadmin identified by strmadmin_123 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.175.172.194)(PORT = 1521)))(CONNECT_DATA =(SID = master)(SERVER = DEDICATED)))';

通过下面来测试是否通过才行。

select * from global_name@master.zxin;

select * from global_name@slava.zxin;

这里推荐一篇好文

database link ORA-02019: 未找到远程数据库的连接说明

http://www.51testing.com/html/25/58025-83988.html

4.创建队列

主库
begin
dbms_streams_adm.set_up_queue(
queue_table=>'master_quu_tab_reptable',
queue_name=>'master_quu_reptable');
end;
/
从库
begin
dbms_streams_adm.set_up_queue(
queue_table=>'slave_quu_tab_reptable',
queue_name=>'slave_quu_reptable');
end;
/

检查一下
select owner,name,queue_table from dba_queues where owner=upper('strmadmin');
select owner,name,queue_table from dba_queues where owner=upper('strmadmin');

5.为主库创建捕获进程-传播进程
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'reptable',
streams_type => 'capture',
streams_name => 'capture_master_reptable',
queue_name => 'strmadmin.master_quu_reptable',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/

begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'reptable',
streams_name => 'prop_master_reptable',
source_queue_name => 'strmadmin.master_quu_reptable',
destination_queue_name => 'slave_quu_reptable@slave.zxin',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'master.zxin',
inclusion_rule => true);
end;
/

begin
dbms_aqadm.alter_propagation_schedule(
queue_name =>'master_quu_reptable',
destination => 'slave.zxin',
latency => 0);
end;
/

6.为从库创建应用进程
begin
dbms_streams_adm.add_schema_rules(
   schema_name => 'reptable',
   streams_type => 'apply',
   streams_name => 'apply_slave_reptable',
   queue_name=>'slave_quu_reptable',
   include_dml => true,
   include_ddl => true,
   include_tagged_lcr => false,
   source_database => 'master.zxin',
   inclusion_rule => true);
end;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_slave_reptable',
parameter => 'disable_on_error',
value => 'n');
END;
/

7.主库环境中实例化复制数据库
exp userid=reptable/reptable@master_10.175.172.194 file='d:/strmmaster.dmp' object_consistent=y rows=n
imp userid=system/sys@slave_10.175.172.147 file='d:/strmmaster.dmp' ignore=y commit=y log='d:/strmmaster.log' streams_instantiation=y fromuser=reptable touser=reptable

DECLARE
  v_scn NUMBER;
BEGIN
  v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@slave(
  SOURCE_SCHEMA_NAME => 'reptable',
  source_database_name => 'master.zxin',
  instantiation_scn => v_scn,
  RECURSIVE => true );
END;
/

启动从库
exec dbms_apply_adm.start_apply(apply_name=>'apply_slave_reptable');
启动主库
exec dbms_capture_adm.start_capture(capture_name=>'capture_master_reptable');

这样就可以开始测试下了。

9.remove流复制

从-停止应用进程
exec dbms_apply_adm.stop_apply(apply_name=>'apply_slave_reptable');
主-停止传播
exec dbms_propagation_adm.stop_propagation(propagation_name=>'prop_master_reptable');
主-停止捕获
exec dbms_capture_adm.stop_capture(capture_name=>'capture_master_reptable');
主
begin dbms_capture_adm.drop_capture(
capture_name => 'capture_master_reptable',
drop_unused_rule_sets => true);
end;
/

begin dbms_propagation_adm.drop_propagation(
propagation_name => 'prop_master_reptable',
drop_unused_rule_sets => true);
end;
/

从
begin dbms_apply_adm.drop_apply(
apply_name => 'apply_slave_reptable',
drop_unused_rule_sets => true);
end;
/
主
begin dbms_streams_adm.remove_queue(
queue_name => 'strmadmin.master_quu_reptable',
cascade => true,
drop_unused_queue_table => true);
end;
/
从
begin dbms_streams_adm.remove_queue(
queue_name => 'slave_quu_reptable',
cascade => true,
drop_unused_queue_table => true);
end;
/