好贷网好贷款

演示基于单表的流复制案例--可以同步DDL操作

发布时间:2016-12-3 8:22:38 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"演示基于单表的流复制案例--可以同步DDL操作",主要涉及到演示基于单表的流复制案例--可以同步DDL操作方面的内容,对于演示基于单表的流复制案例--可以同步DDL操作感兴趣的同学可以参考一下。

环境如下:基于表及基于用户的实验都在此环境下操作。 一、配置初始化环境 1.修改初始化参数: 目标库上:--因是使用虚拟机克隆的,所以两个库数据名和实例名一样,此时要修改目标库的db_unique_name和global_name 主库上也要做修改,像db_unique_name,global_name可以不修改。 [email protected]>alter system set db_unique_name=bys2 scope=spfile; [email protected]>alter database rename global_name to bys2; [email protected]>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; [email protected]>select SUPPLEMENTAL_LOG_DATA_MIN,force_logging from v$database; SUPPLEMENTAL_LOG FORCE_ ---------------- ------ YES              YES [email protected]>col db_unique_name for a20 [email protected]>select name,db_unique_name from v$database; NAME               DB_UNIQUE_NAME ------------------ -------------------- BYS1               BYS2 ############################################################## 2.配置双方监听,在两台主机上能够通过服务名互相访问 sqlplus [email protected] sqlplus [email protected] ############################################# 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 [email protected]>select * from global_name; GLOBAL_NAME ---------------------------------------------------------------------------------------------------- BYS2 [email protected]>show parameter db_domain NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_domain                            string conn strmadmin/strmadmin create database link bys2 connect to strmadmin identified by strmadmin using 'bys2'; [email protected]>select * from [email protected]; GLOBAL_NAME ----------------- BYS2 目标库: conn strmadmin/strmadmin create database link bys1 connect to strmadmin identified by strmadmin using 'bys1'; [email protected]>select * from [email protected]; GLOBAL_NAME --------------------- BYS1 到这一步可以关了虚拟机做个备份。 二、基于表的复制案例 源库名bys1,目标库名bys2,数据同步基于的表是test8用户的test1表。 提示:在涉及table_name及source_database等参数时要注意根据自己实际测试环境修改。 需要双方首先都创建test8用户并创建test1表。并且注意配置时要使用strmadmin用户。 create user test8 identified by test8; grant connect,resource to test8; conn test8/test8 create table test1(aa number primary key); ################################################################ 1.在源数据库bys1上创建Source 队列 connect  [email protected]; BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'SOURCE_QUEUE_TABLE', queue_name  => 'SOURCE_QUEUE', queue_user  => 'strmadmin');            END; / 移除: begin dbms_streams_adm.remove_queue( queue_name => 'SOURCE_QUEUE', cascade => true, drop_unused_queue_table => true); 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.目标库BYS2上创建接收队列 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.源库BYS1上创建capture进程 BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name     => 'test8.test1', 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.源库BYS1上创建传播进程 BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name                => 'test8.test1', streams_name               => 'source_to_target', source_queue_name          => 'strmadmin.SOURCE_QUEUE', destination_queue_name    => [email protected]', 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.目标库上创建APPLY进程 BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name      => 'test8.test1', 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; / 查看状态: 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_OBJECT_NAME =>中的用户名和source_database_name =>表名以及数据库名。 BEGIN DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN( SOURCE_OBJECT_NAME => 'test8.test1', source_database_name => 'bys1', instantiation_scn => 1684128); END;  如果同步需要同步两个表,实例化的写法如下: 注意SOURCE_OBJECT_NAME => 'test9.test1,test9.test2',实验中每次写一个表执行两次是不行的,需要同时写进去。 同步两个表写法: BEGIN DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN( SOURCE_OBJECT_NAME => 'test9.test1,test9.test2', source_database_name => 'bys1', instantiation_scn => 1673182); END; / ##################################### 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 BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'capture_stream'); END; / #停止Capture进程 begin dbms_capture_adm.stop_capture( capture_name => 'capture_stream'); end; / 查看状态: [email protected]>select capture_name,status,CAPTURED_SCN,APPLIED_SCN from dba_capture; ######################### 9.数据同步测试: 情景一:关闭源库Capture进程及目标库停止APPLY进程。 在源库的test8用户的test1表中插入一条数据111.目标库不插入。然后查询SCN。设置目标库SCN来进行实例化,然后启动 此时源和目标的test8用户的test1表中数据存在差异。 在源库的test8用户的test1表中插入数据,同步情况是已经存在的111条目不会传送到目标,新进行的插入操作可以传送。 源库操作: [email protected]>select * from test1;         AA ----------        111 [email protected]>insert into test1 values(222); 1 row created. [email protected]>commit; Commit complete. [email protected]>set time on 21:15:34 [email protected]>insert into test1 values(333); 1 row created. 21:15:45 [email protected]>commit; Commit complete. 目标库查询操作: [email protected]>select * from test1; no rows selected [email protected]>select * from test1;         AA ----------        222 [email protected]>set time on 21:15:34 [email protected]>select * from test1;         AA ----------        222 21:15:50 [email protected]>select * from test1;        AA ----------        222 21:15:52 [email protected]>select * from test1;---可以观察到源库的DML操作后有一定的延迟才能传送到备库。         AA ----------        222        333 情景二;源库删除表中所有数据,因目标库表中不存在111,此时目标库中的接收进程ABORT,并且此删除操作不对目标库起作用。 主库日志报错如下: Tue Sep 03 21:17:01 2013 knlbmEnq: all subscribers are inactive - stop enqueuing 解决方法是:停止源和目标库的传播和接收进程,重新实例化两个库,再重新打开源和目标库的传播和接收进程。 实验如下: 源库操作: 21:15:58 [email protected]>delete test1; 3 rows deleted. 21:16:24 [email protected]>commit; Commit complete. 21:16:28 [email protected]>select * from test1; no rows selected 目标库:日志无报错,但是进程ABORTED 21:16:41 [email protected]>select * from test1;         AA ----------        222        333 [email protected]>select apply_name,queue_name,status from dba_apply; APPLY_NAME           QUEUE_NAME           STATUS -------------------- -------------------- ---------------- TARGET_APPLY_STREAM  TARGET_QUEUE         ABORTED 情景三:对TEST1表进行TRUNCATE--DDL操作,可以同步。 源库操作: [email protected]>set time on 18:56:34 [email protected]>select * from test1; no rows selected 18:56:48 [email protected]>insert into test1 values(1); 1 row created. 18:56:52 [email protected]>commit; Commit complete. 18:56:54 [email protected]>select * from test1;         AA ----------          1 18:56:56 [email protected]>truncate table test1; Table truncated. 18:57:09 [email protected]>select * from test1; no rows selected 18:57:11 [email protected]> 目标库查询 [email protected]>set time on 18:56:38 [email protected]>select * from test1; no rows selected 18:56:41 [email protected]> 18:56:59 [email protected]>select * from test1;         AA ----------          1 18:57:00 [email protected]>select * from test1; no rows selected 18:57:14 [email protected]> 情景四:对TEST1表进行DROP--DDL操作,可以同步。 [email protected]>set time on 10:29:26 [email protected]>select * from test1; no rows selected 10:30:06 [email protected]>select * from tab; TNAME                          TABTYPE  CLUSTERID ------------------------------ ------- ---------- TEST1                          TABLE 10:30:09 [email protected]>select * from test1; no rows selected 10:30:14 [email protected]>drop table test1 purge; Table dropped. 10:30:28 [email protected]>select * from tab; no rows selected 目标库查询: [email protected]>set time on 10:29:49 [email protected]>select * from test1; no rows selected 10:29:53 [email protected]>select * from tab; TNAME                                                       TABTYPE          CLUSTERID ------------------------------------------------------------ -------------- ---------- TEST1                                                       TABLE 10:29:57 [email protected]>select * from test1; no rows selected 10:30:19 [email protected]>select * from tab; no rows selected 10:30:38 [email protected]>select * from test1; select * from test1               * ERROR at line 1: ORA-00942: 表或视图不存在

上一篇:man kernel
下一篇:【Android】项目中每个文件夹的作用

相关文章

相关评论