Oracle 10G streams configuration example.

最新推荐文章于 2024-10-30 10:59:51 发布

csdn201420142015

最新推荐文章于 2024-10-30 10:59:51 发布

阅读量108

点赞数

文章标签：数据库

Streams configuration step by step

Streams is a new way to duplicate data. Normally, we have the Streams to duplicate data from one database to another database; we can also have the data duplication with Stream technology within one database, Here, I will give an example to do Streams configuration in a database to duplicate data from one schema (TEST1) to another schema (TEST2).

Environments:

OS: Linux version 2.6.9-67.ELsmp

Oracle: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

1, Overview of the streams architecture:

Key components:

1) LCR: logic change record, classify into DML LCR and DDL LCR, it includes changes made to the database objects.

2) QUEUE: to store LCR.

3) Capture process: to extract information from redo logfile through LOGMNR, and encapsulate them as LCR, and put these LCR into QUEUE.

4) Propagation process: to ship the LCR from one queue to another.

5) Apply Process: to dequeue the QUEUE and get LCR and apply LCR to the corresponding database objects.

6) RULES, to guild what information should be extracted, shipped and applied by capture process/propagation process/apply process.

The component propagation is optional, in case capture process and apply process shares the queue, there is unnecessary to have the propagation.

2, parameter settings

job_queue_processes=10

aq_tm_processes=10

compatible=10.2.0

global_names=TRUE

streams_pool_size=128M

Database run in archive log mode

3, Create Oracle users:

Test schemas: test1 and test2

create user test1 identified by test1 default tablespace users;

create user test2 identified by test2 default tablespace users;

grant connect,resource to test1;

grant connect,resource to test2;

Streams administer:

create user strmadmin identified by strmadmin default tablespace stream;

grant connect,resource,dba,aq_administrator_role to strmadmin;

begin

dbms_streams_auth.grant_admin_privilege(

grantee=>'strmadmin',

grant_privileges=>true);

end;

Force database add supplement log:

alter database add supplemental log data(primary key,unique) columns;

prepare objects to be duplicated under schema TEST1:

conn test1/test1

create table abc(a number, b date);

4, Streams configuration:

Create queues:

conn strmadmin/strmadmin

begin

dbms_streams_adm.set_up_queue(

queue_table=>'capture_tab_1',

queue_name=>'queue_capture_1');

end;

begin

dbms_streams_adm.set_up_queue(

queue_table=>'apply_tab_1',

queue_name=>'queue_apply_1');

end;

Create capture process, it is in disable status

begin

dbms_streams_adm.add_schema_rules(

schema_name=>'test1',

streams_type=>'capture',

streams_name=>'capture_1',

queue_name=>'queue_capture_1',

include_dml=>true,

include_ddl=>true,

source_database=>null,

inclusion_rule=>true);

end;

Create propagation process:

begin

dbms_streams_adm.add_schema_propagation_rules(

schema_name=>'test1',

streams_name=>'propagation_1',

source_queue_name=>'queue_capture_1',

destination_queue_name=>'queue_apply_1',

include_dml=>true,

include_ddl=>true,

include_tagged_lcr=>false,

inclusion_rule=>true);

end;

begin

dbms_aqadm.alter_propagation_schedule(

queue_name=>'queue_capture_1',

latency=>0);

end;

Create apply process:

begin

dbms_streams_adm.add_schema_rules(

schema_name=>'test1',

streams_type=>'apply',

streams_name=>'apply_1',

queue_name=>'queue_apply_1',

include_dml=>true,

include_ddl=>true,

include_tagged_lcr=>false,

inclusion_rule=>true);

end;

Rule-base transformation, to convert changes made to test2:

declare

v_dml_rule varchar2(100);

begin

select rule_name into v_dml_rule from dba_streams_schema_rules

where streams_name='APPLY_1' and rule_type='DML';

DBMS_STREAMS_ADM.RENAME_SCHEMA(

rule_name => v_dml_rule,

from_schema_name => 'test1',

to_schema_name => 'test2',

operation => 'ADD');

end;

Instantiate the objects under steams:

exp file=test1.dmp owner=test1 OBJECT_CONSISTENT=Y

imp fromuser=test1 touser=test2 file=test1.dmp STREAMS_INSTANTIATION=Y

Start apply process and capture process:

exec dbms_apply_adm.start_apply('APPLY_1');

exec dbms_capture_adm.start_capture('CAPTURE_1');

5, test

insert into test1.abc select rownum,sysdate+rownum from dba_objects where rownum<4;

commit;

select * from test1.abc;

A B

---------- -------------------

1 2009-05-07 16:18:48

2 2009-05-08 16:18:48

3 2009-05-09 16:18:48

select * from test2.abc;

A B

---------- -------------------

1 2009-05-07 16:18:48

2 2009-05-08 16:18:48

3 2009-05-09 16:18:48

delete from test1.abc where a=1;

update test1.abc set b=b-1 where a=2;

commit;

select * from test1.abc;

A B

---------- -------------------

2 2009-05-07 16:18:48

3 2009-05-09 16:18:48

select * from test2.abc;

A B

---------- -------------------

2 2009-05-07 16:18:48

3 2009-05-09 16:18:48

6, diagnose

To check status of capture/propagation/apply process:

Select status from dba_capture/dba_propagation/dba_apply

To check apply error message:

Select error_creation_time,error_message from dba_apply_error.

7, summary,

There are 2 types of rule-based transformations in Stream configuration: declarative Rule-Based transformations (provide by Oracle), custom Rule-Based Transformations (coded by Oracle users). From the test, it seems that the declarative does not support DDL operations.

In this example, we can remove the propagation process since the duplication happens within an instance. It can create only one queue and shared by capture process and apply process.

References:

Metalink 753158.1

Oracle Streams concepts and administration (10.2) B14229-04

[@more@]