Hi all,

I'm setting up a Streams environment; two 9iR2 databases are involved,
with one-way DML replication on a subset of the tables in a single
schema. DDL changes are not replicated. In addition to receiving DML
changes via Streams, the destination database also has users entering
data directly.

I'm wondering how to handle application upgrades in this environment.
Our upgrade process amounts to running a SQL script that applies a
schema delta and manipulates data, and I don't want the data
manipulation that took place in the source database replicated in the
target database by Streams. I'm guessing the process should go
something like this:

1) Stop the capture process in the source database
2) Wait for the apply process in the target database to catch up (if
it hasn't), then stop it
3) Go through the upgrade on each database
4) Reset the instantiation SCNs in the target database
5) Start the apply process
6) Start the capture process

....and hopefully, we carry on as before. Is that going to work? Have I
missed something?

TIA
Tarby

Tarby777 wrote:
> Hi all,
>
> I'm setting up a Streams environment; two 9iR2 databases are involved,
> with one-way DML replication on a subset of the tables in a single
> schema. DDL changes are not replicated. In addition to receiving DML
> changes via Streams, the destination database also has users entering
> data directly.
>
> I'm wondering how to handle application upgrades in this environment.
> Our upgrade process amounts to running a SQL script that applies a
> schema delta and manipulates data, and I don't want the data
> manipulation that took place in the source database replicated in the
> target database by Streams. I'm guessing the process should go
> something like this:
>
> 1) Stop the capture process in the source database
> 2) Wait for the apply process in the target database to catch up (if
> it hasn't), then stop it
> 3) Go through the upgrade on each database
> 4) Reset the instantiation SCNs in the target database
> 5) Start the apply process
> 6) Start the capture process
>
> ...and hopefully, we carry on as before. Is that going to work? Have I
> missed something?
>
> TIA
> Tarby
>

Hi Tarby,
that can be done in an easy way.

Look at the streams documentation for TAGS.

Basically you'll need to :

- Check the current session tag.
- Write it down.
- Alter the tag to a different value - this will avoid the apply process
to apply the next created LCRs.
- Do your DML / Process
- Commit;
- Set the TAG to the original value.

This will avoid any changes made with the new TAG to be applied on the
target databases.

Hope it helps,
Pedro.

On 5 May, 23:20, Pedro Lopes <pedro.lo...@netvisao.pt> wrote:
> Hi Tarby,
> that can be done in an easy way.
>
> Look at the streams documentation for TAGS.
>
> Basically you'll need to :
>
> - Check the current session tag.
> - Write it down.
> - Alter the tag to a different value - this will avoid the apply process
> to apply the next created LCRs.
> - Do your DML / Process
> - Commit;
> - Set the TAG to the original value.
>
> This will avoid any changes made with the new TAG to be applied on the
> target databases.
>
> Hope it helps,
> Pedro.

That's a great solution, Pedro - it works really well, thankyou. I
guess the one thing I have to be careful of is that our upgrade script
sometimes changes the database connection; it might do some stuff as
SYSTEM, then it might reconnect as the user who owns the application
schema to do some more stuff, and so on. What I see from my tests this
morning is that DBMS_STREAMS.SET_TAG only affects the current
connection... it's not global, so any time I change connection, I'll
also have to set a non-null tag again.

Thanks again,
Tarby