http://www.dbdao.com 数据岛 IT 在线教育 Oracle 11g OCM 考纲 Streams 单向流 复制 工程师 : 报告生成日期 : 郭兆伟 2015 年 7 月 20 日
文档控制 此文档仅供诗檀软件内部审阅, 不得向与此无关的个人或机构传 阅或复制 变更记录 日期作者及更新人版本号变更信息 郭兆伟 V1.0 审阅人 版本号审阅人职位相关评 论 审批人 版本号批准人日期相关评论 文档分发 分发号文档名分发位置 1 SHCH-1 ASANA dbdao.com IT 技术学习课件, 版权所有, 翻版必究 2
目录 文档控制... 2 变更记录... 2 审阅人... 2 审批人... 2 文档分发... 2 目录... 3 1. 实验环境... 4 2. 检查和调整数据库参数... 4 2.1 源端和目标端... 4 2.2 数据库参数... 5 2.3 确认归档模式... 6 3. 流环境配置... 6 3.1 创建表空间 STREAMS_TBS, 创建流用户 STRMADMIN 使用此表空间并赋权... 6 3.2 创建目录对象... 6 3.3 创建 DBlink... 7 4. 配置复制和测试... 7 5. 其他... 9 其他问题... 10 未解决的问题... 10 已解决的问题... 10 dbdao.com IT 技术学习课件, 版权所有, 翻版必究 3
1. 实验环境 SYS user passwd:oracle SYSTEM user password:oracle 节点 1 节点 2 Oracle SID Orcl Orcl Global DB name/service Orcl1 Orcl2 Name Net server Name amer euro 字符集 AL32UTF8 AL32UTF8 2. 检查和调整数据库参数 2.1 源端和目标端 tnsnames.ora 的配置 : vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora amer = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.118)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1) ) ) euro = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.225)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) dbdao.com IT 技术学习课件, 版权所有, 翻版必究 4
) ) (SERVICE_NAME = orcl2) 2.2 数据库参数 参数 选项或值 Compatible 11.2.0.0.0( 或更高 ) global_names TRUE service_names Orcl1 orcl2 Processes 150 job_queue_processes 1000 memory_target 700M memory_max_target 700M sga_max_size 700M streams_pool_size 200M export ORACLE_SID=amer sqlplus / as sysdba show parameter compatible show parameter global_names alter system set global_names=true; show parameter service_names show parameter processes show parameter memory_target show parameter memory_max_target show parameter sga_max_target show parameter stream_pool_size alter system set streams_pool_size=200m; 调整 globa_name 和 service_name: 源端 : alter database rename global_name to orcl1; alter system set service_name=orcl1; 目标端 : alter database rename global_name to orcl2; alter system set service_name=orcl2; dbdao.com IT 技术学习课件, 版权所有, 翻版必究 5
2.3 确认归档模式 archive log list; 2. 流环境配置 3.1 创建表空间 STREAMS_TBS, 创建流用 户 STRMADMIN 使用此表空间并赋权 源端和目标端 : 创建表空间 create tablespace streams_tbs datafile '/u01/app/oracle/oradata/orcl/streams_tbs01.dbf' size 25M; 创建用户 : create USER strmadmin identified by strmadmin streams_tbs QUOTA UNLIMITED ON streams_tbs ; 赋权 : DEFAULT TABLESPACE grant dba to strmadmin; execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN'); 3.2 创建目录对象源端 : create directory SRC_EXP_DIR as '/home/oracle/amer';!mkdir -p /home/oracle/amer 目标端 : DIRECTORY_PATH (/hom/oracle/euro) 中. create directory DEST_EXP_DIR as '/home/oracle/euro'; dbdao.com IT 技术学习课件, 版权所有, 翻版必究 6
!mkdir -p /home/oracle/euro 3.3 创建 DBlink 源端 : sqlplus strmadmin/strmadmin@amer CREATE DATABASE LINK orcl2 CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'euro'; 目标端 : sqlplus strmadmin/strmadmin@euro CREATE DATABASE LINK orcl1 CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'amer'; 4. 配置复制和测试 在源端创建测试表 : sqlplus scott/scott@amer set sqlprompt "AMER>" create table dept_history as select * from dept; 在目标端查询 : sqlplus scott/scott@euro set sqlprompt "EURO>" select count(*) from dept_history; 创建队列 : conn strmadmin/strmadmin execute dbms_streams_adm.set_up_queue(queue_table => 'strmadmin.queue_table', queue_nam e => 'strmadmin.queue_table', queue_user => 'STRMADMIN'); dbdao.com IT 技术学习课件, 版权所有, 翻版必究 7
创建进程 : 源端端 conn strmadmin/strmadmin execute dbms_streams_adm.add_schema_rules( schema_name => 'scott', streams_type => 'captu re', streams_name => 'capture_src', queue_name => 'strmadmin.queue_table', include_d ml => true, include_ddl => true); 目标端 : conn strmadmin/strmadmin execute dbms_streams_adm.add_schema_rules( schema_name => 'scott', streams_type => 'apply ', streams_name => 'apply_dest', queue_name => 'strmadmin.queue_table', include_dml => true, include_ddl => true, inclusion_rule => true); 使用 expdp 进行数据初始化 : 目标端 expdp strmadmin/strmadmin directory=dest_exp_dir dumpfile=scott.dmp nologfile=y network_link=orcl1 schemas=scott impdp strmadmin/strmadmin directory=dest_exp_dir dumpfile=scott.dmp nologfile=y remap_schema=scott:scott remap_tablespace=users:users table_exists_action=replace # 创建传播规则并修改 propagation 休眠时间为 0, 表示实时传播 LCR 源端 : connect strmadmin/strmadmin execute dbms_streams_adm.add_schema_propagation_rules(schema_name => 'scott',streams_na me => 'prop_to_dest',source_queue_name => 'strmadmin.queue_table',destination_queu e_name => 'strmadmin.queue_table@orcl2', include_dml => true, include_ddl => true,source_database =>'orcl1' ); execute dbms_aqadm.alter_propagation_schedule(queue_name => 'strmadmin.queue_table',dest ination => 'orcl2', destination_queue =>'strmadmin.queue_table',latency => 0); dbdao.com IT 技术学习课件, 版权所有, 翻版必究 8
开启进程 : 源端 : conn strmadmin/strmadmin execute dbms_capture_adm.start_capture(capture_name => 'capture_src'); 目标端 : conn strmadmin/strmadmin execute dbms_apply_adm.start_apply(apply_name => 'apply_dest'); 在源端插入测试数据 insert into dept_history values (87, 'Joe', 'jsmith'); commit; 目标端再次查询 : select * from dept_history where DEPTNO=87; 5. 其他 --- 停止进程 : exec dbms_capture_adm.stop_capture(capture_name => 'capture_src '); exec dbms_apply_adm.stop_apply(apply_name => 'apply_dest'); 删除全部配置 : exec DBMS_STREAMS_ADM.remove_streams_configuration(); dbdao.com IT 技术学习课件, 版权所有, 翻版必究 9
其他问题 未解决的问题 问题号问题描述解决方案日期 已解决的问题 问题号问题描述解决方案解决日期 dbdao.com IT 技术学习课件, 版权所有, 翻版必究 10
ParnassusData Corporation, Shanghai, GaoPing Road No. 733. China Phone: (+86) 400-690-3643 dbdao.com Copyright 2013, ParnassusData and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, or including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. UNIX is a registered trademark licensed through X/Open Company, Ltd. 0410 Copyright 2015 ParnassusData Corporation. All Rights Reserved. dbdao.com IT 技术学习课件, 版权所有, 翻版必究 11