So you want to use Oracle GoldenGate with Virtual Private Database (VPD)...

I recently had a customer that wanted to extract one client's data out of a VPD enabled multi-tenant database. They were convinced GoldenGate was the solution... but they were way ahead of me with scheduling resources for implementation before fully fleshing out the problem.

What's the (real) problem?

• Extract 1 client’s data from a VPD enabled Multi-Tenant database
• Send this extracted data to a target staging database
• Provide incremental updates to the target staging database with least amount of resources possible (avoid resource and time wasting flush and fill)

Why not GoldenGate?
I am a fan of GoldenGate. I have been using it since 2005, have met or exceeded customer expectations of transactional data movement with it, and generally had good experience with the product and support. I love telling a customer who wants data in a target system nightly or a couple of times a day that I can get it there in seconds with GoldenGate.

But as far as the interplay between GoldenGate and VPD it is all about context.

A regular user login will abide by VPD policies. The policies create WHERE clauses that are added to the user's query at parse time. The results returned to the user abide by the policy rules so they don't see data they are not supposed to see.

GoldenGate primarily operates by reading blocks that it sees fly by in the redo stream. VPD does not live in this space, Only raw Redo blocks live in this space. GoldenGate sees all the blocks and extracts changes for tables listed in its Extract parameter file. Since GoldenGate does not query the database directly looking for changes it does not have the opportunity to leverage VPD policies.

There are some cases when GoldenGate would try to directly access to a table via a login. Keep in mind this is not the mechanism GoldenGate uses to get the bulk of the data from the table, only when it needs to pick up info it can't get via the Redo stream. Here is a common scenario when this happens:

CONNECT ggextract/ggextract
SELECT * FROM AR.INVOICES;

ORA-28113: policy predicate has error

And how does Oracle suggest you get around this error? Apply EXEMPT_ACCESS_POLICY to ggextract user (or whatever user GG logs into for its administrative tasks). This allows GoldenGate access to all records in the table so on the rare occasion when it needs to inspect a record directly comes up it is allowed to read any record in the table.

Well there goes the last sliver of a chance of leveraging the database's VPD feature.

So how do I replicate data and respect VPD policies at the same time?
There are a few options, each has their pluses and minuses:
Attempt to duplicate the policies in the GoldenGate Extract or Replicat Parameter Files

Either in the Extract parameter file:

TABLE AR.INVOICES, WHERE (CUSTOMER_ID=123);

Or in the Replicat parameter file:

MAP AR.INVOICES, TARGET AR.INVOICES, WHERE (CUSTOMER_ID=123);

This will work, but I consider it high on the risk and complexity scale.If someone adds a table and they don't add it correctly to GoldenGate then you may miss data or extract data you didn't expect to.

The other risk is the complexity of VPD policies. Not all VPD Policies are simple WHERE clauses as in the example. If you have a particularly complex VPD policy you may be able to expose a StoredProcedure and add a TOKEN via SQLEXEC but again is the risk worth the reward? For a simple system maybe, for a more complex system you may be creating a very complex and fragile code ecosystem to try to support this behavior.

Use a data movement tool that logs in like a regular user

• ETL Tools like Informatica can log in to a user that respects the VPD policies
• MViews over DB links might be an option. The DB Link should have access to a user that is locked down to 1 client's data.
• EXP as a specific user. Just throwing ideas out here... but if you have a user that respects VPD and has ability to EXP or EXPDP, then it will only export the data it sees

Replicate data wholesale, use VPD policies again on target database
I used this technique to separate reporting functionality from a VPD enabled OLTP databae. I replicated all columns and records of the reporting related tables to the target database. The target database had the same VPD policies implemented as the source database. Any access to the data on the target side was via a user that accessed the data via VPD policies.

In this case the client that needed access to the data for reporting would get a login that enforced VPD policies so they could only see their own data. This is often done via a login trigger.

Conclusion
VPD and GoldenGate live in parallel Universes. VPD lives inside the database and tacks on extra criteria to WHERE clauses of parsed SQL. GoldenGate watches redo blocks to strip out data related to tables you are interested in replicating. They do not work directly together but there are possibly some ways to get to coexist together, it just depends on the amount of complexity and risk you are willing to take on with your project.