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!