Enable PeopleTools to Support Oracle Active Data Guard (2024)

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

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.

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.

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.

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

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

   See Also

   Verken Oracle Database-oplossingen voor maximale efficiëntie

   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) ) ) )

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

1. 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;

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

3. 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';