About Using a Standby Database

You should use a standby database for its high availability and failover functions, and as a backup for the primary database.

You schedule frequent and regular replication jobs from the primary database to a secondary database in a standby database configuration. Configure short intervals in the replication to enable writing to the primary database and facilitate reading from the secondary database without causing any synchronization or data integrity problems.

Because a standby database is essentially a read-only database, you can use the standby database as a business intelligence query server, relieving the workload of the primary database and improving query performance.

The following topics explain how to use a standby database:

• Configure a Standby Database

• Create the Database Object for the Standby Database Configuration

• Create Connection Pools for the Standby Database Configuration

• Update Write-Back Scripts in a Standby Database Configuration

• Set Up Usage Tracking in a Standby Database Configuration

• Set Up Event Polling in a Standby Database Configuration

• Set Up Oracle BI Scheduler in a Standby Database Configuration

Configure a Standby Database

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

When you use a standby database configuration, 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 isn't 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 the illustration standby_db_config.gif

Create 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 isn't assigned, to prevent the Oracle BI Server from creating temporary tables in the standby database.

1. In the Model 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.

Create Connection Pools for the Standby Database Configuration

After you've 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. 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 Create or Change Connection Pools.

1. In the Model 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.
6. In the Model Administration Tool, in the Physical layer, right-click the database object for the standby database configuration and select New Object, then select Connection Pool.
7. Provide a name for the connection pool, and ensure that the call interface is appropriate for the primary database type.
8. Provide the Data source name for the primary database.
9. Enter a user name and password for the primary database.
10. Click OK.

Update 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 is automatically transferred to the standby database through the regularly scheduled replication between the primary and secondary databases. The information is available through the standby connection pool.

The following example shows a write-back 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"

Set 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 Model 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 Administering Oracle Analytics Server.

Set 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 available to the Oracle BI Server. It's normally exposed only in the Physical layer of the Model Administration Tool, where it's 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're 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 Administering Oracle Analytics Server for full information about event polling, including how to set up, activate, and populate event tables.

Set 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.