How to identify who is locking the Oracle account

With AUDIT_TRAIL

The first and preferred solution is with Oracle standard auditing feature. Start by setting initialization parameter AUDIT_TRAIL to db and restart your Oracle database as it is static parameter.
Then activate network auditing with (as SYS):

SQL> AUDIT network BY ACCESS;   AUDIT succeeded.

With below query you get everything needed:

SELECT * FROM dba_audit_session ORDER BY sessionid DESC;

Returncode column contains Oracle error code and so different of 0 if logon/logoff issue. The invalid password is the error we are chasing:

[oracle@server1 ~]$ oerr ora 1017 01017, 00000, "invalid username/password; logon denied" // *Cause: // *Action:

So if you find 1017 values in this column then we have found what we were looking for. For example with my test case where I intentionally specify a wrong password for my account:

SQL> SELECT username,userhost,returncode FROM dba_audit_session WHERE username='YJAQUIER' ORDER BY sessionid DESC;   USERNAME USERHOST RETURNCODE ------------------------------ -------------------- ---------- YJAQUIER server1 1017 YJAQUIER GVADT30596 0 YJAQUIER server1 0 YJAQUIER server1 0 . . .

And if you insist, as explained, you get:

SQL> SELECT username, account_status,lock_date, PROFILE FROM dba_users WHERE username='YJAQUIER';   USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- -------------------- ------------------------------ YJAQUIER LOCKED(TIMED) 23-nov-2012 10:30:37 DEFAULT

If you set AUDIT_TRAIL to db behave the size of SYS.AUD$ table as a small list of audits are already implemented by default:

SQL> SET lines 200 SQL> SET pages 200 SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;   USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- ALTER SYSTEM BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS

So you must put in place a purging policy for this table.

Without AUDIT_TRAIL

The only drawback of the previous solution is that you have to restart the database. And maybe two times because after problem solved you would like to deactivate auditing. This is most probably not reliable solution on a production database so I have been looking for a better solution with no database reboot.
I initially thought of the AFTER LOGON trigger but you need to be logged-in and the BEFORE LOGON does not exits. Then at same documentation place I found the AFTER SERVERERROR trigger and decided to give it a try.
First I created a dummy table to log server error (columns inherited from dba_audit_session dictionary table):

CREATE TABLE sys.logon_trigger ( USERNAME VARCHAR2(30), USERHOST VARCHAR2(128), TIMESTAMP DATE );

Second I created below trigger:

CREATE OR REPLACE TRIGGER sys.logon_trigger AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR(1017)) THEN INSERT INTO logon_trigger VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'), SYSDATE); COMMIT; END IF; END; /

Then third simulated a wrong password access with my account and issued:

SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';   SESSION altered.   SQL> SET lines 200 SQL> col USERHOST FOR a30 SQL> SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;   USERNAME USERHOST TIMESTAMP ------------------------------ ------------------------------ -------------------- yjaquier ST\GVADT30596 23-nov-2012 11:05:56

Tablespace not displaying in Application designer to save records

Below SQL will help to resolve the Tablespace not displaying in Application designer to save records

INSERT INTO PSTBLSPCCAT
SELECT DISTINCT DDLSPACENAME, DBNAME ,'R',' ', '' FROM PSRECTBLSPC A
WHERE TEMPTBLINST = 'N'
AND NOT EXISTS (SELECT 'X' FROM PSTBLSPCCAT
WHERE DDLSPACENAME = A.DDLSPACENAME
AND DBNAME = A.DBNAME);

JSL/WSL (jolt/Work Listener) failed to start with error Could not establish listening address on network

These error messages indicate that the port number that the Tuxedo process is attempting to start listening on is either in use, invalid, or the hostname/IP address specified for that address is invalid.

The Application Server will spawn new processes when it boots up based on the Min and Max Handlers for the Workstation Listener and Jolt Listener. Make sure you have your ports spread out between the WSL and JSL to accommodate the Max Handlers value for the Workstation Listener. For example if you are using port 7000 for WSL and have a Max Handlers value of 5, ports 7001 - 7005 will be used as well. You will want to set the JSL to no less then 7006

