In this Streams test environment, there are two databases called “strm1” and “strm2”, whose global name are “strm1.com” and “strm2.com” respectively. The source side is “strm1”, and target side is “strm2”. The synchronization level is SCHEMA level and single-directions replication.
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 “alter 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
4.1 Configure streams queue in both sides. (Afterwards, if not specially mentioned, the user is always “strmadmin”)
-- strm1
begin
dbms_streams_adm.set_up_queue(
queue_table => 'strm1_cap_queue_table',
queue_name => 'strm1_cap_queue');
end;
/
-- confirm
strmadmin@STRM1> select name,queue_table from user_queues;
NAME QUEUE_TABLE
--------------------------- ---------------------
AQ$_STRM1_CAP_QUEUE_TABLE_E STRM1_CAP_QUEUE_TABLE
STRM1_CAP_QUEUE STRM1_CAP_QUEUE_TABLE
-- strm2
begin
dbms_streams_adm.set_up_queue(
queue_table => 'strm2_apl_queue_table',
queue_name => 'strm2_apl_queue');
end;
/
-- confirm
strmadmin@STRM2> select name,queue_table from user_queues;
NAME QUEUE_TABLE
--------------------------- ---------------------
AQ$_STRM2_APL_QUEUE_TABLE_E STRM2_APL_QUEUE_TABLE
STRM2_APL_QUEUE STRM2_APL_QUEUE_TABLE
Note: Program of removing queue is as below:
begin
dbms_streams_adm.remove_queue(
queue_name => 'strm1_cap_queue',
cascade => true);
end;
/
4.2 Create capture process in strm1 side
-- strm1: set up capture process
-- before setup, minimal supplemental log is disabled
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?
------------ ------------ ------------ ------------ ------------
NO NO NO NO NO
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'dbausr',
streams_type => 'capture',
streams_name => 'strm1_capture',
queue_name => 'strmadmin.strm1_cap_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/
-- after set up, supplemental log is enabled automatically
Minimum Primary Key Foreign Key Unique All Columns
Supplemental Supplemental Supplemental Supplemental Supplemental
Logging? Logging? Logging? Logging? Logging?
------------ ------------ ------------ ------------ ------------
YES NO NO NO NO
5. Duplicate DBAUSR schema from source to target
5.1 Duplicate by using exp/imp
-- make sure DBAUSR user exists in target side, or else create it
strmadmin@STRM2> create user dbausr identified by nhy67ujm default tablespace users temporary tablespace temp;
-- export DBAUSR schema from source side
exp userid=dbausr/nhy67ujm@strm1 file='/tmp/dbausr.dmp' object_consistent=y rows=y
-- import to target side
imp userid=system/nhy67ujm@strm2 file='/tmp/dbausr.dmp' ignore=y commit=y log='/tmp/dbausr.log' streams_instantiation=y fromuser=dbausr touser=dbausr
6 Create propagation/apply rules
6.1 Create propagation rules
-- strm1: set propagation rule
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'dbausr',
streams_name => 'strm1_to_strm2',
source_queue_name => 'strmadmin.strm1_cap_queue',
destination_queue_name => 'strmadmin.strm2_apl_queue@strm2.com',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'strm1.com',
inclusion_rule => true);
end;
/
-- change the latency to 0, which means real time propagation
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'strm1_cap_queue',
destination => 'strm2',
latency => 0);
end;
/
6.2 Create apply process in target side
-- strm2 : set apply rule
-- before setup, minimal supplemental log is disabled
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?
------------ ------------ ------------ ------------ ------------
NO NO NO NO NO
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'dbausr',
streams_type => 'apply',
streams_name => 'strm2_apply',
queue_name => 'strmadmin.strm2_apl_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'strm1',
inclusion_rule => true);
end;
/
-- after set up, supplemental log is not enabled automatically
Minimum Primary Key Foreign Key Unique All Columns
Supplemental Supplemental Supplemental Supplemental Supplemental
Logging? Logging? Logging? Logging? Logging?
------------ ------------ ------------ ------------ ------------
NO NO NO NO NO
7. Start/stop streams and verificating its functionality
7.1 Start streams
-- strm2: start apply
begin
dbms_apply_adm.start_apply(
apply_name => 'strm2_apply');
end;
/
-- strm1 : start capture
begin
dbms_capture_adm.start_capture(
capture_name => 'strm1_capture');
end;
/
7.2 Verification
-- [DDL Test] strm1: Before create
dbausr@STRM1> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
-- strm2 :
strmadmin@STRM2> select count(*) from dbausr.test;
select count(*) from dbausr.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
-- [DDL Test] strm1: After create
dbausr@STRM1> create table test as select * from emp;
Table created.
dbausr@STRM1> select count(*) from test;
COUNT(*)
----------
14
-- strm2 :
strmadmin@STRM2> select count(*) from dbausr.test;
COUNT(*)
----------
14
-- [DML Test] strm1: Before insert
dbausr@STRM1> select EMPNO,ENAME from dbausr.test where job='ANALYST';
EMPNO ENAME
---------- ----------
7788 SCOTT
7902 FORD
-- strm2 :
strmadmin@STRM2> select EMPNO,ENAME from dbausr.test where job='ANALYST';
EMPNO ENAME
---------- ----------
7788 SCOTT
7902 FORD
-- [DML Test] strm1: After insert
dbausr@STRM1> insert into test values ('9900','TEST','ANALYST',7566,sysdate,3000,0,20);
dbausr@STRM1> commit;
dbausr@STRM1> select EMPNO,ENAME from dbausr.test where job='ANALYST';
EMPNO ENAME
---------- ----------
7788 SCOTT
7902 FORD
9900 TEST
-- strm2 :
-- hint, it will take some seconds to reflect the change
strmadmin@STRM2> select EMPNO,ENAME from dbausr.test where job='ANALYST';
EMPNO ENAME
---------- ----------
7788 SCOTT
7902 FORD
9900 TEST
7.3 Stop streams
-- strm1: stop capture
begin
dbms_capture_adm.stop_capture(
capture_name => 'strm1_capture');
end;
/
select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
------------------------------ --------
STRM1_CAPTURE DISABLED
-- stop propagation
begin
dbms_propagation_adm.stop_propagation(
propagation_name => 'strm1_to_strm2',
force => false);
end;
/
select propagation_name,status from dba_propagation;
PROPAGATION_NAME STATUS
------------------------------ --------
STRM1_TO_STRM2 DISABLED
-- strm2: stop apply
begin
dbms_apply_adm.stop_apply(
apply_name => 'strm2_apply');
end;
/
select apply_name,status from dba_apply;
APPLY_NAME STATUS
------------------------------ --------
STRM2_APPLY DISABLED
8. Remove and clear streams environment
-- strm1
exec DBMS_STREAMS_ADM.remove_streams_configuration();
-- target
exec DBMS_STREAMS_ADM.remove_streams_configuration();
Note: If you want remove particular capture/propagation/apply process,
use dbms_capture(/propagation/apply) _adm.drop_capture(/propagation/apply)
begin
dbms_capture_adm.drop_capture(
capture_name => '',
drop_unused_rule_sets => true);
end;
/
begin
dbms_propagation_adm.drop_propagation(
propagation_name => '',
drop_unused_rule_sets => true);
end;
/
begin
dbms_apply_adm.drop_apply(
apply_name => '',
drop_unused_rule_sets => true);
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/317003/viewspace-598151/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/317003/viewspace-598151/