Oracle10g Stream 单表复制(本地捕获)

最新推荐文章于 2021-04-14 15:53:30 发布

原创最新推荐文章于 2021-04-14 15:53:30 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#stream #oracle10g #数据库 #database #table #oracle

oracle 专栏收录该内容

30 篇文章

订阅专栏

本文详细介绍如何使用Oracle Stream技术实现两数据库之间的数据同步，包括环境搭建、配置步骤、测试验证及清理流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、Stream概述
Stream实际上是Oracle 的消息队列(也叫Oracle Advanced Queue)技术的一种扩展应用，Oracle 的消息队列是通过发布/订阅的方式来解决事件管理。从专业的角度来讲，在Stream 环境下，复制的起点数据库叫作Source Database(源数据库)，复制的终点数据库叫作Target Database（目标数据库）。在这两个数据库上都要创建一个队列，其中的Source Database上的是发送队列，而Target Database上的是接收队列。Source Database的所有操作都会被记录在日志中。等配好Stream环境后，在Source Database上会有一个捕获进程(Capture Process)捕获Redo日志(该进程利用Logminer技术从日志中提取DDL,DML语句，这些语句用一种特殊的格式表达，叫作逻辑变更记录(Logical Change Record, LCR). 一个LCR对应一个原子的行变更，因此源数据库上的一个DML语句，可能对应若干个LCR记录。这些LCR会保存到Sourece Database的本地发送队列中)，然后传播进程(Propagation Process)通过网络把这些记录传播到Target Database的接收队列中，在Target Database上会有一个应用进程(Apply Process)，这个进程从本地的接收队列中取出LCR记录，然后在本地应用，实现数据同步。Stream的这种特性比较适用于分布式的企业应用、数据仓库、高可用解决方案等等。
流复制(Stream replication)只是基于它的一个数据共享技术，也可以被用作一个可灵活定制的高可用性方案。它可以实现两个数据库之间数据库级，schema级，Table级的数据同步，并且这种同步可以是双向的。 Oracle Stream也是通过数据冗余来提高可用性，这一点和Data Guard 类似。Oracle 高级复制(Oracle advanced Replication) 和流复制(Stream Replication) 是从名称和功能上都是相似的两种技术。但前者是基于触发器的，后者是基于日志挖掘(Logminer)技术。

二、DataGuard 和Stream区别
DataGuard有两种类型：physical standby和logical standby。这两中standby 都有3个功能模块:日志传送,日志接收,日志恢复。两种standby在前两个模块中是一样的，都是通过LGWR或者ARCn进程发送日志，通过RFS进程接受日志。区别在第三个模块:Physical Standby 使用的是Media Recovery技术直接在数据块级别进行恢复，因此Physical Standby 能够做到两个数据库的完全同步，没有数据类型限制。 Logical Standby实际是通过Logminer技术，把日志中的记录还原成SQL语句，然后通过Apply Engine 执行这些语句实现数据同步，因此Logical Standby不能保证数据的完全一致。比如Logical Standby 不支持某些数据类型，这一点在选择Logical Standby时必须要考虑. Logical Standby 不支持的数据类型可以从DBA_LOGSTDBY_UNSUPPORTED是不里查看.
SQL>SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
Stream 使用的是Logical Standby 第三个模块,也就是在Source Database一端,Capture 进程利用Logminer 技术把日志内容还原成LCR,然后发送到Target Database,而在Target database 一端，也是通过Apply Engine 执行这些LCR。因此Stream在使用上也有些限制条件。这些可以从视图ALL/DBA_STREAMS_NEWLY_SUPPORTED,ALL/DBA_STREAMS_UNSUPPORTED 查看stream复制支持与不支持的数据类型。
SQL>SELECT table_name, reason FROM ALL_STREAMS_NEWLY_SUPPORTED;
SQL>SELECT table_name, reason FROM DBA_STREAMS_NEWLY_SUPPORTED;

SQL>SELECT table_name, reason FROM DBA_STREAMS_UNSUPPORTED;
SQL>SELECT table_name, reason FROM ALL_STREAMS_UNSUPPORTED;

