Home » Server Options » Streams & AQ » Trying to use streams to replicate tables on same machine (10g,10.2.0.1.0,Fedora 9 Sulphur)
|
|
|
|
|
|
|
|
| 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   |
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 #363263 is a reply to message #363030] |
Fri, 05 December 2008 10:31 |
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 |
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 #376499 is a reply to message #376288] |
Wed, 17 December 2008 10:39 |
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 |
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,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 07:15:20 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
