oracle streams 11g数据复制,Oracle 11g OCM考纲Streams单向流复制

Oracle 11g OCM考纲Streams单向流复制

1.     实验环境

SYS user passwd

racle

SYSTEM user password

racle

节点1节点2

Oracle SIDOrclOrcl

Global DB name/service NameOrcl1Orcl2

Net server Nameamereuro

字符集AL32UTF8AL32UTF8

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)

(SERVICE_NAME = orcl2)

)

)

2.2数据库参数参数选项或值

Compatible11.2.0.0.0(或更高)

global_namesTRUE

service_namesOrcl1 |orcl2

Processes150

job_queue_processes1000

memory_target700M

memory_max_target700M

sga_max_size700M

streams_pool_size200M

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;

dbdao.com

调整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;

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  DEFAULT TABLESPACE streams_tbs  QUOTA UNLIMITED ON streams_tbs ;

赋权：

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';

!mkdir -p /home/oracle/euro

dbdao.com

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;

dbdao.com

在目标端查询：

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_name => ‘strmadmin.queue_table’, queue_user => ‘STRMADMIN’);

创建进程：

源端端

conn strmadmin/strmadmin

execute dbms_streams_adm.add_schema_rules( schema_name => ‘scott’, streams_type => ‘capture’, streams_name => ‘capture_src’, queue_name => ‘strmadmin.queue_table’, include_dml => 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_name => ‘prop_to_dest’,source_queue_name => ‘strmadmin.queue_table’,destination_queue_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’,destination => ‘orcl2′, destination_queue =>’strmadmin.queue_table’,latency => 0);

开启进程：

源端：

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();