For example appserver use port 9618 and spaned 5 more in use and it fails when rebooting.Find the spawned process in use using netstat -lpn and get the PID - kil the PID and start the appserver

netstat -lpn | grep 961*

tcp        0      0 10.137.4.25:9619            0.0.0.0:*                   LISTEN      16347/JSH          
tcp        0      0 10.137.4.25:9620            0.0.0.0:*                   LISTEN      16348/JSH          
tcp        0      0 10.137.4.25:9621            0.0.0.0:*                   LISTEN      16349/JSH          
tcp        0      0 10.137.4.25:9622            0.0.0.0:*                   LISTEN      16350/JSH          
tcp        0      0 10.137.4.25:9623            0.0.0.0:*                   LISTEN      16351/JSH


[PS_HOME/appserv]$ kill -9 16347 16348 16349 16350 16351

Query to find user security access by navigation

select a.oprid as OPRID, a.oprdefndesc as NAME, b.rolename as ROLENAME, c.descr254 as NAVIGATION, c.descr150_mixed as DESCRIPTION
from psoprdefn a, psroleuser b, psroleclass d, psauthitem e, ps_authitem_cmp c
where a.oprid = b.roleuser
  and b.rolename = d.rolename
  and d.classid = e.classid
  and e.menuname = c.menuname
  and e.barname = c.barname
  and e.baritemname = c.baritemname
  and e.pnlitemname = c.pnlitemname
  and a.oprclass like 'PP_UK%'
  and a.acctlock = 0
order by a.oprid, b.rolename;

Maintaining Security in PeopleSoft Upgarde

In order to preserve security through your upgrade passes, you will have 3 options.

1) Low Risk - Re-enter your security changes into one of the target databases. Once this is complete, those security tables can be exported out and imported into any other upgraded databases at the same tools release using security migration reDocument:610138.1, included below.

2) Low Risk - Re-do the Initial Upgrade again. This will get the security as it is when you take the copy of production. Many customers chose this option because they are most comfortable with it. Once the initial upgrade is completed, follow the steps outlined in reDocument:610138.1 (Included below) to migrate security to your other upgraded databases.

3) High Risk -
Attention! This workaround has not gone through our formal quality regression test cycle. We strongly recommend that you thoroughly test this workaround in a development environment before applying it to your production environment.

Be sure to document this change as this workaround may be detected during your next upgrade. Because this workaround has not yet gone through our formal quality regression test cycle, this workaround will have risk.

You should only follow this option if you only want the latest operator security in production migrated to your upgraded database. Do the Initial Upgrade again, up through the end of Chapter 2 (Updating PeopleTools). You will need to definitely run the Relnnn.sql scripts, copy the projects and do the alters - but to make sure you get everything correct, it would be best to run all steps in Chapter 2. Once completed your tools release will be at the same level as your fully upgraded database that you want operator security imported into. DO NOT FOLLOW Document:610138.1 as you will LOSE ALL security delivered by the new application release you just upgraded to. To migrate your operator security, select the appropriate Data Mover script for your PeopleTools release to export and import operator security:

For PT 8.4x -

******Export******
USEREXPORT.dms

This script looks as follows:

-- USERS
EXPORT PSOPRDEFN;
EXPORT PSOPRALIAS;
EXPORT PSROLEUSER;
EXPORT PSUSERATTR;
EXPORT PSUSEREMAIL;
EXPORT PSUSERPRSNLOPTN;
EXPORT PS_ROLEXLATOPR;
EXPORT PS_RTE_CNTL_RUSER;

******Import******
USERIMPORT.dms

This script looks as follows:

UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'UPM';

REPLACE_DATA *;

UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'SYS';
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'UPM';

UPDATE PSOPRDEFN SET VERSION = (SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'UPM');

Steps for PopleTools 8.5X PIA and Weblogic Installation with JRockit

1) To download the latest WebLogic maintenance pack (10.3.6) for PeopleTools 8.52, do the following:


a. Go to the following URL for the “Oracle Software Delivery Cloud site (aka eDelivery): http://edelivery.oracle.com/

