1 Reply Latest reply: Apr 25, 2013 12:43 AM by Rc-Oracle RSS

    Truncate, then reload causes ora-00001 unique constraint on apply side

    kenkrug
      I setup schema level replication using the maintain_schema procedure. Its replicating DML and not DDL.
      I have a source side process that truncates a table then reloads it every day. Since DDL is not replicating, the reload produces generates an ora-00001 errors in apply process.
      I don't want to replicate all DDL because of apply errors it creates

      I've added a DDL schema rule to replicate truncate statements on the capture and apply rule, but the truncate is still not replicating. Here are the rules I'm using:
        dbms_streams_adm.add_schema_rules(
          schema_name => 'PRODUCT',
          streams_type => 'CAPTURE',
          streams_name => '&src_db$CAP',
          queue_name => 'STRMADMIN.&src_db$CAPQ',
          include_dml => TRUE,
          include_ddl => FALSE,
          include_tagged_lcr => TRUE,
          source_database => '&src_db..&gbl_nm',
          inclusion_rule => TRUE);
      
        DBMS_STREAMS_ADM.add_schema_rules (
          schema_name       => 'PRODUCT',
          streams_type      => 'CAPTURE',
          streams_name      => '&src_db$CAP',
          queue_name        => 'STRMADMIN.&src_db$CAPQ',
          include_dml       => FALSE,
          include_ddl       => TRUE,
          source_database   => '&src_db..&gbl_nm',
          inclusion_rule    => TRUE,
          and_condition     => '(:ddl.get_command_type() = ''TRUNCATE TABLE'')');
      Here's the capture rule_condition from dba_streams_rules:
      ((:dml.get_object_owner() = 'PRODUCT') and :dml.get_source_database_name() = 'PKIO.LOUDCLOUD.COM' )
      ((((:ddl.get_object_owner() = 'PRODUCT' or :ddl.get_base_table_owner() = 'PRODUCT') and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name() = 'PKIO.LOUDCLOUD.COM' )) and ((:ddl.get_command_type() = 'TRUNCATE TABLE')))

      On the apply side I have the following
        dbms_streams_adm.add_schema_rules(
          schema_name => 'PRODUCT',
          streams_type => 'APPLY',
          streams_name => '',
          queue_name => 'STRMADMIN.&src_db$APPQ',
          include_dml => TRUE,
          include_ddl => FALSE,
          include_tagged_lcr => TRUE,
          source_database => '&src_db..&gbl_nm',
          inclusion_rule => TRUE);
      
        DBMS_STREAMS_ADM.add_schema_rules (
          schema_name       => 'PRODUCT',
          streams_type      => 'APPLY',
          streams_name      => '',
          queue_name        => 'STRMADMIN.&src_db$APPQ',
          include_dml       => FALSE,
          include_ddl       => TRUE,
          source_database   => '&src_db..&gbl_nm',
          inclusion_rule    => TRUE,
          and_condition     => '(:ddl.get_command_type() = ''TRUNCATE TABLE'')');
      And the corresponding rule_conditions look like:
      ((:dml.get_object_owner() = 'PRODUCT') and :dml.get_source_database_name() = 'PKIO.LOUDCLOUD.COM' )
      ((((:ddl.get_object_owner() = 'PRODUCT' or :ddl.get_base_table_owner() = 'PRODUCT') and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name() = 'PKIO.LOUDCLOUD.COM' )) and ((:ddl.get_command_type() = 'TRUNCATE TABLE')))

      When the source side process that truncates and reloads the table, the apply process crashes. To workaround this I manually truncate the table on the target side and execute DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS then restart apply.

      What do I need to do to replicate truncate statements with out replicating other DDL statements?

      Thanks!