下面列举的是他们主要区别：
Stream                                                                 DataGuard
主要目的是数据共享                                                 主要目的是灾难恢复和高可用性
可以多方向同步                                                       只能是单向，从Primary--> Standby
数据粒度可以是数据库，Schema，Table三个级别         只有数据库级别
支持异种平台的同步(Heterogeneous Platforms)         必须同种平台 (Homogeneous Platforms)
参与复制的每个数据库可以读写                                  只有Primary可以读写，Standby 只读
支持Oracle 和非Oracle 数据库间的同步                      只能是Oracle数据库间

三、前提条件
源数据库：
操作系统：redhat ES5.4
oracle版本：10.2.0.4(64位)
数据库名：orcl
ip地址：192.168.1.10
global_name:orcl

目标数据库：
操作系统：redhat ES5.4
oracle版本：10.2.0.4(64位)
数据库名：orclbak
ip地址：192.168.1.11
global_name:orclbak

同步orcl数据库中ydmm用户下的user表,同时orabak数据库中必须要有ydmm用户，最好保持密码相同
源数据库与目标数据库的字符集一定要相同，否则会在导入数据同步的时候报错!

四、设置源和目标数据库初始化参数及归档模式
sqlplus "/as sysdba";
SQL>create pfile='/home/oracle/pfile.ora' from spfile;
修改生成的pfile.ora内容如下
*.aq_tm_processes=2        #启用对队列消息的时间监视
*.job_queue_processes=10   #指定例程的 SNP 作业队列进程的数量
以上两个参数为修改的参数
下面的参数为增加的参数
*.global_names='true'      #建db_link的设置
*.undo_retention=3600      #控制事务被commit后，undo信息保留的时间
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'
*.streams_pool_size=209715200 #控制streams缓存空间的大小
*.utl_file_dir='*'            #设定Oracle只能读写utl_file_dir 指定目录
*.open_links=10               #调用db_link链路数设置
下面两个参数主要是用于归档
*.log_archive_dest_1='LOCATION=/home/oracle/archivelog'
*.log_archive_format='%t_%s_%r.dbf'

注意streams_pool_size一定要够大，因为如果启用了SGA_TARGET，ORACLE可能分配很少内存给stream,这样会导致大量信息被spill到磁盘,导致查询DBA_APPLY,DBA_CAPTURE,DBA_PROPGATION全部状态ENABLED但就是没有数据被同步。

SQL>shutdown immediate
SQL>startup mount pfile='/home/oracle/pfile.ora'
SQL>create spfile from pfile='/home/oracle/pfile.ora'
SQL>alter database archivelog;
SQL>archive log list
SQL>shutdown immediate
SQL>startup

五、创建stream用户相关环境
源数据库orcl上的操作
CREATE TABLESPACE stream DATAFILE '/u01/app/oracle/oradata/orcl/stream01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; #源数据库增加stream表空间
execute dbms_logmnr_d.set_tablespace('stream'); #将logminer的数据字典从system表空间转移到新建的表空间，防止撑满system表空间
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE stream QUOTA UNLIMITED ON stream;#创建stream用户
GRANT DBA to strmadmin; #10g要求dba角色以简化配置
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('strmadmin'); # 赋予流管理特权
目标数据库orclbak上的操作
CREATE TABLESPACE stream DATAFILE '/home/oracle/oradata/orclbak/stream01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;从数据库增加stream表空间
execute dbms_logmnr_d.set_tablespace('stream'); #将logminer的数据字典从system表空间转移到新建的表空间，防止撑满system表空间
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE stream QUOTA UNLIMITED ON stream;#创建stream用户
GRANT DBA to strmadmin; #10g要求dba角色以简化配置
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('strmadmin'); # 赋予流管理特权

六、配置tnsnames.Ora
在源数据库上的tnsnames.Ora增加如下连接信息
orclbak =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclbak)
    )
)

在目标数据库上的tnsnames.Ora增加如下连接信息
orcl =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
    )
)

