This discussion is archived
1 Reply Latest reply: Mar 26, 2012 10:58 AM by JoseFormiga RSS

Replication Five DBs to One DB

JoseFormiga Newbie
Currently Being Moderated
Hi.
I have a complex replication environment to build. Some Oracle SRs opened was not sufficient to give solution to questions of myself.
Then, I need help. This have more three months trying to define and install.

Environment:

1) - DBs SOURCEs:

DB ONE

Table A:
col1 number;
col2 number;
Table Owner: abcd
Db Link: dbdest <---------- Destination DB

DB TWO

Table A:
col1 number;
col2 number;
Table Owner: abcd
Db Link: dbdest <---------- Destination DB

DB THREE

Table A:
col1 number;
col2 number;
Table Owner: abcd
Db Link: dbdest <---------- Destination DB

DB FOUR

Table A:
col1 number;
col2 number;
Table Owner: abcd
Db Link: dbdest <---------- Destination DB

DB FIVE

Table A:
col1 number;
col2 number;Table
Owner: abcd
Db Link: dbdest <---------- Destination DB


DESTINATION (TARGET):
-----------------------------------
DB ALLONONE

Table A:
col1 number;
col2 number;
col3 number; <---------------------- values: 1,2,3,4 or 5 (DBs Sources identification above)
Table Owner: newabcd
Db Link: dbone, dbtwo,dbthree,dbfour,dbfive (all defined above)
Grant all on newabcd.<table_name> to strmadmin;


2)-DEFINITIONS :
--------------------
2.1 - In DBs ONE, TWO, THREE, FOUR,FIVE I executed:

begin
dbms_streams_adm.add_table_rules (table_name => 'abcd.table_a',
streams_type => 'capture',
streams_name => 'strepsp_capture',
queue_name => 'strmadmin.strepsp_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
/

begin
dbms_streams_adm.add_table_propagation_rules (table_name => 'abcd.table_a',
streams_name => 'DO_RS_PARA_SPAULO',
source_queue_name => 'strmadmin.strepsp_queue',
destination_queue_name => 'strmadmin.strepSP_queue@dbdest',
include_dml => true,
include_ddl => true,
source_database => 'abcd',
inclusion_rule => true);
end;
/

declare
source_scn number;
begin
source_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@dbdest (source_object_name => 'abcd.table_a',
source_database_name => 'abcd',
instantiation_scn => source_scn);
end;
/

2.2 - In DBs ONE, TWO, THREE, FOUR,FIVE was created:

begin DBMS_STREAMS_ADM.ADD_COLUMN(
rule_name=>'TABLE_AXXXX', <------------Rule Name
table_name=> 'abcd.table_a'
,column_name=>'col3',
column_value =>ANYDATA.ConvertNumber(1), <---------- values 1,2,3,4 and 5 (= Sources DBs identification)
value_type=>'NEW',
step_number=>0,
operation=>'ADD');
END;
/

begin DBMS_STREAMS_ADM.ADD_COLUMN(
rule_name=>'TABLE_AXXXX', <------------Rule Name
table_name=> 'abcd.table_a'
,column_name=>'col3',
column_value =>ANYDATA.ConvertNumber(1), <---------- values 1,2,3,4 and 5 (= Sources DBs identification)
value_type=>'OLD',
step_number=>1,
operation=>'ADD');
END;
/

2.3 - In DESTINATION (Target) was created:

begin
dbms_streams_adm.rename_schema(
rule_name => 'table_aXXXX' <---------------- Rule Name
,from_schema_name => 'ABCD'
,to_schema_name => 'NEWABCD'
,step_number => 0
,operation => 'ADD');
end;
/


BEGIN
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name => 'NEWABCD.TABLE_A'
,column_list => 'COL1,COL3');
END;
/

begin
dbms_apply_adm.compare_old_values
(object_name => 'NEWABCD.TABLE_A'
,column_list => '*',operation => '*',
compare => false); end;
/

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

Now, the problem: THIS DO NOT ANY REPLICATION. Always give me a 1403 (data not found) in Destination (target) DB.

Please, some orientation ?

Thanks.
JoseFormiga
  • 1. Re: Replication Five DBs to One DB
    JoseFormiga Newbie
    Currently Being Moderated
    Hi.
    Sorry, I forget the APPLY section in my Streams Environment:

    In Destination (target) DB, for each Source DB (Five Sources DBs), was created:

    begin
    dbms_streams_adm.add_table_rules (table_name => 'abcd.table_a',
    streams_type => 'apply',
    streams_name => 'BA_CON_APP',
    queue_name => 'strmadmin.BA_CON_APP_q',
    include_dml => true,
    include_ddl => true,
    source_database => 'abcd',
    inclusion_rule => true);
    end;
    /

    Thanks.
    JoseFomirga

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points