-- 开启归档

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/archlog

Oldest online log sequence     452

Next log sequence to archive   454

Current log sequence           454


--配置数据库相关参数


SQL> show parameter global_names


NAME                                 TYPE        VALUE

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

global_names                         boolean     FALSE



SQL> alter system set global_names=TRUE scope=both;


System altered.


--建议的最低值为4,定义传播进程数,11GR2默认为1000

SQL> show parameter job_queue_process;    



NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     1000


--定义数据库的db_link连接数,保持默认值4


SQL> show parameter open_links


NAME                                 TYPE        VALUE

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

open_links                           integer     4

open_links_per_instance              integer     4

SQL>


SQL> show parameter statistics_level


NAME                                 TYPE        VALUE

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

statistics_level                     string      TYPICAL


SQL> show parameter aq


NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     0



SQL> alter system set aq_tm_processes=1 scope=both;


System altered.


QL> alter system set streams_pool_size=256M scope=spfile；


System altered.


SQL> alter system set "_job_queue_interval"=1 scope=spfile;


System altered.


上面参数修改完成后重启数据库生效


3，配置流复制用户

--创建表空间


SQL>  create tablespace streams_tbs datafile '/u01/datafile/streams_tbs.dbf' size 100m autoextend on next 1m maxsize unlimited;

Tablespace created.


-创建用户

SQL> create user stradmin identified by stradmin default tablespace streams_tbs ;


User created.


--赋予权限


SQL> grant dba to stradmin;


Grant succeeded.


begin

dbms_streams_auth.grant_admin_privilege(

grantee => 'stradmin',

grant_privileges => true);

end;


配置监听

--source db 配置


targetdb =

 (DESCRIPTION =

   (ADDRESS_LIST =

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

   )

   (CONNECT_DATA =

     (SERVICE_NAME = PREMIUMIT)

   )

 )


-- target db 配置


sourcedb =

 (DESCRIPTION =

   (ADDRESS_LIST =

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

   )

   (CONNECT_DATA =

     (SERVICE_NAME = PREMIUMIT)

   )

 )




--创建db link


-- source db


SQL> conn stradmin/stradmin

Connected.


SQL> create database link strm_link connect to stradmin identified by stradmin using 'targetdb';


Database link created.



SQL> select sysdate from dual@strm_link;


SYSDATE

---------

30-NOV-13




--创建测试用户（source 和target 同时创建）


SQL> create user test1 identified by test1;


User created.



SQL> grant connect,resource to test1;


Grant succeeded.


SQL> conn test1/test1

Connected.



SQL> create table test1 as select rownum id from dual;


Table created.



-- 创建source queue

BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE(

queue_table => 'SOURCE_QUEUE_TABLE',

queue_name  => 'SOURCE_QUEUE',

queue_user  => 'stradmin');          

END;

/


-- 在目标端创建接收队列

BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE(

queue_table => 'TARGET_QUEUE_TABLE',

queue_name  => 'TARGET_QUEUE',

queue_user  => 'stradmin');

END;

/




-- 创建source db 创建 capture进程


BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name     => 'test1.test1',

streams_type      => 'capture',

streams_name     => 'capture_stream',

queue_name       => 'stradmin.SOURCE_QUEUE',

include_dml       => true,

include_ddl        => true,

source_database => 'PREMIUMI',

include_tagged_lcr => false,

inclusion_rule     => true);

end;

/



--查看状态


SQL> col CAPTURE_NAME for a15;

SQL> col  QUEUE_NAME for a15;

SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;


CAPTURE_NAME    QUEUE_NAME       START_SCN STATUS   CAPTURE_TY

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

CAPTURE_STREAM  SOURCE_QUEUE       4467438 DISABLED LOCAL


SQL>



-- 创建传播进程

BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(

table_name                => 'test1.test1',

streams_name               => 'source_to_target',

source_queue_name          => 'stradmin.SOURCE_QUEUE',

destination_queue_name    => 'stradmin.TARGET_QUEUE@strm_link',

include_dml               => true,

include_ddl               => true,

source_database           => 'PREMIUMI',

inclusion_rule            => true,

queue_to_queue            => true);

END;

/


SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation;



PROPAGATION_NAM SOURCE_QUEUE_NA DESTINATION_QUE DESTINATION_DBL STATUS

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

SOURCE_TO_TARGE SOURCE_QUEUE    TARGET_QUEUE    STRM_LINK       ENABLED

T



-- TARGET DB 创建apply进程

BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name      => 'test1.test1',

streams_type    => 'apply',

streams_name    => 'target_apply_stream',

queue_name      => 'stradmin.TARGET_QUEUE',

include_dml     => true,

include_ddl     => true,

include_tagged_lcr => false,

source_database => 'PREMIUMI',

inclusion_rule => true);

END;

/


-- 查看apply进程状态

SQL> select apply_name,queue_name,status from dba_apply;


APPLY_NAME                     QUEUE_NAME                     STATUS

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

TARGET_APPLY_STREAM            TARGET_QUEUE                   DISABLED



-- 通过设置scn进行实例化

source db scn


SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;


DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()

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

                                 4469126


-- target db 执行过程

BEGIN

DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(

SOURCE_OBJECT_NAME => 'test1.test1',

source_database_name => 'PREMIUMI',

instantiation_scn => 4469126);

END;

/


-- 启动apply进程

BEGIN

DBMS_APPLY_ADM.START_APPLY(

apply_name => 'target_apply_stream');

END;

/



SQL> select apply_name,queue_name,status from dba_apply;


APPLY_NAME                     QUEUE_NAME                     STATUS

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

TARGET_APPLY_STREAM            TARGET_QUEUE                   ENABLED



-- 启动capture进程

BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name => 'capture_stream');

END;

/



SQL> select capture_name,status,CAPTURED_SCN,APPLIED_SCN from dba_capture;


CAPTURE_NAME    STATUS   CAPTURED_SCN APPLIED_SCN

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

CAPTURE_STREAM  ENABLED       4467438     4467438


-- 验证



--source db

SQL> select * from test1;


       ID

----------

        1


SQL> insert into test1 values (2);


-- tartget db



SQL> select * from test1;


       ID

----------

        1


SQL> /


       ID

----------

        1


SQL> /


       ID

----------

        1


SQL> /


       ID

----------

        1



SQL> /


       ID

----------

        1

        2


可以看出有一定的延迟

--ddl 同步

source db

SQL>  alter table test1 add name varchar2(10);


Table altered.



SQL> create table test2 (id int);


Table created.




target db

SQL>  desc test1;

Name                                      Null?    Type

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

ID                                                 NUMBER

NAME                                               VARCHAR2(10)


SQL> desc test2;

ERROR:

ORA-04043: object test2 does not exist