Oracle  Stream 配置文档

环境：
OS:Redhat Linux
Oracle 10.2.0.4

source:stream1
destnation:stream2

==准备==
1.归档日志配置：
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/stream1_arch

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/stream2_arch

---以下没有专门说明的都是sys用户---

2.在源stream1上配置Supplemental loging：（启用追加日志）

在源上：
SQL> alter database add supplemental log data;

Database altered.

3.两台机器上修改global_name:

stream1:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to stream1.com;

stream2:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to stream2.com;

4.修改初始化参数：
参考官方文档确定需要来检查和设定参数

alter system set aq_tm_processes=2 scope=both;
alter system set global_names=true scope=both;
alter system set job_queue_processes=10 scope=both;
alter system set parallel_max_servers=20 scope=both;
alter system set undo_retention=3600 scope=both;
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
alter system set streams_pool_size=1500M scope=spfile;
alter system set utl_file_dir='*' scope=spfile;
alter system set open_links=4 scope=spfile;

5.创建stream管理用户并表空间，配置权限，source和dest：
5.1 为strmadmin用户创建独立表空间

stream1:

SQL> CREATE TABLESPACE strm_tbs DATAFILE '/data/oracle/oradata/ora10g/strm_tbs.dbf'
   SIZE 125M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace created.

stream2:
SQL> CREATE TABLESPACE strm_tbs DATAFILE '/data/oracle/oradata/meganote/strm_tbs.dbf'
    SIZE 125M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace created.

5.2 source和dest创建相同用户和权限：
SQL> CREATE USER strmadmin IDENTIFIED BY lhy
     DEFAULT TABLESPACE strm_tbs
     QUOTA UNLIMITED ON strm_tbs;

User created.

SQL> GRANT DBA TO strmadmin;

Grant succeeded.

SQL> BEGIN
    DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
      grantee          => 'strmadmin',   
      grant_privileges => true);
 END;
 /

PL/SQL procedure successfully completed.

6.配置listener.ora和tnsnames.ora保证：（service naming后面要用到，必须保证netmgr测试成功才可继续进行）

stream1上：sqlplus system/sys@stream2 (密码根据实际情况适当修改)
stream2上：sqlplus system/sys@stream1
能够连通

---如果没有说明以下都是strmadmin/strmadminpw 用户---

7.创建DB link:
单向复制之需要一个database link即source 到dest:stream1-->stream2

stream1上：
sqlplus strmadmin/strmadminpw
SQL> CREATE DATABASE LINK stream2.com CONNECT TO strmadmin IDENTIFIED BY lhy   strmjils  USING 'stream2';

Database link created.
测试：
SQL> select * from global_name@stream2.com;

（Stream2即为在netmgr中的service naming，必须一致。）

GLOBAL_NAME
-----------------------------------------
STREAM2.COM

==开始stream的配置===

8. source和dest 创建队列：

stream1,stream2都要创建
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

上面命令会创建一个队列缺省名：streams_queue，队列表缺省是：STREAMS_QUEUE_TABLE
队列存储的object类型是anaydata

可以用查询dba_queues,dba_queue_tables来检查：
SQL> select owner,queue_table,name from dba_queues where wner='STRMADMIN';

OWNER           QUEUE_TABLE                     NAME
----------------   ----------------------------------   ---------------------------------------------
STRMADMIN    STREAMS_QUEUE_TABLE    STREAMS_QUEUE
STRMADMIN    STREAMS_QUEUE_TABLE    AQ$_STREAMS_QUEUE_TABLE_E

SQL> select owner,queue_table,object_type from dba_queue_tables where wner='STRMADMIN';

OWNER            QUEUE_TABLE                        OBJECT_TYPE
-----------------   ---------------------------------      -------------------------------
STRMADMIN    STREAMS_QUEUE_TABLE       SYS.ANYDATA

-----------------------创建其他队列

begin
dbms_streams_adm.set_up_queue(
queue_table => 'target_queue_table',
queue_name => 'target_queue');
end;
/

9. 在source: stream1上创建Stream propagation：

SQL> BEGIN
 DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
 schema_name                    => 'meganotes',
 streams_name                   => 'stream1_stream2',
 source_queue_name         => 'strmadmin.streams_queue',
 destination_queue_name => 'strmadmin.streams_queue@stream2.com',
 include_dml                        => true,
 include_ddl                       => true,
 source_database              => 'stream1.com',
 inclusion_rule                    => true,
 queue_to_queue              => true);
 END;
 /

PL/SQL procedure successfully completed.

可以通过dba_propagations查看结果：
SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation;

PROPAGATION_NAME      SOURCE_QUEUE_NAME   DESTINATION_QUEUE_NAME   DESTINATION_DBL   STATUS
-------------------------------- ------------------------------    --------------------------------- --------------- --------
STREAM1_TO_STREAM2 STREAMS_QUEUE            STREAMS_QUEUE              STREAM2.COM     ENABLED

9.在source: stream1上创建Capture进程：

SQL> BEGIN
    DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
      schema_name     => 'meganotes',  
     streams_type      => 'capture',
      streams_name     => 'capture02',
      queue_name       => 'strmadmin.streams_queue',
     include_dml         => true,
      include_ddl         => true,
      inclusion_rule     => true);
 END;
 /

PL/SQL procedure successfully completed.

可以通过dba_capture查看：
SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;

CAPTURE_NAME            QUEUE_NAME            START_SCN   STATUS   CAPTURE_TY
----------------------------   -------------------------- --------------- ------------ ----------
CAPTURE_STREAM1     STREAMS_QUEUE      504733         DISABLED   LOCAL

SQL> select * from ALL_CAPTURE_PREPARED_SCHEMAS;                           
                                                                           
