今天测试了一下
stream之schema复制,如下:[@more@]
source database是linux oracle10g
target database是 window oracle11g
1,配置前准备 global_names参数必须等于true
数据库必须为archive模式
查询global name的方法
2,source database上执行SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORACLE10G.COM.CN
SQL> create tablespace stream_tbs datafile '/u01/oradata/oracle10/stream_tbs.dbf' size 100m;
Tablespace created.
SQL> create user strmadmin identified by strmadmin default tablespace stream_tbs;
User created.
SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;
Grant succeeded.
SQL> begintarget database上执行
2 dbms_streams_auth.grant_admin_privilege(
3 grantee => 'strmadmin',
4 grant_privileges => true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> create tablespace stream_tbs datafile 'd:apporadataorclstream_tbs.dbf' size 100m;
Tablespace created.
SQL> create user strmadmin identified by strmadmin default tablespace stream_tbs;
User created.
SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;
Grant succeeded.
SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee => 'strmadmin',
4 grant_privileges => true);
5 end;
6 /
PL/SQL procedure successfully completed.
3,在source database 和target database 中配置tnsname.ora和listener.ora
4,创建database link
source database (database link名字必须和global_name一样)
target database上SQL> conn strmadmin/strmadmin
Connected.
SQL> create database link test.com.cn connect to strmadmin identified by "strmadmin" using 'test';
Database link created.
5.创建流队列SQL> conn strmadmin/strmadmin
Connected.
SQL> create database link oracle10g.com.cn connect to strmadmin identified by "strmadmin" using 'oracle10g';
Database link created.
source database
SQL> show usertarget database
USER is "STRMADMIN"
SQL> begin
2 dbms_streams_adm.set_up_queue(
3 queue_table=> 'source_queue_table',
4 queue_name=> 'source_queue'
5 );
6 end;
7 /
PL/SQL procedure successfully completed
SQL> show user
USER is "STRMADMIN"
SQL> begin
2 dbms_streams_adm.set_up_queue(
3 queue_table=> 'target_queue_table',
4 queue_name=> 'target_queue'
5 );
6 end;
7 /
PL/SQL procedure successfully completed.
6,source database 中创建捕获进程
7,source database中创建传播进程SQL> show user
USER is "STRMADMIN"SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name => 'scott',
4 streams_type=> 'capture',
5 streams_name=> 'capture_stream',
6 queue_name=> 'strmadmin.source_queue',
7 include_dml=> true,
8 include_ddl=> true,
9 inclusion_rule => true);
10 end;
11 /
SQL> show user
USER is "STRMADMIN"SQL> begin
2 dbms_streams_adm.add_schema_propagation_rules(
3 schema_name=> 'scott',
4 streams_name=> 'source_to_target',
5 source_queue_name=> 'strmadmin.source_queue',
6 destination_queue_name=> 'strmadmin.target_queue@test.com.cn',
7 include_ddl=> true,
8 include_dml=> true,
9 inclusion_rule => true,
10 queue_to_queue=>true,
11 source_database=> 'oracle10g.com.cn'
12 );
13 end;
14 /
PL/SQL procedure successfully completed.
8,实例化复制数据库
在target端执行
SQL> DECLARE
2 iscn NUMBER;
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@test.com.cn(
6 source_object_name => 'scott',
7 source_database_name => 'oracle10g.com.cn',
8 instantiation_scn => iscn);
9 END;
10 /
PL/SQL procedure successfully completed.
9.target database创建应用进程
SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name => 'scott',
4 streams_type=> 'apply',
5 streams_name=> 'apply_stream',
6 queue_name=> 'strmadmin.target_queue',
7 include_dml=> true,
8 include_ddl=> true,
9 inclusion_rule => true,
10 source_database=> 'oracle10g.com.cn');
11 end;
12
13 /
PL/SQL procedure successfully completed.
10,启动stream
source database启动capture进程
target database启动apply进程SQL> begin
2 dbms_capture_adm.start_capture(capture_name=>'capture_stream' );
3 end;
4 /
PL/SQL procedure successfully completed.停止capture 进程SQL> begin
2 dbms_capture_adm.stop_capture(capture_name=>'capture_stream' );
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_apply_adm.start_apply(apply_name=> 'apply_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
11,清除所有配置停止apply进程SQL> begin
2 dbms_apply_adm.stop_apply(apply_name=> 'apply_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration();
PL/SQL procedure successfully completed.
测试脚本================================================================================begindbms_streams_adm.set_up_queue(
queue_table=> 'source_queue_table',
queue_name=> 'source_queue'
);
end;
begin
dbms_streams_adm.set_up_queue(
queue_table=> 'target_queue_table',
queue_name=> 'target_queue'
);
end;
===========================
source database:
exec dbms_streams_adm.set_up_queue();
target database:
exec dbms_streams_adm.set_up_queue();
grant connect,resource,dba,aq_administrator_role to strmadmin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;------------------------------------------------------------------------------------------------------------------------------begin
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type=> 'capture',
streams_name=> 'capture_stream',
queue_name=> 'strmadmin.streams_queue',
include_dml=> true,
include_ddl=> true,
inclusion_rule => true);
end;
===========================
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type=> 'capture',
streams_name=> 'capture_stream',
queue_name=> 'strmadmin.source_queue',
include_dml=> true,
include_ddl=> true,
inclusion_rule => true);
end;------------------------------------------------------------------------------------------------------------------------------begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name=> 'scott',
streams_name=> 'source_to_target',
source_queue_name=> 'strmadmin.streams_queue',
destination_queue_name=> 'strmadmin.streams_queue@test.com.cn',
include_ddl=> true,
include_dml=> true,
inclusion_rule => true,
queue_to_queue=>true,
source_database=> 'oracle10g.com.cn'
);
end;
=============================
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name=> 'scott',
streams_name=> 'source_to_target',
source_queue_name=> 'strmadmin.source_queue',
destination_queue_name=> 'strmadmin.target_queue@test.com.cn',
include_ddl=> true,
include_dml=> true,
inclusion_rule => true,
queue_to_queue=>true,
source_database=> 'oracle10g.com.cn'
);
end;------------------------------------------------------------------------------------------------------------------------------connect strmadmin/strmadmin@oracle10g
set serveroutput on
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
connect strmadmin/strmadmin
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name=> 'scott',
source_database_name => 'oracle10g',
instantiation_scn => &iscn);
END;
===========================================
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@test.com.cn(
source_object_name => 'scott',
source_database_name => 'oracle10g.com.cn',
instantiation_scn => iscn);
END;------------------------------------------------------------------------------------------------------------------------------begin
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type=> 'apply',
streams_name=> 'apply_stream',
queue_name=> 'strmadmin.target_queue',
include_dml=> true,
include_ddl=> true,
inclusion_rule => true,
source_database=> 'oracle10g.com.cn');
end;------------------------------------------------------------------------------------------------------------------------------begin
dbms_capture_adm.start_capture(capture_name=>'capture_stream' );
end;
begin
dbms_capture_adm.stop_capture(capture_name=>'capture_stream' );
end;
======================================
begin
dbms_apply_adm.start_apply(apply_name=> 'apply_stream');
end;
begin
dbms_apply_adm.stop_apply(apply_name=> 'apply_stream');
end;
begin
dbms_apply_adm.stop_apply(apply_name=> 'target_apply_stream');
end;
exec DBMS_STREAMS_ADM.remove_streams_configuration();
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24237320/viewspace-1060147/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
网络上的等待事件
请登录后发表评论
登录
全部评论
最新文章
- [Linux7] HA Package Yum源配置
- [ASM] ORA-15032 ORA-15260
- [RAC] Oracle11g RAC禁止DB自动启动
- [Oracle] Device Checks for ASM - This is a pre-check........
- [Linux7] Centos7 multipath 盘权限绑定.
- [Linux7] zabbix 3.2.11安装
- [Oracle] Error in invoking target 'agent nmhs' of makefile
- [Linux] 查看环境变量
- [HPUX] HP Unix server vg自动激活
- [HPUX] UX:vxfs mount: ERROR: V-3-21264
