Switch from a synchronous capture to a capture process in Oracle Streams

Switch from synchronous capture to capture process

Complete the following steps

1. Ensure that the source database is running in ARCHIVELOG mode.

Archive log list;

 
2. Log in to the destination database as the Oracle Streams administrator. Example assumes that the Oracle Streams administrator is strmadmin at each database.
 
3. Create the queue for the apply process that will apply the changes that were captured by the capture process.

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

4. Create an apply process that applies the changes in the queue created.
Note: Ensure that the apply_captured parameter is set to TRUE. 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.cap_proc_dest',
apply_name     => 'apply_cap_proc',
rule_set_name  => 'strmadmin.apply_rules',
apply_captured => TRUE);
END;
/

 
5. Ensure that the apply process is configured properly for your environment. Specifically, ensure that the new apply process is configured properly regarding the following items:
Apply user
Apply handlers
Apply tag
 
6. Stop the apply process that applies changes captured by the synchronous capture.

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

7. Connect to the source database. Create the queue for the capture process.

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

8. Create a propagation that sends changes from the queue created.

BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
propagation_name   => 'cap_proc_prop',
source_queue       => 'strmadmin.cap_proc_source',
destination_queue  => 'strmadmin.cap_proc_dest',
destination_dblink => 'db2.example.com',
rule_set_name      => 'strmadmin.prop_rules');
END;
/

9. Create a capture process. Ensure that the parameters are set properly in the CREATE_CAPTURE procedure:
–Set the queue_name parameter to the queue created in Step 7.
–Set the rule_set_name parameter to the rule set used by the existing synchronous capture.
 
10. If the existing synchronous capture uses a capture user that is not the Oracle Streams administrator, then set the capture_user parameter to the correct capture user for the new capture process.

BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name    => 'strmadmin.cap_proc_source',
capture_name  => 'cap_proc',
rule_set_name => 'strmadmin.cap_rules');
END;
/

11. Prepare the replicated tables for instantiation. The replicated tables are the tables for which the capture process captures changes.
For example, if the capture process captures changes to the hr.employees and hr.departments tables, then run the following procedures:

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
table_name           => 'hr.employees',
supplemental_logging => 'keys');
END;
/

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
table_name           => 'hr.departments',
supplemental_logging => 'keys');
END;
/

Note: Lock all of the replicated tables in SHARE MODE.
In this example, run the following SQL statement:
LOCK TABLE hr.employees, hr.departments IN SHARE MODE;

12. Determine the current system change number (SCN) by running the following query:

SELECT CURRENT_SCN FROM V$DATABASE;

This example assumes that the switch SCN is 2700000.
 
13. Run a COMMIT statement to release the lock on the replicated tables:

COMMIT;

14. Set the apply process that applies changes from the synchronous capture to stop applying changes when it reaches the SCN returned
For example, if the switch 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_sync_cap',
parameter    => 'maximum_scn',
value        => '2700001');
END;
/

15. Start the apply process that applies changes from the synchronous capture.
In this example, run the following procedure:

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

Note: Wait until the apply process that applies changes that were captured by the synchronous capture 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.
 
16. Determine if the apply process has reached this point, query the DBA_APPLY view.

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

17. Set the instantiation SCN for the replicated tables to the SCN value 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;
/

18. Start the apply process that you created.

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

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

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

20. If it is no longer needed, then drop the queue that was used by the apply process that you dropped in Step 20.

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

21. Start the capture process that you created.

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

22. Drop the synchronous capture.

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

23. Drop the propagation that sent changes that were captured by the synchronous capture.

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

24. Drop the queue that was used by the synchronous capture and propagation.

BEGIN
DBMS_STREAMS_ADM.REMOVE_QUEUE(
queue_name              => 'strmadmin.sync_cap_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.