基于用户的流复制实验

  

	源端	目标端
IP	172.17.61.131	172.17.61.132
ORACLE_SID	ORCL	ORCL
GLOBAL_NAMES	ORCl1	ORCL2

 

 目标是同步LJG下所有的对象。

 

1.   准备工作

a)     源数据库和目标数据库必须是归档的

 

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> archivelog list

b)   源库与目标库上配置追加日志：

SQL> alter database add supplemental log data;

c)  源库和目标库需要设置如下参数：

alter system setglobal_names=true scope=both;//可以也设置为flase,不过就必须得设置db_unique_name，因为两台数据库的sid都是orcl

   alter system set aq_tm_processes=2 scope=both;

    以下参数都是10g默认值，检查下就可以了，无需设置：

   show parameter job_queue_processes

   alter system set job_queue_processes=10 scope=both;

   alter system set sga_target=300m scope=spfile;

   alter system set open_links=4 scope=spfile;

   alter system set staistics_level='TYPICAL' scope=both;

   alter system set logmnr_max_persistent_sessions=1 scope=spfile;

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

   alter system set aq_tm_processes=1;

   alter system set streams_pool_size=200m scope=both;

 

d)  源库和目标库创建相同表空间和用户并授予权限：

       i.     创建表空间

SQL> create tablespace streams_tbs datafile'/u01/app/oracle/oradata/orcl/streams_tbs01.dbf' size 100m autoextend onmaxsize unlimited;

      ii.     创建用户

SQL> create user strmadminidentified by strmadmin default tablespace streams_tbs quota unlimited onstreams_tbs;

    iii.     给用户授权

SQL> grant dba to strmadmin;

SQL> execdbms_streams_auth.grant_admin_privilege('strmadmin');

 

e)  源库和目标库创建监听，并配置tns，可以互相访问

 

[oracle@rhel131 admin]$ tnsping orcl131

[oracle@rhel131 admin]$ tnsping orcl132

 

f)  源库和目标库都创建db link，可以互相访问：

 

由于设置了global_names=true，所以db link的名字必须与目标端的global_name名字一致。

http://blog.csdn.net/jolly10/article/details/11117421

源端：

SQL> conn strmadmin/strmadmin

SQL> create database link orcl2 connect to strmadminidentified by strmadmin using 'orcl132';

 

目标端：

SQL> conn strmadmin/strmadmin

SQL> create database link orcl1 connect to strmadminidentified by strmadmin using 'orcl131';

 

g)  由于要建立用户级的同步，所以在目标端和源端建立测试用户：

SQL> create user ljg identified by ljg;

 

SQL> grant dba to ljg;

 

2.   创建source队列：

 

a)     在源库上建立source队列：

SQL> begin

dbms_streams_adm.set_up_queue(

queue_table=>'SOURCE_QUEUE_TABLE',

queue_name=>'SOURCE_QUEUE',

queue_user=>'strmadmin');

end;

/

PL/SQL procedure successfully completed.

 

查看创建队列的信息：

SQL> select owner,queue_table,name from   dba_queues where  owner='STRMADMIN';

 

OWNER                          QUEUE_TABLE                    NAME

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

STRMADMIN                      SOURCE_QUEUE_TABLE             SOURCE_QUEUE

STRMADMIN                      SOURCE_QUEUE_TABLE             AQ$_SOURCE_QUEUE_TABLE_E

 

b)    在目标库上创建接收队列：

 

 

SQL> begin

dbms_streams_adm.set_up_queue(

queue_table=>'TARGET_QUEUE_TABLE',

queue_name=>'TARGET_QUEUE',

queue_user=>'strmadmin');

end;

/

PL/SQL proceduresuccessfully completed.

 

SQL> selectowner,queue_table,name from   dba_queueswhere  owner='STRMADMIN';

 

OWNER                          QUEUE_TABLE                    NAME

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

STRMADMIN                      TARGET_QUEUE_TABLE             TARGET_QUEUE

STRMADMIN                      TARGET_QUEUE_TABLE             AQ$_TARGET_QUEUE_TABLE_E

 

3.   源库上创建capture进程

 

SQL> begin

  2 dbms_streams_adm.add_schema_rules(

  3 schema_name=>'LJG',

  4 streams_type=>'capture',

  5 streams_name=>'capture_stream',

  6 queue_name=>'strmadmin.SOURCE_QUEUE',

  7 include_dml=>true,

  8 include_ddl=>true,

9 source_database=>'ORCL1',

  10  include_tagged_lcr=>false,

 11 inclusion_rule=>true);

 12  end;

 13  /

 

可以通过dba_capture查看：

SQL> selectCAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;

 

