Home » Server Options » Streams & AQ » Trying to use streams to replicate tables on same machine (10g,10.2.0.1.0,Fedora 9 Sulphur)
Trying to use streams to replicate tables on same machine [message #363030] Thu, 04 December 2008 17:29 Go to next message
y2raza
Messages: 34
Registered: March 2008
Member
i have been trying to replicate DDL,DML between 2 databases which reside on same machine & am following the notes from this link:

http://dbataj.blogspot.com/2008/01/oracle-streams-setup-between-two.html

unfortunately the replication is not working at all any help will be appreciated.

note i am keeping the global_names false since both DBs reside on same machine!

[Updated on: Thu, 04 December 2008 17:30]

Report message to a moderator

Re: Trying to use streams to replicate tables on same machine [message #363041 is a reply to message #363030] Thu, 04 December 2008 23:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My car does not work.
Help me to make my car works.

Do you realize that "unfortunately the replication is not working" does help us in any way to know why "it is not working".

Regards
Michel
Re: Trying to use streams to replicate tables on same machine [message #363241 is a reply to message #363030] Fri, 05 December 2008 09:15 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member
i have linked the procedure i am following, the thing is when i do DDL & DML operations on a table which resides on DB1, the changes are not applied to the table which sits on the second database DB2.

note that both databases are on the same machine,

i have followed the instructions to the tee,
Re: Trying to use streams to replicate tables on same machine [message #363246 is a reply to message #363241] Fri, 05 December 2008 09:31 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Please provide more details the only we can give you some
solution.This is what Michel explained you.
Regards,
Varun Punj
Re: Trying to use streams to replicate tables on same machine [message #363248 is a reply to message #363246] Fri, 05 December 2008 09:38 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member

ok as i have stated above in my post, the Oracle Stream is setup to replicate any DDL & DML on TABLE A on DATABASE A to TABLE B on DATABASE B, & both Databases sit on same machine, after making changes & committing on TABLE A of DATABASE A, the changes are not showing up on TABLE B of DATABASE B, so now i can not be more specific than that, unless you can point out to something which i can look into!!!

please note i am a JUNIOR member
Re: Trying to use streams to replicate tables on same machine [message #363255 is a reply to message #363248] Fri, 05 December 2008 09:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> unless you can point out to something which i can look into
dba_apply/v$streams_capture/alertlogs
Re: Trying to use streams to replicate tables on same machine [message #363263 is a reply to message #363030] Fri, 05 December 2008 10:31 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member
SQL> select apply_name,queue_name,rule_set_name,status from dba_apply;

APPLY_NAME	  QUEUE_NAME 	      RULE_SET_NAME 	     STATUS
-------------------- -------------------- ------------------------- --------
APPLY_STREAM	  STREAMS_QUEUE	  RULESET$_32	     ENABLED

SQL>SELECT CAPTURE_NAME,
        LOGMINER_ID,
        AVAILABLE_MESSAGE_NUMBER,
        TO_CHAR(AVAILABLE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') 
AVAILABLE_MESSAGE_CREATE_TIME
  6    FROM V$STREAMS_CAPTURE;

Capture 				                              Time of
Name		     LogMiner ID Last Redo SCN Last Redo SCN
--------------------       -----------  -------------       -----------------
CAPTURE_STREAM	      44       2241143         10:28:43 12/05/08


[Updated on: Fri, 05 December 2008 13:00] by Moderator

Report message to a moderator

Re: Trying to use streams to replicate tables on same machine [message #363264 is a reply to message #363030] Fri, 05 December 2008 10:32 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member
SQL> 
SELECT CAPTURE_NAME,
       PARAMETER, 
       VALUE,
       SET_BY_USER  
  FROM DBA_CAPTURE_PARAMETERS;TERS;


Capture
Process
Name		Parameter	     Value		  Set by User?
--------------- -------------------- -------------------- --------------------
CAPTURE_STREAM	PARALLELISM	     1			  NO
CAPTURE_STREAM	STARTUP_SECONDS      0			  NO
CAPTURE_STREAM	TRACE_LEVEL	     0			  NO
CAPTURE_STREAM	TIME_LIMIT	     INFINITE		  NO
CAPTURE_STREAM	MESSAGE_LIMIT	     INFINITE		  NO
CAPTURE_STREAM	MAXIMUM_SCN	     INFINITE		  NO
CAPTURE_STREAM	WRITE_ALERT_LOG      Y			  NO
CAPTURE_STREAM	DISABLE_ON_LIMIT     N			  NO
CAPTURE_STREAM	DOWNSTREAM_REAL_TIME Y			  NO

Capture
Process
Name		Parameter	     Value		  Set by User?
--------------- -------------------- -------------------- --------------------
		_MINE

[Updated on: Fri, 05 December 2008 13:00] by Moderator

Report message to a moderator

Re: Trying to use streams to replicate tables on same machine [message #363328 is a reply to message #363264] Fri, 05 December 2008 18:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What about alert logs?
This would be a good starting point.
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10727/strms_tr.htm
Re: Trying to use streams to replicate tables on same machine [message #364486 is a reply to message #363328] Mon, 08 December 2008 16:57 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member
I have followed the Instructions to implement the STREAMS to the TEE, & have gone thru the link provided by You but to no avail!!

the only thing i am having a little difficulty is with the DB link, it is working & my GLOBAL_NAMES parameter is set to false!
Re: Trying to use streams to replicate tables on same machine [message #364697 is a reply to message #364486] Tue, 09 December 2008 12:37 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
What is the issue you are facing right now.
Please set global_names=true.
Regards,
Varun Punj,
Re: Trying to use streams to replicate tables on same machine [message #364969 is a reply to message #364697] Wed, 10 December 2008 13:15 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member
i have tried setting my global names = true but then my database link does not works, please note that the database link is working just fine without having the global names = true & both databases sit on the same machine!!
Re: Trying to use streams to replicate tables on same machine [message #376288 is a reply to message #363030] Tue, 16 December 2008 17:34 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
Well there could be many reasons for streams not applying changes.
Mostly the problem lies in Instantiation the object when we are implementing first time.

Instantiate the tables that you are replicating and check again.


Re: Trying to use streams to replicate tables on same machine [message #376499 is a reply to message #376288] Wed, 17 December 2008 10:39 Go to previous messageGo to next message
gb74it
Messages: 3
Registered: November 2008
Junior Member
Hi,
global_names=true is mandatory to use Oracle Stream.

If you call your first DB DB1.WORLD and your second DB DB2.WORLD
try to use the script below, it works:

connect system/password@DB1.WORLD
define PATH_TS=your_path

prompt creating tablespace 'ts_default_your_user'
create tablespace ts_default_your_user
datafile '&path_ts\default_your_user.df'
size 100m reuse autoextend on next 20m
extent management local uniform size 2m
segment space management auto;

create user your_user identified by password
default tablespace ts_default_your_user
temporary tablespace temp
quota unlimited on ts_default_your_user;

grant connect to your_user;
commit;

create tablespace streams_tbs
datafile '&path_ts\streams_tbs.dbf'
size 25m reuse
autoextend on maxsize unlimited;

create user strmadmin
identified by strmadmin
default tablespace streams_tbs
temporary tablespace temp
quota unlimited on streams_tbs;

grant connect,resource,dba to strmadmin;

connect sys/password@DB1.world as sysdba
begin
dbms_streams_auth.grant_admin_privilege
(
grantee=>'strmadmin',
grant_privileges=>true
);
end;
/

grant select_catalog_role to strmadmin;
grant select any dictionary to strmadmin;
commit;

connect strmadmin/strmadmin@DB1.world
begin
dbms_streams_adm.set_up_queue(
queue_table => 'strmadmin.captured_db1_table',
queue_name => 'strmadmin.captured_db1');
end;
/
begin
dbms_streams_adm.set_up_queue(
queue_table => 'strmadmin.from_db2_table',
queue_name => 'strmadmin.from_db2');
end;
/
commit;

--CREAZIONE DB_LINK
create database link db2.world connect to strmadmin identified by strmadmin using 'db2.world';
commit;

connect your_user/password@db1.world
create table simple_table(id integer not null,
text varchar2(2000),
upd_date timestamp default systimestamp,
end_date date default date '2000-01-01');
alter table your_user.simple_table add (constraint simple_table_pk primary key (id));

create or replace trigger your_user.insert_time_simple_table
before
insert or update on your_user.simple_table for each row
begin
if :old.upd_date is null or :old.upd_date < systimestamp then
:new.upd_date := systimestamp;
else
:new.upd_date := :old.upd_date + 1 / 86400;
end if;
end;
/
commit;
--------------------------------------------------------------------------------
connect system/password@db2.world
--CREAZIONE TABLESPACE,UTENTI E GRANT
prompt creating tablespace 'ts_default_your_user'
create tablespace ts_default_your_user
datafile '&path_ts\default_your_user.df'
size 100m reuse autoextend on next 20m
extent management local uniform size 2m
segment space management auto;

create user your_user identified by password
default tablespace ts_default_your_user
temporary tablespace temp
quota unlimited on ts_default_your_user;

grant connect to your_user;
commit;

create tablespace streams_tbs
datafile '&path_ts\streams_tbs.dbf'
size 25m reuse
autoextend on maxsize unlimited;

create user strmadmin
identified by strmadmin
default tablespace streams_tbs
temporary tablespace temp
quota unlimited on streams_tbs;

grant connect,resource,dba to strmadmin;

connect sys/password@db2.world as sysdba
begin
dbms_streams_auth.grant_admin_privilege
(
grantee=>'strmadmin',
grant_privileges=>true
);
end;
/
grant select_catalog_role to strmadmin;
grant select any dictionary to strmadmin;
commit;

connect strmadmin/strmadmin@db2.world
begin
dbms_streams_adm.set_up_queue(
queue_table => 'strmadmin.captured_db2_table',
queue_name => 'strmadmin.captured_db2');
end;
/
begin
dbms_streams_adm.set_up_queue(
queue_table => 'strmadmin.from_db1_table',
queue_name => 'strmadmin.from_db1');
end;
/
commit;
--CREAZIONE DB_LINK
create database link db1.world connect to strmadmin identified by strmadmin using 'db1.world';
commit;
--------------------------------------------------------------------------------
connect your_user/password@db2.world

create table simple_table(id integer not null,
text varchar2(2000),
upd_date timestamp default systimestamp,
end_date date default date '2000-01-01');
alter table your_user.simple_table add (constraint simple_table_pk primary key (id));

create or replace trigger your_user.insert_time_simple_table
before
insert or update on your_user.simple_table for each row
begin
if :old.upd_date is null or :old.upd_date < systimestamp then
:new.upd_date := systimestamp;
else
:new.upd_date := :old.upd_date + 1 / 86400;
end if;
end;
/
commit;
--------------------------------------------------------------------------------
connect strmadmin/strmadmin@db1.world
alter table your_user.simple_table add supplemental log data (ALL) columns;
commit;

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'your_user.simple_table',
streams_type => 'capture',
streams_name => 'capture_db1',
queue_name => 'strmadmin.captured_db1',
include_tagged_lcr => true,
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DB2.WORLD(
source_object_name => 'your_user.simple_table',
source_database_name => 'DB1.WORLD',
instantiation_scn => iscn);
END;
/
commit;

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'your_user.simple_table',
streams_type => 'apply',
streams_name => 'apply_from_db2',
queue_name => 'strmadmin.from_db2',
include_dml => true,
include_ddl => true,
include_tagged_lcr => true,
source_database => 'DB2.WORLD',
inclusion_rule => true);
END;
/

BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'apply_from_db2',
apply_tag => HEXTORAW('1'));
END;
/


DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'text';
cols(2) := 'upd_date';
cols(3) := 'end_date';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name => 'your_user.simple_table',
method_name => 'MAXIMUM',
resolution_column => 'upd_date',
column_list => cols);
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'your_user.simple_table',
streams_name => 'db1_to_db2',
source_queue_name => 'strmadmin.captured_db1',
destination_queue_name => 'strmadmin.from_db1@db2.world',
include_dml => true,
include_ddl => true,
source_database => 'db1.world',
inclusion_rule => true,
queue_to_queue => true,
include_tagged_lcr => true,
and_condition => ':lcr.get_tag() != HEXTORAW(''1'')');
END;
/


commit;


--------------------------------------------------------------------------------

connect strmadmin/strmadmin@db2.world
alter table your_user.simple_table add supplemental log data (ALL) columns;

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'your_user.simple_table',
streams_type => 'capture',
streams_name => 'capture_db2',
queue_name => 'strmadmin.captured_db2',
include_tagged_lcr => false,
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DB1.WORLD(
source_object_name => 'your_user.simple_table',
source_database_name => 'DB2.WORLD',
instantiation_scn => iscn);
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'your_user.simple_table',
streams_type => 'apply',
streams_name => 'apply_from_db1',
queue_name => 'strmadmin.from_db1',
include_dml => true,
include_ddl => true,
include_tagged_lcr => true,
source_database => 'DB1.WORLD',
inclusion_rule => true);
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'your_user.simple_table',
streams_name => 'db2_to_db1',
source_queue_name => 'strmadmin.captured_db2',
destination_queue_name => 'strmadmin.from_db2@db1.world',
include_dml => true,
include_ddl => true,
source_database => 'db2.world',
inclusion_rule => true,
queue_to_queue => true);
END;
/
commit;

--------------------------------------------------------------------------------
connect strmadmin/strmadmin@db2.world
DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'text';
cols(2) := 'upd_date';
cols(3) := 'end_date';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name => 'your_user.simple_table',
method_name => 'MAXIMUM',
resolution_column => 'upd_date',
column_list => cols);
END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_from_db1',
parameter => 'disable_on_error',
value => 'n');
END;
/
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_from_db1');
END;
/
commit;
connect strmadmin/strmadmin@db1.world

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

connect strmadmin/strmadmin@db1.world
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_db1');
END;
/
commit;

--------------------------------------------------------------------------------

connect strmadmin/strmadmin@db2.world
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_db2');
END;
/
commit;
Re: Trying to use streams to replicate tables on same machine [message #379465 is a reply to message #376499] Tue, 06 January 2009 11:20 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member
thanks for your extensive reply, but as i have stated in earlier posts, whenever i set the global names parameter = true, the database link does not work, for example if i run a simple select query on db2 from db1, it does not work, but rather if i have global names = false the same query works fine, i will post the error message later,
Re: Trying to use streams to replicate tables on same machine [message #380278 is a reply to message #363030] Sat, 10 January 2009 01:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> it does not work
my motorscooter does not work, Tell me who to make it go!

It is such a worthless statement!
There are a gazillion reason it does not WORK!
Re: Trying to use streams to replicate tables on same machine [message #380333 is a reply to message #363030] Sat, 10 January 2009 23:23 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Set global_name to true and also fix your database link. Recreate database link with the same name of global_name of the other database.
Re: Trying to use streams to replicate tables on same machine [message #383184 is a reply to message #380333] Tue, 27 January 2009 10:02 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member
well i have been able to fix the global_names situation, but even after fixing that the stream is not working, my AQ_TM_PROCESSES parameter was set to 0, can some one please expand on this parameter & help me get this baby working!!
Re: Trying to use streams to replicate tables on same machine [message #383185 is a reply to message #383184] Tue, 27 January 2009 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Expand? http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams008.htm#CHDICCHD

There are preraquisites on this parameter to use Streams. See documentation.

Regards
Michel

[Updated on: Wed, 28 January 2009 01:17]

Report message to a moderator

Re: Trying to use streams to replicate tables on same machine [message #383196 is a reply to message #383185] Tue, 27 January 2009 12:12 Go to previous messageGo to next message
y2raza
Messages: 34
Registered: March 2008
Member
Michael I appreciate your kind & some times sarcastic help, there are 2 options for AQ_TM PROCESS parameter either i reset it & let the oracle run it automatically or i set it to a certain number, the stream is set perfectly according to the documentation i am following,
http://dbataj.blogspot.com/2008/01/oracle-streams-setup-between-two.html

can u try giving me a more specific answer to this parameter ??
Re: Trying to use streams to replicate tables on same machine [message #383278 is a reply to message #383196] Wed, 28 January 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the stream is set perfectly according to the documentation i am following,
http://dbataj.blogspot.com/2008/01/oracle-streams-setup-between-two.html

I don't think a blog is a reference.
For myself I prefer:
Streams Advanced Queuing User's Guide and Reference
Streams Concepts and Administration
Streams Replication Administrator's Guide
You will find at http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-STR

And a quick search in these docs will surely give you the answer:
http://www.oracle.com/pls/db102/search?remark=quick_search&word=AQ_TM_PROCESSES&tab_id=&format=ranked

Regards
Michel

[Updated on: Wed, 28 January 2009 01:21]

Report message to a moderator

Re: Trying to use streams to replicate tables on same machine [message #405132 is a reply to message #383278] Tue, 26 May 2009 08:11 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

I don't know why it is not working for OP. but as you can see at the end of my posting in my blog I given Oracle Documentation Reference which I followed to create stream site.

Thanks.

Regards,
Taj
Previous Topic: AQ between Oracle - MS SQL Server
Next Topic: Blob data type Streams replication
Goto Forum:
  


Current Time: Thu Mar 28 11:55:48 CDT 2024