Oracle流复制技术

Oracle流复制是结合日志挖掘、队列等技术，实现多数据库、异构、远程等环境下数据同步的一种实现方式。主要被用于灵活的复制和容灾解决方案。

Oracle流复制相比较其他数据库同步方式，如Dataguard、Advanced Replication，流复制拥有以下几点显著的优势：

1、灵活的复制策略：可以分别针对数据库、模式、表等不同级别设定复制策略，相比Dataguard必须整个数据库复制而言，可以节省相当的资源。

2、高可用性：在异构环境下（不同的操作系统），Dataguard无法使用，流复制可以充分利用现有的设备与技术。

3、对网络条件的轻度依赖：流复制的传播是经过logmnr挖掘并包装的逻辑变更记录（LCRs），相比Dataguard传送archived redo log、Advanced Replication的mview log与mview刷新的方式，流复制对网络的需求降低了很多。

4、实时性：由监控进程负责实时监控用户操作反应在log当中的记录并传递给目标数据库进行接收，然后转换为实际的操作同步目标数据库，并可根据实际情况调整同步的间隔。

5、对主数据库性能的低影响：相对于其他复制方式，流复制基于对log物理文件进行分析等动作完成，只占用极少部分资源，并且无论流复制执行成功与否，都不会影响到主库的正常使用。

流复制中，源库必须设置为归档模式，如果是双向复制，则源库和目标库都要置于归档模式。

一、搭建流复制环境

1、本地节点的流复制环境搭建

修改实例参数

alter system set global_names=true;

alter system set aq_tm_processes=1;

其它可能需要关注的几个参数

job_queue_processes：调度作业能够执行的进程数，它影响可分配的传播进程数。

open_links：远程数据库的最大连接数。

open_links_per_instance：每个实例的远程数据库的最大连接数。

parallel_max_servers：并行执行进程的最大数目，该参数需要支持流和其它程序的并行执行。

processes：连接到数据库的最大操作系统进程数。

sessions：数据库支持的最大会话数。

shared_pool_size：当采用非自动内存管理而又没有设置流池时，Oracle将共享池的10%用于流池。

streams_pool_size：流池大小，缓存流队列。

创建streams_tbs表空间

create tablespace streams_tbs datafile 'd:/oradata/mes/streams_tbs01.dbf' size 200m;

将logminer 的数据字典从system表空间转移到streams_tbs表空间，防止撑满system表空间

execute dbms_logmnr_d.set_tablespace('streams_tbs');

创建strmadmin用户并授权

create user strmadmin identified by strmadmin default tablespace streams_tbs quota unlimited on streams_tbs;

grant connect, resource, dba, aq_administrator_role to strmadmin;

begin

    dbms_streams_auth.grant_admin_privilege(grantee          => 'strmadmin',

                                            grant_privileges => true);

end;

/

在tnsnames.ora中添加服务名

mes_0 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ora10g-1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = mes)

    )

  )

创建指向主服务器端的数据库链接

create public database link dl_mes_0 connect to strmadmin identified by strmadmin using 'mes_0';

创建与数据库链接访问同名的global_name

第一个本地节点可命名为dl_mes_1，第二个本地节点可命令为dl_mes_2，以此类推

conn strmadmin/strmadmin

alter database rename global_name to dl_mes_1;

创建流队列

conn strmadmin/strmadmin

exec dbms_streams_adm.set_up_queue();

创建应用进程，这里创建了模式级别的应用

conn strmadmin/strmadmin

begin

    dbms_streams_adm.add_schema_rules(schema_name     => 'CMES',

                                      streams_type    => 'apply',

                                      streams_name    => 'apply_streams',

                                      queue_name      => 'strmadmin.streams_queue',

                                      include_dml     => true,

                                      include_ddl     => true,

                                      source_database => 'dl_mes_0',

                                      inclusion_rule  => true);

end;

/

2、主服务器端的流复制环境搭建

修改实例参数

alter system set global_names=true;

alter system set aq_tm_processes=1;

alter system set open_links=10 scope=spfile;

alter system set open_links_per_instance=10 scope=spfile;

创建streams_tbs表空间

create tablespace streams_tbs datafile 'd:/oradata/mes/streams_tbs01.dbf' size 200m;

将logminer的数据字典从系统表空间转移到streams_tbs表空间，防止撑满系统表空间

execute dbms_logmnr_d.set_tablespace('streams_tbs');

创建strmadmin用户并授权

create user strmadmin identified by strmadmin default tablespace streams_tbs quota unlimited on streams_tbs;

grant connect, resource, dba, aq_administrator_role to strmadmin;

begin

    dbms_streams_auth.grant_admin_privilege(grantee          => 'strmadmin',

                                            grant_privileges => true);

end;

/

在tnsnames.ora中添加指向各个本地节点的网络服务名

