Implementing Oracle Active Data Guard
This section provides an overview and steps to configure the Oracle Active Data Guard.
Oracle Active Data Guard, with Oracle Database Enterprise Edition 11g or higher, enables you to offload resource-intensive activities from a production database to a synchronized standby database. Oracle Active Data Guard (ADG) 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 ADG at your site, PeopleTools provides the infrastructure to use ADG with your PeopleSoft application databases.
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 ADG 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.
The PeopleSoft offload reporting infrastructure using ADG enables the use of a standby database for a subset of the PeopleTools reporting features. For an optimal implementation, you should configure ADG for sub-second replication. Slow performance can be expected in configurations where there is significant network latency between the primary and standby databases. To mitigate potential performance issues over WANs with large network latency, an alternative configuration that can be used with our infrastructure is:
local primary database and local standby database + remote standby database
where the local standby database is used for ADG reporting, and the remote standby database is used for high availability purposes.
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.
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:"
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:
|
|
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: Application Engine PeopleSoft Query:
PS/nVision:
SQRs:
Audit Utilities:
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: Do not run DDDAUDIT report on a standby database. 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. |
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.
This section discusses how to configure the secondary access ID for use with your primary and standby databases.
To configure the secondary access ID:
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.
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;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.
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:
Select PeopleTools, Security, Copy User Profiles, and enter a new user ID and password.
From the Symbolic ID drop-down list on the General tab for the user ID you just created, select the symbolic ID associated with your secondary access ID.
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:
createlocalsynonyms.sql
createdblinktoprimary.sql
createremotesynonyms.sql
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 generates the following four sql scripts:
Execute the generated scripts to create local synonyms for all PeopleSoft tables and views, and to grant select privileges to the secondary access ID for all PeopleSoft tables and views. Run these scripts against the primary database while connected as the secondary access ID:
Run these scripts against the primary database while connected as the primary access ID.
|
Run this script against the primary database while connected as the secondary access ID. Before you run the script, edit it to make the following changes:
|
|
createdblinktoprimary.sql |
This script creates a fixed database link between the secondary access ID and the primary access ID on the primary database. |
Run this script against the primary database while connected as the secondary access ID Before you run the script, edit it to make the following changes:
|
|
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 while connected as the secondary access ID. |
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=password
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.
Note: For Active Data Guard users, the StandbyUserId and StandbyUserPswd values are the User ID and password that you created in Creating the Domain Boot User.
For Golden Gate users, the StandbyUserId and StandbyUserPswd values are the same as your User ID and password. For example, if your User ID is QEDMO, then your StandbyUserId is also QEDMO.
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:
In Application Designer, open the component.
Select View, Definition Properties.
On the Component Properties dialog box, select the Use tab.
Select the Read Only check box.
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.
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.
To configure Process Scheduler processes for read-only processing:
Select PeopleTools, Process Scheduler, Processes.
Open the process definition.
On the Process Definition page, select the Read Only check box.
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.
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:
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 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 -IUse 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
;=========================================================================
UseLocalOracleDB=0
;ORACLE_SID=
EnableDBMonitoring=1
PSDB Maximum Cursors=
Disable ICQueryService Standby Routing=1