b. Follow instructions for filling in personal info and confirm acceptance of terms, etc. Then click 'Continue' button

c. On the "Media Pack Search" page, click on the first ("Product Pack") drop down list, and choose "Oracle Fusion Middleware".

d. In the second, "Platform" drop down list, chose Linux x86-64

e. After making your choices, click the "Go" button.

f. Click the 'Oracle Fusion Middleware 11g Media Pack for Linux x86-64' hyperlink

g. Then click the 'Download' button next to 'Oracle WebLogic Server 11gR1 (10.3.6) Generic and Coherence' (part# is V29856-01)

h. After the file is downloaded, unzip it.



2) To download the latest JRockit:

a. Go to My Oracle Support through http://support.oracle.com and log in

b. Once logged in, click the "Patches & Updates" tab.

d. Select "Product or Family (Advanced Search)". (on top right corner of page)

e. Enter the following info:

Product is: "Oracle JRockit"

Release is "Oracle JRockit 28.2.5"

Platform is: "Linux x86-64"

f. Click "Search" to continue. You will see a list of the JRockit patches available.

Please select "JDK160 ORACLE JROCKIT R28.2.5" (Make sure you get JDK160, and NOT JDK150!).

g. Click "Download", and save the patch to your local drive. The file name will be in *.zip format. For example: p14261101_2825_Linux-x86-64.zip



3) To install WebLogic 10.3.6 and the new JRockit:

a) Unzip the JRockit zip file (from step 3) to the directory where you want it installed. (I'd suggest you install it in the same directory where you are installing WebLogic
c) Now install WebLogic 10.3.6. Follow the instructions in the PeopleTools 8.52 Installation guide

(see section: "Installing Oracle WebLogic on Linux or UNIX)



4) Now try to re-install the WebLogic PIA

Linux kernal parameters for PeopleTools 8.52

Below Linux kernal parameter has to be set to for PeopleTools 8.52 installtion on Linux else the the process with fail to start with less allocated memory


Add the following lines to /etc/sysctl.conf


# Added for PS
kernel.sem = 256 32000 128 192


# Added for PS
kernel.msgmni = 2048



# Added for PS
net.ipv4.ip_local_port_range = 1024 65000



to activate:

/sbin/sysctl -p



to retain settings after booting add the following line to /etc/rc.local

/sbin/sysctl -p

shell Script to monitor log file

Below script will monitor for the error log file updated date every 5 minutes and compare it with the current date and send email if it is updated today.After the issue has been fixed you may need to backup the old log

Scheduled to run 5 mins:


0,5,10,15,20,25,30,35,40,45,50,55 * * * * /opt/hrms/webserv/hr/applications/peoplesoft/PSIGW/monitor.sh



Monitor.sh Script:

#!/bin/sh

cd /opt/hrms/webserv/hr/applications/peoplesoft/PSIGW

Current_Date=`date +"%b %e"`

Filerrordate=`ls -ltr /opt/hrms/webserv/hr/applications/peoplesoft/PSIGW/errorLog.html
awk '{print $6,$7}'`

if [ "$Current_Date" = "$Filerrordate" ] ; then

echo $Current_Date":Error logged in IB gateway log" >> /opt/hrms/webserv/hr/applications/peoplesoft/PSIGW/dailystatus.log

cat errorLog.html
mailx -s "URGENT::Error occured in IB Gateway please Check immediatly" xxxx@name.com

exit 0

else

echo $Current_Date":No new error in error log" >> /opt/hrms/webserv/hr/applications/peoplesoft/PSIGW/dailystatus.log

exit 0

fi

Integration Broker gateway setup with Load Balancer

What should the Gateway URL be and what should the Physical Gateways URL's be?

The gateway URL should actually be the URL of the load balancer, and not any of the web servers hosting the Gateways.

If the load balancer checkbox is checked, then additional field is opened on the page where the URLs for the physical web servers (hosting the gateways) can be listed. These URLs are for information purposes only. There is no load balancing code/logic being triggered as a result of these values. The load balancing logic/code to redirect connections to one of the physical web servers (hosting the gateways) should be in the load balancer.



