Understanding Oracle GoldenGate and PeopleSoft Environments

Oracle GoldenGate enables you to offload resource-intensive activities from a production database (source) to a synchronized standby (target) database. Oracle GoldenGate enables access to a physical target database for queries, sorting, reporting, web-based access, and so on, while continuously applying changes received from the source production database. If you use Oracle GoldenGate at your site, PeopleTools provides the infrastructure to use Oracle GoldenGate with your PeopleSoft application databases.

The following diagram depicts how Oracle GoldenGate enables you to incorporate a target database to offload designated read-only transactions, freeing resources to handle the read-write transactions on the source database.

Oracle GoldenGate synchronizing data on source and target databases

The following table describes the elements within the diagram:

Element Description

Source Database (primary)

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.

Target Database (standby or reporting database)

Your clone of the source database designed to handle read-only (RO), or mostly-read-only (MRO), requests so that those transactions can be offloaded from your source 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 GoldenGate

Synchronizes the data stored in the source and target databases so that they remain exact duplicates. This is achieved using a combination of Oracle GoldenGate features, DBLINKS, and Remote Synonyms defined by scripts delivered with PeopleTools.

Primary Access ID

The PeopleSoft access ID used for connecting to the source production database as well as the target database.

The access ID and access ID passwords must be the same on the source and target databases. For example, if you use EMDBO as your access ID on the source database, you must also use EMDBO as the access ID on the target database, and you must keep the passwords for EMDBO the same on both databases.

Note: Only one access ID is required, unlike Oracle Active Data Guard, where the PeopleSoft implementation requires two access IDs (a source and a secondary access ID).

Read Only components

When Oracle GoldenGate is enabled and PeopleSoft is configured with a target database, these components are RO enabled:

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

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

  • Tree Manager: Tree Viewer

  • BI Publisher: Query Report Viewer

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

  • 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 target 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 GoldenGate the following processes are enabled, as delivered, to run against the target 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 target 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 target database, and selects output type Feeds on the Process Scheduler Request page, that process will be redirected to the source database. This overrides the RO enabled Run Scheduled Query process.

Note: The use of Oracle GoldenGate 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 target database, refer to Configuring Read-Only Processes. SQR Processes that are generally considered reports are ideal candidates for redirection to the target database.

Oracle GoldenGate 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. See Installing Oracle GoldenGate later in this topic.

The source and target databases need to be set up according to the Oracle GoldenGate documentation.

The basic configuration for PeopleSoft and Oracle GoldenGate (OGG) consists of two databases, the source database and the target database. Characteristics of the server connections to these databases are:

Server Connection Characteristics

Application Server

  • Source connection and target connection both use the same access ID.

  • Each server process (PSAPPSRV, PSSAMSRV, and so on) can connect to either database, as needed.

Process Scheduler Server

  • Source connection and target connection both use the same access ID.

  • Each batch server element (PSAESRV, PSAE, COBOL, SQR) can connect to the source database as needed.

  • Only PSAESRV connects to the target database as needed.

Note:

For an Oracle GoldenGate environment, the secondary connection to the target database utilizes the same login credentials used for the source connection.

Note:

Whether both databases reside on the same or different servers, Oracle GoldenGate binaries need to be installed twice, in two separate directories. One installation is for the source database, and the other is for the target database.

See your Oracle Golden Gate documentation for more information.