6. Making Changes that Do Not Replicate using TAGs
7. Excluding or Adding a Table to an Existing Replication Scheme.
The capture process reads the redo to mine changes using Log Miner. The changes are validated using the capture process rules and either discarded or placed in the Logical Change Record (LCR) and enqueued into the Capture Queue. Rules are discussed in another section.
A server can have one or more Capture Process. Normally you will want one Capture Process but that is not always the case.
Most of the current status information about all Capture Process on a server is contained in the view DBA_CAPTURE.
The Capture Process is controled by the oracle supplied package DBMS_CAPTURE_ADM.
sqlplus strmadmin/password
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
------------------------------ --------
CAPTURE_XYZ ENABLED
To get some more useful information you need to add some columns. It the Capture Process has encountered an error, the error message and error number columns will contain that information, otherwise they are null. The status_change_time column records the last time the status changed, either by you starting/stopping the capture process or the capture process aborting on an error.
select
capture_name,
status,
status_change_time,
error_number,
error_message
from dba_capture;
SQL> exec dbms_capture_adm.stop_capture ('CAPTURE_XYZ');
SQL> exec dbms_capture_adm.start_capture ('CAPTURE_XYZ');
set serveroutput on
spool /tmp/capture_bounce.lst
begin
dbms_capture_adm.stop_capture ('CAPTURE_XYZ');
dbms_lock.sleep(15);
dbms_capture_adm.start_capture ('CAPTURE_XYZ');
exception
when others then
begin
dbms_output.put_line('SQLCODE: '||SQLCODE);
dbms_output.put_line('Message: '||SQLERRM);
end;
end;
/
spool off
Sometimes you need to jump a capture process forward. This may be
because of corrupt redo, last archive log, etc. To do this drop the current
capture process and recreate it.
dbms_capture_adm.drop_capture('CAPTURE_XYZ');
To recreate the capture process add the schema(table) rule. If the capture process
exist it will add the rule, if the capture process does not exist it will create a new
capture process and add the rule.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'PUBS',
streams_type => 'capture',
streams_name => 'capture_xyz',
queue_name => 'CAPT_QUEUE',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => NULL,
inclusion_rule => true);
END;
/
This will create a new capture process called CAPTURE_XYZ becuase it does not exist.
It will add the schema rule and reinstantiated all objects in the PUBS schema.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'HR',
streams_type => 'capture',
streams_name => 'capture_xyz',
queue_name => 'CAPT_QUEUE',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => NULL,
inclusion_rule => true);
END;
/
The above command will add a schema rule to the CAPTURE_XYZ process and
reinstaintate all objects in the HR schema.
When Bi-Directional replication is running it uses TAGs to determine if a change
was a local change or was applied by the apply process. The apply process create changes
with a non-null TAG. Even if you create one direction replication you can exclude non-null TAGs
with include_tagged_lcr => false.
To make local changes that do not get captured, set your session TAG to a non-null values;
begin
dbms_streams.set_tag(tag=> HEXTORAW('2A'));
end;
/
To check your session TAG use:
SQL> select dbms_streams.get_tag from dual;
To clear you TAG set it to null or exit and log back on.
Sometimes you want to replicate a schema, but want to exclude one or more tables from
the replicaiton. To exclude a table add a false table rule to the capture process rule set.
Here we exclude DML and DDL on tablexyz from the replication. This exclusion takes place at
capture processs.
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'OWNER.TABLEXYZ',
streams_type => 'CAPTURE',
streams_name => 'capture_xyz',
queue_name => 'rep_capt_queue',
include_dml => true,
include_ddl => true,
source_database => 'PROD.ORCL.LOCAL',
inclusion_rule => false);
end;
/
The streams_type defines where the rule is applied. The inclusion_rule defines whether we
are excluding (false) or including (true) the changes to the table.
To add a table to an existing replication use the same command.
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'OWNER.TABLEXYZ',
streams_type => 'CAPTURE',
streams_name => 'capture_xyz',
queue_name => 'rep_capt_queue',
include_dml => true,
include_ddl => true,
source_database => 'PROD.ORCL.LOCAL',
inclusion_rule => true);
end;
/
You also have to add the rule to the propagator and the apply process or the changes
will be dropped by those processes.
begin
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'OWNER.TABLEXYZ',
streams_name => 'prop_xyz',
source_queue_name => 'rep_capt_queue',
destination_queue_name => 'rep_apply_queue',
include_dml => true,
include_ddl => true,
source_database => 'PROD.ORCL.LOCAL',
inclusion_rule => true);
end;
/
Add the inclusion rule to the apply processs.
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'OWNER.TABLEXYZ',
streams_type => 'APPLY',
streams_name => 'apply_xyz',
queue_name => 'rep_apply_queue',
include_dml => true,
include_ddl => true,
source_database => 'PROD.ORCL.LOCAL',
inclusion_rule => true);
end;
/