If you are talking about a limited or at least known number of tables, and the change volume is not huge, than I'd look into streams.

Streams is flexible enough to allow you to modify some fields before the row is inserted into the target table (using Apply Handler).

We take a backup of production and restore it to QA and then run a scrub script to remove sensitive information. (eg change all the credit card numbers to a test cc number)

This allows you to have a copy of prod and allows you to test your backup. Yes, it takes a while but it does test your backup process.

Tom,

Ok... I've got some things to think about...

But on a similar note... what about one-way replication in PROD-to-PROD environment?

In 10.2.0.4 we are using a crude materialized view refresh
to do a periodic one way sync from PROD-A to PROD-B databases.
Could STREAMS be a better approach for this instead of MV?

Thanks,

Robert.

Tom,

It seems crude to me because the guts of the MV is this...

select * from table@remote_db

And every time it runs (once or twice a day) there is heavy i/o on database.

I inherited this MV and the basic design/philosophy of the thing.

Is there a better way to update a materialized view than just a mass "select *" periodically?

Thanks,

Robert.

Tom,

No sir, we do not have any MV logs for these views.

It was set up very crudely (thought effectively in a simple way).
I'm not sure if there was any method to this 'madness'
or if it was just designed this way out of naivete.

Would MV logs be the way to go then;
dispersing the activity/load of the remote table updates over time
instead of a massive update once a day?

Thanks,

Robert.

I have received a "requirement" quite similar to the original question:

- keep a copy of a production database "in sync"
- on demand suspend the sync process, then
- do some updates on the copy, then
- back out the updates and reactivate the sync process.

One approach being considered is :

- Use Streams Replication to update the copy
- Suspend the "apply process"
- Grab the SCN on the copy database
- do the updates
- Flash back the copy database (or the appropriate tables) to the "old" SCN
- restart the apply process.

I do not like this whole idea. However, like it or not, it would be easier to discard this idea if I knew it didn't work.

I gather from your original answer that doing any updates to the copy will break replication for good. Is that true for this specific scenario as well?

What about using dbms_wm?

I think another way of making a test database(B) which can be in synch by using the following idea:

1)Configure streams between Database A --> B .
2)Make sure Conflict handler is Off on Tables on DB B
2)Synch Database B using streams
3)Stop Apply process on B
4)Now you can Make any changes on Database B and test the way you want
5)Once you are done flashback database B and start apply process. This will again synch the database B.

Thanks - Adarsh Kumar

@Adarsh Kumar : your idea is exactly the same as the one I was trying to describe on October 13. Have you actually gotten this to work?

@Tom : I was witholding embarrassing information, sorry about that. Database B would be a small subset of database A (thus the idea of replication rather than Data Guard) and would be used as a substitute production database while A is stopped for cold backup and batch processing. Then when A is back in business, applications would apply B's changes to A.

Now how awful is that? For over a decade they have been doing TP all day and backup + batch processing at night and they don't know how to move on. Now they want to do some TP while the production database is down, so they came up with the idea of temporarily using database B.

My question above was assuming at least version 10gR2.
I have since found out that, not only is database A still in 9.2, but they are still using rollback segments. I just don't see any way forward without starting over and using the database as it was intended to work.

...a) why would you cold backup?...
They are using TimeFinder (which copies the disk files) and don't dare do an update while it is doing its thing.

...b)...I only see serious and harsh disadvantages...
You said it.

For what it is worth, I will strongly recommend doing what it takes to use the single database, even if it means (gasp!) learning how Oracle really works. No matter what, the application will have to deal with batch updates and TP updates coexisting.

I believe the idea is TimeFinder will copy the files and then you use RMAN on the copies rather than the original. I have no idea why anyone would consider this necessary, unless they think they are still working on mainframes from the '80s.

I shall continue to recommend changing to hot backup, and I shall enjoy quoting you IN UPPERCASE.