RE: using flashback in streams env

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Wed, 24 Aug 2011 06:33:50 +0200
Message-ID: <4814386347E41145AAE79139EAA39898150E4F4740_at_ws03-exch07.iconos.be>

Venkat,

I don't think that is possible to restart the apply after flashing back the source db and opening it with resetlogs. Not without flashing back the target databases as well.

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

---

From: Venkat Krish [mailto:venkat.lear_at_gmail.com] Sent: woensdag 24 augustus 2011 4:40
To: D'Hooge Freek
Cc: oracle-l
Subject: Re: using flashback in streams env

Thanks Freek. Sorry if my first email wasn't very clear.

The source db is the one that needs to be flashed back. but I don't want the target databases ( destination database where apply is running) to be rolled back..( because there are many other objects other than the replicated objects)

I theorizing that..
I can stop apply on target and 
then do flashback on source, then restart apply

but was just checking if anyone had done it and if yes, to get some pointers/lessons learned.

Thanks for your time & response,
Venkat
On Tue, Aug 23, 2011 at 12:44 PM, D'Hooge Freek <Freek.DHooge_at_uptime.be> wrote: Venkat,

Are you going to perform the flashback on the source db or on the target database?

A couple of months ago I worked with streams on a migration project, where we replicated from the old environment to the new one. During the day users where testing on the new environment and at night we would flashback the database and let it catch up with the replication.

Following steps where performed to release the target db to the testers:

. stop streams on the source db
. stop the apply on the target db
. create a flashback restore point on the target db
. recreate the sequences on the target db for which the last_number in the source db is different then that in the target db
. recompile the invalid packages
. enable the jobs in dbms_scheduler (we disabled them after the db was initially setup)

To put the db back in replication:

. flashback the target database to the restore point
. open the database with resetlogs
. modify the passwords on the target db to prevent end users from connecting
. start the apply on the target db
. start streams on the source db
. drop the restore point

In your case, you would add the generation of the data (on the target db) after the recompilation (or after enabling the jobs)

If you are going to flashback the source db you would perform the same steps, but you would also need to create a restore point on all target databases before creating the restore point on the source db. The flashback should then be done on all databases before restarting the capture / apply.

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

---
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Venkat Krish
Sent: dinsdag 23 augustus 2011 16:22
To: oracle-l
Subject: using flashback in streams env

Hello listers -
 Just wondering if anyone have experience on dealing/implementing with Streams+Flashback combination.

Here is my situation..

-- 30+ tables changes are to be captured in source db (say db_source) and to be replicated to diff database (say db_target)
-- db version: 11.2.0.2 (both database) running on Solaris

this is a testing environment & there is a pre-req to run some process from application to setup some mandatory data into database before the start of each test. (no access to application code and the option of getting it done from db-scripts is not feasible)

I know flashback would work great , but I am not sure about the same on an env with streams configured.

Here is my idea

(-- assume all db configurations are in place to store flashback logs and database is enabled with Flash-logging )

t1) stop capture process at source db, run the application to set up data (needed before every test)
t2) create a restore point
t3) start capture
t4) run the test


now if I need to do a different test, I am hoping to get back to t2 (flashback database + db bounce).

Am I missing/overlooking anything to consider? The target database (where apply is running) is not critical to be in-sync with source or to maintain data integrity. (if needed, I can stop apply process and truncate tables on target..)

Any clues/pointers would be much appreciated,

Thanks in advance,
Venkat

PS: Only a handful tables ( less than 50 tables ) are being replicated. So I am also open to run  flashback table (instead of flashback database).

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 23 2011 - 23:33:50 CDT