Programmatically Suspending and Restarting the Process Scheduler

I found this question on a message forum, wrote a note, and forgot about it:

Anyone have any tricks on suspending the process schedulers programmatically?  I just tried using the following but the weird thing was I saw at least one of my process schedulers unsuspend itself, so I must be missing an update to a table.

UPDATE PSSERVERSTAT set SERVERSTATUS = '2' where SERVERNAME like '%PSUNX%'

The process scheduler writes its status to SERVERSTATUS so that it can be seen in the Process Monitor.  Instructions to the process scheduler are read from SERVERACTION, so this is the column that must be updated.  Both columns have a set of XLAT values that translate the status.

PeopleSoft Field Name	Description
SERVERSTATUS	Server Status 0=Error 1=Down 2=Suspended 3=Running 4=Purging 5=Running With No Report Node 6=Suspended - Disk Low 7=Suspended - Offline 8=Running - Report Rep. Full 9=Overloaded
SERVERACTION	Process Server Action 0=None 1=Stop 2=Suspended 3=Restart 4=Purge

See PSSERVERSTAT.

You can see how PeopleSoft does this by tracing the Process Monitor component as it issues commands to the process scheduler.  Hence you can issue commands as follows:

• Stop

update psserverstat
set    serveraction = 1 /*Stop*/
where  serverstatus = 3 /*Running*/
and    servername = …
/
commit
/

• Suspend

update psserverstat
set    serveraction = 2 /*Suspend*/
where  serverstatus = 3 /*Running*/
and    servername = …
/
commit
/

• Restart (after suspension)

update psserverstat
set    serveraction = 3 /*Restart*/
where  serverstatus = 2 /*Suspended*/
and    servername = …
/
commit
/

• Startup (if the Tuxedo domain is running)

update psserverstat
set    serveraction = 3
where  servername = …
/
commit
/

Oracle SQL Tracing Processes from Startup

Sometimes, ASH and AWR are not enough.  SQL may not be sampled by ASH if it is short-lived, and even if it is sampled, the SQL may not be captured by AWR.  Sometimes, in order to investigate a problem effectively, it is necessary to use database session SQL trace.  

It is easy to trace a process initiated by the process scheduler with a trigger (see Enabling Oracle Database Trace on PeopleSoft processes with a Trigger).

Another tactic is to use an AFTER LOGON trigger with logic to look at the program name.  The program name can be read using SYS_CONTEXT().  If it matches what I am looking for, I can enable session trace.

Here is an example I used for the OpenXML nVision server PSNVSSRV

• I want to trace SQL and not any wait events or bind variables.  Therefore, I will set event 10046 at level 1.
• I also set a tracefile_identifier that will be included in the trace file name, so I can more easily identify the trace file.

REM additional SQL trace triggers
CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_trace_on_logon
AFTER LOGON
ON sysadm.schema
DECLARE
  l_process_instance INTEGER;
  l_program          VARCHAR2(64 CHAR);
  l_sql              VARCHAR2(100);