SCHEMA_NAME   TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME
-------------------- ---------------    -------------- -------------- -------------   --------
HR                      12-JUN-08      IMPLICIT       IMPLICIT     IMPLICIT     NO     

Capture parameter:

exec dbms_capture_adm.set_parameter('capture02','_SGA_SIZE',300);

exec dbms_capture_adm.set_parameter('capture02','_CHEcKPOINT_FREQUENCY',550);

10.将stream1上的scott schema数据导到stream2上：
在stream2上：
sqlplus system/sys
SQL> create public database link stream1.com connect to system identified by ora10g2 using 'stream1';

Database link created.

此处若出错，可用drop命令删除链接，然后重建

SQL> select * from global_name@stream1.com;

GLOBAL_NAME
------------------------------------------------------
STREAM1.COM

导数据，必须迁移此用户进行同步数据的初始化，否则无法保证两边的数据相同：
oracle@node2 ~]$ impdp system/ora10g2 network_link=stream1.com schemas=meganotes exclude=statistics logfile=1.log parallel=3

Import: Release 10.2.0.2.0 - Production on Thursday, 12 June, 2008 17:47:59

Copyright (c) 2003, 2005, Oracle. All rights reserved.
.....
......
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:50:32

11. 在stream1上设置stream2上meganotes schema的Instantiation SCN

sqlplus strmadmin/strmadminpw
SQL> DECLARE
    iscn NUMBER;        
    BEGIN
    iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
    DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@stream2.com(
     source_schema_name    => 'meganotes',
    source_database_name => 'stream1.com',
    instantiation_scn     => iscn,
     recursive             => true);
 END;
 /

PL/SQL procedure successfully completed.

12.在stream2上创建apply进程apply_stream2：

SQL> BEGIN
   DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
      schema_name      => 'meganotes',
      streams_type    => 'apply',
      streams_name    => 'apply02',
      queue_name      => 'strmadmin.streams_queue',
      include_dml     => true,
      include_ddl     => true,
      source_database => 'stream1.com',
      inclusion_rule => true);
 END;
 /

PL/SQL procedure successfully completed.

可以通过：
dba_apply
v$streams_apply_reader
v$streams_apply_coordinator
v$streams_apply_server
查看状态

SQL> select apply_name,queue_name,status from dba_apply;

APPLY_NAME          QUEUE_NAME            STATUS
---------------------      ------------------------   --------
APPLY_STREAM2    STREAMS_QUEUE       DISABLED

Apply parameter:

exec dbms_apply_adm.set_parameter('apply_stream2', 'TXN_LCR_SPILL_THRESHOLD', 80000);

exec dbms_apply_adm.set_parameter('apply_stream2', '_TXN_BUFFER_SIZE', 2);

exec dbms_apply_adm.set_parameter('apply_stream2', '_HASH_TABLE_SIZE', 1000000);

exec dbms_apply_adm.set_parameter('apply_stream2', '_RESTRICT_ALL_REF_CONS','N');

exec dbms_apply_adm.set_parameter('apply_stream2','_SGA_SIZE','300');

exec dbms_apply_adm.set_parameter('apply_stream2','PARALLELISM','4');

==启动==

13.启动capture和apply:

13.1 Stream2上启动 Apply Process

CONNECT strmadmin/strmadminpw
SQL> BEGIN
    DBMS_APPLY_ADM.SET_PARAMETER(
      apply_name => 'apply02',
      parameter   => 'disable_on_error',
      value       => 'n');
 END;
 /

PL/SQL procedure successfully completed.

SQL> BEGIN
    DBMS_APPLY_ADM.START_APPLY(
      apply_name => 'apply02');
 END;
 /

PL/SQL procedure successfully completed.

SQL> select apply_name,queue_name,status from dba_apply;

APPLY_NAME                   QUEUE_NAME               STATUS
------------------------------ --------------------------      --------
APPLY_STREAM2            STREAMS_QUEUE         ENABLED

这时候alert log有：
Thu Jun 12 18:00:36 2008
Streams APPLY A001 started with pid=25, OS id=30819
Streams Apply Reader started P000 with pid=26 OS id=30821
Streams Apply Server started P001 with pid=27 OS id=30823

如果有问题，没有能启动就查dba_apply的error_messages列

13.2 stream1上启动capture process:

sqlplus strmadmin/strmadminpw
SQL> BEGIN
    DBMS_CAPTURE_ADM.START_CAPTURE(
      capture_name => 'capture02');
 END;
 /

PL/SQL procedure successfully completed.

SQL> select capture_name,status from dba_capture;

CAPTURE_NAME              STATUS
------------------------------    ------------
CAPTURE_STREAM1       ENABLED

alert 日志有：
Thu Jun 12 18:04:46 2008
Streams CAPTURE C001 started with pid=27, OS id=11884
Thu Jun 12 18:04:49 2008
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 1
LOGMINER: Memory Size = 10M, Checkpoint interval = 10M
LOGMINER: session# = 1, builder process P001 started with pid=32 OS id=11966
LOGMINER: session# = 1, reader process P000 started with pid=29 OS id=11964
: session# = 1, preparer process P002 started with pid=33 OS id=11968
Thu Jun 12 18:04:53 2008
LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf
Thu Jun 12 18:06:40 2008
Thread 1 advanced to log sequence 15
Current log# 2 seq# 15 mem# 0: /u01/oradata/stream1/redo02.log
Thu Jun 12 18:06:53 2008
Thread 1 advanced to log sequence 16
Current log# 3 seq# 16 mem# 0: /u01/oradata/stream1/redo03.log
...
LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf
Thu Jun 12 18:07:34 2008
LOGMINER: End mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf
Thu Jun 12 18:07:34 2008
LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_14_657197948.dbf

信息，说明已经开始mining logfile.