七、创建DB_Link
在源端建到目标库的db_link
先在目标数据库确定global_name
SQL>select * from global_name；
GLOBAL_NAME
-------------------
orclbak.REGRESS.RDBMS.DEV.US.ORACLE.COM
注意：这时我们发现目标数据库的global_name后面带有默认的域名REGRESS.RDBMS.DEV.US.ORACLE.COM，当用命令alter database rename global_name to orclbak;在目标数据库上进行域名修改时，
修改的global_name(oradb)还是会带上默认的域名REGRESS.RDBMS.DEV.US.ORACLE.COM，在参数global_name=true情况下我们不需要带默认的域名，则执行下面语句进行解决
SQL>update props$ set value$ = 'orclbak' where name = 'GLOBAL_DB_NAME';
SQL>commit;
然后再在源数据库上创建 db_link
SQL> conn strmadmin/strmadmin;
SQL> create database link orclbak connect to strmadmin identified by strmadmin using 'orclbak';
测试：
SQL> select * from global_name@orclbak;

GLOBAL_NAME
-------------------
orclbak

在目的端建到源库的db_link
同样也要先在源库上确定global_name
SQL>select * from global_name；
GLOBAL_NAME
-------------------
orcl.REGRESS.RDBMS.DEV.US.ORACLE.COM
修改global_name
SQL>update props$ set value$ = 'orclbak' where name = 'GLOBAL_DB_NAME';
SQL>commit;
然后再在目标数据库上创建 db_link
SQL> conn strmadmin/strmadmin;
SQL> create database link orcl connect to strmadmin identified by strmadmin using 'orcl';
测试：
SQL> select * from global_name@orcl;
GLOBAL_NAME
-------------------
orcl
这样两边的db_link都创建好了。

八、创建流队列
在源数据库上生成队列（发送队列）
SQL>conn strmadmin/strmadmin;
SQL>exec dbms_streams_adm.set_up_queue(); #这样创建是oracle自己生成的队列名
或者
SQL>BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
         queue_table => 'ORCL_QUEUE_TABLE',
         queue_name => 'ORCLE_QUEUE',
         queue_user => 'strmadmin');
END;
/
#这样创建的是自己指定的队列名
注：
SQL>select owner,queue_table,name from dba_queues where owner='STRMADMIN'; #查询生成的队列名
SQL>exec dbms_streams_adm.remove_queue(queue_name => 'ORCL_QUEUE',cascade => true,drop_unused_queue_table => true); #移除生成的队列，不需要的时候执行

在目标数据库上生成队列（接收队列）
SQL>conn strmadmin/strmadmin;
SQL>exec dbms_streams_adm.set_up_queue(); #这样创建是oracle自己生成的队列名
或者
SQL>BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
         queue_table => 'ORCLBAK_QUEUE_TABLE',
         queue_name => 'ORCLEBAK_QUEUE',
         queue_user => 'strmadmin');
END;
/
#这样创建的是自己指定的队列名

注：
SQL>select owner,queue_table,name from dba_queues where owner='STRMADMIN'; #查询生成的队列名
SQL>exec dbms_streams_adm.remove_queue(queue_name => 'ORCLBAK_QUEUE',cascade => true,drop_unused_queue_table => true); #移除生成的队列，不需要的时候执行

九、在源数据库上创建捕获进程及传播进程
源数据库上创建捕获进程
SQL>conn strmadmin/strmadmin;
SQL>begin
dbms_streams_adm.add_table_rules(
table_name => 'ydmm.user',
streams_type => 'capture',
streams_name => 'capture_streams',
queue_name => 'strmadmin.ORCL_QUEUE',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
/

注：
SQL>select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture; #查询生成的捕获进程名及相关信息
SQL>exec dbms_capture_adm.stop_capture("capture_streams"); #停止捕获进程
SQL>exec dbms_capture_adm.drop_capture("capture_streams"); #移除生成的捕获进程

源数据库上创建传播进程
SQL>conn strmadmin/strmadmin;
SQL>begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'ydmm.user',
streams_name => 'orcl_to_orclbak',
source_queue_name => 'strmadmin.orcl_QUEUE',
destination_queue_name => 'strmadmin.orclbak_QUEUE@orclbak',
include_dml => true,
include_ddl => true,
source_database => 'orcl',
inclusion_rule => true,
queue_to_queue => true);
end;
/

