1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Streams

Discussion in 'Server Administration and Options' started by Tariq Bashir Malhi, Apr 26, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Please spare some time and resolve my issue i have created a stream for single table, below are steps i performed for this. I have exp/imp to make table available in destination. After all this there is no impact on table in destination side.
    This is first time i work on aracle streams.

    -------------------------Destination------------------

    --Step No.1

    CREATE TABLESPACE streams_tbs
    DATAFILE '/u01/app/oracle/
    streams_tbs.dbf' SIZE 25M;

    --Step No.2

    CREATE USER strmadmin
    IDENTIFIED BY strmadmin
    DEFAULT TABLESPACE streams_tbs
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON streams_tbs;

    --Step No.3 (connect as sys )
    GRANT CONNECT, RESOURCE, DBA
    TO strmadmin;

    --Step No.4 (connect as sys )

    begin
    DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee => 'strmadmin',
    grant_privileges => true);
    END;
    /

    --Step No.5 (connect as sys )

    GRANT SELECT_CATALOG_ROLE TO strmadmin;

    --Step No.6 (connect as sys )

    GRANT SELECT ANY DICTIONARY TO strmadmin;

    --Step 7

    alter system set global_names=true scope=spfile;

    --Step 8

    alter system set STREAMS_POOL_SIZE =50M ;

    -- Name of database link should be same as name of the database it connecting to, because global_names=true otherwise
    -- we can give database link anyname.

    --Step 9 (Optional)

    create public database link pcba connect to strmadmin identified by strmadmin use 'pcba';

    --Step 10

    begin
    dbms_streams_adm.set_up_queue();
    end;

    --Step 11 Execute this command vis SYS

    grant all on tariq.cb_insured_person to strmadmin;

    --Step 12

    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name => ' tariq.cb_insured_personp',
    streams_type => 'apply',
    streams_name => 'apply_stream',
    queue_name =>
    'strmadmin.streams_queue',
    include_dml => true,
    include_ddl => true,
    source_database => 'PCBA',
    inclusion_rule => true);
    END;
    /

    --Step 13

    BEGIN
    DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name => 'apply_stream',
    parameter => 'disable_on_error',
    value => 'n');
    END;
    /

    --Step 14

    BEGIN
    DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'apply_stream');
    END;
    /

    ------------------------------------------Source------------------------------

    --Step 1

    CREATE TABLESPACE streams_tbs
    DATAFILE '/opt/oracle/
    streams_tbs.dbf' SIZE 25M;

    --Step 2
    CREATE USER strmadmin
    IDENTIFIED BY strmadmin
    DEFAULT TABLESPACE streams_tbs
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON streams_tbs;

    --Step 3 (as sys user)
    GRANT CONNECT, RESOURCE, DBA TO strmadmin;

    --Step 4 (as sys user)

    begin
    DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee => 'strmadmin',
    grant_privileges => true);
    END;
    /

    --Step 5 (as sys user)

    GRANT SELECT_CATALOG_ROLE TO strmadmin;

    --Step 6 (as sys user)

    GRANT SELECT ANY DICTIONARY TO strmadmin;

    --Step 7

    alter system set global_names=true scope=spfile;

    --Step 8

    alter system set STREAMS_POOL_SIZE =200M ;

    -- Name of database link should be same as name of the database it connecting to, because global_names=true otherwise
    -- we can give database link anyname.

    --Step 9

    create public database link tmdb connect to strmadmin identified by strmadmin use 'tmdb';

    --Step 10

    begin
    dbms_streams_adm.set_up_queue();
    end;

    --Step 11

    alter table core_business.cb_insured_person
    add supplemental log data
    (primary key, unique) columns;

    --Step 12

    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name => 'core_business.cb_insured_person',
    streams_type => 'capture',
    streams_name => 'capture_stream',
    queue_name =>
    'strmadmin.streams_queue',
    include_dml => true,
    include_ddl => true,
    inclusion_rule => true);
    END;
    /

    --Step 13

    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name => 'core_business.cb_insured_person',
    streams_name => 'PCBA_to_TMDB',
    source_queue_name => 'strmadmin.streams_queue',
    destination_queue_name => 'strmadmin.streams_queue@TMDB',
    include_dml => true,
    include_ddl => true,
    source_database => 'PCBA',
    inclusion_rule => true);
    END;
    /

    --Step 14

    DECLARE
    source_scn NUMBER;
    BEGIN
    source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
    DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@TMDB(
    source_object_name => 'core_business.cb_insured_person',
    source_database_name => 'PCBA',
    instantiation_scn => source_scn);
    END;
    /

    --Step 15

    BEGIN
    DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name =>
    'capture_stream');
    END;
    /
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Oracle are you using? No one can answer your question without that information.
     
  3. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    i am using 11gr2 on ubuntu 8.04