Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

kenkrugMar 3 2013 — edited Apr 25 2013
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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 23 2013
Added on Mar 3 2013
1 comment
1,539 views