CAPTURE_NAME                   QUEUE_NAME                      START_SCN STATUS   CAPTURE_TY

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

CAPTURE_STREAM                 SOURCE_QUEUE                       587185 DISABLED LOCAL

 

SQL> select *from  ALL_CAPTURE_PREPARED_SCHEMAS;

 

SCHEMA_NAME                    TIMESTAMP SUPPLEME SUPPLEMESUPPLEME SUPPLEME

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

LJG                            05-SEP-13 IMPLICITIMPLICIT IMPLICIT NO

 

 

4.   在源库上创建传播进程：

SQL> begin

  2 dbms_streams_adm.add_schema_propagation_rules(

  3 schema_name=>'LJG',

  4 streams_name=>'source_to_target',

  5 source_queue_name=>'strmadmin.SOURCE_QUEUE',

  6 destination_queue_name=>'strmadmin.TARGET_QUEUE@ORCL2',

  7 include_dml=>true,

  8 include_ddl=>true,

  9 source_database=>'ORCL1',

 10 inclusion_rule=>true,

 11 queue_to_queue=>true);

 12  end;

 13  /

 

PL/SQL proceduresuccessfully completed.

 

查看propagation进程的信息：

  selectpropagation_name,status from all_propagation;

 启动和关闭propagation进程的命令：

            execdbms_propagation_adm.start_propagation('source_to_target');  

            execdbms_propagation_adm.stop_propagation('source_to_target');

修改propagation休眠时间为0，表示实时传播LCR：

SQL> begin

dbms_aqadm.alter_propagation_schedule(

queue_name=>'SOURCE_QUEUE',

destination=>'ORCL2',

destination_queue=>'TARGET_QUEUE',

latency=>0);

end;

/

PL/SQL proceduresuccessfully completed.

 

 

5.   在目标库上创建APPLY进程：

SQL> begin

  2 dbms_streams_adm.add_schema_rules(

  3 schema_name=>'LJG',

  4 streams_type=>'apply',

  5 streams_name=>'target_apply_stream',

  6 queue_name=>'strmadmin.TARGET_QUEUE',

  7 include_dml=>true,

  8 include_ddl=>true,

  9 source_database=>'ORCL1',

 10 include_tagged_lcr=>false,

 11 inclusion_rule=>true);

 12  end;

 13  /

 

PL/SQL proceduresuccessfully completed.

 

查看apply进程：

SQL> select apply_name,queue_name,statusfrom dba_apply;

 

APPLY_NAME                     QUEUE_NAME                     STATUS

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

TARGET_APPLY_STREAM            TARGET_QUEUE                   DISABLED

 

还有相关视图：v$streams_apply_reader,

                   v$streams_apply_coordinator,

                  v$streams_apply_server

 

6.   设置SCN的方式进程源库与目标库的同步：

查看源库当前的scn号：

SQL>  selectdbms_flashback.get_system_change_number() from dual;

 

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()

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

                                   589986

 

 

设置目标端开启同步的SCN:

SQL> begin

dbms_apply_adm.set_schema_instantiatiOn_scn(

source_schema_name=>'LJG',

source_database_name=>'ORCL1',

instantiation_scn=>'589986');

end;

/    

PL/SQL procedure successfully completed.

 

7.   在目标库启动apply进程：

 

SQL> begin

dbms_apply_adm.start_apply(

apply_name=>'target_apply_stream');

end;

 /

SQL> selectapply_name,queue_name,status,apply_captured from dba_apply;

 

APPLY_NAME                     QUEUE_NAME                     STATUS   APP

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

TARGET_APPLY_STREAM            TARGET_QUEUE                   ENABLED  YES

 

 

8.   在源库上启动capture

SQL> begin

dbms_capture_adm.start_capture(

capture_name=>'capture_stream');

end;

/  2    3   4    5

 

PL/SQL procedure successfully completed.

 

SQL>   selectcapture_name,status from dba_capture;

 

CAPTURE_NAME                   STATUS

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

CAPTURE_STREAM                 ENABLED

 

 

9.   最后测试一下

源端：

SQL> conn ljg/ljg

Connected.

SQL> create tablet (id int primary key);

 

Table created.

 

目标端：

SQL> select * fromtab;

 

TNAME                          TABTYPE  CLUSTERID

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

T                              TABLE

 

成功了！

 

查看各进程状态的sql:

 

selectCAPTURE_NAME,QUEUE_NAME,START_SCN,APPLIED_SCN,STATUS,CAPTURE_TYPE fromdba_capture;

 

selectpropagation_name,source_queue_name,destination_queue_name,destination_dblink,statusfrom dba_propagation;

 

select apply_name,queue_name,status,apply_capturedfrom dba_apply;