[笔记] 单向Table级streams环境搭建

以后再整理，先写上
1. Prepare
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 殿lter 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
-- strm2: connect as stream admin
conn strmadmin@strm2
strmadmin@STRM2> create database link strm1.com connect to strmadmin identified by nhy67ujm using 'strm1';
-- login as sys
sys@STRM2> select NAME,CTIME,USERID from sys.link$;
NAME       CTIME               USERID
---------- ------------------- ----------
STRM1.COM  2009/05/11 20:50:47 STRMADMIN


-- source
exec dbms_streams_adm.set_up_queue();
select name,queue_table from user_queues;
-- target
EXEC  DBMS_STREAMS_ADM.SET_UP_QUEUE();
-- s
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'dbausr.test_nolog',
    streams_name            => 'str1_to_str2',
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@strm2.com',
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'strm1.com',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/
select propagation_name,status from dba_propagation;

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name     => 'dbausr.test_nolog',  
    streams_type   => 'capture',
    streams_name   => 'capture_simp',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    => true,
    include_ddl    => true,
    inclusion_rule => true);
END;
/
select capture_name,status from dba_capture;
-- t
DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@strm2.com(
    source_object_name    => 'dbausr.test_nolog',
    source_database_name  => 'strm1.com',
    instantiation_scn     => iscn);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'dbausr.test_nolog',
    streams_type    => 'apply',
    streams_name    => 'apply_simp',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'strm1.com',
    inclusion_rule  => true);
END;
/
select apply_name,status from dba_apply;

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_simp',
    parameter   => 'disable_on_error',
    value       => 'n');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_simp');
END;
/

-- s
BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_simp');
END;
/
select capture_name,status from dba_capture;

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

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