stream step-by-step

最新推荐文章于 2024-09-30 17:19:22 发布

cjysdw2000

最新推荐文章于 2024-09-30 17:19:22 发布

阅读量154

点赞数

文章标签：数据库

no time to read the manual but want to taste how stream works. here is what you can go through in 30min.[@more@]

stream step-by-step

1. prerequisite on both databases:

stzhao:pts/6:sambar:hdvl > sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 14 16:52:40 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL> alter system set global_names=true;

System altered.

SQL> alter system set job_queue_processes=10;

System altered.

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
SQL> show parameter stream_pool_size

SQL> alter system set streams_pool_size=30m;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/home/oracle/admin/hdvl/arch/arch_hdvl
Oldest online log sequence 235
Next log sequence to archive 238
Current log sequence 238

2. setup user and queue, link:

SQL> GRANT DBA TO strmadmin IDENTIFIED BY strmadminpw;

Grant succeeded.

SQL> ALTER USER strmadmin DEFAULT TABLESPACE sysaux
quota unlimited on sysaux;

User altered.

SQL> conn strmadmin/strmadminpw
Connected.
SQL> exec dbms_streams_adm.set_up_queue();

PL/SQL procedure successfully completed.

SQL> create database link htst connect to strmadmin
2 identified by strmadminpw using 'htst';

Database link created.

testing schema:

connected

SQL>
SQL> create table test_stream ( a number, b varchar2(200));

Table created.

SQL> insert into test_stream values (1,'stzhao');
SQL> insert into test_stream values (2,'stzhao');

1 row created.

SQL> commit;

Commit complete.

SQL> grant all on stzhao.test_stream to strmadmin;

Grant succeeded.

on target db:

SQL> GRANT DBA TO strmadmin IDENTIFIED BY strmadminpw;

Grant succeeded.

SQL> ALTER USER strmadmin DEFAULT TABLESPACE sysaux
quota unlimited on sysaux; 2

User altered.

SQL> conn strmadmin/strmadminpw
Connected.
SQL> exec dbms_streams_adm.set_up_queue();

PL/SQL procedure successfully completed.

Configure Propagation at source database

SQL> conn strmadmin/strmadminpw
Connected.

SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
3 table_name => 'stzhao.test_stream',
4 streams_name => 'hdvl_to_htst',
source_queue_name => 'strmadmin.streams_queue',
5 6 destination_queue_name => 'strmadmin.streams_queue@htst',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'hdvl',
10 inclusion_rule => true,
11 queue_to_queue => true);
12 END;
13 /

PL/SQL procedure successfully completed.

Configure the Capture Process at source database

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'stzhao.test_stream',
streams_type => 'capture',
streams_name => 'capture_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

Set the Instantiation SCN at target database

SQL> DECLARE
2 iscn NUMBER; -- Variable to hold instantiation SCN value
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@htst(
6 source_object_name => 'stzhao.test_stream',
7 source_database_name => 'hdvl',
instantiation_scn => iscn);
8 9 END;
10 /

PL/SQL procedure successfully completed.

Configure the Apply Process at target database

SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'stzhao.test_stream',
4 streams_type => 'apply',
5 streams_name => 'apply_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'hdvl',
6 7 8 9 10 inclusion_rule => true);
END;
11 12 /

PL/SQL procedure successfully completed.

Start the Apply Process at target database

SQL> BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_simp',
parameter => 'disable_on_error',
value => 'n');
END;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_simp');
END;
/
2 3 4 5

PL/SQL procedure successfully completed.

Start the Capture Process at source database

SQL> BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_simp');
END;
/ 2 3 4 5

PL/SQL procedure successfully completed.

start verfiy process

source:

SQL> conn stzhao/stzhao
Connected.
SQL> insert into test_stream values ( 2, 'heqing');