oracle stream之schema级复制

最新推荐文章于 2022-09-14 12:16:31 发布

cizhiyou2391

最新推荐文章于 2022-09-14 12:16:31 发布

阅读量137

点赞数

文章标签：数据库

今天测试了一下 stream之schema复制,如下:[@more@]

source database是linux oracle10g

target database是 window oracle11g

1,配置前准备

global_names参数必须等于true

数据库必须为archive模式

查询global name的方法

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORACLE10G.COM.CN

2,source database上执行

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> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee => 'strmadmin',
4 grant_privileges => true);
5 end;
6 /

PL/SQL procedure successfully completed.

target database上执行

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一样)

SQL> conn strmadmin/strmadmin
Connected.
SQL> create database link test.com.cn connect to strmadmin identified by "strmadmin" using 'test';

Database link created.

target database上

SQL> conn strmadmin/strmadmin
Connected.
SQL> create database link oracle10g.com.cn connect to strmadmin identified by "strmadmin" using 'oracle10g';

Database link created.

5.创建流队列

source database

SQL> show user
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

target database

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 中创建捕获进程

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 /

7,source database中创建传播进程

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进程

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.

target database启动apply进程

SQL> begin
2 dbms_apply_adm.start_apply(apply_name=> 'apply_stream');
3 end;
4 /

PL/SQL procedure successfully completed.

停止apply进程

SQL> begin
2 dbms_apply_adm.stop_apply(apply_name=> 'apply_stream');
3 end;
4 /

PL/SQL procedure successfully completed.

11,清除所有配置

SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration();

PL/SQL procedure successfully completed.

测试脚本

================================================================================

begin

dbms_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();