Re: How to force Oracle Streams restart and uni-directional propagation?

From: Finn Jorgensen <finn.oracledba_at_gmail.com>
Date: Tue, 5 Feb 2008 10:36:58 -0500
Message-ID: <74f79c6b0802050736v4e18abb5gc7175d568e5da097@mail.gmail.com>


Joel,

If you just truncate the tables on the destination side and reinstantiate the primary with a new SCN for which you have archive logs you will end up with the apply process aborting due to errors constantly because records that are supposed to be updated and/or deleted do not exist in the destination. If you turn off the "abort apply due to error" functionality you will end up with "logically corrupt" data. This is not a solution.

If you have lost those archive log files the only real solution is to rebuild the destination data and reinstantiate the primary. The destination data can be rebuilt using exp/imp using the following syntax :

exp userid=user/pwd_at_source FILE=schema1.dmp owner=dept OBJECT_CONSISTENT=y ROWS=Y
imp userid=user/pwd_at_dest FILE=schema1.dmp IGNORE=y COMMIT=y LOG=import.logSTREAMS_INSTANTIATION=y

This will create new data in the destination and set the instantiation SCN which is necessary for the capture and apply processes to be in sync. Then start the capture and apply processes again.

exec DBMS_APPLY_ADM.START_APPLY(apply_name => 'your_apply_name'); -- On destination
exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'your_capture_name'); -- On source side

Finn

On 2/5/08, Joel Lieberman <jlieberman_at_combinatorx.com> wrote:
>
> Greetings
>
>
>
> We have 2 Oracle database servers - a primary (provider) and secondary
> (reporting) server. The provider (primary) instance has several schemas
> that are each duplicated on the secondary (reporting) machine. The
> replication is one-way only: from the primary to the secondary. After
> rebooting the machines, the streams infrastructure stopped working (it has
> restarted without any problems after previous reboots). Capture was in an
> aborted state for each schema on the provider, and could not be restarted
> with the OEM GUI interface.
>
>
>
> The alert log shows that the streams cannot find some required archived
> log files. The application vendor has suggested that we essentially rebuild
> the entire set of instances and re-setup all of the streams. This seems to
> be an unnecessarily complex solution.
>
>
>
> From a "business perspective" it appears that we only need to effectively
> truncate the secondary (reporting) schema, reset the provider streams to
> capture and propagate and let the one-way flow fill up the secondary
> schema. I am aware (but not experienced) with the idea that the streams
> admin schema (STRMADMIN) keeps records of SCNs for all original and
> replicated objects and uses these in its propagation and application logic.
> Since we have lost the required logs (I do not know how that could have
> happened) I am thinking that we should be able to just issue the appropriate
> commands to the provider and STRMADMIN to tell them to disregard the past
> and just start up with the current live set of provider and destination
> schema objects.
>
>
>
> I have not been able to figure out how to force restart for the streams.
> Should it be as simple as truncating the destination tables and simply
> forcing a provider restart? What would the DBMS~PACKAGE(s) commands look
> like? Two days of research have not turned up an actual concrete example of
> how to get things working again without a complete rebuild.
>
>
>
> Thanks for any help that you can provide
>
>
>
> Joel E. Lieberman, Ph.D.
> Data Systems Engineer
> CombinatoRx, Incorporated
> 245 First Street
> Sixteenth Floor
> Cambridge, MA 02142
>
>
>
> Voice: 617-301-7058
> Email: jlieberman_at_combinatorx.com
> Company: www.combinatorx.com
> *^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*
> This email may contain CONFIDENTIAL or PRIVILEGED information and is a
> private communication between the intended addressee and CombinatoRx, Inc.
> If you have received this email in error, reading, copying, using, or
> disclosing its contents to others is prohibited. Please notify us of the
> delivery error by replying to this message, and then delete it from your
> system. Thank you.
>
> *^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 05 2008 - 09:36:58 CST

Original text of this message