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

ORA - 600 -- Look at these Metalink notes for Help

ORA-600 Errors 1 to 3000
Note 138300.1 "ORA-600 [105]"
Note 263295.1 "ORA-600 [106]"
Note 28104.1 "ORA-600 [504]"
Note 138871.1 "ORA-600 [510]"
Note 138888.1 "ORA-600 [525]"
Note 138939.1 "ORA-600 [711]"
Note 39308.1 "ORA-600 [723]"
Note 31056.1 "ORA-600 [729]"
Note 131490.1 "ORA-600 [733]"
Note 217860.1 "ORA-600 [1013]"
Note 138123.1 "ORA-600 [1100]"
Note 41767.1 "ORA-600 [1113]"
Note 40514.1 "ORA-600 [1114]"
Note 137262.1 "ORA-600 [1115]"
Note 66387.1 "ORA-600 [1158]"
Note 138354.1 "ORA-600 [1236]"
Note 28045.1 "ORA-600 [2103]"
Note 28929.1 "ORA-600 [2662]"
Note 31057.1 "ORA-600 [2845]"
Note 138733.1 "ORA-600 [2865]"


ORA-600 Errors 3001 to 6000
Note 30866.1 "ORA-600 [3020]"
Note 93665.1 "ORA-600 [3668]"
Note 47456.1 "ORA-600 [4000]"
Note 96642.1 "ORA-600 [4036]"
Note 43914.1 "ORA-600 [4137]"
Note 28226.1 "ORA-600 [4146]"
Note 39282.1 "ORA-600 [4193]"
Note 39283.1 "ORA-600 [4194]"
Note 138822.1 "ORA-600 [4400]"
Note 29702.1 "ORA-600 [4414]"
Note 138836.1 "ORA-600 [4454]"
Note 39553.1 "ORA-600 [4511]"
Note 73455.1 "ORA-600 [4512]"
Note 27955.1 "ORA-600 [4519]"
Note 204536.1 "ORA-600 [4820]"
Note 41840.1 "ORA-600 [4882]"


ORA-600 Errors 6001 to 9000
Note 47449.1 "ORA-600 [6002]"
Note 116552.1 "ORA-600 [6034]"
Note 40640.1 "ORA-600 [6101]"
Note 99300.1 "ORA-600 [6122]"
Note 138913.1 "ORA-600 [6193]"
Note 39399.1 "ORA-600 [6731]"
Note 41719.1 "ORA-600 [6856]"


ORA-600 Errors 9001 to 15000
Note 138325.1 "ORA-600 [12209]"
Note 33174.1 "ORA-600 [12235]"
Note 138332.1 "ORA-600 [12261]"
Note 138340.1 "ORA-600 [12304]"
Note 35928.1 "ORA-600 [12333]"
Note 28229.1 "ORA-600 [12700]"
Note 67496.1 "ORA-600 [13009]"
Note:28185.1 "ORA-600 [13013]"


ORA-600 Errors 15001 to 17000
Note 138428.1 "ORA-600 [15160]"
Note 138430.1 "ORA-600 [15201]"
Note 138431.1 "ORA-600 [15203]"
Note 131186.1 "ORA-600 [15212]"
Note 137266.1 "ORA-600 [15419]"
Note 216277.1 "ORA-600 [15456]"
Note 138457.1 "ORA-600 [15709]"
Note 67490.1 "ORA-600 [15851]"
Note 76528.1 "ORA-600 [15868]"
Note 138499.1 "ORA-600 [16201]"
Note 106607.1 "ORA-600 [16365]"
Note 138523.1 "ORA-600 [16515]"
Note 138526.1 "ORA-600 [16607]"