第一个本地节点可命名为mes_1，第二个本地节点可命令为mes_2，以此类推，各节点计算机名对应为oraxe11g-1、oraxe11g-2等

mes_1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oraxe11g-1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = mes)

    )

  )

创建数据库链接

对应各个本地节点的网络服务名来创建，如dl_mes_1对应mes_1，dl_mes_2对应mes_2

create public database link dl_mes_1 connect to system identified by mesHz2 using 'mes_1';

创建与数据库链接访问同名的global_name

conn strmadmin/strmadmin

alter database rename global_name to dl_mes_0;

创建流队列

conn strmadmin/strmadmin

exec dbms_streams_adm.set_up_queue();

创建捕获进程，这里创建了模式级别的捕获

begin

    dbms_streams_adm.add_schema_rules(schema_name    => 'CMES',

                                      streams_type   => 'capture',

                                      streams_name   => 'capture_streams',

                                      queue_name     => 'strmadmin.streams_queue',

                                      include_dml    => true,

                                      include_ddl    => true,

                                      inclusion_rule => true);

end;

/

创建传播进程，这里创建了模式级别的传播

conn strmadmin/strmadmin

begin

    dbms_streams_adm.add_schema_propagation_rules(schema_name            => 'CMES',

                                                  streams_name           => 'main_to_node1',

                                                  source_queue_name      => 'strmadmin.streams_queue',

                                                  destination_queue_name => 'strmadmin.streams_queue@dl_mes_1',

                                                  include_dml            => true,

                                                  include_ddl            => true,

                                                  source_database        => 'dl_mes_0',

                                                  inclusion_rule         => true,

                                                  queue_to_queue         => true);

end;

/

当需要创建多个传播进程向不同本地节点发布时，需要指定不同的stream_name和destination_queue_name，如以下创建指向第二个本地节点的传播进程

conn strmadmin/strmadmin

begin

    dbms_streams_adm.add_schema_propagation_rules(schema_name            => 'CMES',

                                                  streams_name           => 'main_to_node2',

                                                  source_queue_name      => 'strmadmin.streams_queue',

                                                  destination_queue_name => 'strmadmin.streams_queue@dl_mes_2',

                                                  include_dml            => true,

                                                  include_ddl            => true,

                                                  source_database        => 'dl_mes_0',

                                                  inclusion_rule         => true,

                                                  queue_to_queue         => true);

end;

/

3、实例化本地节点

根据具体业务，利用数据泵进行导入。这里具体业务是需要创建几个自己的表空间和用户，并从主服务器上直接导入模式数据到本地。

创建表空间

create tablespace cmes datafile 'd:/oradata/mes/cmes01.dbf' size 100m;

create tablespace rmes datafile 'd:/oradata/mes/rmes01.dbf' size 2g;

create tablespace indx datafile 'd:/oradata/mes/indx01.dbf' size 2g;

create tablespace hmes datafile 'd:/oradata/mes/hmes01.dbf' size 2g;

创建RMES、BOSCH、ABS用户并授权

create user rmes identified by rmes default tablespace rmes;

create user bosch identified by huizhong default tablespace rmes;

create user abs identified by huizhong default tablespace rmes;

grant connect,resource to rmes,bosch,abs;

导入主服务器端的CMES模式基础数据

$impdp strmadmin/strmadmin network_link=dl_mes_0 schemas=cmes

导入主服务器端的RMES、BOSCH、ABS模式元数据

$impdp strmadmin/strmadmin network_link=dl_mes_0 schemas=rmes,bosch,abs content=metadata_only

用SYS用户编译无效对象

conn / as sysdba

@?/rdbms/admin/utlrp

4、启动流复制进程

本地节点启动应用进程

exec dbms_apply_adm.start_apply('apply_streams');

主服务器端启动捕获进程

exec dbms_capture_adm.start_capture('capture_streams');

检查主服务器端警告日志，确认日志挖掘的启动

Fri Apr 14 16:47:04 2017

LOGMINER: Begin mining logfile for session 1 thread 1 sequence 40, E:/ARCHIVELOG/MES/ARC_78AE6A4D_1_941117583_40.LOG

Fri Apr 14 16:47:05 2017

LOGMINER: End mining logfile: E:/ARCHIVELOG/MES/ARC_78AE6A4D_1_941117583_40.LOG

Fri Apr 14 16:47:05 2017

LOGMINER: Begin mining logfile for session 1 thread 1 sequence 41, D:/ORADATA/MES/REDO02.LOG

Fri Apr 14 16:47:14 2017

LOGMINER: End mining logfile: D:/ORADATA/MES/REDO02.LOG

Fri Apr 14 16:47:14 2017

LOGMINER: Begin mining logfile for session 1 thread 1 sequence 42, D:/ORADATA/MES/REDO03.LOG

