Hi,

How to proactively monitor peoplesoft environments before the process goes down?

Thanks & Regards,Ravi

Thanks for posting your question!

Below are excerpts from my previous posts on the blog site.

How to monitor PeopleSoft process scheduler for availability?

There are many approaches to monitor the PeopleSoft process scheduler. One of the options that come to
mind is to have a script running on the OS hosting the process scheduler to monitor the OS process. But
having process schedulers running on multiple OS
(PSUNX and PSNT) will need development of multiple
scripts.My preferred method is to use the below SQL
to monitor all the process schedulers running on the database.

SELECT A.SERVERNAME, B.XLATSHORTNAME,B.FIELDNAME,B.FIELDVALUE,TO_CHAR
(B.EFFDT,’YYYY-MM-DD’)

FROM PSSERVERSTAT A, PSXLATITEM B

WHERE B.EFFDT =

(SELECT MAX(B_ED.EFFDT) FROM PSXLATITEM B_ED

WHERE B.FIELDNAME = B_ED.FIELDNAME

AND B.FIELDVALUE = B_ED.FIELDVALUE

AND B_ED.EFFDT <= SYSDATE)

AND B.FIELDNAME = ‘SERVERSTATUS’

AND B.FIELDVALUE = A.SERVERSTATUS

Depending on the results of the above SQL you can trigger
an email notification or any other form of action.

Using PSADMIN utility

psadmin -p status -d $DOMAIN

Monitoring Application Server

Use the below SQL to determine whether there are any sessions from the Application server in the database.

select * from v$session where upper(program) like ‘%APP%’;

Using PSADMIN utility

psadmin -c sstatus -d $DOMAIN

Monitoring Weblogic Web Server

java weblogic.Admin –url $HOST:$PORT -username
USER
–password PASS GETSTATE

Monitoring Integration Broker Messages/Subscription/Publication Contracts

One of the hallmarks of a good administrator is that they are already troubleshooting the problem before the users complain!

The key to ensuring that the system does not spring surprises is to
automate monitoring (without impacting the system performance).
In my previous posts, I have provided steps to monitor the process
scheduler and application server. This post will provide insights into monitoring the integration broker messages, publication and
subscription contracts.

Use the below SQLs for monitoring.

SUBSCRIPTION

SELECT A.CHNLNAME,

DECODE(A.SUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,

A.SUBCONSTATUS) STATUS, COUNT(*)

FROM PSAPMSGSUBCON A

WHERE

— not older than 99 days

A.LASTUPDDTTM >= sysdate – 99

— in error or timeout status

AND (A.SUBCONSTATUS IN (0, 6)

— in NEW status but was last updated 30 mins agao.

OR (A.SUBCONSTATUS = 1 AND A.LASTUPDDTTM

< SYSDATE – 1/48))

GROUP BY A.CHNLNAME, DECODE(A.SUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,

A.SUBCONSTATUS)

/

MESSAGE

SELECT A.CHNLNAME, DECODE(A.PUBSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,

A.PUBSTATUS) STATUS, COUNT(*)

FROM PSAPMSGPUBHDR A

WHERE

— not older than 99 days

A.LASTUPDDTTM >= sysdate – 99

— in error or timeout status

AND (A.PUBSTATUS IN (0, 6)

— in NEW status but was last updated 30 mins agao.

OR (A.PUBSTATUS = 1 AND A.LASTUPDDTTM < SYSDATE – 1/48))

GROUP BY A.CHNLNAME, DECODE(A.PUBSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,

A.PUBSTATUS)

/

PUBLICATION

SELECT A.CHNLNAME, DECODE(A.PUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,

A.PUBCONSTATUS) STATUS, COUNT(*)

FROM PSAPMSGPUBCON A

WHERE

— not older than 99 days

A.LASTUPDDTTM >= sysdate – 99

— in error or timeout status

AND (A.PUBCONSTATUS IN (0, 6)

— in NEW status but was last updated 30 mins agao.

OR (A.PUBCONSTATUS = 1 AND A.LASTUPDDTTM

< SYSDATE – 1/48))

GROUP BY A.CHNLNAME, DECODE(A.PUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,

A.PUBCONSTATUS)

/

Value

Status

0

ERROR

1

NEW

2

STARTED

3

WORKING

4

DONE

5

RETRY

6

TIMEOUT

7

EDITED

8

CANCELE

9

HOLD

SUBSCRIPTION

SELECT MIN(A.CHNLNAME) COLA, ‘ ‘,

MIN(A.SUBCONSTATUS) COLB, COUNT(*)

FROM PSAPMSGSUBCON A

WHERE A.CHNLNAME IN

(SELECT S.CHNLNAME

FROM PS_AMM_CHNL_SECVW S

WHERE S.OPRID = ‘NPAI’)

AND A.LASTUPDDTTM >= TO_DATE(SUBSTR(‘2007-07-12-11.07.42.000000’, 0, 19),

‘YYYY-MM-DD-HH24.MI.SS’)

GROUP BY A.CHNLNAME, A.SUBCONSTATUS ORDER BY COLA, COLB

MESSAGE

SELECT MIN(A.CHNLNAME) COLA, ‘ ‘, MIN(A.PUBSTATUS) COLB, COUNT(*)

FROM PSAPMSGPUBHDR A

WHERE A.CHNLNAME IN

(SELECT S.CHNLNAME

FROM PS_AMM_CHNL_SECVW S

WHERE S.OPRID = ‘NPAI’)

AND A.LASTUPDDTTM >= TO_DATE(SUBSTR(‘2007-07-12-11.12.57.000000’, 0, 19),

‘YYYY-MM-DD-HH24.MI.SS’)

GROUP BY A.CHNLNAME, A.PUBSTATUS ORDER BY COLA, COLB

PUBLICATION

SELECT MIN(A.CHNLNAME) COLA, ‘ ‘, MIN(A.PUBCONSTATUS) COLB, COUNT(*)

FROM PSAPMSGPUBCON A

WHERE A.CHNLNAME IN

(SELECT S.CHNLNAME

FROM PS_AMM_CHNL_SECVW S

WHERE S.OPRID = ‘NPAI’)

AND A.LASTUPDDTTM >= TO_DATE(SUBSTR(‘2007-07-12-11.15.25.000000’, 0, 19),

‘YYYY-MM-DD-HH24.MI.SS’)

GROUP BY A.CHNLNAME, A.PUBCONSTATUS ORDER BY COLA, COLB

0 ERROR

1 NEW

2 STARTED

3 WORKING

4 DONE

5 RETRY

6 TIMEOUT

7 EDITED

8 CANCELED

9 HOLD

Posted by Nitin Pai
Comments (0)
November 7th, 2008

Comments (0)