注：
SQL>select PROPAGATION_NAME,STATUS from dba_propagation;                      #查询生成的传播进程名及相关信息
SQL>exec dbms_propagation_adm.stop_propagation("orcl_to_orclbak");            #停止传播进程
SQL>exec dbms_propagation_adm.drop_propagation("orcl_to_orclbak");            #移除生成的传播进程

十、在目标数据库创建应用进程：
SQL>conn strmadmin/strmadmin;
SQL>begin
dbms_streams_adm.add_table_rules(
table_name => 'ydmm.user',
streams_type => 'apply',
streams_name => 'apply_streams',
queue_name => 'strmadmin.ORCLBAK_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'orcl',
inclusion_rule => true);
end;
/

注：
SQL>select apply_name,queue_name,status from dba_apply;                      #查询生成的应用进程名及相关信息
SQL>exec dbms_apply_adm.stop_apply("apply_streams");                         #停止应用进程
SQL>exec dbms_apply_adm.drop_apply("apply_streams");                         #移除生成的应用进程

十一、将源数据中的表ydmm.user导入到目标数据库
可以用exp/imp,rman等方式进行数据导入，因为已经有db_link了，所以我直接进行生成数据
在目标数据库执行如下操作：
SQL>conn strmadmin/strmadmin
SQL>create table ydmm.user as select * from user@orcl;

十二、在目标数据库上设置应用进程开始执行的SCN
SQL>conn strmadmin/strmadmin;
SQL>DECLARE
source_scn NUMBER;
BEGIN
source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@ORCLBAK(
source_object_name=> 'ydmm.user',
source_database_name=> 'orcl',
instantiation_scn=> source_scn);
END;
/

十三、在目标数据库上启动应用进程
SQL>conn strmadmin/strmadmin;
SQL>exec dbms_apply_adm.start_apply('APPLY_STREAMS');
注：
SQL>select apply_name,status from dba_apply; #查询应用进程状态信息
SQL>select error_message from DBA_APPLY_ERROR; #查询应用进程错误信息

十四、在源数据库启用捕获进程
SQL>conn strmadmin/strmadmin
SQL>exec dbms_capture_adm.start_capture('capture_streams');
SQL>select capture_name,status from dba_capture; #查询捕获进程状态信息

十五、测试验证
在源数据库上update,delete,insert一条语句，然后查看目标数据库是否同步，在此，我紧做一次update语句，其他的请自行测试
在源数据库上的操作：
SQL>conn ydmm/ydmm
SQL>select name from user where user_id=10;
name
-------------------
aobama

SQL>update user set name='wahaha' where user_id=10;
SQL>commit;
SQL>select name from user where user_id=10;
name
-------------------
wahaha

在目标数据库上进行查询确定是否同步：
SQL>conn ydmm/ydmm
SQL>select name from user where user_id=10;
name
-------------------
wahaha

至此，单表复制（本地捕获）已经完成

十六、清除所有stream配置信息
当不需要复制并要清除 Stream配置信息时，需要先执行停止Stream相关进程然后再清除配置。
以strmadmin身份，登录源数据库。
SQL>connect strmadmin/strmadmin
SQL>exec dbms_capture_adm.stop_capture("capture_streams");
SQL>exec dbms_propagation_adm.stop_propagation("orcl_to_orclbak");
SQL>exec DBMS_STREAMS_ADM.remove_streams_configuration();
以strmadmin身份，登录从数据库。
SQL>connect strmadmin/strmadmin
SQL>exec dbms_apply_adm.stop_apply("apply_streams");
SQL>exec DBMS_STREAMS_ADM.remove_streams_configuration();

注意事项：
不能update主键，否则会导致stream应用失效