ORA-600 Errors 17001 to 30000
Note 138537.1 "ORA-600 [17003]"
Note 138541.1 "ORA-600 [17012]"
Note 41472.1 "ORA-600 [17034]"
Note 138554.1 "ORA-600 [17059]"
Note 39616.1 "ORA-600 [17069]"
Note 29616.1 "ORA-600 [17090]"
Note 138565.1 "ORA-600 [17099]"
Note 47411.1 "ORA-600 [17112]"
Note 39453.1 "ORA-600 [17113]"
Note 34782.1 "ORA-600 [17114]"
Note 138576.1 "ORA-600 [17128]"
Note 138580.1 "ORA-600 [17147]"
Note 34781.1 "ORA-600 [17148]"
Note 138586.1 "ORA-600 [17172]"
Note 263110.1 "ORA-600 [17175]"
Note 34779.1 "ORA-600 [17182]"
Note 45725.1 "ORA-600 [17271]"
Note 138597.1 "ORA-600 [17274]"
Note 134139.1 "ORA-600 [17280]"
Note 39361.1 "ORA-600 [17281]"
Note 138602.1 "ORA-600 [17285]"
Note 138621.1 "ORA-600 [17585]"
Note 138640.1 "ORA-600 [18209]"
Note 216278.1 "ORA-600 [18261]"
Note 138678.1 "ORA-600 [20084]"
Note 100073.1 "ORA-600 [25012]"


ORA-600 Errors kc
Note 138981.1 "ORA-600 [kcbgcur_2]"
Note 70097.1 "ORA-600 [kcbgcur_3]"
Note 114058.1 "ORA-600 [kcbgcur_9]"
Note 138990.1 "ORA-600 [kcbgtcr_4]"
Note 138991.1 "ORA-600 [kcbgtcr_5]"
Note 261264.1 "ORA-600 [kcbgtcr]"
Note 248874.1 "ORA-600 [kcbgtcr_6]"
Note 233612.1 "ORA-600 [kcbgtcr_12]"
Note 204512.1 "ORA-600 [kcbnew_3]"
Note 216104.1 "ORA-600 [kcbrbo1]"
Note 139011.1 "ORA-600 [kcbzpb_1]"
Note 139012.1 "ORA-600 [kcbzpb_2]"
Note 229467.1 "ORA-600 [kcbzwb_4]"
Note 139013.1 "ORA-600 [kccsbck_first]"
Note 216108.1 "ORA-600 [kcllcu_0]"
Note 263225.1 "ORA-600 [kclwcrs_15]"
Note 76434.1 "ORA-600 [kcoapl_blkchk]"
Note 248718.1 "ORA-600 [kcratr1_lostwrt]"


ORA-600 Errors kd to kw
Note 139037.1 "ORA-600 [kdddgb2]"
Note 139042.1 "ORA-600 [kdisle:nrows]"
Note 139051.1 "ORA-600 [kghalo2]"
Note 139052.1 "ORA-600 [kghasp1]"
Note 139066.1 "ORA-600 [kghxhdr1]"
Note 229809.1 "ORA-600 [kgliep_1]"
Note 66501.1 "ORA-600 [kkrqtab2]"
Note 139095.1 "ORA-600 [kkslgop1]"
Note 139116.1 "ORA-600 [kohdtf048]"
Note 264061.1 "ORA-600 [kqludp2]"
Note 139162.1 "ORA-600 [kssrmp1]"
Note 247822.1 "ORA-600 [ksmals]"
Note 139153.1 "ORA-600 [ksmguard2]"
Note 233864.1 "ORA-600 [kteuproptime-2]"
Note 139180.1 "ORA-600 [ktsgsp5]"
Note 139193.1 "ORA-600 [kttmupd_segment-]"
Note 228480.1 "ORA-600 [kwqitnmptme:read]"
Note 228480.1 "ORA-600 [kwqitnmptme:ready]"
Note 228364.1 "ORA-600 [kwqitnmptme:wait]"


ORA-600 Errors q to z
Note 248095.1 "ORA-600 [qctcte1]"
Note 216273.1 "ORA-600 [qctstc2o1]"
Note 209363.1 "ORA-600 [qerrmofbu1]"
Note 237598.1 "ORA-600 [qertqtableallocate2]"
Note 226887.1 "ORA-600 [qkagby4]"
Note 222876.1 "ORA-600 [qmxiunppacked2]"
Note 244365.1 "ORA-600 [rworupo.1]"
Note 139263.1 "ORA-600 [srsget_1]"
Note 260951.1 "ORA-600 [ttcgcshnd]"
Note 216452.1 "ORA-600 [ttcgcshnd-1]"
Note 216453.1 "ORA-600 [ttcgcshnd-2]"


