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

4 thoughts on “How to Remove Oracle Streams from Your 10g Environment

  1. Thanks for sharing . . . I have come across this problem a couple of times.
    Your correct dbms_streams_adm.remove_streams_configurations does NOT always work.
    I prefer the first method that you have mentioned.

  2. HI,
    thanks alot for ur post that was so good.
    yen when i remove stream configuration in destination databse i got this error:
    sql>EXECUTE DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;

    ERROR st line1:
    ORA-00911: invalid character
    ORA-06512: at “SYS.DBMS_UTILITY”,
    ORA-06512: at “SYS.DBMS_APPLY_ADM”,line 811
    ORA-06512: at “SYS.DBMS_STREAMS_ADM”, line 1860
    ORA-06512: at line 1

    i removed strmadmin and execute the above command multiple time ….and some config will remain in instantiated object and dont remove…..after all when i rebuild stream enviroment it dosent replicate any thing and all of the proccess are Enable…what i must to do

  3. Thank you for sharing – I’ve used option #1 quite extensively in a sandbox environment. I execute dbms_streams_adm.remove_streams_configurations and also drop the streams administrator after that.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s