Re: VPD aware replication ideas?
Date: Tue, 26 Jun 2012 08:25:36 -0700 (PDT)
Message-ID: <1340724336.90196.YahooMailNeo_at_web162002.mail.bf1.yahoo.com>
David,
As you indicated, VPD policies are based on traditional access. I think you are aware of all the ways.
The only way to replicate data based on the existing VPD policies would be to do "SELECT" statements to get the policies enforced to define what data gets replicated. If it is an ETL tool doing the select or an MV refresh job doing the select, assuming the login sets the context needed by your VPD policies, only the appropriate data will select.
If you want to replicate subsets of data, you can use GG as you mention, but you'll be defining the replication policies in GG logic so that GG knows what data with what keys/labels goes to which system in real time.
I don't see an easy way out for you that enables you to take advantage of the existing VPD based policies. How big are these data sets? Could you do full refreshes of all subset tables in the 2 hour time window you have or do you need real-time / incremental feeds to keep up?
From: David Mann <dmann99_at_gmail.com> To: oracle-l_at_freelists.org
Sent: Tuesday, June 26, 2012 10:30 AM
Subject: VPD aware replication ideas?
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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 26 2012 - 10:25:36 CDT