VPD aware replication ideas?
Date: Tue, 26 Jun 2012 10:30:40 -0400
Message-ID: <CAGazuyULCn-29NRNXi+HDPTyaSBvZBXyj-bjQ669gm2P9gRiig_at_mail.gmail.com>
One of the apps I support is has an 11.2 DB that uses VPD for data segregation between clients. We have a contractual requirement to provide a staging database with only 1 client's data in it (primarily so we can DataGuard it to their location and they can hammer it with reports).
So now comes the fun part, providing a feed of changes from just 1 client in that VPD enabled database. I have experience with VPD and I have experience with replication tools, but have only replicated base tables wholesale, not segregated just 1 client's data out while trying to leverage VPD policies.
o We would like the updating of the staging database to be
as-close-to-real-time-as-possible but a minimum is probably every 2
hours
o With a DataGuard step to get it to the client we would like to
reduce redo churn, so trying to avoid a flush and fill scenario
Implementations I have been mulling over...
- It seems that a lot of tools are centered on redo stream... and VPD just doesn't live in that space so I don't see a sane way to do this with something like GoldenGate/Streams/CDC/etc. I like GG and have used it since 2005, but don't see any easy way to invoke VPD policies during replication except for replicating the VPD policy logic (if we can) in the parameter files or maybe by writing a custom stored proc and invoking by a SQLEXEC call... but that seems dicey/risky/unmaintainable if it can be done at all.
And the other side of the coin would be a replication method that leverages a DB login instead of a redo stream...
2) I have been tossing around fast refreshable MVs over a DB link. If the DB link login has a login trigger that sets context for the VPD policies then I might be able to create 1 MV per table as SELECT * FROM REMOTETABLE_at_LINK; . So I would set up the MVs and refresh by polling the source database.
3) Informatica is our approved ETL tool, I could toss it over the fence to that group. If they need real time I hear Informatica has a real time option. I'm no expert on the pros and cons of this, just getting it down on paper. It seems like Informatica just uses a regular login so it could leverage VPD policies.
Any other ideas on how to set up a data feed for 1 client from a VPD enabled source database?
--
Dave Mann
www.brainio.us
www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 26 2012 - 09:30:40 CDT