How to Remove Oracle Streams from Your 10g Environment

When I first started experimenting with Oracle Streams, I found that removing Streams from our environment was challenging. Oracle documentation just says use dbms_streams_adm.remove_streams_configurations, which didn’t clean the Streams completely, and as such, trying to reconfigure Streams again was unsuccessful.

Here are 2 ways I use to remove bi-directional schema replication from our environment. Both have its pros and cons.

These steps have been tested in 10.2.0.4 environment.

  1. Use dbms_streams_adm package
  2. Drop strmadmin user

 

Using dbms_streams_adm

Here are the steps, execute on every replication site:

 

A) Disable propagation schedule


exec dbms_aqadm.disable_propagation_schedule(
queue_name => ‘capture name’,
destination => ‘destination’,
destination_queue => ‘apply name’);

 

B) Drop propagation


exec dbms_propagation_adm.drop_propagation(
propagation_name => ‘propagation name’,
drop_unused_rule_sets => true);

 

C) Remove Streams


exec  dbms_streams_adm.remove_streams_configuration;

 

D) Drop Queues for capture and apply


— drop capture queue
exec  DBMS_STREAMS_ADM.REMOVE_QUEUE(‘<capture queue name>’,TRUE);

— drop apply queue
Exec DBMS_STREAMS_ADM.REMOVE_QUEUE(‘<apply queue name>’,TRUE);

When finished executing these steps, you should see only be left with 1 object only (database link) under strmadmin.

Again remember to execute on every site.

Pros

  1. It drops the replication configuration without dropping strmadmin or database link which makes it simpler to rebuild your Streams environment.

Cons

  1. Too many steps.
  2. It has been my experience if your replication is hung with “Pause for flow control” these steps might not work.

 

Dropping strmadmin

Just drop Streams admin user  (strmadmin) on every site.

drop user strmadmin cascade;

Pros

  1. Just 1 step.
  2. It has been my experience when replication is hung that is the only way to clean it up.

Cons

  1. User strmadmin must be disconnected. You might have to kill few sessions before dropping the user.
  2. If your intention is to rebuild the Streams environment, then you must recreate the user on every site and grant proper privileges.

 

Caution

If you are completely removing replication from your environment, then remember to disable supplemental logging. Supplemental log does use some CPU and additional disk space. Both of methods mentioned in this post don’t disable the supplemental logging.

alter table <schema.object_name> drop supplemental log group <group name>;

You can get object name and group name from dba_log_groups.

 

Hazem Ameen
Senior Oracle DBA

Advertisements