Re: Oracle streams

Aha...someone else who is struggling with streams (like I am).

Ok, I've got it working on RHAS 4 and 10gR2.  The setup I have is bi-directional between two test tables, test1 and test2 on ORCL1 and test1 and test2 on ORCL2 (btw, my example shows Win paths.  I have it working on both RHAS 4 and WinXP.  Was just easier to cut-n-paste from my notepad notes).

I modified my spfile to include these params on both instances:
compatible=10.2.0
global_names=true
job_queue_processes=2
aq_tm_processes=1
parallel_max_servers=10
streams_pool_size=200M
open_links=4
undo_retention=900
_job_queue_interval=1

Next, on ORCL1 I created a db directory:
create or replace directory SOURCE_DIRECTORY
as 'C:\oracle\product\10.2.0\admin\orcl2\dpdump';

I created the strmadmin user on both ORCL1 and ORCL2:

-- orcl1
create tablespace strm01
datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\strm01.dbf' size 50m
autoextend on next 10m 
maxsize 1000m
extent management local;

-- orcl2
create tablespace strm01
datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\strm01.dbf' size 50m
autoextend on next 10m 
maxsize 1000m
extent management local;

Then granted the appropriate privs:
grant connect, resource, aq_administrator_role, dba to strmadmin;

exec sys.dbms_streams_auth.grant_admin_privilege('strmadmin');

Created a test user on both ORCL1 and ORCL2
create user scott identified by tiger
default tablespace users;

grant connect to scott;
grant create table to scott;
alter user scott
quota unlimited on users;

Then on both ORCL1 and ORCL2, created a separate LOGMNR tablespace:
-- orcl1
create tablespace logmnrts 
datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\logmnrts.dbf' size 25m
autoextend on maxsize 1000M;

-- orcl2
create tablespace logmnrts 
datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\logmnrts.dbf' size 25m
autoextend on maxsize 1000M;

begin
   dbms_logmnr_d.set_tablespace('logmnrts');
end;
/

Created the db links as strmadmin:
-- on orcl1 as strmadmin 
conn strmadmin@orcl1
select * from global_name;
CREATE DATABASE LINK orcl2 CONNECT TO strmadmin IDENTIFIED BY strmadmin
USING 'orcl2';

-- on orcl2 as strmadmin 
conn strmadmin@orcl2
select * from global_name;
CREATE DATABASE LINK orcl1 CONNECT TO strmadmin IDENTIFIED BY strmadmin
USING 'orcl1';

Created a couple of test tables on ORCL1 as SCOTT:
conn scott@orcl1

create table test1
(col1 number primary key,
 col2 varchar2(5),
 col3 timestamp);

create table test2
(col1 number primary key,
 col2 number,
 col3 timestamp);

Now, the  fun part.  I generated the streams scripts on ORCL1 using the strmadmin account as follows:

DECLARE
tables DBMS_UTILITY.UNCL_ARRAY;
BEGIN

tables(1) := 'scott.test1';
tables(2) := 'scott.test2';

DBMS_STREAMS_ADM.MAINTAIN_TABLES(
table_names => tables,
source_directory_object => NULL,
destination_directory_object => NULL,
source_database => 'orcl1',
destination_database => 'orcl2',
perform_actions => false,
script_name => 'configure_rep.sql',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => NULL,
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => NULL,
capture_name => 'capture_pkiuser',
propagation_name => 'prop_pkiuser',
apply_name => 'apply_pkiuser',
script_directory_object => 'SOURCE_DIRECTORY',
bi_directional => true,
include_ddl => true,
instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
END;
/

A script called configure_rep.sql will be generated in the SOURCE_DIRECTORY, which in my case was C:\oracle\product\10.2.0\admin\orcl2\dpdump.

View the script.  If you run it, it should set up bidirectional replication between ORCL1 and ORCL2 for the SCOTT schema.  It's worked so far.  I'm now trying to get conflict resolution working.

You'll need to read up on Oracle Streams Replication Administrator¢s Guide and Oracle Streams Concepts and Administration.  Some good info there.  Also, metalink is a good resource.  Sorry, no references since I didn't keep tabs on what docs I used.

hth

--
mohammed
----- Original Message ----

From: "Fedock, John (KAM.RIC)" <John.Fedock@us.kline.com>
To: oracle-l@freelists.org
Sent: Thursday, October 18, 2007 11:26:36 AM
Subject: Oracle streams





 
 

 

 


<!--

 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;text-decoration:underline;}
span.EmailStyle17
	{font-family:Arial;color:windowtext;}
 _filtered {margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{}
-->






Looking to see if people are successfully using streams to
move data between 2 databases.  This would be on 10g, HP-UX machines. 

 

  
 

I have attempted a few test cases and have not been able to
get the data to move over.  I am still not grasping creating the capture, adding
table rules, creating apply process, instantiating the scn, etc.  
 

  
 

Does anyone have a checklist on which task to do on what
server (source vs destination)?  I have been looking at metalink note
298877.1 and the session 249 from collaborate 2007.  
 

  
 

  
 

Thanks all,
 

  
 

John Fedock
 

"K" Line
 America ,
ISD Department
 

  
 










       
____________________________________________________________________________________
Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

--
http://www.freelists.org/webpage/oracle-l