Enable PeopleTools to Support Oracle Active Data Guard

PeopleSoft PeopleTools versions 8.52 and higher support Oracle Active Data Guard, which enables offloading queries to a physical standby.

You need the following to enable PeopleTools to support Oracle Active Data Guard:

A physical standby database that has Oracle Active Data Guard enabled
A database service that can be started on the Oracle Active Data Guard database instance
A secondary Access ID created in PeopleSoft
An additional database schema that will be associated with the secondary Access ID
A database link that uses a service that only runs on the primary

About Implementing Oracle Active Data Guard

Keep the following important items in mind when implementing support for Oracle Active Data Guard:

The PeopleSoft application server domains at both the primary and secondary sites must be configured to support Oracle Active Data Guard for query offload to work after a switchover or failover.
The PSFTADG2 user will need its own database link to access the standby. When manually creating the new database link, make sure the database link name matches the name of the target database, including the DB_DOMAIN if set.
IMPORTANT: Once the PeopleTools application domain server is configured to support Oracle Active Data Guard, the PeopleSoft application will not start if the PSQUERY service is not available. If the standby database is not available, the PSQUERY service must be started on the primary. It can be relocated back to the standby when the standby is again available without restarting the application.

Note:

If you plan to open the standby database as a snapshot standby for testing, you must first relocate the PSQUERY service to the primary.

If your on-premises PeopleSoft implementation is already configured to use Oracle Active Data Guard for offloading queries, then you must make sure that the configuration for the application server and process scheduler are carried over to the first and second OCI deployments. The database will already have the schema user associated with the PeopleSoft secondary access ID. In our case, this additional schema is called PSFTADG2.

Implement Oracle Active Data Guard

If you choose to newly implement Active Data Guard support for offloading queries, then follow the procedure referenced here to enable PeopleTools support for Oracle Active Data Guard.

Follow the steps in Implementing Oracle Active Data Guard.

These steps are designed to provide Oracle Maximum Availability Architecture (Oracle MAA) best practices.

Configure the Primary and Standby Database Servers for Oracle Active Data Guard

The primary database server must access a service at the standby database through a database link. Start by creating that database service and adding the tnsnames.ora entries to be able to resolve the network address of the standby.

Add a role-based database service to the primary and secondary regions.

Add the role-based database service PSQUERY at both the primary and secondary regions, to run only when the database is fulfilling the PHYSICAL_STANDBY role. The following are examples of the services added:
Primary
```
$ srvctl add service -db <primary DB unique name> -pdb HR92U033 -service PSQUERY -preferred "CDBHCM1,CDBHCM2" failovermethod BASIC -failovertype SELECT -notification TRUE -role PHYSICAL_STANDBY -failoverretry 10 -failoverdelay 3
```
Standby
```
$ srvctl add service -db <standby DB unique name> -pdb HR92U033 -service PSQUERY -preferred "CDBHCM1,CDBHCM2" failovermethod BASIC -failovertype SELECT -notification TRUE -role PHYSICAL_STANDBY -failoverretry 10 -failoverdelay 3
```
Create a tnsnames.ora “include file”, or ifile, with a TNS connect string that reports that are running at the Oracle Active Data Guard standby database can use to record information about the jobs in the primary database. Place the ifile in the $TNS_ADMIN directory on each RAC database node at both primary and standby sites.
This allows reports running on the Oracle Active Data Guard database to connect back to the primary and to update run data in the database.

Note:

Do not place this TNS connect string alias onto any of the middle tiers. This connect string is only used by the database link created in a later step on the database servers.

For example, the following is a TNS connect string placed in an ifile named tns_ps_adg.ora.
```
HR92U033_PRIMARY =
(DESCRIPTION_LIST =
   (LOAD_BALANCE=off)(FAILOVER=on)
   (DESCRIPTION =
       (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
       (ADDRESS_LIST =
           (LOAD_BALANCE=on)
           (ADDRESS = (PROTOCOL = TCP)(HOST = iadexadb-bw5wn-scan.exadbprivate.ebscloudmaavcn.oraclevcn.com)(PORT = 1521))
       )
        (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = HR92U033_ONLINE)
       )
    )
    (DESCRIPTION =
       (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
       (ADDRESS_LIST =
           (LOAD_BALANCE=on)
           (ADDRESS = (PROTOCOL = TCP)(HOST = phxexadb-krppw-scan.dbprivateexa.maacloud2vcn.oraclevcn.com)(PORT = 1521))
       )
        (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = HR92U033_ONLINE)
       )
    )
 )
```
Add a line for an IFILE directive to the bottom of your database tnsnames.ora files to include the ifile in the tnsnames definition.
```
IFILE=TNS_ADMIN full path/ tns_ps_adg.ora
```

Update the Database for Oracle Active Data Guard

Update the database contents to hold both a pointer used by the PeopleSoft application at the standby site to locate the primary database, and the database link used for that purpose. The data inserted at the primary is propagated to the standby by Oracle Data Guard.

Insert a new row into the PS.PSDBOWNER table on the primary database.
This row will enable the application servers to authenticate with the Oracle Active Data Guard standby database.
1. Log in to one of the Oracle Exadata Database Service on Dedicated Infrastructure database servers as the oracle OS user.
2. Source the database environment (CDBHCM.env).
3. Start a SQL*Plus session and insert the new row.
  The following example uses PDB name
```
HR92U033
```
  :
```
sqlplus / as sysdba 

SQL> ALTER SESSION SET CONTAINER = HR92U033;
SQL> INSERT INTO PS.DBOWNER VALUES (‘PSFTADG’, ‘EMDBO’);

COMMIT;
```
Create the database link on the primary database.

See the steps in Implementing Oracle Active Data Guard to create a database link on the primary database.
Use SQL*Plus to connect to the second database schema (PSFTADG2) and create the database link.

Note:
This uses the TNS alias HR92U033_PRIMARY, a service that only runs when the database is in the primary role.
For example:
```
CREATE DATABASE LINK PRIMARY CONNECT TO EMDBO IDENTIFIED BY password USING 'HR92U033_PRIMARY'; 
```