Replication: does Streams works after role transition i.e switchover or failover

Hello,

Today I have got a call containing couple of questions on streams replication in a standby environment especially when the streams is on downstream capture database.

Here are the questions & my answers to it.

Environment:-

Primary or Source: -10GR2 RAC with 2 node

Standby: Physical Standby

Streams: Downstreams capture database in different server

Behavior of streams at the time of shifting from Primary to Standby and vice versa :

a ) In case if Primary is shifted to Secondary(stand by) DB, the streams DB is to be replicated fully (not from point where it stopped)

This is called switchover/Failover or the role transitions. In your streams environment as the changes captured in downstream database (different database) with minimal changes or the pre-requisite this transitions can be performed seamlessly and the streams continue to work.

Procedure:-

Pre-requisities:-

1) Standby Site must be in Supplimental Logging mode

2) DB Links must resolve from the standby, i.e DBLinks should exists before Switchover/failoer

3) Client TNS Names should have name resolution to Primary/Standby/Streams DB address, streams tns entry should have both

4) Add a service name pointing to streams DB so that standby start shipping to streams DB once role transition happens(basically a client failover capability nothing but adding a address line in tns entry to standby site in streams capture database)

Technical:-

1) Ensure Primary / Standby are in sync

2) Shutdown Node 2 (as its in RAC)

3) Perform role transition and verify the roles swapped or not, i.e primary to standby and standby to primary

4) Enable the archive destination of streams db in standby database (current primary)

5) Verify streams working (or require start of capture/propagation/apply) process.

b) Once the shift happens back to Primary DB again streams DB is to be replicated fully (not from the point where it stopped) :

1) No changes required, the procedure applicable as above, ensure primary/standby sync before role transition

2. If we want to delete the existing table from Streams DB what is the process.

Need a complete procedure on how to handle this.

assuming delete=drop

1. Must inform DBA to remove the streams rule before dropping the table that exists in streams.

3. If we mistakenly deleted any table on the Streams DB what is the process to restore. It was suggested the need to import the Table would be from LIVE however need to find whether it would automatically apply the latest changes?. – Need clarification.

1. Yes, export in production and import in streams database and it will automatically apply the changes with an option streams_instantiation=y while import, no other additional considerations are required, if not working some manual instantiation is required (rare cases)

4. IF we stop the Streams DB sync and LIVE DB has shifted to data guard (Maintenance activity performed) and after the maintenance activity we will bring the Secondary DB to Primary what will happen on the Streams DB. Will the Sync happen or not.--> Need Clarification.

1. In line with the Point role transitions, some pre configurations on Primary/Standby is required and streams replication continue to happen irrespective of source site.

BTW, reference document can be found here:-

http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gdataguardroletransitionss-133727.pdf

-Thanks

Geek DBA