检查本地节点警告日志，确认应用进程的启动

Fri Apr 14 16:46:41 2017

Streams APPLY AP01 for APPLY_STREAMS started with pid=24, OS id=3144

Fri Apr 14 16:46:42 2017

Streams Apply Server for APPLY_STREAMS started AS02 with pid=31 OS id=1768

Fri Apr 14 16:46:42 2017

Streams Apply Server for APPLY_STREAMS started AS03 with pid=32 OS id=2484

Fri Apr 14 16:46:42 2017

Streams Apply Reader for APPLY_STREAMS started AS01 with pid=29 OS id=4040

Fri Apr 14 16:46:42 2017

Streams Apply Server for APPLY_STREAMS started AS04 with pid=35 OS id=756

Fri Apr 14 16:46:42 2017

Streams Apply Server for APPLY_STREAMS started AS05 with pid=36 OS id=740

5、流复制功能验证

分别重启本地节点数据库和主服务器数据库

shutdown immediate

startup

测试主服务器端数据库CMES模式的更新，是否能够自动同步到本地节点，包括所有DML和DDL操作。如未能同步，则查看主服务器和本地节点的警告日志信息，排查出错原因。可以在主服务器端和本地节点安装64位的PL/SQL Developer，便于测试和后续的操作。

-- 表数据更新

select * from cmes.c_emp_t;

update cmes.c_emp_t t set t.emp_password = '111111' where t.emp_no = 'TEST';

commit;

-- 增加表

create table cmes.c_emp1_t as select * from cmes.c_emp_t;

select * from cmes.c_emp1_t;

-- 修改表结构

alter table cmes.c_emp1_t add remark varchar2(20);

update cmes.c_emp1_t t set t.remark = 'test' where t.emp_no = 'TEST';

commit;

-- 增加索引

create index cmes.idx_emp1_remark on cmes.c_emp1_t(remark);

select table_name, index_name, index_type, status, tablespace_name from dba_indexes where owner='CMES' and table_name='C_EMP1_T';

-- 删除索引

drop index cmes.idx_emp1_remark;

select table_name, index_name, index_type, status, tablespace_name from dba_indexes where owner='CMES' and table_name='C_EMP1_T';

-- 删除表

drop table cmes.c_emp1_t purge;

select * from cmes.c_emp1_t;

-- 更新存储过程

-- 新增存储过程

create or replace procedure my_test(res out varchar2) as

begin

    res := 'OK';

end;

-- 删除存储过程

drop procedure my_test;

6、建立流复制心跳

为监视流复制的工作状态，在主服务器上创建心跳表

create table cmes.c_heartbeat_t(hb_name varchar2(20), hb_time varchar2(20)) tablespace cmes;

插入数据

insert into cmes.c_heartbeat_t values('dl_mes_0', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));

创建调度作业，设置为每分钟更新一次心跳时间

begin

    dbms_scheduler.create_job(job_name        => 'streams_hb',

                              job_type        => 'plsql_block',

                              job_action      => 'update cmes.c_heartbeat_t set hb_time = to_char(sysdate, ''yyyy-mm-dd hh24:mi:ss'') where hb_name = ''dl_mes_0'';',

                              start_date      => sysdate,

                              repeat_interval => 'freq = minutely; interval = 1',

                              enabled         => true,

                              auto_drop       => false);

end;

/

观察本地节点的心跳表数据是否按心跳时间同步更新。

二、针对表级别的配置说明

如果流复制定义在表级别，则几个规则的创建可采用如下形式。

源库创建表级别的传播规则

begin

    dbms_streams_adm.add_table_propagation_rules(table_name             => 'scott.emp',

                                                 streams_name           => 'source_to_target',

                                                 source_queue_name      => 'strmadmin.streams_queue',

                                                 destination_queue_name => 'strmadmin.streams_queue@dl_mes_arc',

                                                 include_dml            => true,

                                                 include_ddl            => true,

                                                 source_database        => 'dl_mes_pro',

                                                 inclusion_rule         => true,

                                                 queue_to_queue         => true);

end;

/

源库创建表级别的捕获规则

begin

    dbms_streams_adm.add_table_rules(table_name     => 'scott.emp',

                                     streams_type   => 'capture',

                                     streams_name   => 'capture_streams',

                                     queue_name     => 'strmadmin.streams_queue',

                                     include_dml    => true,

                                     include_ddl    => true,

                                     inclusion_rule => true);

end;

/

目标库创建表级别的应用规则

begin

    dbms_streams_adm.add_table_rules(table_name      => 'scott.emp',

                                     streams_type    => 'apply',

                                     streams_name    => 'apply_streams_emp',

                                     queue_name      => 'strmadmin.streams_queue',

                                     include_dml     => true,

                                     include_ddl     => true,

                                     source_database => 'dl_mes_pro',

                                     inclusion_rule  => true);