Happy Troubleshooting !!!

SSL in 11i -- Where all we can have certificates

Yup one off the major problem for Apps DBA comes, when we have all bounch of security in our environment. SSL is one off them. So you have to take care of Apache ceritificate and moreover all issues with certificate or SSL handshake etc etc. But where to look for ceritificates:

Application Tier:
Look at httpd.conf file and search for ssl.crt or passkey or ssl.key, you will get the location where these files are stored. Make sure you change your ceritificate in the location mentioned in httpd.conf file.
ssl.crt > contains ca.crt, rootca.crt and server.crt files
ssl.key> contains server.key
passkey> Contains the certificate password

so for Apache certificate if you have above mentioned files it will work.

Now comes Client machine:
We have certificate here too:
C:\Program Files\Oracle\JInitiator 1.3.1.18\lib\security look at certdb file. Your ca.crt certificate must match with one of certificate in this file.

And look at ceritificate in your browser too:
IE > Internet Options > contents > Ceritficates> Click on tab Trusted Root Certification Authorities> Specially look at Class 2,3 certificate expiry date.

If all the above this are ok, you should not face any issue with Apache and SSL.

Happy Troubleshooting !!!

Troubleshooting if you have SSO enabled 11i

You might ask what and how does it matter if you have SSO enable or not. And how does it help in case we disable SSO. Yes it does !!!
I would suggest just try this. I have seen many occasion in my Production env giving errors for SSL handshake, each and every pointer in google or metalink is saying something is wrong with root certificate or you didn't imported your root certificate well. I tried it many times, but error remains same.
Now what, I have disabled the SSO and tried login and amazingly it was working fine. But now how to resolve the issue. While looking at certificate I reaslized one off the certificate in client end, means my System has some issue. I have reinstalled that from server and re-enabled my SSO .... vola it worked !!!!!!!
I know you might feel its imaginery situation, but it always works.

Happy Troubleshooting !!!

Enabling Debug for Discoverer 4i

One of the other way to find out issues in discoverer connection specially for from Oracle Apps. Just enable log and check the log file for errors:

Put following lines in discwb.sh file and restart services

AFLOG_ENABLED=Y
AFLOG_FILENAME=/Complete_Path/6iserver/discwb4/disc.log
AFLOG_LEVEL=STATEMENT
AFLOG_MODULE="fnd.src.security.afscp fnd.src.osd.afenv"
export AFLOG_ENABLED AFLOG_FILENAME AFLOG_LEVEL AFLOG_MODULE

Happy Trobleshooting !!!

Orphan Profiles in Oracle Apps

You must be wondering what's that. Yup it does exists sometime in our clone instances and it takes us to somewhere else. Debugging and troubleshooting takes long time with no results. Check the following if you have any rows results out of following query that means you have someone which you don't want in your profiles:

col profile_option_name format a40
col "Orphan ID" format a10
select
v.level_value "Server Orphan",
v.level_value2 "ServResp Orphan",
decode(v.level_id,
10005, 'Server',
10007, 'ServResp',
'Other') LEVEL_SET,
p.Profile_option_name
from
fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n
where
p.profile_option_name = n.profile_option_name
and p.profile_option_id = v.profile_option_id (+)
and
( /* check Server level */
(
v.level_id=10005
and v.level_value > 0
and v.level_value
not in
(
select
f.node_id
from
fnd_nodes f
)
)
or /* check ServResp level */
(
v.level_id=10007
and (v.level_value2 is not null)
and (v.level_value2 > 0)
and v.level_value2
not in
(
select
f.node_id
from
fnd_nodes f
)
)
);

Now what, if you get some rows: Do this