BEGIN

  SELECT sys_context('USERENV', 'CLIENT_PROGRAM_NAME')
  INTO   l_program
  FROM   dual;

  IF l_program like 'PSNVSSRV%' THEN --then this is a NVISION session
    EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier = ''PSNVSSRV''';
    EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
  END IF;

EXCEPTION 
  WHEN OTHERS THEN NULL;
END;
/
show errors
ALTER TRIGGER sysadm.gfc_nvision_trace_on_logon ENABLE;

See also Reading Trace files with SQL

Adding Flags to Trace Level Overrides in Process Definitions

A trace level is set in a process definition in PS_PRCSDEFN precedence over a trace level set in the process scheduler configuration file (psprcs.cfg).

I often set the process scheduler trace level for Application Engine to 1152 to enable batch timings to both the database batch timings tables and the AE trace file, but then I often find that a trace is left enabled on a few processes to aid performance analysis of a troublesome process.

This script updates the trace level set in the parameter list in the process definition to include the bit flags set by 1152 (to enable batch timings).  

• The current trace level is extracted with regular expression substring functions.
• A bitwise OR is performed between the current trace level and the desired settings.  There is no single function to do this in Oracle SQL, but it can be calculated simply (see Oracle blog: There is no BITOR() in Oracle SQL).  
• The old trace value is replaced with the new one in the parameter list with a regular expression replace function.
• The version number on the process definition is also updated as it would be if updated by the process definition component in the PIA.  Thus it is correctly re-cached by the process scheduler, the scheduler does not need to be recycled, nor does the cache need to be cleared

The script is available on Github.

REM fixprcstracelevel.sql
set pages 99 lines 200 serveroutput on
spool fixprcstracelevel append
ROLLBACK;
DECLARE
  l_counter INTEGER := 0;
  l_trace_expr VARCHAR2(20); /*expression containing TRACE keyword and value*/
  l_req_trace_level INTEGER := 1152; /*trace value set in the scheduler config*/
  l_cur_trace_level INTEGER; /*current trace level*/
  l_new_trace_level INTEGER; /*new calculated trace level*/
  l_parmlist ps_prcsdefn.parmlist%TYPE;
BEGIN
  for i in (
    SELECT t.*
    FROM   ps_prcsdefn t
    WHERE  UPPER(t.parmlist) LIKE '%-%TRACE%'
    AND   prcstype LIKE 'Application Engine'
--  AND parmlisttype IN('1','2','3')
  ) LOOP
    l_trace_expr := REGEXP_SUBSTR(i.parmlist,'\-trace[ ]*[0-9]+',1,1,'i');
    l_cur_trace_level := TO_NUMBER(REGEXP_SUBSTR(l_trace_expr,'[0-9]+',1,1,'i'));
    l_new_trace_level := l_req_trace_level+l_cur_trace_level-bitand(l_cur_trace_level,l_req_trace_level);
    l_parmlist := REGEXP_REPLACE(i.parmlist,l_trace_expr,'-TRACE '||l_new_trace_level,1,1,'i');

    IF l_new_trace_level = l_cur_trace_level THEN
      dbms_output.put_line(i.prcstype||':'||i.prcsname||':'||i.parmlist||'=>No Change');
    ELSE
      l_counter := l_counter + 1;
      IF l_counter = 1 THEN
        UPDATE psversion
        SET    version = version+1
        WHERE  objecttypename IN('SYS','PPC');

        UPDATE pslock
        SET    version = version+1
        WHERE  objecttypename IN('SYS','PPC');
      END IF;
      dbms_output.put_line(l_counter||':'||i.prcstype||' '||i.prcsname||':'||i.parmlist||'=>'||l_parmlist);
      UPDATE ps_prcsdefn
      SET    version = (SELECT version FROM psversion WHERE objecttypename = 'PPC')
      ,      parmlist = l_parmlist
      WHERE  prcstype = i.prcstype
      AND    prcsname = i.prcsname;
    END IF;
  END LOOP;
  COMMIT;
END;
/
spool off

The script reports the old and new parameter list setting for each process definition altered.  

Below is a sample output:

Application Engine:GL_JEDIT:-TRACE 1159=>No Change
1:Application Engine PTDEFSECINRL:-toolstacepc 2048 -toolstracesql 15 -TRACE 15=>-toolstacepc 2048 -toolstracesql 15 -TRACE 1167

• TRACE for GL_JEDIT is already 1159, so no change is required.
• TRACE for PTDEFSECINRL was changed from 15 to 1167.

Reporting View Hierarchies

It is a characteristic of PeopleSoft that it uses lots of views to present data within the application, and frequently views reference other views.  There are examples of this design going 5 levels deep in HR and deeper in Financials.  When faced with a SQL execution plan for such a view you often wonder which view referenced which table.

However, the Oracle database describes "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links" in the view ALL_DEPENDENCIES.

This hierarchical query (depend_heir.sql) on this view will report the structure of views within views. 

REM depend_hier.sql
undefine view_name
set pages 999 lines 176 long 50000
break on name skip 1 on owner 
ttitle 'Dependency Hierarchy'
column my_level format a5 heading 'Level'
column owner format a12
column name format a18
column type format a7
column referenced_type format a7 heading 'Refd|Type'
column referenced_owner format a6 heading 'Refd|Owner'
column referenced_name format a18 heading 'Refd|Name'
column referenced_link_name format a10 heading 'Refd|Link'
column dependency_type heading 'Dep|Type'
column text heading 'View Text' format a80 wrap on
spool depend_hier.&&view_name..lst
with d as (
  select * from all_dependencies
  union all
  select null, null, null, owner, view_name, 'VIEW', null, null
  from all_views 
  where owner = 'SYSADM' and view_name = UPPER('&&view_name')
)
select LPAD(TO_CHAR(level),level,'.') my_level
, d.type, d.owner, d.name
, d.referenced_type, d.referenced_owner, d.referenced_name, d.referenced_link_name
, d.dependency_type
, v.text
from d
  left outer join all_views v 
    on  v.owner = d.referenced_owner
    and v.view_name = d.referenced_name
  connect by nocycle
        d.name = prior d.referenced_name
  and   d.owner = prior d.referenced_owner
start with d.owner IS NULL and d.name IS NULL
/
spool off
ttitle off

For example, this is the report for PS_POSN_HISTORY3 from a demo HCM database. It is only three levels deep. "POSN_HISTORY3 is the third of three nested views which retrieve position incumbent history.  It selects job records with effective dates before position exits to obtain exit salaries."

Wed Aug 11                                                                                                                                                             page    1
                                                                              Dependency Hierarchy

                                              Refd    Refd   Refd               Refd       Dep
Level TYPE    OWNER        NAME               Type    Owner  Name               Link       Type View Text
----- ------- ------------ ------------------ ------- ------ ------------------ ---------- ---- --------------------------------------------------------------------------------
1                                             VIEW    SYSADM PS_POSN_HISTORY3                   SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,B.EFFDT ,B.EFF
                                                                                                SEQ ,B.Sal_Admin_Plan ,B.Grade ,B.Step ,B.Comprate ,B.Comp_Frequency ,B.Currency
                                                                                                _Cd ,' ' ,' ' ,' ' ,' ' FROM PS_POSN_HISTORY2 A ,PS_JOB B WHERE B.EmplID = A.Emp
                                                                                                lID AND B.EMPL_RCD = A.EMPL_RCD AND B.Position_Nbr = A.Position_Nbr AND B.EffDt
                                                                                                = ( SELECT MAX(C.EffDt) FROM PS_JOB C WHERE C.EmplID = B.EmplID AND C.EMPL_RCD =
                                                                                                 B.EMPL_RCD AND (C.EffDt < A.Position_End_Dt OR (C.EffDt = A.Position_End_Dt AND
                                                                                                 C.EffSeq = A.EffSeq - 1))) AND B.Effseq = ( SELECT MAX(C.Effseq) FROM PS_JOB C
                                                                                                WHERE C.EmplID = B.EmplID AND C.EMPL_RCD = B.EMPL_RCD AND (C.EffDt < A.Position_
                                                                                                End_Dt OR (C.EffDt = A.Position_End_Dt AND C.EffSeq = A.EffSeq - 1)))


.2    VIEW    SYSADM       PS_POSN_HISTORY3   TABLE   SYSADM PS_JOB                        HARD
.2    VIEW                                    VIEW    SYSADM PS_POSN_HISTORY2              HARD SELECT A.Position_Nbr , A.Position_Entry_Dt , A.Emplid , A.EMPL_RCD , B.EffDt ,
                                                                                                B.EffSeq , B.Action FROM PS_POSN_HISTORY A , PS_JOB B WHERE A.EmplID = B.EmplID
                                                                                                AND A.Empl_Rcd = B.Empl_Rcd AND B.EffDt = ( SELECT MIN(C.EffDt) FROM PS_JOB C WH
                                                                                                ERE C.EmplID = B.EmplID AND C.Empl_Rcd = B.Empl_Rcd AND (C.EffDt > A.Position_En
                                                                                                try_Dt OR (C.EffDt = A.Position_Entry_Dt AND C.EffSeq > A.EffSeq)) AND ((C.Posit
                                                                                                ion_Nbr <> A.Position_Nbr) OR (C.HR_STATUS <> 'A'))) AND B.EffDt<=TO_DATE(TO_CHA
                                                                                                R(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND B.EffSeq = ( SELECT MIN(D.EffSeq) FROM
                                                                                                 PS_JOB D WHERE D.EmplID = B.EmplID AND D.Empl_Rcd = B.Empl_Rcd AND D.EffDt = B.
                                                                                                EffDt AND ((D.Position_Nbr <> A.Position_Nbr) OR (D.HR_STATUS <> 'A')))


..3   VIEW    SYSADM       PS_POSN_HISTORY2   TABLE   SYSADM PS_JOB                        HARD
..3   VIEW                                    VIEW    SYSADM PS_POSN_HISTORY               HARD SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,A.EFFSEQ ,A.EF
                                                                                                FDT ,A.Sal_Admin_Plan ,A.Grade ,A.Step ,A.CompRate ,A.Comp_Frequency ,A.Currency
                                                                                                _Cd ,' ' ,' ' ,' ' ,' ' FROM PS_Job A WHERE A.Position_Entry_Dt = A.Effdt AND A.
                                                                                                Effseq = ( SELECT MIN(B.Effseq) FROM PS_Job B WHERE B.Emplid = A.Emplid AND B.EM
                                                                                                PL_RCD = A.EMPL_RCD AND B.Effdt = A.Effdt AND B.Position_Nbr = A.Position_Nbr)


...4  VIEW    SYSADM       PS_POSN_HISTORY    TABLE   SYSADM PS_JOB                        HARD

We can see from the report that view PS_POSN_HISTORY3 calls view PS_POSN_HISTORY2 that in turn calls view PS_POSN_HISTORY. 

Each of the views also contains multiple references to PS_JOB that perform various effective date/sequence sub-queries. Where there are multiple references to the same object, there is still only one dependency.

The script is available on Github as a part of my psscripts repository.

Oracle Active Data Guard in PeopleSoft with Oracle 19c DML Redirection

Active Data Guard is a configuration option that became available in Oracle 11g where a standby database is maintained as a synchronised physical replica of the primary database and is also open for read-only SQL queries.

PeopleSoft added configuration to direct certain read-only components and processes to an ADG standby using secondary connections in the application servers and process schedulers.  However, in PeopleSoft, all scheduled processes update at least the process scheduler request tables, even if they make no updates to application tables.  This cannot be done on a read-only standby database and must be directed back to the primary database.

PeopleBooks sets out a method for Implementing Active Data Guard (this link is to the PeopleTools 8.58 documentation).  It uses a second schema to which PeopleSoft application servers and process schedulers connect using a second access profile.  The second schema contains synonyms for each table and view that either point to the corresponding object in the original schema on the ADG standby, or if the object is going to be updated by the application then via database links to the corresponding object in the primary database.  This approach requires knowledge of which tables are updated during otherwise read-only processing, a lot of scripting to generate all the synonyms and grants, and ongoing maintenances as new objects are added to the database.

However, that approach is rendered obsolete by Active Data Guard DML redirection, a new feature in 19c.  This post explains how to configure PeopleSoft to make use of Active Data Guard on Oracle 19c with DML redirect.

With DML redirection enabled, updates on the secondary database are automatically redirected back to the primary database via a SQL*Net connection between the databases (not unlike a database link), and then they will be replicated back to the standby database like any other change.  PeopleSoft no longer needs to be configured specially to handle updated tables differently.  Consequently, the PeopleSoft ADG configuration is massively simplified.

Processes no longer have to be strictly read-only to run on the ADG database.  If there are only a small quantity of updates the redirect can handle it.

Database Initialisation Parameters

The parameter ADG_REDIRECT_DML should be set to true on both the primary and active data guard standby databases.

PeopleSoft Connection Configuration

A second row must be added to PS.PSDBOWNER to map the TNS name of the standby database to the database owner ID, in this case SYSADM.

SQL>select * from ps.psdbowner;

DBNAME   OWNERID
-------- --------
FINPRD   SYSADM
FINADG   SYSADM

Application Server Configuration

To facilitate read-only components in the PIA (such as the query manager) redirecting to the standby database, all Application Server domains should still be configured to connect to both the primary database and the standby database, as described in the PeopleSoft documentation

[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=FINPRD
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}FEhk7rIFt2f0GRYaH6B9la8DXXMNtsz1kPZ+
ConnectId=PEOPLE
ConnectPswd={V2}Mw3RFr0MHFBpJHbqXh7Dx9qCsO7TFT4G
StandbyDBName=FINADG
StandbyDBType=ORACLE
StandbyUserId=PSAPPS
StandbyUserPswd={V2}Ski/r2xYCvbTbBhXOGfH8HO7zCRxoDFK5rmb

Process Scheduler Configuration

The approach for Process Schedulers is slightly different.

Application Engine Limitation in Active Data Guard

It is a documented limitation of Application Engine that only PSAESRV Application Engine server processes can connect via the second connection to the standby database.  If a standalone PSAE process attempts this the connection will fail.  

See:

• Oracle Support Note: E-AE: Application Engine Process Might Stay in Initiated Status if PSAESRV Disabled on PeopleSoft ADG Enabled Environment (Doc ID 1641764.1)
• This was raised as Bug 18482301: PSAE may stay in initiated status on ADG configured environment.  It was closed as 'not a bug'.
• Using Two Temporary Tablespace in PeopleSoft.

Error in sign on
 Database Type: 7 (ORACLE)
 Database Name: HCM91
 Server Name: 
 OperID: 
 ConnectID: people
 Process Instance: 0
 Reason: Invalid user ID or password for database signon. (id=)
 Note: Attempt to authenticate using GUID 6a1ced41-2fe0-11e2-9183-be3e31d6e740
Invalid command line argument list.
 process command line: -CT ORACLE -CD HCM91 -GUID 6a1ced41-2fe0-11e2-9183-be3e31d6e740 -SS NO -SN NO 
 GUID command line : -CT ORACLE -CD HCM91 -CO "PS" -CP Unavailable -R 1 -I 852 -AI AEMINITEST -OT 6 -FP 
"C:\app\pt\appserv\prcs\HCM91PSNT\log_output\AE_AEMINITEST_852\" -OF 1

To continue to use stand-alone PSAE processes, as recommended in the PeopleTools Performance Guidelines Red Paper (Doc ID: 747389.1), it is necessary to configure separate process schedulers that connect only to the Active Data Guard standby database, and processes will need to be redirected appropriately by process categories to these schedulers.

System Settings

Process Categories can be used to restrict certain processes to certain processes schedulers.  In the Process Scheduler System Settings component, create a new category ADGOnly.

New Process Schedulers for ADG

New process schedulers should be created for running ADG only jobs.  They will only be connected to the standby database.  The standby connection should be left blank.  All of the SQL updates made by the Process Scheduler processes will be handled by DML redirection.  You will need to have at least 2 so they do not become a single point of failure.

[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=FINADG
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}YoAQq7Ut4WBHJL89N9gv9E0AWwLaecGZ4qep
ConnectId=PEOPLE
ConnectPswd={V2}OlSYHuFMZa2c8uonfYkKk+3+APYvTU9N
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=

These schedulers will ONLY run:

• Processes in the new ADGOnly process category.  
• A single LOADCACHE category process will be permitted.
• The max concurrence of all other categories will be 0.
• Other process types such as SQR might be needed if they are to be run on the ADG standby

They should not run master process scheduler processes. This process should only run schedulers connected to the primary database.

Existing Process Schedulers

All process categories apply to all process schedulers.  The concurrency of the ADG category should be set to 0 on all other process schedulers to prevent it from running there.  These schedulers will remain connected to the primary database only.  They will not be connected to the secondary database.

[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=FINPRD
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}EcxeV3mit3GMT5kDfz/z+s0L9B1aUb6ix04f
ConnectId=PEOPLE
ConnectPswd={V2}NtXafW7hlcGY016bhazl2kqqvlSNYMK1
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=

Process Definition

Normally, the processes that are to be run on ADG should be marked read-only and then they will be scheduled using the secondary connection.    However, Application Engine cannot be run in standalone PSAE mode via the second connection.  So, the ADG process schedulers are configured to connect directly to the ADG database via the primary connection.  Thus, the read-only flag has no effect.  Instead, all read-only processes should be moved to the ADGOnly category.

Configuration by SQL

It may be easier to update the process scheduler configuration by SQL

• ADGOnly concurrency will be 0, or it will be the maximum API aware concurrency on ADG process schedulers.
• PSQUERY, any read-only processes, and any processes in ADGOnly category, will all be marked as being both read-only and in the ADGOnly category.

update pslock 
set    version = version + 1
where  objecttypename IN('SYS','PPC')
/
update psversion
set    version = version + 1
where  objecttypename IN('SYS','PPC')
/
update  ps_servercategory c 
set     maxconcurrent = CASE WHEN servername like 'PSUNX_A%' 
                             THEN (SELECT s.maxapiaware FROM ps_serverdefn s 
                                   WHERE  s.servername = c.servername)
                             ELSE 0 END
where   prcscategory = 'ADGOnly'
/
update ps_serverdefn
set    version = (SELECT version from psversion where objecttypename = 'PPC')
,      lastupddttm = systimestamp
/
update ps_prcsdefn 
set    version = (SELECT version from psversion where objecttypename = 'PPC')
,      prcsreadonly = 1
,      prcscategory = 'ADGOnly'
,      lastupddttm = systimestamp
where  prcsreadonly = 1
or     prcscategory = 'ADGOnly'
or     prcsname = 'PSQUERY'
/
select prcstype, prcsname, prcscategory, prcsreadonly
from   ps_prcsdefn
where  prcsreadonly = 1
or     prcscategory = 'ADGOnly'
or     prcsname = 'PSQUERY'
/
select * from ps_servercategory
where prcscategory IN('ADGOnly')
order by 2,1
/
commit
/

Sample Output

PSOFT-FINADG>Select prcstype, prcsname, prcscategory, prcsreadonly
  2  From   ps_prcsdefn
  3  where  prcsreadonly = 1
  4  or     prcscategory = 'ADGOnly'
  5  or     prcsname = 'PSQUERY'
  6  /

PRCSTYPE                       PRCSNAME     PRCSCATEGORY                   P
------------------------------ ------------ ------------------------------ -
Application Engine             AEMINITEST   ADGOnly                        1
COBOL SQL                      PTPDBTST     ADGOnly                        1
SQR Report                     PTSQRTST     ADGOnly                        1
Application Engine             FB_GEN_EXTR  ADGOnly                        1
SQR Report                     XRFWIN       ADGOnly                        1
SQR Report                     SWPAUDIT     ADGOnly                        1
SQR Report                     SYSAUDIT     ADGOnly                        1
SQR Report                     XRFAPFL      ADGOnly                        1
SQR Report                     XRFAEPL      ADGOnly                        1
SQR Report                     XRFPGDZ      ADGOnly                        1
SQR Report                     DDDAUDIT     ADGOnly                        1
SQR Report                     XRFEVPC      ADGOnly                        1
SQR Report                     XRFFLPC      ADGOnly                        1
SQR Report                     XRFFLPN      ADGOnly                        1
SQR Report                     XRFFLRC      ADGOnly                        1
SQR Report                     XRFIELDS     ADGOnly                        1
SQR Report                     XRFMENU      ADGOnly                        1
SQR Report                     XRFPANEL     ADGOnly                        1
SQR Report                     XRFPCFL      ADGOnly                        1
SQR Report                     XRFPNPC      ADGOnly                        1
SQR Report                     XRFRCFL      ADGOnly                        1
SQR Report                     XRFRCPN      ADGOnly                        1
Application Engine             PSQUERY      ADGOnly                        1
Application Engine             PSCONQRS     ADGOnly                        1

SYSADM-FINADG>select * from ps_servercategory
  2  where prcscategory IN('ADGOnly')
  3  order by 2,1
  4  /

SERVERNA PRCSCATEGORY                   P MAXCONCURRENT
-------- ------------------------------ - -------------
…
PSNT     ADGOnly                        5             0
PSUNX    ADGOnly                        5             0
PSUNX_A1 ADGOnly                        5             5
…

PSQUERY Application Engine Amendment

If you have PS/Queries that reference a remote database via a database link then you may experience errors when you run them via the scheduled Application Engine process PSQUERY on an ADG database.

Using a database link, even if only for a query, starts a database transaction, and then you can't get DML redirect to work because it also starts a transaction.  However, if you already have a transaction open through DML redirect you can't start the transaction associated with the database link.  The two transactions become mutually exclusive.

PeopleTools 8.58.07 - Application Engine
Copyright (c) 1988-2021 Oracle and/or its affiliates.
All Rights Reserved

PeopleTools SQL Trace value: 159 (0x9f): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA1/log_output/AE_PSQUERY_xxxxxxxx/AE_PSQUERY_xxxxxxxx.trc
PeopleTools PeopleCode Trace value: 64 (0x40): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA1/log_output/AE_PSQUERY_xxxxxxxx/AE_PSQUERY_xxxxxxxx.trc

File: /vob/peopletools/src/pssys/qpm.cppSQL error. Stmt #: 8495  Error Position: 3055  Return: 16000 - ORA-16000: database or pluggable database open for read-only access 
Failed SQL stmt: SELECT …
<a query that references a remote database via a database link>
…
Error in running query because of SQL Error, Code=16000, Message=ORA-16000: database or pluggable database open for read-only access  (50,380)

PeopleCode Exit(1) Abort invoked by Application at PSQUERY.MAIN.ExecQry. (108,543)

Process xxxxxxxx ABENDED at Step PSQUERY.MAIN.ExecQry (PeopleCode) -- RC = 16 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

So we need to close the transactions by making the PSQUERY application engine commit.  Application Engine steps explicitly commit after each step.  I have added an extra step added that does nothing, but Application Engine issues a commit

Disable Query Statistics

I have found that query statistics can cause a problem when the query is run on ADG.

PeopleTools 8.58.07 - Application Engine
Copyright (c) 1988-2021 Oracle and/or its affiliates.
All Rights Reserved

PeopleTools SQL Trace value: 159 (0x9f): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA2/log_output/AE_PSQUERY_19356674/AE_PSQUERY_19356674.trc

File: /vob/peopletools/src/pssys/qdmutil.cppSQL error. Stmt #: 4608  Error Position: 91  Return: 16397 - ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed 
Failed SQL stmt: SELECT EXECCOUNT, AVGEXECTIME, AVGFETCHTIME, LASTEXECDTTM, AVGNUMROWS, OPRID, QRYNAME FROM PSQRYSTATS WHERE OPRID = :1 AND QRYNAME = :2 FOR UPDATE OF EXECCOUNT
Application Engine program PSQUERY ended normally

They can be disabled in the PIA

• Navigate to: PeopleTools, Utilities, Administration, Query Administration, 
• Go to the last tab (Settings), 
• Uncheck Run Query Statistics.

Enable Query Execution Logging

Query stats (logged in the table PSQRYSTATS) are not particularly useful as they only compute average metrics over a period that is not defined.  Instead, query execution logging (to the table PSQRYEXECLOG) is far more useful for performance analysis because you know who ran the query when it ran, and how many rows it retrieved.  It is enabled for individual queries.  It can be enabled across the board with the following SQL.

update pslock
set    version = version + 1
where  objecttypename IN('SYS','QDM')
/
update psversion
set    version = version + 1
where  objecttypename IN('SYS','QDM')
/
update psqrydefn
set    version = (SELECT version from psversion where objecttypename = 'QDM')
,      execlogging = 'Y'
,      lastupddttm = systimestamp
where  execlogging != 'Y'
/
commit
/