What happens if the Gateway URL is down, what kind of failover will happen?
If the Gateway URL (Load Balancer) is down, then messaging will not work as it will not be able to direct requests to one of the Physical gateways/web servers. But if one of the Physical Gateways were down, it will just failover to another one. There is no significance of the order in which you add those Physical Gateways.



When hitting the Load Gateway Connectors button, is that only loading connectors for the primary gateway URL? Do they need to do that for each Physical URL also?
You only need to hit the Load button once, provided you have not added or modified any of the connectors on any of the webservers. We are assuming all connectors on any of the web servers are the same and as delivered. When you hit the Load button, we are only going through one Gateway, and loading the connectors from one webserver, then loading them into the database.



When you ping a node, which Gateway is it using?
Load Balancing typically is a Round Robin approach driven by the load balancer, and PSFT does not control which Gateway to use. The way to test each Gateway is to bring down all but one > then ping. Then bring down all but the next Gateway > then ping. etc until all are tested successfully.

Process tables

PS_PMN_SRVRLIST

PS_PMN_SRVRLIST – PeopleSoft Process Scheduler Server List Record.The corresponding information is available also in the PeopleSoft interface on the menu Home>PeopleTools>Process Scheduler>Process Monitor, Server List tab.The table stores the system name that identifies the server (SERVERNAME),the name of server on which the PeopleSoft Process Scheduler Server Agent was started (HOSTNAME), the last time that you refreshed the server list to display the most current information (LASTUPDDTTM),the name of the report node where the Distribution Agent posted all generated reports, logs, or trace files (DISTNODENAME), the status of the Process Scheduler server:Running, Down, or Suspended (SERVERSATUS,SERVERSTATUSDESCR), the CPU threshold percentage value specified in the server definition – CPU usage exceeds this value, the server will not schedule new requests until CPU usage drops below this amount (MAXCPU), memory threshold in percent (MINEM), the disk threshold specified in Process Scheduler configuration file (PRCSTHRESHOLD), the amount of disk space available (PRCSDISKSPACE), server load balancing option (SRVRLOADBALOPTN), the name/version of the operating system of the server (OPSYS) and some other Deamon services related information.

PSPRCSLOCK

PSPRCSLOCK – Peoplesoft Process Scheduler Lock table,contains the number of process scheduler locks (PRCSLOCK) and is used to single thread process requests and to avoid any deadlocks on the PSPRCSRQST table.

PS_SERVERNOTIFY

PS_SERVERNOTIFY – PeopleSoft Process Scheduler Notification table which can be, also, identified and updated from the menu Home>PeopleTools>Process Scheduler>Servers, Notification tab. It records the Process Scheduler name (SERVERNAME), the distribution OPRID -DISTID,the distribution id type -DISTIDTYPE (2-User ID, 3-Role Name), the options of notifying when the process scheduler errors – NOTIFYSERVERERROR, when it is down – NOTIFYWHENDOWN or when it has been started – NOTIFYWHENSTARTD, or when suspended/overloaded -NOTIFYWHENSUSP, or disabled – NOTIFYDISABLED.

PS_SERVERDEFN

