演示基于用户进行流复制案例-可以同步用户的DDL操作

长平狐 发布于 2013/09/17 15:12
阅读 234
收藏 0

环境如下:基于表及基于用户的实验都在此环境下操作。

环境如下:基于表及基于用户的实验都在此环境下操作。

一、配置初始化环境

1.修改初始化参数:

目标库上:--因是使用虚拟机克隆的,两个库数据名和实例名一样要修改db_unique_name和global_name
主库上也要做修改,像db_unique_name,global_name可以不修改。

SYS@bys2>alter system set db_unique_name=bys2 scope=spfile;
SYS@bys2>alter database rename global_name to bys2;
SYS@bys2>select * from global_name;
GLOBAL_NAME
-------------
BYS2
alter system set "_job_queue_interval"=1 scope=spfile;
alter system set aq_tm_processes=1;
alter system set streams_pool_size=100m scope=both;
alter database force logging;
BYS@bys2>select SUPPLEMENTAL_LOG_DATA_MIN,force_logging from v$database;
SUPPLEMENTAL_LOG FORCE_
---------------- ------
YES              YES
SYS@bys2>col db_unique_name for a20
SYS@bys2>select name,db_unique_name from v$database;
NAME               DB_UNIQUE_NAME
------------------ --------------------
BYS1               BYS2
##############################################################

2.配置双方监听,在两台主机上能够通过服务名互相访问

sqlplus bys/bys@bys1
sqlplus bys/bys@bys2
#############################################

3.创建用户和DBLINK---源和目标库都需要做

创建stream管理账户并赋予权限----这个操作需要SYSDBA用户创建数据链--要确认数据链可用才能做下一步

CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT DBA to strmadmin;
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('strmadmin');   -- 这一步需要SYSDBA授予

源库:

注意:如果db_domain值为空,并且global names设置为true的情况下,那么这里link关键词后面的这个“bys2”必须写目标端global_name的值,而using关键词后面的“bys2”表示的是连接目标端的SERVICE NET NAME

conn strmadmin/strmadmin
create database link bys2 connect to strmadmin identified by strmadmin using 'bys2';
STRMADMIN@bys1>select * from global_name@bys2;
GLOBAL_NAME
-----------------
BYS2
目标库:

conn strmadmin/strmadmin
create database link bys1 connect to strmadmin identified by strmadmin using 'bys1';
STRMADMIN@bys2>select * from global_name@bys1;
GLOBAL_NAME
---------------------
BYS1
到这一步可以关了虚拟机做个备份。

二、基于用户的复制案例

首先需要创建实验用的用户和表:

在source/target端创建测试用户test9
CREATE USER test9 IDENTIFIED BY test9 DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
grant dba to test9;
双方都需要创建一个test1表,用来做数据同步测试。
conn test9/test9
create table test1(aa number primary key);
create table test2(bb number primary key);
###############################3

1.在源上创建Source 队列

sqlplus strmadmin/strmadmin
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'SOURCE_QUEUE_TABLE',
queue_name  => 'SOURCE_QUEUE',
queue_user  => 'strmadmin');                  
END;                                   
/
查看状态:
select owner,queue_table,name from dba_queues where owner='STRMADMIN';
select owner,queue_table,object_type from dba_queue_tables where owner='STRMADMIN';
########################################################

2.在目标库上创建接收队列

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'TARGET_QUEUE_TABLE',
queue_name  => 'TARGET_QUEUE',
queue_user  => 'strmadmin');
END;                                   
/
查看状态:
select owner,queue_table,name from dba_queues where owner='STRMADMIN';
select owner,queue_table,object_type from dba_queue_tables where owner='STRMADMIN';
############################################################################

3.在源数据库上创建capture 进程

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name     => 'test9',
streams_type      => 'capture',
streams_name     => 'capture_stream',
queue_name       => 'strmadmin.SOURCE_QUEUE',
include_dml       => true,
include_ddl        => true,
source_database => 'bys1',
include_tagged_lcr => false,
inclusion_rule     => true);
END;
/
查看状态:
select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;
select * from  ALL_CAPTURE_PREPARED_SCHEMAS;
###########################################################################

4.在源库创建传播进程

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name                => 'test9',
streams_name               => 'source_to_target',
source_queue_name          => 'strmadmin.SOURCE_QUEUE',
destination_queue_name    => 'strmadmin.TARGET_QUEUE@bys2',
include_dml               => true,
include_ddl               => true,
source_database           => 'bys1',
inclusion_rule            => true,
queue_to_queue            => true);
END;
/
查看状态:
select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation;
#########################################################

5.在目标数据库bys2上创建Apply进程

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name      => 'test9',
streams_type    => 'apply',
streams_name    => 'target_apply_stream',
queue_name      => 'strmadmin.TARGET_QUEUE',
include_dml     => true,
include_ddl     => true,
include_tagged_lcr => false,
source_database => 'bys1',
inclusion_rule => true);
END;
/
查看状态
col queue_name for a20
col apply_name for a20
select apply_name,queue_name,status from dba_apply;
#########################################################################

6.通过设置SCN进行实例化

查询源库的SCN
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
设置为目标库互置用户的SCN:在instantiation_scn =>处指定上一步在源库查询到的SCN
同时要注意指定SOURCE_SCHEMA_NAME =>中的用户名和source_database_name =>表名以及数据库名。

BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name => 'test9',
source_database_name => 'bys1',
instantiation_scn => &iscn);
END;
/
输入源库查到的SCN即可。
############################################################################

