[原创] Schema级单向Streams构建笔记

Normal 0 0 2 false false false MicrosoftInternetExplorer4 1. Prepare

In this Streams test environment, there are two databases called “strm1” and “strm2”, whose global name are “strm1.com” and “strm2.com” respectively. The source side is “strm1”, and target side is “strm2”. The synchronization level is SCHEMA level and single-directions replication.

1.1 Init parameter prerequisite (Both db)

a) global_names should be true. global_name =db_name+”.”+domain_name

alter system set global_names=true;

select * from global_name;

GLOBAL_NAME

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

STRM1.COM

GLOBAL_NAME

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

STRM2.COM

b) job_queue_processes >= 2

alter system set job_queue_processes=10;

c) compatible >= 10.2 and target side >= source side

show parameter compatible

d) streams_pool_size should set up with suitable value (here is 50MB)

alter system set streams_pool_size=50M scope=spfile;

e) archive mode is enabled in source side

archive log list

Database log mode Archive Mode

……

f) open_links>=4

alter system set open_links=4 scope=spfile;

g) parallel_max_servers (each capture process and apply process can use multiple parallel execution servers).

alter system set parallel_max_servers=20 scope=both;

h) change logminer data dictionary default tablespace from SYSAUX to TBS_LOGMN tablespace

-- strm1

create tablespace tbs_logmn datafile '/oracle/oradata/strm1/tbs_ logmn01.dbf' size 100m autoextend on;

execute dbms_logmnr_d.set_tablespace('tbs_logmn');

-- strm2

create tablespace tbs_logmn datafile '/oracle/oradata/strm2/tbs_ logmn01.dbf' size 100m autoextend on;

execute dbms_logmnr_d.set_tablespace('tbs_logmn');

i) supplemental logging

In 10gR2, supplemental logging is automatically configured for tables on which primary, unique, or foreigh keys are defined when database object is prepared for Streams capture.

Related concepts, please refer < Streams Replication Administrator's Guide (10.2)> Chapter 12

Part Number B14228-04

Supplemental log can be explictly enabled by command below:

-- minimal supplemental log mode

alter database add supplemental log data;

COLUMN log_min HEADING 'Minimum|Supplemental|Logging?' FORMAT A12

COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging?' FORMAT A12

COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging?' FORMAT A12

COLUMN log_ui HEADING 'Unique|Supplemental|Logging?' FORMAT A12

COLUMN log_all HEADING 'All Columns|Supplemental|Logging?' FORMAT A12

SELECT SUPPLEMENTAL_LOG_DATA_MIN log_min,

SUPPLEMENTAL_LOG_DATA_PK log_pk,

SUPPLEMENTAL_LOG_DATA_FK log_fk,

SUPPLEMENTAL_LOG_DATA_UI log_ui,

SUPPLEMENTAL_LOG_DATA_ALL log_all

FROM V$DATABASE;

Minimum      Primary Key  Foreign Key  Unique       All Columns

Supplemental Supplemental Supplemental Supplemental Supplemental

Logging?     Logging?     Logging?     Logging?     Logging?

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

YES           NO           NO           NO           NO

Disable supplemental log is using “alter database drop supplemental log data”.

Note: If you forget to enable it, it is OK. Because when configuring Streams capture/apply process, it will be automatically enabled.

 

1.2 Edit tnsnames.ora in both sides

STRM1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.101)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = strm1.com)

    )

  )

STRM2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.101)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = strm2.com)

    )

  )

 

2. Streams administrator account creation 

2.1 Create streams admin in strm1 db

-- Create streams tablespace

sqlplus sys@strm1 as sysdba

create tablespace tbs_stream datafile '/oracle/oradata/strm1/tbs_stream01.dbf' size 100m autoextend on;

-- Create streams admin

create user strmadmin identified by nhy67ujm default tablespace tbs_stream temporary tablespace temp;

-- streams admin MUST have dba privilege

grant connect,resource,dba to strmadmin;

begin

dbms_streams_auth.grant_admin_privilege(

grantee => 'strmadmin',

grant_privileges => true);

end;

/

-- Confirm

select * from dba_streams_administrator;

USERNAME                       LOC ACC

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

STRMADMIN                      YES YES

 

2.2 Create streams admin in strm2 db

-- Create streams tablespace

sqlplus sys@strm2 as sysdba

create tablespace tbs_stream datafile '/oracle/oradata/strm2/tbs_stream01.dbf' size 100m autoextend on;

-- Create streams admin

create user strmadmin identified by nhy67ujm default tablespace tbs_stream temporary tablespace temp;

-- Grant dba to streams admin

grant connect,resource,dba to strmadmin;

begin

dbms_streams_auth.grant_admin_privilege(

grantee => 'strmadmin',

grant_privileges => true);

end;

/

-- Confirm

select * from dba_streams_administrator;