PS_SERVERDEFN – PeopleSoft Process Scheduler Definition table, which can be, also, identified and updated from the menu Home>PeopleTools>Process Scheduler>Servers, Definition, Distribution and Daemon tab.This record stores the Process Scheduler name (SERVERNAME),its VERSION, its description (DESCR), the sleep time, in seconds, SLEEPTIME – the time the Process Scheduler stops searching for processes that have entered in its queue for running, therefore once every interval set in the SLEEPTIME, the process scheduler wakes up and looks for processes in”Queued” state.The generraly recommended sleeptime is 30-60 seconds and the maximum allowed is 9999 seconds.The table contains, also, the HEARTBEAT of the Process Scheduler- the interval at which the Process Scheduler Agent checks the status of the server (Running,Suspended,Down), each time this verfication is done the agent updates the field LASTUPDDTTM in the PS_SERVERDEFN record. This prevents the database from accepting more than one Process Server Agent with the same name on one or multiple servers. This table also stores the Maximum number of Application Program Interface (API) aware tasks running concurrently – MAXIAPIAWARE, the Maximum number of non Application Program Interface aware tasks running concurrently -MAXIAPIUNAWARE,the defined operating system -OPSYS (0-DOS,1-NT/Win95 Client,2-NT/Win2000,3-OS/2,4-UNIX,5-VMS,6-MPE/XL,7-OS390,9-OS/400), the distribution node name (DISTNODENAME),the Transfer System Files to Report Repository flag – TRANSFERLOGFILES (1, if checked in the PIA checkbox), the Interval for Transfer Attempt – TRANSFERINTERVAL, Maximum Transfer Retries -TRANSFERMAXRETRY, Server Load Balancing Option -SRVRLOADBALOPTN (0-Do Not Use for Load Balancing,1-Use for Load Balancing),Redistribute Workload Option – REDISTWRKOPTION (0-Do Not Redistribute, 1-Redistribute with same O/S,2-Redistribute to any O/S),the daemon enabled flag – DAEMONENABLED,the daemon servers group -DAEMONGROUP,the daemon sleep time -in minutes-DAEMONSLEEPTIME, the deamon recycle count, which recycles the deamon agent after the set number of iterations -DEAMONCYCLECNT,the deamon Process Instance -DEAMONPRCSINSTANCE, the CPU utilization treshold – MAXCPU and the Memory utilization threshold – MINMEM,both recorded as percentages.

PS_SERVEROPRTN

PS_SERVEROPRTN – PeopleSoft Process Scheduler server Operation times table, available in PT 8.4xx versions, contains the defined Process Scheduler names (SERVERNAME) and their set availability days ( established in the day of the week format with values from 0-6,Sunday is represented by 0, Monday by 1,Saturday by 6 and so on) and the start time/hour of day (STARTTIME) and the time of the day the availability ends (ENDTIME). Both STARTTIME and ENDTIME are stored at the database level as their minute value (eg: the hour 05:00 is represented in the DB as 300,23:59 as 1439 and so on).Also, the content of this table is avialble for editing and viewing from the menu Home>PeopleTools>Process Scheduler>Servers, Operation tab for the specific selected Process Scheduler name.

Process Scheduler monitoring useful script

SELECT S.SERVERNAME, X.XLATSHORTNAME,X.FIELDVALUE,cast(S.MAXCPU as varchar2(3))||’%’ MAXCPU,cast(S.PRCSDISKSPACE as varchar2(15))||’ MB’ PRCS_DISK_SPACE,TO_CHAR(S.LASTUPDDTTM,’DD-MM-YYYY HH:MI:SS’)LAST_UPDATE_TIME
FROM PSSERVERSTAT S, PSXLATITEM X
WHERE X.FIELDNAME = ‘SERVERSTATUS’
AND X.FIELDVALUE = S.SERVERSTATUS

PSPRCSRQST and PSPRCSQUE

PSPRCSRQST and PSPRCSQUE tables – when a user submits a process request to the Process Scheduler a corresponding record will be inserted in the PSPRCSRQST (Process Request) table and afterwards replicated into the PSPRCSQUE table and further acknowledged by the Process Scheduler to be processed. Therefore every “x” seconds (whatever the Sleep time is set your PS environment) 3 retrieve commands get exceuted on the PSPRCSQUE table. While the PSPRCSRQST table is used more with informational purposes to ensure that the correct data is displayed in the Process Monitor tool,the PSPRCSQUE table is the one managing the queue of processes and jobs; modifying this last table is what can detrmine a specific PS job or process to stop. Actually, the parameter that must be affected, at each process instance level, in order to generate the previously mentioned behaviour is  RUNSTATUS and it controls whether or not the job is further processed by the Process Scheduler. DISTATUS is the distribution state and controls whether or not the distribution agent will pick up the job.
In order to avoid diverse Process Scheduler related problems the following tables must be always synchronized: PSPRCSRQST,PSPRCSQUE and PSPRCSPARMS.In conclusion, the values returned by the three count statements must be equal:
select count(*) from PSPRCSRQST;
select count(*) from PSPRCSQUE;
select count(*) from PSPRCSPARMS;
Also, to check if there are any incompatibilities between the fields of the above mentioned 3 tables the the following commands should be issued:
SELECT * FROM PSPRCSQUE Q WHERE NOT EXISTS (SELECT ‘N’ FROM PSPRCSRQST R WHERE R.PRCSINSTANCE = Q.PRCSINSTANCE);
SELECT * FROM PSPRCSQUE Q WHERE NOT EXISTS (SELECT ‘N’ FROM PSPRCSPARMS P WHERE P.PRCSINSTANCE = Q.PRCSINSTANCE);