end;

/

目标库的实例化

在目标库上导入源库表

$impdp strmadmin/strmadmin network_link=dl_mes_pro schemas=scott include=table:"in('EMP')" table_exists_action=replace

三、流复制配置的删除

conn strmadmin/strmadmin

停止应用进程：

begin

  dbms_apply_adm.stop_apply(apply_name => 'apply_streams');

end;

/

删除应用进程

begin

  dbms_apply_adm.drop_apply(apply_name            => 'apply_streams',

                            drop_unused_rule_sets => true);

end;

/

停止捕获进程

begin

  dbms_capture_adm.stop_capture(capture_name => 'capture_streams',

                                force        => true);

end;

/

删除捕获进程

begin

  dbms_capture_adm.drop_capture(capture_name          => 'capture_streams',

                                drop_unused_rule_sets => true);

end;

/

停止传播进程

begin

  dbms_propagation_adm.stop_propagation(propagation_name => 'main_to_node1',

                                        force            => true);

end;

/

删除传播进程

begin

  dbms_propagation_adm.drop_propagation(propagation_name      => 'main_to_node1',

                                        drop_unused_rule_sets => true);

end;

/

删除源库和目标库的队列及队列表

conn strmadmin/strmadmin

begin

dbms_streams_adm.remove_queue(queue_name              => 'STREAMS_QUEUE',

                              cascade                 => true,

                              drop_unused_queue_table => true);

end;

/

删除流配置

exec dbms_streams_adm.remove_streams_configuration;

四、流复制的状态查询

查看创建的流队列和队列表

select owner, name, queue_table, queue_type from dba_queues where owner = 'STRMADMIN';

OWNER                          NAME                           QUEUE_TABLE                    QUEUE_TYPE

------------------------------ ------------------------------ ------------------------------ --------------------

STRMADMIN                      STREAMS_QUEUE                  STREAMS_QUEUE_TABLE            NORMAL_QUEUE

STRMADMIN                      AQ$_STREAMS_QUEUE_TABLE_E      STREAMS_QUEUE_TABLE            EXCEPTION_QUEUE

查看流队列表信息

select owner, queue_table, object_type from dba_queue_tables where owner = 'STRMADMIN';

OWNER                QUEUE_TABLE                    OBJECT_TYPE

-------------------- ------------------------------ ---------------

STRMADMIN            STREAMS_QUEUE_TABLE            SYS.ANYDATA

查看传播进程信息

col destination_dblink for a30

select propagation_name, source_queue_name, destination_queue_name, destination_dblink, status from dba_propagation;

PROPAGATION_NAME               SOURCE_QUEUE_NAME              DESTINATION_QUEUE_NAME         DESTINATION_DBLINK             STATUS

------------------------------ ------------------------------ ------------------------------ ------------------------------ --------

SOURCE_TO_TARGET               STREAMS_QUEUE                  STREAMS_QUEUE                  DL_MES_ARC                     ENABLED

查看捕获进程信息

select capture_name, queue_name, start_scn, status, capture_type from dba_capture;

CAPTURE_NAME                   QUEUE_NAME                      START_SCN STATUS   CAPTURE_TY

------------------------------ ------------------------------ ---------- -------- ----------

CAPTURE_STREAMS                STREAMS_QUEUE                      620367 DISABLED LOCAL

查看应用进程信息

select apply_name,queue_name,status from dba_apply;

APPLY_NAME                     QUEUE_NAME                     STATUS

------------------------------ ------------------------------ --------

APPLY_STREAMS_EMP              STREAMS_QUEUE                  DISABLED

五、其它问题

1、可以基于Database级别或Table级别启用追加日志（Supplemental Log）

alter database add supplemental log data;

在建立根据Schema粒度进行复制的Oracle Stream环境中，如果确认Schema下所有Table都有合理的主键（Primary Key），则不再需要启用追加日志。

2、如果等了很长时间数据还没有复制过来，仔细检查capture/propagation/apply各进程的状态是否有异常。并可尝试修改以下隐含参数并重启：

alter system set "_job_queue_interval"=1 scope=spfile;

3、可修改传播进程的休眠时间，如改为0，表示实时传播

begin

    dbms_aqadm.alter_propagation_schedule(queue_name        => 'streams_queue',

                                          destination       => 'dl_mes_1',

                                          destination_queue => 'streams_queue',

                                          latency           => 0);

end;

/

4、直接设置SCN的方式进行源库与目标库的同步

查看源库的SCN

select dbms_flashback.get_system_change_number() from dual;

设置目标库的SCN

begin

    dbms_apply_adm.set_schema_instantiatin_scn(source_schema_name   => 'CMES',

                                               source_database_name => 'MES',

                                               instantiation_scn    => '3363169');

end;

/