好贷网好贷款

streams table级别复制

发布时间:2016-12-4 7:52:44 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"streams table级别复制",主要涉及到streams table级别复制方面的内容,对于streams table级别复制感兴趣的同学可以参考一下。

实验环境: OEL 5.8 64bit + oracle 11.2.0.3 源数据库: source 目标数据库:target     源数据库和目标数据库都必须是归档的   1. 环境准备   1.1 初始化参数调整   调整两个参数 global_name 和 aq_tm_processes   show parameter global_name show parameter aq_tm_processes show parameter streams_pool_size   alter system set aq_tm_processes=1 scope=both; alter system set global_names=true scope = both; alter system set streams_pool_size=200m scope=both; alter system set "_job_queue_interval"=1 scope=spfile; show parameter job_queue_processes; show parameter sga_target; show parameter open_links;   注意streams_pool_size一定要够大,因为如果启用了SGA_TARGET,ORACLE可能分配很少内存给stream导致大量信息被spill到磁盘导致查 DBA_APPLY,DBA_CAPTURE,DBA_PROPGATION全部状态ENABLED但就是没有数据被同步。 同时设置_job_queue_interval也是为了提高队列检查时间,防止apply出问题。     遇到一个数据传不过去的case,查了dave的博客,里面发现有这样一个记录,按照提示,修改后,数据传输过去了。 注意:与复制有关的2个参数: 如果等了足够长的时间发现数据没有复制过来,仔细检查了capture/propagation/apply各进程的状态都是正常的, 并检查参数. alter system set "_job_queue_interval"=1 scope=spfile; 并且将aq_tm_processes参数改为1(我原来这是为10) alter system set aq_tm_processes=1; 改完后重启,发现数据就可以去了。这个隐含参数只是控制对job队列的检查频率,默认5秒。   1.2 数据库归档模式   SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     4 Next log sequence to archive   6 Current log sequence           6   1.3 创建stream管理用户 source: create tablespace streams datafile '/u01/app/oracle/oradata/source/streams01.dbf' size 100M autoextend on ; create user streamadmin identified by streamadmin default tablespace streams quota unlimited on streams; grant connect,resource,dba,aq_administrator_role to streamadmin;   execute dbms_logmnr_d.set_tablespace('streams'); 授权: grant connect,resource,dba,aq_administrator_role to streamadmin;      /* 10g要求dba角色以简化配置 */ /* 赋予流管理特权 */ begin   dbms_streams_auth.grant_admin_privilege(   grantee => 'streamadmin',   grant_privileges => true); end; / target: create tablespace streams datafile '/u01/app/oracle/oradata/target/streams01.dbf' size 100M autoextend on ; create user streamadmin identified by streamadmin default tablespace streams quota unlimited on streams; execute dbms_logmnr_d.set_tablespace('streams'); grant connect,resource,dba,aq_administrator_role to streamadmin;     begin   dbms_streams_auth.grant_admin_privilege(   grantee => 'streamadmin',   grant_privileges => true); end; /   1.4 配置网络连接tnsnames.ora   在source 和target端配置tnsnames.ora文件 SOURCE =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = source.up.com)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = source)     )   )   TARGET =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = target.up.com)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = target)     )   )   1.5 追加日志 可以基于Database级别或Table级别,启用追加日志(Supplemental Log)。在建立根据Schema粒度进行复制的Oracle Stream环境中,如果确认Schema下所有Table都有合理的主键(Primary Key),则不再需要启用追加日志。          #启用Database 追加日志  一般只需要配置supplemental log就可以了。   alter database add supplemental log data;   #启用Table追加日志    ------如果不是所有的额表都有合理的primary key,则不需要建立这个追加日志   alter table add supplement log group log_group_name(table_column_name) always;   SQL> select force_logging, supplemental_log_data_min from v$database;   FOR SUPPLEME --- -------- YES YES     1.6 创建DBlink   On source as STRMADMIN create database link to target conn streamadmin/streamadmin CREATE DATABASE LINK TARGET CONNECT TO streamadmin IDENTIFIED BY streamadmin USING 'TARGET';   On target as STRMADMIN create database link to source conn streamadmin/streamadmin CREATE DATABASE LINK SOURCE CONNECT TO streamadmin IDENTIFIED BY streamadmin USING 'SOURCE';         2 创建流队列     2.1 创建流队列   SOURCE: conn streamadmin/streamadmin; BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(          queue_table => 'SOURCE_QUEUE_TABLE',              queue_name  => 'SOURCE_QUEUE_DILLON',             queue_user  => 'streamadmin');                                                                                    END;                                   / select owner,queue_table,name from dba_queues where owner='STREAMADMIN';   TARGET: conn streamadmin/streamadmin; BEGIN   DBMS_STREAMS_ADM.SET_UP_QUEUE(                                                                                                     queue_table => 'TARGET_QUEUE_TABLE',                                                                                        queue_name  => 'TARGET_QUEUE_DILLON',                                                                                            queue_user  => 'streamadmin');                                                                                    END;                                   / select owner,queue_table,name from dba_queues where owner='STREAMADMIN';   2.2 、在源数据库上创建捕获进程及传播进程     源数据库上创建捕获进程 SOURCE: conn streamadmin/streamadmin; begin dbms_streams_adm.add_table_rules( table_name => 'dillon.test', streams_type => 'capture', streams_name => 'capture_streams_dillon', queue_name => 'streamadmin.SOURCE_QUEUE_DILLON', include_dml => true, include_ddl => true, inclusion_rule => true); end; / select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;     源数据库上创建传播进程 conn streamadmin/streamadmin; begin dbms_streams_adm.add_table_propagation_rules( table_name => 'dillon.test', streams_name => 'source_to_target_dillon', source_queue_name => 'streamadmin.SOURCE_QUEUE_DILLON', destination_queue_name => [email protected]', include_dml => true, include_ddl => true, source_database => 'SOURCE', inclusion_rule => true, queue_to_queue => true); end; /   select PROPAGATION_NAME,STATUS from dba_propagation;    2.3、在目标数据库创建应用进程: conn streamadmin/streamadmin; begin dbms_streams_adm.add_table_rules( table_name => 'dillon.test', streams_type => 'apply', streams_name => 'apply_streams_dillon', queue_name => 'streamadmin.TARGET_QUEUE_DILLON', include_dml => true, include_ddl => true, source_database => 'SOURCE', inclusion_rule => true); end; /   select apply_name,queue_name,status from dba_apply;   2.4、将源数据中的表ydmm.user导入到目标数据库 可以用exp/imp,rman等方式进行数据导入,因为已经有db_link了,所以我直接进行生成数据 在目标数据库执行如下操作: conn dillon/dillon create database link dillon_source connect to dillon identified by dillon using 'SOURCE'; create table test as select * from [email protected]_source;   2.5、在目标数据库上设置应用进程开始执行的SCN conn streamadmin/streamadmin; DECLARE source_scn  NUMBER; BEGIN source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); [email protected]( source_object_name=> 'dillon.test', source_database_name=> 'source', instantiation_scn=> source_scn); END; /   2.6、在目标数据库上启动应用进程 conn streamadmin/streamadmin; exec dbms_apply_adm.start_apply('APPLY_STREAMS_DILLON'); 注: select apply_name,status from dba_apply;                select error_message from DBA_APPLY_ERROR;   2.7、在源数据库启用捕获进程 conn streamadmin/streamadmin exec dbms_capture_adm.start_capture('capture_streams_dillon'); select capture_name,status from dba_capture;   3 测试: source: SQL> conn dillon/dillon Connected. SQL> select * from test;         ID ----------          1          2   SQL> insert into test values(3); 1 row created. SQL> commit; Commit complete.   SQL> select * from test;           ID ----------          1          2          3   SQL> insert into test values (4); 1 row created. SQL> commit; Commit complete.   target: SQL> conn dillon/dillon Connected. SQL> select * from test;         ID ----------          1          2          3          4          3          4   6 rows selected.       看到结果有2个3,2个4,这是因为做这个实验之前,我先做了一个schema级别的复制。一个是dillon schema ,一个是dillon.test表,所以会出现这两个重复的结果。 source:查看capture进程和传播进程 SQL> conn streamadmin/streamadmin Connected. SQL> select capture_name,status from dba_capture;   CAPTURE_NAME                   STATUS ------------------------------ -------- CAPTURE_STREAM                 ENABLED CAPTURE_STREAMS_DILLON         ENABLED   SQL> select PROPAGATION_NAME,STATUS from dba_propagation;   PROPAGATION_NAME               STATUS ------------------------------ -------- SOURCE_TO_TARGET_DILLON        ENABLED SOURCE_TO_TARGET               ENABLED     target:查看apply进程 SQL> conn streamadmin/streamadmin Connected. SQL> select apply_name,status from dba_apply;   APPLY_NAME                     STATUS ------------------------------ -------- TARGET_APPLY_STREAM            ENABLED APPLY_STREAMS_DILLON           ENABLED         参考文档:dave博客http://blog.csdn.net/tianlesoftware/article/details/4759356    

上一篇:nise_bosh在openstack上搭建cloudfoundry中cf.yml的完整版
下一篇:Oracle中REDO日志

相关文章

相关评论