USERNAME                       LOC ACC

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

STRMADMIN                      YES YES

 

3. Create Database link

-- Note: database link name must same as global name of target db

-- command below can change global name

alter database rename global to strm1.com;

-- strm1: connect as stream admin

conn strmadmin@strm1

strmadmin@STRM1> create database link strm2.com connect to strmadmin identified by nhy67ujm using 'strm2';

-- login as sys

sys@STRM1> select NAME,CTIME,USERID from sys.link$;

NAME       CTIME               USERID

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

STRM2.COM  2009/05/03 23:55:16 STRMADMIN

 

4.1 Configure streams queue in both sides. (Afterwards, if not specially mentioned, the user is always “strmadmin”)

-- strm1

begin

dbms_streams_adm.set_up_queue(

queue_table => 'strm1_cap_queue_table',

queue_name => 'strm1_cap_queue');

end;

/

-- confirm

strmadmin@STRM1> select name,queue_table from user_queues;

NAME                        QUEUE_TABLE

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

AQ$_STRM1_CAP_QUEUE_TABLE_E STRM1_CAP_QUEUE_TABLE

STRM1_CAP_QUEUE             STRM1_CAP_QUEUE_TABLE

-- strm2

begin

dbms_streams_adm.set_up_queue(

queue_table => 'strm2_apl_queue_table',

queue_name => 'strm2_apl_queue');

end;

/

-- confirm

strmadmin@STRM2> select name,queue_table from user_queues;

NAME                        QUEUE_TABLE

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

AQ$_STRM2_APL_QUEUE_TABLE_E STRM2_APL_QUEUE_TABLE

STRM2_APL_QUEUE             STRM2_APL_QUEUE_TABLE

Note: Program of removing queue is as below:

begin

dbms_streams_adm.remove_queue(

queue_name => 'strm1_cap_queue',

cascade => true);

end;

/

 

4.2 Create capture process in strm1 side

-- strm1: set up capture process

-- before setup, minimal supplemental log is disabled

SELECT SUPPLEMENTAL_LOG_DATA_MIN log_min,SUPPLEMENTAL_LOG_DATA_PK log_pk,

SUPPLEMENTAL_LOG_DATA_FK log_fk,SUPPLEMENTAL_LOG_DATA_UI log_ui,

SUPPLEMENTAL_LOG_DATA_ALL log_all FROM V$DATABASE;

Minimum      Primary Key  Foreign Key  Unique       All Columns

Supplemental Supplemental Supplemental Supplemental Supplemental

Logging?     Logging?     Logging?     Logging?     Logging?

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

NO           NO           NO           NO           NO

 

begin

dbms_streams_adm.add_schema_rules(

schema_name => 'dbausr',

streams_type => 'capture',

streams_name => 'strm1_capture',

queue_name => 'strmadmin.strm1_cap_queue',

include_dml => true,

include_ddl => true,

include_tagged_lcr => false,

source_database => null,

inclusion_rule => true);

end;

/

-- after set up, supplemental log is enabled automatically

Minimum      Primary Key  Foreign Key  Unique       All Columns

Supplemental Supplemental Supplemental Supplemental Supplemental

Logging?     Logging?     Logging?     Logging?     Logging?

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

YES          NO           NO           NO           NO

 

5. Duplicate DBAUSR schema from source to target

5.1 Duplicate by using exp/imp

-- make sure DBAUSR user exists in target side, or else create it

strmadmin@STRM2> create user dbausr identified by nhy67ujm default tablespace users temporary tablespace temp;

-- export DBAUSR schema from source side

exp userid=dbausr/nhy67ujm@strm1 file='/tmp/dbausr.dmp' object_consistent=y rows=y

-- import to target side

imp userid=system/nhy67ujm@strm2 file='/tmp/dbausr.dmp' ignore=y commit=y log='/tmp/dbausr.log' streams_instantiation=y fromuser=dbausr touser=dbausr

 

6 Create propagation/apply rules

6.1 Create propagation rules

-- strm1: set propagation rule

begin

dbms_streams_adm.add_schema_propagation_rules(

schema_name => 'dbausr',

streams_name => 'strm1_to_strm2',

source_queue_name => 'strmadmin.strm1_cap_queue',

destination_queue_name => 'strmadmin.strm2_apl_queue@strm2.com',

include_dml => true,

include_ddl => true,

include_tagged_lcr => false,

source_database => 'strm1.com',

inclusion_rule => true);

end;

/

-- change the latency to 0, which means real time propagation

begin

dbms_aqadm.alter_propagation_schedule(

queue_name => 'strm1_cap_queue',

destination => 'strm2',

latency => 0);

end;

/

 

6.2 Create apply process in target side

-- strm2 : set apply rule

-- before setup, minimal supplemental log is disabled

SELECT SUPPLEMENTAL_LOG_DATA_MIN log_min,SUPPLEMENTAL_LOG_DATA_PK log_pk,

