1 Reply Latest reply: Mar 26, 2012 12:58 PM by JoseFormiga RSS

    Replication Five DBs to One DB

    JoseFormiga
      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
          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