alter trigger APPS.FND_PROFILE_OPTION_VALUES_AD disable;

delete from fnd_profile_option_values
where
level_id = 10005
and level_value in (Value of Node);

commit;

alter trigger APPS.FND_PROFILE_OPTION_VALUES_AD enable;

Happy Troubleshooting !!!

Apache Debug

You might be knowing it, but I would like to summarize the steps again to take Debug for Apache, believe me set this and you can easily resolve the errors:

1. Shut the HTTP Server (Apache/iAS) down. - You can grep for the 'httpd' process to verify it is down
2. Rename (or delete) the following files so we get a fresh copy of them:
/Apache/Jserv/logs/jserv.log
/Apache/Jserv/logs/mod_jserv.log
/Apache/Jserv/logs/jvm (the whole directory if it exists)/Apache/Apache/logs/access_log*
/Apache/Apache/logs/error_log
/Apache/Apache/logs/error_log_pls (if it exists)

Now we will turn on debugging in the log files:

3. Modify the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf file. Search for the parameter:
ApJServLogLevel notice
Change the 'notice' to 'debug'

4. Modify the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties file. Search for the following section:

log=false
Change this to be log=true
and Changelog.channel=false
To be: log.channel=true

5. Modify the $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf file. Search for:
LogLevel
Set the LogLevel to 'debug' from its current value.

6. Modify the $IAS_ORACLE_HOME/Apache/Apache/conf/httpd_pls.conf file (if it exists). Search for the following parameter
LogLevelSet the LogLevel to 'debug' from its current value.

7. In the zone.properties...- servlet.framework.initArgs=debuglevel=1

8. Start the HTTP Server

Happy Troubleshooting !!!

Finding Huge files

Yes its a big tasks for DBA's, when you do df -k in solaris and see your mount points are reaching 100%. It difficult to find what to delete for what to compress. I usually try some commands mentioned below :

This command will list top 10 directory from parent directory
du -ks * sort -rn head -10

This command will list out all files greater than mentioned size.
find . -size +10240

Keep Troubleshooting !!!

Old Oracle Apps 11i Looks

Hey you might be wondering what I am going to talk now. Yup its about oracle Apps front end look and feel. Some of the users still using same old grey and blue looks and DBA wonder if he missed something. No not at all, just check these two profiles and you are done.

Change the following profiles at user level first-
"Java Look and Feel " to oracle and
"Java Color Scheme" to blaf

If it works for you then change it Site level to have others enjoy the same.!!!

Keep Troubleshooting !!!

Script for Checking Java Mailer Status

---------------------------------Script Start-----------------------------------
#!/bin/ksh
#Script to monitor WF Mailer Status
. .env
cd /tmp
sqlplus /nolog << EOF
connect apps/password
set heading off
set pages 0
set feedback off
spool monitor_mailer.lst
select COMPONENT_STATUS from FND_SVC_COMPONENTS
where COMPONENT_ID='10006';
spool off
EOF
MONITOR=`grep -v "SQL>" monitor_mailer.lstsed s/\ //g`

if [ $MONITOR != "RUNNING" ] ; then
echo $MONITOR mailx -s "WFMAILER: Check Workflow Mailer STATUS" sudubey@gmail.com
fi
/bin/rm -f /tmp/monitor_mailer.lst
-----------------------Script End -------------------------------------------------

Put this in crontab for every 10 min or so. It will send a status whenever its not running. No more OAM checking !!!!

Note: This has been tested in Sun Solaris

Keep Troubleshooting !!!

Apps Password changed from sqlplus

Many of you might have done this mistake, or no option left like in my client usually happen in every six month, it force you to change apps password via sqlplus. Now what application login will stop working, isn't it. Yes !!

Do this :
1. Change to old apps password via sqlplus;

SQL> Alter user apps identified by oldappspasswd;

2. Run autoconfig with this password in all tiers.

3. Restore customizations if any.

4. Start all services and you all set to login to your apps frontend login.

Hope this helps you all.

Keep Troubleshooting !!!