Switch from capture process to synchronous capture in Oracle Streams

Switch from a capture process to a synchronous capture

Following are the steps:

1. Connect to the source database as the Oracle Streams administrator.
This example assumes that the Oracle Streams administrator is strmadmin at each database.
 
2. Stop the capture process.

BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'cap_proc');
END;
/

3. Connect to the destination database as the Oracle Streams administrator.
Create a commit-time queue for the apply process that will apply the changes that were captured by the synchronous capture.

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.sync_cap_dest_qt',
queue_name  => 'strmadmin.sync_cap_dest');
END;
/

4. Create an apply process that applies the changes in the queue created. Ensure that the apply_captured parameter is set to FALSE. Also, ensure that the rule_set_name parameter specifies the rule set used by the existing apply process.

BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(
queue_name     => 'strmadmin.sync_cap_dest',
apply_name     => 'apply_sync_cap',
rule_set_name  => 'strmadmin.apply_rules',
apply_captured => FALSE);
END;
/

5. Ensure that the apply process is configured properly for your environment. Apply user
Apply handlers
Apply tag
If appropriate, then ensure that the new apply process is configured in the same way as the existing apply process regarding these items.
 
6. Connect to the source database as the Oracle Streams administrator.
Create a commit-time queue for the synchronous capture.

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.sync_cap_source_qt',
queue_name  => 'strmadmin.sync_cap_source');
END;
/

7. Create a propagation that sends changes from the queue created. Ensure that the rule_set_name parameter specifies the rule set used by the existing propagation.

BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
propagation_name   => 'sync_cap_prop',
source_queue       => 'strmadmin.sync_cap_source',
destination_queue  => 'strmadmin.sync_cap_dest',
destination_dblink => 'db2.example.com',
rule_set_name      => 'strmadmin.prop_rules');
END;
/

8. Create a synchronous capture. Ensure that the queue_name parameter specifies the queue created. Also, ensure that the rule_set_name parameter specifies the rule set used by the existing capture process.

BEGIN
DBMS_CAPTURE_ADM.CREATE_SYNC_CAPTURE(
queue_name    => 'strmadmin.sync_cap_source',
capture_name  => 'sync_cap',
rule_set_name => 'strmadmin.capture_rules');
END;
/

The specified rule set must only contain rules that were created using the ADD_TABLE_RULES and ADD_SUBSET_RULES procedures in the DBMS_STREAMS_ADM package.
If the current capture process rule set contains other types of rules, then create a rule set for the synchronous capture and use the ADD_TABLE_RULES and ADD_SUBSET_RULES procedures to add rules to the new rule set.
 
In addition, a synchronous capture cannot have a negative rule set. If the current capture process has a negative rule set, and you want the synchronous capture to behave the same as the capture process, then add rules to the positive synchronous capture rule set that result in the same behavior.
 
If the existing capture process uses a capture user that is not the Oracle Streams administrator, then ensure that you use the capture_user parameter in the CREATE_SYNC_CAPTURE procedure to specify the correct capture user for the new synchronous capture.
 
7. Verify that the tables that are configured for synchronous capture are the same as the ones configured for the existing capture process by running the following query:

SELECT * FROM DBA_SYNC_CAPTURE_TABLES ORDER BY TABLE_OWNER, TABLE_NAME;

Note: If any table is missing or not enabled, then use the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure to add the table.
 
8. Prepare the replicated tables for instantiation. The replicated tables are the tables for which the synchronous capture captures changes.
For example, if the synchronous capture captures changes to the hr.employees and hr.departments tables, then run the following function:

SET SERVEROUTPUT ON
DECLARE
tables       DBMS_UTILITY.UNCL_ARRAY;
prepare_scn  NUMBER;
BEGIN
tables(1) := 'hr.departments';
tables(2) := 'hr.employees';
prepare_scn := DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION(
table_names => tables);
DBMS_OUTPUT.PUT_LINE('Prepare SCN = ' || prepare_scn);
END;
/

The returned prepare system change number (SCN) is used. This example assumes that the prepare SCN is 2700000.All of the replicated tables must be included in one call to the PREPARE_SYNC_INSTANTIATION function.
 
9. Connect to the destination database as the Oracle Streams administrator.
Set the apply process that applies changes from the capture process to stop applying changes when it reaches the SCN returned.
For example, if the prepare SCN is 2700000, then run the following procedure to set the maximum_scn parameter to 2700001 (2700000 + 1).:

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name   => 'apply_cap_proc',
parameter    => 'maximum_scn',
value        => '2700001');
END;
/

10. Connect to the source database as the Oracle Streams administrator.
In this example, run the following procedure:

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'cap_proc');
END;
/

11. Connect to the destination database as the Oracle Streams administrator.
Wait until the apply process that applies changes that were captured by the capture process has reached the SCN specified. When this event occurs, the apply process is automatically disabled with error ORA-26717 to indicate the SCN limit has reached.
 
12. Determine if the apply process has reached this point, query the DBA_APPLY view. In this example, run the following query:

SELECT 1 FROM DBA_APPLY
WHERE STATUS       = 'DISABLED' AND
ERROR_NUMBER = 26717 AND
APPLY_NAME   = 'APPLY_CAP_PROC';

Do not proceed to the next step until this query returns a row.
 
13. Set the instantiation SCN for the replicated tables to the SCN value the SCN returned.

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name    => 'hr.employees',
source_database_name  => 'db1.example.com',
instantiation_scn     => 2700000);
END;
/

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name    => 'hr.departments',
source_database_name  => 'db1.example.com',
instantiation_scn     => 2700000);
END;
/

14. Start the apply process that you created.

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

15. Drop the apply process that applied changes that were captured by the capture process.

BEGIN
DBMS_APPLY_ADM.DROP_APPLY(
apply_name => 'apply_cap_proc');
END;
/

16. f it is no longer needed, then drop the queue that was used by the apply process that you dropped.

BEGIN
DBMS_STREAMS_ADM.REMOVE_QUEUE(
queue_name  => 'strmadmin.cap_proc_dest',
drop_unused_queue_table => TRUE);
END;
/

17. Connect to the source database as the Oracle Streams administrator.
Stop the capture process.

BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'cap_proc');
END;
/

18. Drop the capture process.

BEGIN
DBMS_CAPTURE_ADM.DROP_CAPTURE(
capture_name => 'cap_proc');
END;
/

19. Drop the propagation that sent changes that were captured by the capture process.

BEGIN
DBMS_PROPAGATION_ADM.DROP_PROPAGATION(
propagation_name => 'cap_proc_prop');
END;
/

20. Drop the queue that was used by the capture process and propagation that you dropped.

BEGIN
DBMS_STREAMS_ADM.REMOVE_QUEUE(
queue_name  => 'strmadmin.cap_proc_source',
drop_unused_queue_table => TRUE);
END;
/

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.