Skip to Main Content
Return to Navigation

Implementing Oracle Active Data Guard

This section provides an overview and discusses:

Understanding Active Data Guard Within PeopleSoft

Oracle Active Data Guard, with Oracle Database 11g Enterprise Edition, enables you to offload resource-intensive activities from a production database to a synchronized standby database. Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, and so on, while continuously applying changes received from the production database.

If you use Oracle Active Data Guard at your site, PeopleTools provides the infrastructure to use Active Data Guard with your PeopleSoft application databases.

Note: The intent of this feature integration is that most SQLs in a mostly-read-only (MRO) marked unit of work are redirected to the standby database in an Oracle Active Data Guard configuration.

The ability to redirect a unit of work(UOW) to a standby database is limited to PeopleSoft components and processes. Within any MRO marked component or process, not all SQL is always redirected to a standby database. The percentage of SQL that is redirected depends on the specific component or process and what types of calls are made within that UOW.

Note: For an optimal implementation of PeopleSoft on Active Data Guard, you should configure ADG for sub-second replication.

Image: Oracle Active Data Guard synchronizing the primary and standby database so read-only requests can be routed to the standby database for processing

The following diagram depicts how Oracle Active Data Guard enables you to incorporate the use of a standby database for you to offload designated read-only transactions, freeing up more resources to handle the read-write transactions on your primary (production) database.

Oracle Active Data Guard synchronizing the primary and standby database so read-only requests can be routed to the standby database for processing

Note: This documentation uses the acronyms RO to refer to "read-only" and MRO to refer to "mostly-read-only."

The following table describes the elements within the diagram:

Element

Description

Primary Database

Your production database, handling the read-write requests of your transactional system. For example, this database fills orders, updates employee information, adds new product offerings, and so on.

Standby Database

Your clone of the primary database designed to handle read-only, or mostly-read-only (MRO), requests so that those transactions can be offloaded from your primary database, conserving resources on the production system. Examples of MRO requests include, PSQUERY Viewer, Tree Viewer, components that only submit SELECT SQL to display lists of employees, products, and so on.

Oracle Active Data Guard

Synchronizes the data stored in the primary and standby databases so that they remain exact duplicates. This is achieved using a combination of Oracle Active Data Guard features and DBLINKS and Remote Synonyms defined by scripts delivered with PeopleTools.

Primary Access ID

The PeopleSoft access ID used for connecting to the primary, production database.

Secondary Access ID

The alternative PeopleSoft database user (access ID), which will be used for connecting to the standby database and subsequently accessing the primary database’s tables using LOCAL and REMOTE synonyms.

Read Only components

When Oracle Active Data Guard is enabled and PeopleSoft is configured with a standby database, these components are RO enabled "out of the box:"

  • Query Manager: PSQUERY Viewer queries will be run on the Standby database.

    Note: The PSQUERY Viewer does not need to be explicitly marked RO. Units of work utilizing ICQueryService are redirected to the Standby DB. Because PSQUERY Viewer uses the ICQueryService, its SQL will be redirected to the standby database.

  • Tree Manager: Tree Viewer

  • BI Publisher: Query Report Viewer

  • Pivot Grid: Pivot Grid Viewer and Pivot Grid viewed as a pagelet

  • SES Feeds Crawler

  • Feeds Reader

  • QAS (Query as a Service)

  • Component Interfaces: Component Interfaces work on any component marked as MRO.

Other components can also be made to run against the standby database, by setting the Read Only option in the component properties dialog box in Application Designer.

See Configuring Read-Only Components.

Limitations:

  • BI Publisher: If Query Report Scheduler is RO enabled, you cannot Schedule a Bursted report.

  • Application Engine: An Application Engine process cannot be marked as RO if it makes any publish call to Integration Broker. There is a limited set of Integration Broker service operations that are enabled, and those operations are specifically related to Feeds and QAS.

Read Only processes

When PeopleSoft is configured for Oracle Active Data Guard the following processes are enabled, as delivered, to run against the standby database:

PeopleSoft Query:

  • PSCONQRS: Run Scheduled Connected Queries

  • PSQUERY: Scheduled Query

PS/nVision:

  • Report

  • Report drilldown

  • Report book

SQRs:

  • XRFAPFL

  • XRFFLPN

  • XRFFLRC

  • XRFIELDS

  • XRFMENU

  • XRFPANEL

  • XRFPCFL

  • XRFPNPC

  • XRFRCFL

  • XRFRCPN

  • XRFWIN

  • XRFFLPC

Audit Utilities:

  • SWPAUDIT

  • SYSAUDIT

  • DDDAUDIT

Other processes can also be enabled to run against the standby database by setting the Read Only option on the Process Definition properties page in Process Scheduler.

