Orphan processes in Oracle databases

I have many monitoring tools implemented here which keep bugging my application instances. We have one external careers site too which too increments some orphan processes in my Oracle database. Although we have script in place to take of them and kick them out frequently, but still I can see my total number of process reached close to my max process limit. Here what I have to do in that situation:

1. Run this query to find out them:

SELECT spid FROM v$process WHERE NOT EXISTS
( SELECT 1 FROM v$session WHERE paddr = addr);

2. Grep what are they and what are they doing :

SELECT '!ps -ef : grep ' spid FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);

3. Kill them, if they are not required:

SELECT '!kill -9 ' :: spid FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);

Happy Troubleshooting !!!

Note: Use Pipe sign in place of colon which I have mentioned in statments

Password expiring Alert

Following script will help you to get an alert if users have password longer than certain days. Lets say you set an alert for password to expire in 180 days and you want to sent an alert after 150 days:

. .env

cd /tmp
sqlplus -S /nolog <<>
connect apps/$APPSPASS
set feedback off
set pages 100
set lines 100
col "username" format a15
col "account_status" format a15
col "profile" format a15
col "LAST_PASSWD_DATE" format a16
col "CHANGE BY" format a16
spool /tmp/userpassword.log

select /*+ RULE */ dd.username,dd.account_status,dd.profile,
dd.created "CREATION_DATE", u.ptime "LAST_PASSWD_DATE",u.ptime+180 "CHANGE BY"
from dba_users dd, sys.user$ u
where dd.USERNAME = u.NAME
and u.ptime <>
and dd.account_status = 'OPEN'
and dd.USERNAME != 'APPLSYSPUB' -- Exception as this account should have the default password
order by dd.created,u.username
/
spool off
EOF
if [ -s /tmp/userpassword.log ] ; then
cat /tmp/userpassword.log mailx -s "Users with Password older than 150 days" sudubey@gmail.com
fi

Keep Bugging Users :-)

Keep Troubleshooting !!!

Having issues with JVM

As Apache is using JVM internally, we have to look at troubleshooting JVM too, before jumping onto Apache. See at jvm log files if you see "Full GC" having quiet frequently, I would suggest to make following changes and if you already have mentioned values, increase something higher then this :


1. jserv.conf
ApJServVMTimeout 180

2. jserv.properties
wrapper.bin.parameters=-verbose:gc -Xmx1024M -Xms256M -XX:MaxPermSize=256M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB

3. httpd.conf
Timeout 1800

4. ICX:Session Timeout 30 (profile value)

5. zone.propertiessession.timeout=1800000

Note : > 3,4,5 value has to be same

6. JDBC parameter changes in dbc file
FND_JDBC_BUFFER_DECAY_SIZE=20
FND_JDBC_BUFFER_MIN=20
FND_JDBC_BUFFER_MAX=50%

7. Bounce Apache after above changes

Happy Troubleshooting !!!

APP-FND-02704: Unable to alter user Apps to change password

You might encounter this error while changing apps or other product password using FNDCPASS.

+---------------------------------------------------------------------------+
Working...
APP-FND-02704: Unable to alter user APPS to change password.
+---------------------------------------------------------------------------+
Concurrent request completed

Look at two things:

1. Never start your password other than Alphabatic value.

Even if it doesn't work.

2. change your profile back to default and run FNDCPASS again.

It worked for me...

Happy Troubleshooting !!!

Good tool for troubleshooting - HTTPheaders

For Taking HTTP header trace follow the steps mentioned here:

1. Obtain the HTTP header tracer add-on:
Internet Explorer (IE): http://www.blunck.info/iehttpheaders/iehttpheaders.html
Firefox (FF): http://livehttpheaders.mozdev.org/

2. Once installed and the browser restarted, start the HTTP header tracing add-on is as follows:
(IE) View -> Explorer Bar -> ieHTTPheaders v1.6
(FF) Tools -> Live HTTP Headers

3. Ensure the HTTP capture buffer is clear before you begin.
(IE) In the "ieHTTPheaders window" -> Right-Click -> Clear
(FF) In the "Live HTTP Headers" -> Click on 'Clear'

4. Once ready, direct the browser to the login page of your environment and proceed to replicate the issue.

5. Save the resulting generated output to a file and upload the results..
(IE) In the "ieHTTPheaders window" -> Right-Click -> 'Save'
(FF) In the "Live HTTP Headers" -> Click on 'Save-All'


Other than above steps, you can type following words in your browser address bar to get cookies info:

javascript:unescape(document.cookie)

Happy Troubleshooting !!!