Configure Oracle OBIEE with Standby Database (Active Data Guard)

Because an Oracle Standby database (Active Data Guard) is essentially a read-only database, it can be used as a Business intelligence query server, relieving the workload of the primary database and improving query performance.

How it works

You would think since Oracle Standby database is read only database and Oracle OBIEE only generate sql queries, it should work with default configuration. But it’s not that simple , OBIEE generates some write operations and they need to route to Primary database. Following are the example of OBIEE write operations.

  1. Oracle BI Scheduler job and instance data
  2. Temporary tables for performance enhancements
  3. Writeback scripts for aggregate persistence
  4. Usage tracking data, if usage tracking has been enabled
  5. Event polling table data, if event polling tables are being used

Configuration Steps:

  • Create a single database object for the standby database configuration, with temporary table creation disabled.

Capture database object

  • Configure two connection pools for the database object:

A read-only connection pool that points to the standby database

stadnbyconnection

A second connection pool that points to the primary database for write Operations

primaryconnection

  • Update any connection scripts that write to the database so that they explicitly specify the primary database connection pool.
  • If usage tracking has been enabled, update the usage tracking configuration to use the primary connection.
  • If event polling tables are being used, update the event polling database configuration to use the primary connection.
  • Ensure that Oracle BI Scheduler is not configured to use any standby sources.