Note: For Scheduled Query, if a user attempts to schedule a query to run against the standby database, and selects output type Feeds on the Process Scheduler Request page, that process will be redirected to the primary database. This overrides the RO enabled Run Scheduled Query process

Note: The use of Oracle Active Data Guard with PeopleSoft batch processing only applies to the following: Application Engine processes run through the Process Scheduler with PSAESRV configured and SQR processes.

Note: To enable SQR processes to run against the standby database, refer to Configuring Read-Only Processes. SQR Processes that are generally considered reports are ideal candidates for redirection to the standby database.

Installing and Configuring Oracle Active Data Guard

Oracle Active Data Guard needs to be licensed, installed, and enabled for your server before you can begin setting up your PeopleSoft system to take advantage of this feature. The primary and standby databases need to be set up according to the Oracle Active Data Guard documentation.

Creating the Secondary Access ID

This section discusses how to configure the secondary access ID for use with your primary and standby databases.

To configure the secondary access ID:

  1. Run the psadmin.sql script against your primary database.

    • Run the script from PS_HOME\scripts.

    • When prompted, provide the secondary access ID and password.

  2. Insert a row in the PSDBOWNER table for the standby database and the secondary access ID.

    For example:

    INSERT INTO PS.PSDBOWNER VALUES('DATABASE', 'SECONDARY_ACCESS_ID’);
    Commit;
    
  3. Create an access profile to associate the secondary access ID with a new symbolic ID.

    • In Application Designer, select Tools, Misc Def, Access Profile, and click New.

    • Provide the new symbolic ID, and enter the secondary access ID and password you created using psadmin.sql.

Creating the Domain Boot User

Create or clone a PeopleSoft user ID and associate it with the secondary access ID so that the user profile will be able to start an application server domain and alternate Process Scheduler server connecting to the standby database.

To create the domain boot user:

  1. Select PeopleTools, Security, Copy User Profiles, and enter a new user ID and password.

  2. From the Symbolic ID dropdown list on the General tab for the user ID you just created, select the symbolic ID associated with your secondary access ID.

  3. Open Data Mover and encrypt the new user profile’s password by running the following:

    UPDATE  PSOPRDEFN SET operpswd=’<PASSWORD>’, encrypted=0
    Where oprid=’<USER_ID>’;
    ENCRYPT_PASSWORD *;
    

Creating Synonyms and Database Links

The mechanism that is used to make database objects in the standby database available in the PeopleSoft implementation of Oracle Active Data Guard requires the existence of a secondary access ID. The secondary access ID does not own any objects, so sufficient access to and awareness of objects in the primary database must be made to the secondary access ID.

To associate standby database objects with the equivalents in the primary database, you must create:

  • Local synonyms.

  • Database links to the primary database.

  • Remote synonyms.

PeopleSoft delivers the following scripts (located in PS_HOME\scripts) to create the synonyms and database links:

The following table describes each script, and any modifications that you need to make before you run them.

Script

Description

Usage

createlocalsynonyms.sql

This script creates local synonyms for all PeopleSoft tables and views.

Run this script against the primary database while connected as the primary access ID.

Before you run the script, edit it to replace <SYSADM> with the primary access ID.

createdblinktoprimary.sql

Run this script connected to the primary database with the secondary access ID

Before you run the script, edit it to make the following changes:

  • Replace <DBNAME> with the primary database TNSALIAS.

  • Replace <SYSADM> with the primary access ID.

  • Replace <PASSWORD> with the primary access ID password.

createremotesynonyms.sql

This script creates remote synonyms.

Remote synonyms are required for the tables identified as requiring DML access. If you decide to set a component to "read-only" after your analysis, then you need to include the underlying tables that require DML access to this script manually.

Run this script against the primary database connected as the secondary access ID.

Configuring Domains

The domains connecting to your primary database must also be configured to connect to your standby database. This is accomplished by specifying the standby connection credentials in the Startup section of the domain configuration for both the application server and Process Scheduler domains.

For example:

Values for config section - Startup
    DBName=PRIMARY
    DBType=ORACLE
    UserId=QEDMO
    UserPswd=QEDMO
    ConnectId=people
    ConnectPswd=peop1e
    ServerName=
    StandbyDBName=STANDBY
    StandbyDBType=ORACLE
    StandbyUserId=PTSTNDBY
    StandbyUserPswd=PTSTNDBY

The Startup section enables you to specify signon credentials for both the primary and standby databases. The following table shows which parameters are associated with which database.

Database

Parameters

Primary

DBName

DBType

UserId

UserPswd

Standby

StandbyDBName

StandbyDBType

StandbyUserID

StandbyUserPswd

The Standby… parameters are used to maintain the simultaneous connection to the standby database.

Note: Typically, the primary and standby database share a common connect ID.

Configuring Read-Only Components

When Oracle Active Data Guard is enabled, a collection of components are supported for running against the standby database. The list of components appears previously in this document.

See Understanding Active Data Guard Within PeopleSoft.

However, you can also set other components to run against the standby database to divert selected requests from your primary database. To set a component to run in "Read-Only" mode (run against the standby database) you need to modify the component properties.

To configure a component for read-only processing:

  1. In Application Designer, open the component.

  2. Select View, Definition Properties.

  3. On the Component Properties dialog box, select the Use tab.

  4. Select the Read Only check box.

  5. Click OK.

Note: Selecting the Read Only property should only be done after detailed analysis.

Note: It is important to understand the behavior of a mostly read only (MRO) component when it calls other components. If an MRO component is executed, then all components subsequently called by the MRO marked component will inherit the MRO attribute behavior and the SQL calls and the called components will be redirected to the standby database, when possible. Given this behavior, you need to make sure that the requests generated from the component (and called components) perform operations like selecting and displaying lists, rather than inserting, updating, or deleting rows. If there are some DML operations that the component must execute, the affected tables need to be identified, and a remote synonym needs to be created between the standby and primary databases. See the delivered PS_HOME/scripts/CREATEREMOTESYNONYMS.SQL script for an example of how to create a remote synonym.

Configuring Read-Only Processes

If you have Oracle Active Data Guard configured and enabled for your PeopleSoft system, Process Scheduler processes can be set to run against the standby database to divert selected processes from your primary database.

Note: The use of Oracle Active Data Guard with PeopleSoft batch processing only applies to the following: Application Engine processes run through the Process Scheduler with PSAESRV configured and SQR processes.

Note: PeopleSoft does not deliver any SQR processes enabled by default to run against the standby database. SQR Processes that are generally considered reports are ideal candidates for standby database processing (being set to read-only).

To configure Process Scheduler processes for read-only processing:

  1. Select PeopleTools, Process Scheduler, Processes.

  2. Open the process definition.

  3. On the Process Definition page, select the Read Only check box.

  4. Click OK.

Note: Selecting the Read Only option should only be done after detailed analysis. You need to make sure that the processes perform operations like selecting data or generating reports, rather than inserting, updating, or deleting rows. If there are some DML operations that the process must make, the affected tables need to be identified, and a remote synonym needs to be created between the standby and primary databases.

Note: If an MRO marked SQR program runs and is performing DML on a table not accounted for in the Oracle Active Data Guard configuration, then that SQR process will fail. To fully enable this SQR process, the tables being written to need to have remote synonyms created between the standby and primary databases. See the delivered PS_HOME/scripts/CREATEREMOTESYNONYMS.SQL script for example on how to create a remote synonym.

Disabling Mostly-Read-Only Attributed Features

If you do not want any of the read-only enabled delivered components or processes redirected to the standby database, then you can disable the read-only attribute for the specific component or process by going to the appropriate component definition or process definition and deselecting the read-only check box.

In the following features, the read-only attribute behavior is hard-coded:

  • Feeds reader.

  • SES feeds crawler.

  • Query as a service (QAS).

  • Any component that uses ICQueryService.

    Note: The SQL of any component that uses ICQueryService, will be redirected to the standby database. PSQUERY Viewer is an example of one such component; it does not need to be explicitly marked RO, because it uses ICQueryService.

The following sections describe how to disable hard-coded read-only attributes for these features.

Disabling Feeds Reader and SES Crawler Read-Only Behavior

To disable the read-only behavior of the Feeds Reader and SES Crawler:

  1. Select select PeopleTools, then select Feeds, then select Feed Options.

  2. On the Feed Options page, deselect the Enable ADG check box.

  3. Click Save.

Disabling the Read-Only Behavior for QAS

To disable the read-only behavior for QAS Run the PT_SETQASADG Application Engine program on your database.

For example, from the DOS command line the syntax is:

<PS_HOME>\bin\client\winx86\psae -CD <dbname> -CT ORACLE -CO <userid> -CP <userpswd> -R RUN01 -AI PT_SETQASADG -I

Use the values for the database name and user ID that you entered on the startup tab of Configuration Manager for <dbname> and <userid> respectively. However, be aware that <userpswd> is not the same as the connect password you entered on the Configuration Manager startup tab. Enter a value for <userpswd> that is the password you want to be associated with the <userid>.

Disabling the Read-Only Behavior for Components that use ICQueryService

To disable routing of components that use ICQueryService to the standby database, modify the following line in the Database Options section of the Application Server configuration file:

;Disable ICQueryService Standby Routing=

Enable this line by removing the initial comment character (;) and setting the parameter value to 1. For example:

 [Database Options]
;=========================================================================
; Database-specific configuration options
;=========================================================================
SybasePacketSize=
UseLocalOracleDB=0
;ORACLE_SID=
EnableDBMonitoring=1
PSDB Maximum Cursors=
Disable ICQueryService Standby Routing=1