Reports not Posting

Troubleshooting why reports and files are not posting can be really painful.

Generally you'll be in one of two scenarios:

• All reports are not posting and display not posted in the process monitor. This is generally an infrastructure issue.
• A particular file or report does not post. This could be a development issue or an infrastructure issue.

So where do you start? The Administration tab in Report Manager is updated first. You'll need to know the process instance and report ID (content ID).

If you suspect an infrastructure issue, then the first course of action is to restart the relevant process scheduler(s).

Fold Table of Contents Report Related Tables XML File is Invalid Error HTTP Transfer Error HTTP Status Code is 900 HTTP Status Code is 902 Reports not posting initially Distribution status is N/A Directory Creation Failed Error 404—Not Found Comments

Report Related Tables

Behind the scenes, the records to look at are those starting with CDM (content distribution manager). Here are the key records and the information they give you:

Record	What does it tell you?
PS_CDM_DIST_NODE	These are your report distribution nodes - check the configuration here if you have a system-wide reports not posting issue. Navigation: PeopleTools > Process Scheduler > Report Nodes.
PS_CDM_AUTH	This tells you who (users/roles) the reports were distributed to. DISTIDTYPE of 2 is user, 3 is role. You may just not be able to see the report due to security.
PS_CDM_FILE_EXT	These are the file extension distribution options found under PeopleTools > System Settings > Distribution File Options (tab). Are you using a new file extension? Is that configured and has it been set to display (DISPLAY_OPTION = 1)?
PS_CDM_FILE_LIST	This gives you a list of all files you should be able to see. Is your file in the table? What file type does it think it is - is that right? No file here means that your file probably wasn't transferred to the report repository at all.
PS_CDM_FILTER	These are the filter options selected in report manager by users. Does the user have a filter option that prevents them from seeing the report ?
PS_CDM_FILTER_ARCH	These are the filter options selected in the report manager archive tab by users
PS_CDM_LIST	This is probably the most useful table. It tells the process name, where the process stored its output, where that output was distributed to (if at all) and the distribution status.
PS_CDM_LIST_ARCH	Same as PS_CDM_LIST but for archived reports
PS_CDM_LIST_PURGE	Reports that have been purged from PeopleSoft altogether
PS_CDM_TRANSFER	Cases where reports were transferred between servers
PS_CDM_TRNFR_RJCT	Cases where reports were transferred between servers and the transfer was rejected - failed

With the information from these tables you are now ready to go looking on your process scheduler server(s).

If all looks good in the Administration tab of Report Manager, but you can't see things in the List or Explorer tabs, then you have an integration broker issue.

These are the translate values for the DISTSTATUS field (PeopleTools 8.48.15):

• DISTSTATUS 0 = None
• DISTSTATUS 1 = Scheduled (N/A)
• DISTSTATUS 2 = Processing
• DISTSTATUS 3 = Generated
• DISTSTATUS 4 = Unable to Post
• DISTSTATUS 5 = Posted
• DISTSTATUS 6 = Delete
• DISTSTATUS 7 = Posting (stuck).

XML File is Invalid Error

If you are getting errors in the message log along the lines of:

• The XML file returned by the web server is invalid
• XML document object creation failed

Here's a screenshot of what you might see in the message log:

It could be because you have not set the authentication option on your default local node. Find your default local node under: PeopleTools > Integration Broker > Integration Setup > Nodes (has default local node set to Y e.g. PSFT_HR). Change your authentication option to Password and enter the default user ID and their password as shown:

Once you fix this, go back to the process monitor, view the details for the relevant processes and resend the content.