SUPPLEMENTAL_LOG_DATA_FK log_fk,SUPPLEMENTAL_LOG_DATA_UI log_ui,

SUPPLEMENTAL_LOG_DATA_ALL log_all FROM V$DATABASE;

Minimum      Primary Key  Foreign Key  Unique       All Columns

Supplemental Supplemental Supplemental Supplemental Supplemental

Logging?     Logging?     Logging?     Logging?     Logging?

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

NO           NO           NO           NO           NO

 

begin

dbms_streams_adm.add_schema_rules(

schema_name => 'dbausr',

streams_type => 'apply',

streams_name => 'strm2_apply',

queue_name => 'strmadmin.strm2_apl_queue',

include_dml => true,

include_ddl => true,

include_tagged_lcr => false,

source_database => 'strm1',

inclusion_rule => true);

end;

/

-- after set up, supplemental log is not enabled automatically

Minimum      Primary Key  Foreign Key  Unique       All Columns

Supplemental Supplemental Supplemental Supplemental Supplemental

Logging?     Logging?     Logging?     Logging?     Logging?

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

NO           NO           NO           NO           NO

 

7. Start/stop streams and verificating its functionality

7.1 Start streams

-- strm2: start apply

begin

dbms_apply_adm.start_apply(

apply_name => 'strm2_apply');

end;

/

-- strm1 : start capture

begin

dbms_capture_adm.start_capture(

capture_name => 'strm1_capture');

end;

/

 

7.2 Verification

-- [DDL Test] strm1: Before create

dbausr@STRM1> select count(*) from test;

select count(*) from test

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

-- strm2 :

strmadmin@STRM2> select count(*) from dbausr.test;

select count(*) from dbausr.test

                            *

ERROR at line 1:

ORA-00942: table or view does not exist

-- [DDL Test] strm1: After create

dbausr@STRM1> create table test as select * from emp;

Table created.

dbausr@STRM1> select count(*) from test;

  COUNT(*)

----------

        14

-- strm2 :

strmadmin@STRM2> select count(*) from dbausr.test;

  COUNT(*)

----------

        14

-- [DML Test] strm1: Before insert

dbausr@STRM1> select EMPNO,ENAME from dbausr.test where job='ANALYST';

     EMPNO ENAME

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

      7788 SCOTT

      7902 FORD

-- strm2 :

strmadmin@STRM2> select EMPNO,ENAME from dbausr.test where job='ANALYST';

     EMPNO ENAME

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

      7788 SCOTT

      7902 FORD

-- [DML Test] strm1: After insert

dbausr@STRM1> insert into test values ('9900','TEST','ANALYST',7566,sysdate,3000,0,20);

dbausr@STRM1> commit;

dbausr@STRM1> select EMPNO,ENAME from dbausr.test where job='ANALYST';

     EMPNO ENAME

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

      7788 SCOTT

      7902 FORD

      9900 TEST

-- strm2 :

-- hint, it will take some seconds to reflect the change

strmadmin@STRM2> select EMPNO,ENAME from dbausr.test where job='ANALYST';

     EMPNO ENAME

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

      7788 SCOTT

      7902 FORD

      9900 TEST

 

7.3 Stop streams

-- strm1: stop capture

begin

dbms_capture_adm.stop_capture(

capture_name => 'strm1_capture');

end;

/

select capture_name,status from dba_capture;

CAPTURE_NAME                   STATUS

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

STRM1_CAPTURE                  DISABLED

-- stop propagation

begin

dbms_propagation_adm.stop_propagation(

propagation_name => 'strm1_to_strm2',

force => false);

end;

/

select propagation_name,status from dba_propagation;

PROPAGATION_NAME               STATUS

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

STRM1_TO_STRM2                 DISABLED

-- strm2: stop apply

begin

dbms_apply_adm.stop_apply(

apply_name => 'strm2_apply');

end;

/

select apply_name,status from dba_apply;

APPLY_NAME                     STATUS

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

STRM2_APPLY                    DISABLED

 

8. Remove and clear streams environment

-- strm1

exec DBMS_STREAMS_ADM.remove_streams_configuration();

-- target

exec DBMS_STREAMS_ADM.remove_streams_configuration();

Note: If you want remove particular capture/propagation/apply process,

         use dbms_capture(/propagation/apply) _adm.drop_capture(/propagation/apply)

begin

dbms_capture_adm.drop_capture(

capture_name => '',

drop_unused_rule_sets => true);

end;

/

 

begin

dbms_propagation_adm.drop_propagation(

propagation_name => '',

drop_unused_rule_sets => true);

end;

/

 

begin

dbms_apply_adm.drop_apply(

apply_name => '',

drop_unused_rule_sets => true);

end;

/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/317003/viewspace-598151/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/317003/viewspace-598151/