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

Oracle CRS Reboot Bloopers

This post is just a delightful recap of “not so bright” actions I’ve seen done unintentionally that wind up causing a reboot of a node or more in CRS. Some were done by DBAs while others were done by system administrators.

Environment HP & Solaris 10gR2

Kill ocssd.bin Process
This instantly crashed the node.

Delete Content under /tmp/.oracle
This hanged CRS. Commands such as crsctl check crs won’t even come back. You have to reboot the node to recreate the socket file under tmp.

Change System Date
This rebooted CRS flaging a “Cluster Integrity” issue. Poor system administrator almost had a heart attack.

Change Physical Hostname
System administrator accidentally changed the physical hostname then changed it back. The host file was changed as well. This hung CRS. A reboot took care of it.