1 Reply Latest reply on May 6, 2014 4:34 AM by Renu-Oracle

    Oracle Streams  Replicating large number of tables

    vsaletan

      Oracle Database 11.2.0.3

      Linux  x86-64  Bit

       

       

      Hi,

       

      I need to  replicate  a large number of  tables in a  particular schema  using  Oracle Streams.  This schema contains thousands of tables. I need to replicate  tables starting with  "HS_" .   But I should skip  replication for tables  starting with just  "HS".  Both kind of tables are in very large number, in thousands.

       

      I referred some books,  They give a method  for replicating  tables. As you can see below,  They use four tables  to  replicate.  But  I want to know  if there is any  method  by which we can  replicate  thousands of tables in a schema.  Using below method for thousands of tables will be cumbersome.

       

       

       

      SQL> declare

      2 tables dbms_utility.uncl_array;

      3 begin

      4 tables(1) := 'ALG.EMP1';

      5 tables(2) := 'ALG.EMP';

      6 tables(3) := 'SP.GRADE';

      7 tables(4) := 'SP.SALBONUS';

      8

      9 dbms_streams_adm.maintain_tables (

      10 table_names => tables,

      11 source_database => 'ABC.WORLD',

      12 source_directory_object => 'STREAMSDIR',

      13 destination_database => 'XYZ.WORLD',

      14 destination_directory_object => 'STREAMSDIR',

      15 capture_name => 'ABC_CAP',

      16 capture_queue_name => 'ABC_CAP_Q',

      17 capture_queue_table => 'ABC_CAP_QT',

      18 propagation_name => 'ABC_TO_XYZ_PROP',

      19 apply_name => 'ABC_APP',

      20 apply_queue_name => 'ABC_AP_Q',

      21 apply_queue_table => 'ABC_AP_QT',

      22 dump_file_name => NULL,

      23 log_file => NULL,

      24 bi_directional => FALSE,

      25 include_ddl => TRUE,

      26 perform_actions => TRUE,

      27 instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE

      28 );

      29 end;

      30 /

      job finished

      job finished

      PL/SQL procedure successfully completed.

        • 1. Re: Oracle Streams  Replicating large number of tables
          Renu-Oracle

          Hi,

           

           

          You can use schema level streams rule for replication at schema level.

           

           

          How To Setup One-Way SCHEMA Level Streams Replication (Doc ID 301431.1)

           

           

          DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS  Syntax:

          DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(

          schema_names IN VARCHAR2,

          source_directory_object IN VARCHAR2,

          destination_directory_object IN VARCHAR2,

          source_database IN VARCHAR2,

          destination_database IN VARCHAR2,

          perform_actions IN BOOLEAN DEFAULT TRUE,

          script_name IN VARCHAR2 DEFAULT NULL,

          script_directory_object IN VARCHAR2 DEFAULT NULL,

          dump_file_name IN VARCHAR2 DEFAULT NULL,

          capture_name IN VARCHAR2 DEFAULT NULL,

          capture_queue_table IN VARCHAR2 DEFAULT NULL,

          capture_queue_name IN VARCHAR2 DEFAULT NULL,

          capture_queue_user IN VARCHAR2 DEFAULT NULL,

          propagation_name IN VARCHAR2 DEFAULT NULL,

          apply_name IN VARCHAR2 DEFAULT NULL,

          apply_queue_table IN VARCHAR2 DEFAULT NULL,

          apply_queue_name IN VARCHAR2 DEFAULT NULL,

          apply_queue_user IN VARCHAR2 DEFAULT NULL,

          log_file IN VARCHAR2 DEFAULT NULL,

          bi_directional IN BOOLEAN DEFAULT FALSE,

          include_ddl IN BOOLEAN DEFAULT FALSE,

          instantiation IN INTEGER DEFAULT

          DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);

          ++Prerequisites:

          To use the MAINTAIN_SCHEMAS procedure, following are should be considered :

           

           

          1. Set all the required database parameters related to streams.

           

          2. STRMADMIN is the account for the Streams Administrator  .

          Refer to the Oracle Streams Replication Administrator's Guide for more information about configuring an Oracle Streams administrator.

          To run DBMS_STREAMS_ADM.MAINTAIN_ * , DBA privileges for the

          Streams Administrator Account is required.

           

           

          3. Create the directory objects needed to create the data pump export / import, and also if you need to store the script for later usage, you will need directory object, can be the same one or different one. .

           

           

          4. Creating the required database links.

           

          Refer to the Oracle Streams Replication Administrator's Guide

           

           

           

          Thanks,

          Renu