About Using a Standby Database with Oracle Business Intelligence

A standby database is used mainly for its high availability and failover functions as a backup for the primary database.

In a standby database configuration, there is regularly scheduled replication from the primary database to the secondary database. The latency of this replication must be short enough that writing to the primary database while reading from the secondary database does not cause any synchronization or data integrity problems.

Because a standby database 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.

The following sections explain how to use a standby database with Oracle Business Intelligence:

Configuring a Standby Database with Oracle Business Intelligence

In a standby database configuration, you have two databases: a primary database that handles all write operations and is the source of truth for data integrity, and a secondary database that is exposed as a read-only source.

When you use a standby database configuration with Oracle Business Intelligence, all write operations are off-loaded to the primary database, and read operations are sent to the standby database.

Write operations that need to be routed to the primary source may include the following:

  • Oracle BI Scheduler job and instance data

  • Temporary tables for performance enhancements

  • Writeback scripts for aggregate persistence

  • Usage tracking data, if usage tracking has been enabled

  • Event polling table data, if event polling tables are being used

The following list provides an overview of how to configure the Oracle BI Server to use a standby database.

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

  2. Configure two connection pools for the database object:

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

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

  3. Update any connection scripts that write to the database so that they explicitly specify the primary database connection pool.

  4. If usage tracking has been enabled, update the usage tracking configuration to use the primary connection.

  5. If event polling tables are being used, update the event polling database configuration to use the primary connection.

  6. Ensure that Oracle BI Scheduler is not configured to use any standby sources.

Even though there are two separate physical data sources for the standby database configuration, you create only one database object in the Physical layer. The image shows the database object and connection pools for the standby database configuration in the Physical layer.

Description of GUID-35A8A93D-504B-40FA-AFAE-D12503D3E338-default.gif follows
Description of the illustration GUID-35A8A93D-504B-40FA-AFAE-D12503D3E338-default.gif

Creating the Database Object for the Standby Database Configuration

Use the Administration Tool to create a database object in the repository for the standby database configuration.

When you create the database object, make sure that the persist connection pool is not assigned, to prevent the Oracle BI Server from creating temporary tables in the standby database.

  1. In the Administration Tool, right-click the Physical layer and select New Database to create a database object.
  2. In Name, provide a name for the database.
  3. From the Database Type list, select the type of database.
  4. In the Persist connection pool field, verify that the value is not assigned.

Creating Connection Pools for the Standby Database Configuration

After you have created a database object in the repository for the standby database configuration, use the Administration Tool to create two connection pools, one that points to the standby database, and another that points to the primary database.

Because the standby connection pool is used for the majority of connections, make sure that the standby connection pool is listed first.

Note:

Connection pools are used in the order listed, until the maximum number of connections is achieved. Ensure that the maximum number of connections is set in accordance with the standby database tuning.

See Creating or Changing Connection Pools.

  1. In the Administration Tool, in the Physical layer, right-click the database object for the standby database configuration and select New Object, then select Connection Pool.

  2. Provide a name for the connection pool, and ensure that the call interface is appropriate for the standby database type.

  3. Provide the Data source name for the standby database.

  4. Enter a user name and password for the standby database.

  5. Click OK.

  1. In the Administration Tool, in the Physical layer, right-click the database object for the standby database configuration and select New Object, then select Connection Pool.
  2. Provide a name for the connection pool, and ensure that the call interface is appropriate for the primary database type.
  3. Provide the Data source name for the primary database.
  4. Enter a user name and password for the primary database.
  5. Click OK.

Updating Write-Back Scripts in a Standby Database Configuration

If you use scripts that write to the database, such as scripts for aggregate persistence, you must update the scripts to explicitly refer to the primary connection pool.

Information written through the primary connection will be automatically transferred to the standby database, through the regularly scheduled replication between the primary and secondary databases, and will become available through the standby connection pool.

The following example shows a writeback script for aggregate persistence that explicitly specifies the primary connection pool:

create aggregates sc_rev_qty_yr_cat for "DimSnowflakeSales"."SalesFacts"
("Revenue", "QtySold") at levels ("DimSnowflakeSales"."Time"."Year",
"DimSnowflakeSales"."Product"."Category") using connection pool
"StandbyDemo"."Primary Connection" in "StandbyDemo"."My_Schema"

Setting Up Usage Tracking in a Standby Database Configuration

The Oracle BI Server supports the collection of usage tracking data.

When usage tracking is enabled, the Oracle BI Server collects usage tracking data for each query and writes statistics to a usage tracking log file or inserts them directly to a database table.

If you want to enable usage tracking on a standby database configuration using direct insertion, you must create the table used to store the usage tracking data such as S_NQ_ACCT on the primary database. Then, import the table into the physical layer of the repository using the Administration Tool.

You must ensure that the database object for the usage tracking table is configured with both the standby connection pool and the primary connection pool. Then, ensure that the CONNECTION_POOL parameter for usage tracking points to the primary database. For example, in NQSConfig.ini:

CONNECTION_POOL = "StandbyDatabaseConfiguration"."Primary Connection";

See Managing Usage Tracking in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

Setting Up Event Polling in a Standby Database Configuration

You can use an Oracle BI Server event polling table (event table) as a way to notify the Oracle BI Server that one or more physical tables have been updated.

The event table is a physical table that resides on a database accessible to the Oracle BI Server. It is normally exposed only in the Physical layer of the Administration Tool, where it is identified in the Physical Table dialog as an Oracle BI Server event table.

The Oracle BI Server requires write access to the event polling table. Because of this, if you are using event polling in a standby database configuration, you must ensure that the database object for the event table only references the primary connection pool.

See Cache Event Processing with an Event Polling Table in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for full information about event polling, including how to set up, activate, and populate event tables.

Setting Up Oracle BI Scheduler in a Standby Database Configuration

Oracle BI Scheduler is an extensible application and server that manages and schedules jobs, both scripted and unscripted.

Oracle BI Scheduler is an extensible application and server that manages and schedules jobs, both scripted and unscripted. To use Oracle BI Scheduler in a standby database configuration, you must ensure that the database object for Oracle BI Scheduler only references the primary connection pool.

See Configuration Tasks for Oracle BI Scheduler in Integrator's Guide for Oracle Business Intelligence Enterprise Edition for full information about setting up and using Oracle BI Scheduler.