Hopefully it now posts.

HTTP Transfer Error

Check the distribution agent logs for the process scheduler. If you are seeing lines like this:

(JNIUTIL): Java exception thrown: java.net.SocketException: socket closed

HTTP transfer error.

Then check your report node configuration under:
PeopleTools > Process Scheduler > Report Nodes

In particular:

• Check that your URL and URI is fully qualified and includes both the server name and the domain.
• You only need login information if there is web server authentication, otherwise leave this blank
• If you change the URL or URI you will need to restart the appropriate process schedulers.

Also under PeopleTools > Integration Broker > Nodes

• Open your default local node (e.g. PSFT_HR) - in the search page, find Local Node = 1
• Confirm it is using the authentication option of password
• Confirm that the user ID specified exists and has appropriate roles such as ProcessSchedulerAdmin and ReportDistAdmin
• Retype the password for that user to confirm it is correct
• Ensure the user's account isn't locked

HTTP Status Code is 900

If you see the following in your process message log/distribution agent log:

HTTP Status Code is: 900 (63,72) 

HTTP transfer error

There is probably something wrong with your report node configuration. Browse to PeopleTools > Process Scheduler > Report Nodes, and in particular, check the connection information, such as the protocol (http/https), URI host, URI port, and URI Resource (this should beSchedulerTransfer/ps). Also confirm that the report repository location in the web profile configuration/configuration.properties of the web server is accessible and writable from the webserver.

Any changes will require at least a process scheduler restart, and perhaps a web server restart.

HTTP Status Code is 902

If you see the following in your process message log/distribution agent log:

Java exception thrown: java.lang.SecurityException: illegal URL redirect 

HTTP Status Code is: 902 (63,7 2)

Navigate to PeopleTools > Integration Broker > Integration Setup > Nodes

Open the default local node (Y) (e.g. PSFT_HR):

• Confirm the authentication option is Password
• The default user ID is correct (and exists in the system)
• The password is correct for that user ID

Navigate to PeopleTools > Security > Security Objects > Single Signon

Confirm that your default local node (e.g. PSFT_HR) is included in the trust authentication tokens issued by these nodes list.

Reports not posting initially

If reports are not posting initially, but do post after some length of time, Solution ID 660253.1 on My Oracle Support might be helpful. This involves adding the line 0 to the web.xml file on the web server.

Distribution status is N/A

One reason why the distribution status might stay at N/A for some processes is if the Transfer System Files to Report Repository check box is not set in the distribution tab of the process scheduler where the process is running.

Navigation for this is:

• PeopleTools > Process Scheduler > Servers > [Distribution Tab]

A process scheduler restart might be required.

Note this is value is stored in the field TRANSFERLOGFILES in PS_SERVERDEFN.

Directory Creation Failed

If you get the following errors:

Java Exception: Error while write to file:java.lang.SecurityException: Directory Creation Failed  (6 3,49) 

SchedulerTransfer Servlet error. 

HTTP transfer error.

First check your report repository path is correct in the relevant web profile (and if blank there), in the configuration.properties file for your web server. Also check that the disk is not full for the relevant report repository path. Also check you have the fileauthtokenenabled.html exists in the same location as configuration.properties.

Finally check permissions - is the owner/security for the folder correct? Does it match the OS user that started the process scheduler?

Error 404—Not Found

When opening a report that has run to success and posted through view log/trace or through the report manager, the following error appears (this is for Weblogic):

Error 404--Not Found

From RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:

10.4.5 404 Not Found

The server has not found anything matching the Request-URI. No indication is given of whether the condition 

is temporary or permanent.

If the server does not wish to make this information available to the client, the status code 403 (Forbidden) 

can be used instead. The 410 (Gone) status code SHOULD be used if the server knows, through some internally 

configurable mechanism, that an old resource is permanently unavailable and has no forwarding address.

This is due to an incorrect value in the URL field under:
PeopleTools > Process Scheduler > Reports Nodes

For instance instead of the correct http://machinename:port/psreports/ps, I had http://machinename:port/reports/ps. Having the incorrect value (reports instead of psreports) caused this error.