7.在目标数据库上启动APPLY进程

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'target_apply_stream');
END;
/
## 停止APPLY进程语句:
begin
dbms_apply_adm.stop_apply(
apply_name => 'target_apply_stream');
end;

查看状态:
select apply_name,queue_name,status from dba_apply;
############################################################

8.在源库上启动capture

conn strmadmin/strmadmin

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_stream');
END;
/
### 停止Capture进程语句 :
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_stream');
end;
/
查看状态:
select capture_name,status,CAPTURED_SCN,APPLIED_SCN from dba_capture;
select capture_name,status from dba_capture;
#################################################################

9.数据同步测试:

在TEST9用户下的两个表分别插入数据,均可以同步到目标库。

源库操作1:DML-插入数据,可以实现同步

STRMADMIN@bys1>select capture_name,status from dba_capture;
CAPTURE_NAME                   STATUS
------------------------------ --------
CAPTURE_STREAM                 ENABLED
STRMADMIN@bys1>conn test9/test9
Connected.
TEST9@bys1>set time on
19:08:14 TEST9@bys1>select * from test1;
        AA
----------
        12
       111
19:08:20 TEST9@bys1>insert into test1 values(13);
1 row created.
19:08:32 TEST9@bys1>commit;
Commit complete.

目标库查询1:
STRMADMIN@bys2>select apply_name,queue_name,status from dba_apply;
APPLY_NAME
------------------------------------------------------------
QUEUE_NAME                                                  STATUS
------------------------------------------------------------ ----------------
TARGET_APPLY_STREAM
TARGET_QUEUE                                                ENABLED
STRMADMIN@bys2>conn test9/test9
Connected.
TEST9@bys2>set time on
19:08:06 TEST9@bys2>select * from test1;
        AA
----------
        12
       111
19:08:08 TEST9@bys2>select * from test1;
        AA
----------
        12
        13
       111

19:08:37 TEST9@bys2>


源库操作2:创建表的操作,可以实现同步

19:08:34 TEST9@bys1>create table test3(abc number primary key);

Table created.
19:09:05 TEST9@bys1>select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST1                          TABLE
TEST2                          TABLE
TEST3                          TABLE

目标库查询:

19:08:37 TEST9@bys2>select * from cat;

TABLE_NAME                                                  TABLE_TYPE
------------------------------------------------------------ ----------------------
TEST1                                                       TABLE
TEST2                                                       TABLE
TEST3                                                       TABLE
19:09:16 TEST9@bys2>


源库操作3: 删除表的操作,可以实现同步。

19:09:12 TEST9@bys1>drop table test1;
Table dropped.
19:10:19 TEST9@bys1>drop table test2;
Table dropped.
19:10:29 TEST9@bys1>drop table test3;
Table dropped.
19:10:36 TEST9@bys1>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$5cozUGdbfw/gQKjA0wEMNA==$0 TABLE
BIN$5cozUGdefw/gQKjA0wEMNA==$0 TABLE
BIN$5cozUGdhfw/gQKjA0wEMNA==$0 TABLE

目标库查询:

19:10:22 TEST9@bys2>select * from cat;

TABLE_NAME                                                  TABLE_TYPE
------------------------------------------------------------ ----------------------
BIN$5cozUGdbfw/gQKjA0wEMNA==$0                              TABLE
BIN$5cozUGdefw/gQKjA0wEMNA==$0                              TABLE
TEST3                                                       TABLE
19:10:56 TEST9@bys2>select * from cat;
TABLE_NAME                                                  TABLE_TYPE
------------------------------------------------------------ ----------------------
BIN$5cozUGdbfw/gQKjA0wEMNA==$0                              TABLE
BIN$5cozUGdefw/gQKjA0wEMNA==$0                              TABLE
BIN$5cozUGdhfw/gQKjA0wEMNA==$0                              TABLE

源库操作4:清空回收站的操作不能同步:--需要双方手动执行清空即可。

19:10:44 TEST9@bys1>purge recyclebin;
Recyclebin purged.
19:11:04 TEST9@bys1>

目标库查询:

19:10:58 TEST9@bys2>select * from cat;
TABLE_NAME                                                  TABLE_TYPE
------------------------------------------------------------ ----------------------
BIN$5cozUGdbfw/gQKjA0wEMNA==$0                              TABLE
BIN$5cozUGdefw/gQKjA0wEMNA==$0                              TABLE
BIN$5cozUGdhfw/gQKjA0wEMNA==$0                              TABLE
19:11:00 TEST9@bys2>purge recyclebin;
Recyclebin purged.

源库操作5:清空所有表后新建 表并插入数据,可同步

19:11:04 TEST9@bys1>select * from tab;
no rows selected
19:11:11 TEST9@bys1>create table test1(abc number primary key);
Table created.
19:11:33 TEST9@bys1>insert into test1 values(199);
1 row created.
19:11:53 TEST9@bys1>commit;
Commit complete.
19:11:54 TEST9@bys1>select * from test1;
       ABC
----------
       199

目标库查询:

19:11:34 TEST9@bys2>select * from cat;
TABLE_NAME                                                  TABLE_TYPE
------------------------------------------------------------ ----------------------
TEST1                                                       TABLE
19:12:05 TEST9@bys2>select * from test1;
       ABC
----------
       199


原文链接:http://blog.csdn.net/q947817003/article/details/11226317
加载中
返回顶部
顶部