2 Replies Latest reply: Nov 1, 2011 1:30 AM by misterimran RSS

    Include another schema in STREAMS replication

    misterimran
      Dear All,

      In 11gR2 on Linux databases, I have implemented streams using the following script. It works find.
      This streams replication works for only one schema, that is radius_dsl.

      Now I have want to change this script so that it replicate changes for more then one schema, please guide what parameters I have to change. I have allready tried many changes but didn't work. I want to replicate 3 schema in my database while the script only replicate one.

      Thanks,
      Imran

      *****************************************************************************

      On both database set these parameter:

      ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1;
      ALTER SYSTEM SET AQ_TM_PROCESSES=1 scope=both;
      ALTER SYSTEM SET GLOBAL_NAMES=TRUE;

      create user strmets identified by strmets default tablespace devp_streams temporary tablespace devp_temp02;

      GRANT DBA, CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmets;
      GRANT EXECUTE ON DBMS_AQADM TO strmets;
      GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmets;
      GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmets;
      GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmets;
      GRANT EXECUTE ON DBMS_APPLY_ADM TO strmets;
      GRANT EXECUTE ON DBMS_FLASHBACK TO strmets;
      /

      BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
      privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
      grantee => 'strmets',
      grant_option => FALSE);
      END;
      /

      BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
      privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
      grantee => 'strmets',
      grant_option => FALSE);
      END;
      /

      On DB1 create public database link DB2 that connects to DB2

      On DB2 create public database link DB1 that connects to DB1

      On backup run the following command
      CONNECT strmets/strmets
      EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

      On DB1 create log miner tablespace and user
      CONN sys as sysdba

      CREATE TABLESPACE Logminer
      datafile '+db_1_DATA/db_1/datafile/logminer01.dbf'
      size 1024m
      autoextend on
      next 500m
      extent management local;

      EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(‘Logminer’);

      Connect sys / as sysdba on DB1 and add supplement log group to tables
      alter database force logging;
      alter database add supplemental log data;

      Create stream queues on both databases DB1 and backup

      connect STRMETS/STRMETS

      BEGIN
      DBMS_STREAMS_ADM.SET_UP_QUEUE(
      queue_table => 'ets_queue_table',
      queue_name => 'ets_queue',
      queue_user => 'strmets');
      END;
      /

      Connect strmets/strmets on DB1 and add propagation rules

      BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
      schema_name => 'RADIUS_DSL',
      streams_name => 'devpcom_standby',
      source_queue_name => 'strmets.ets_queue',
      destination_queue_name => 'strmets.ets_queue@db_2',
      include_dml => true,
      include_ddl => true,
      include_tagged_lcr => false,
      source_database => 'db_1');
      END;
      /

      Connect strmets/strmets from DB1 and add schemas rules

      BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
      schema_name => 'RADIUS_DSL',
      streams_type => 'capture',
      streams_name => 'ets_capture',
      queue_name => 'strmets.ets_queue',
      include_dml => true,
      include_ddl => true,
      source_database => 'db_1');
      END;
      /

      Export and import dump from DB1 to backup
      exp userid=radius_dsl/*********** FILE=/tmp/radius_dsl.dmp OBJECT_CONSISTENT=y ROWS=y
      on DB2
      imp userid=radius_dsl/*********** fromuser=radius_dsl touser=radius_dsl FILE=/tmp/radius_dsl.dmp IGNORE=y COMMIT=y LOG=/tmp/radius_dsl.log STREAMS_INSTANTIATION=y


      Connect strmets/strmets on DB1 and run this script
      DECLARE
      ter_scn NUMBER;
      BEGIN
      ter_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
      DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@db_2(
      source_schema_name => 'radius_dsl',
      source_database_name => 'db_1',
      instantiation_scn => ter_scn);
      END;
      /
      CONNECT strmets/strmets at backup and run this script to apply stream

      BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
      schema_name => 'radius_dsl',
      streams_type => 'apply',
      streams_name => 'ets_apply',
      queue_name => 'strmets.ets_queue',
      include_dml => true,
      include_ddl => true,
      source_database => 'db_1');
      END;
      /

      Connect strmets/strmets on backup and execute the script to apply streams
      BEGIN
      DBMS_APPLY_ADM.SET_PARAMETER(
      apply_name => 'ets_apply',
      parameter => 'disable_on_error',
      value => 'n');

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

      Connect strmets/strmets on DB1 and run the script to apply streams
      BEGIN
      DBMS_CAPTURE_ADM.START_CAPTURE(
      capture_name => 'ets_capture');
      END;
      /

      *****************************************************************************