Active Data Guard and Invalidations

To provide data source for our datawarehouse (in a seperate MS SQL database, god help it, but that's beside the point :-), we have a setup where we have several views where the datawarehouse connection user has been granted select rights.

When we got Active Data Guard in the spring, we let the datawarehouse user connect to the standby instance to offload the quite heavy selects from the production database. But we did get a surprise concerning object invalidation in this setup...

Lars Bo Vanting, the consultant from Blue Gecko that handles most of our DBA work, has written this with a good example on the Blue Gecko blog, so I won't repeat his work. I'll just outline the problem below and refer you to his blog post for more details :-)


Say we have a view that joins some tables and selects some of the columns to provide source data for the datawarehouse. Now we make some DDL changes to one of the tables - not something that is relevant for the view, but the view is invalidated, of course.

Before we used Active Data Guard, this would happen:

1. The table DDL invalidates the view.
2. The datawarehouse connects and selects from the view.
3. It is invalid so it is automatically compiled.
4. The compilation goes well (the DDL did not affect the view definition.)
5. The view is now valid.
6. The select is carried out and data returned to the datawarehouse.

With Active Data Guard, this happens instead:

1. The table DDL on primary invalidates the view.
2. The INVALID status is propagated to standby.
3. The datawarehouse connects to standby database and selects from the view.
4. It is invalid and it attempts to automatically compile.
5. But standby database is read-only, and we get an error like:
   ORA-04045: errors during recompilation/revalidation of BGDBA.TESTTAB1_V
   ORA-16000: database open for read-only access

Because the standby database is read-only, the normal revalidation of invalidated objects cannot work!

So we have to make sure that any invalid views that are used in the Active Data Guard standby database, they have to be validated / recompiled on the primary database, so that the VALID status will be propagated to the standby database.

We have thought of scheduling a job to attempt compiling invalid views (only those intended for the datawarehouse) but some of the views are accessed every five minutes, so the job would need to be scheduled very often. If some "on invalidation" trigger had existed, we might have used that.

Luckily it is a rare thing for us, so at the moment we just manually recompile the views as needed.