DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, March 28, 2013

Find and Clear INACTIVE SESSIONS

select 'alter system kill session '||' '||''''||s.sid||','||s.serial# ||''' immediate;' FROM   v$session s
WHERE  s.type!= 'BACKGROUND'
AND S.TYPE='USER'
AND S.USERNAME='<SCHEMA NAME>'
AND TRUNC(S.LOGON_TIME) < '28-MAR-2013'
AND S.STATUS='INACTIVE'
-- and last_call_et > 30       -- more than 30 mins inactive

Wednesday, March 27, 2013

OPP Issue while generating the XML Output in apps 11i



Problem Description:
Ø  Few XML type concurrent programs taking long time and completed with warning OPP log file registered below error
      Caused by: java.lang.ThreadDeath
Ø  CPU utilization taking 100% constantly while running the concurrent programs.


Cause
The java.lang.ThreadDeath error indicates that the Output Post Processor has reached its processing timeout.

Solution
The following configuration changes are recommended to optimize the environment for these type of reports:
1. Increase the value of the Concurrent: OPP Timeout profile option to 10800 seconds.
2. Enable the scalability feature of XML Publisher:

1. Login as SYSADMIN
2. Responsibility: XML Publisher Administrator
3. Function: Administration
4. Set the following properties:
5. Temporary Directory
6. Use XML Publisher's XSLT processor: True
7. Enable scalable feature of XSLT processor: True   -- By default it’s false
8. Enable XSLT runtime optimization: True

> After changing the value also the problem still exist. As per the SR suggestion, we increased the java heap size from 1024M to 2048M.

a. Bring down the concurrent managers.

b. Use the Update statement below, for example:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

c. Bring concurrent managers up again
 

Ref:
427233.1  :Output Post Processing Fails Due To java.lang.ThreadDeath
1268217.1: Output Post Processor (OPP) Log Contains Error “java.lang.OutOfMemoryError: Java heap space"
1266368.1: Output Post Processor (OPP) Log Contains Error   "java.lang.OutOfMemoryError"
978495.1: Why Does OPP Intermittently Completes With Warnings and Error 'java.lang.OutOfMemoryError'?
352518.1 :Concurrent Requests Fail Due to Output Post Processing (OPP) Timeout
364547.1 :Troubleshooting Oracle XML Publisher For The Oracle E-Business Suite

Send emails with UTL_MAIL package in 10g

UTL_MAIL:
UTL_MAIL is a wrapper, which internally uses UTL_TCP and UTL_SMTP
UTL_MAIL package is much easier to use than the UTL_SMTP package.

1) The package is loaded by running the following scripts:

    CONN sys/password AS SYSDBA
    @$ORACLE_HOME/rdbms/admin/utlmail.sql
    @$ORACLE_HOME/rdbms/admin/prvtmail.plb

2) Check instance is running on pfile or spfile.

Pfile:
Shutdown the applications and database

Edit the init parameter file with below values
     smtp_out_server='mail.domain.com:25' 

Start the database and applications.

Spfile:
  CONN sys/password AS SYSDBA
  ALTER SYSTEM SET smtp_out_server = 'mail.domain.com:25' SCOPE=BOTH;

Note: Check instance is running on pfile or spfile.

Sql> show parameter spfile;

If its returns value, it’s running on Spfile otherwise instance using pfile

3) Grants the execute on UTL_MAIL privilege to PUBLIC or Specific USER

     GRANT EXECUTE ON UTL_MAIL TO PUBLIC;

4) Verify if you are able to send email

Replace values specific to your instance
BEGIN
UTL_MAIL.send(sender => 'dbsmgrtst@testserv1.doyensys.com',
recipients => 'xyz@doyensys.com',
cc => 'abc@doyensys.com',
bcc => 'test@doyensys.com',
subject => 'UTL_MAIL Test',
message => 'If you get this message utl_mail is working..');
END;

Friday, March 22, 2013

To generate an email if more than 10 archives are generated in an Hour

SDATE=`date +"%d%b%y %H:%M"`;
sqlplus -s '/ as sysdba' @/home/oracle/COOL/archcounthr.sql > /home/oracle/COOL/archcounthr.log; cnt=`cat /home/oracle/COOL/archcounthr.log`
if [ $cnt -gt 10 ]
then echo "$cnt Archives generated in QTYDB at $SDATE" | mailx -s "QTYDB:Archives Generating >10" dba@xyz.com
fi
exit;

 SQL File
=======
 archcounthr.sql

set head off;
set feed off;
select to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),to_char(sysdate,'HH'),1,0)),'99') "HH24" from v$log_history where trunc(first_time)=trunc(sysdate) group by to_char(first_time,'YYYY-MM-DD') order by to_char(first_time,'YYYY-MM-DD') desc;
exit;

Thursday, March 14, 2013

Failed to start a managed process after the maximum retry limit



Scenario:
========

We have copied the Ias from different machine and while starting opmn we get the following error:

Error :
======

[oracle@doyen ~]$ opmnctl startall
opmnctl: starting opmn and all managed processes...
opmn id=doyen.com:6299
    0 of 1 processes started.

ias-instance id=standalone
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
    HTTP_Server/HTTP_Server/HTTP_Server

Error
--&gt; Process (pid=10533)
    failed to start a managed process after the maximum retry limit
    Log:
    /u01/apex/iassquid/opmn/logs/HTTP_Server~1


Solution:
========

[oracle@doyen ~]$ su - root
Password:
[root@doyen oracle]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2


Now Retest the issue :
=====================

[oracle@doyen ~]$ opmnctl startall
opmnctl: starting opmn and all managed processes...
[oracle@doyen ~]$ opmnctl status

Processes in Instance: standalone
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status
-------------------+--------------------+---------+---------
HTTP_Server        | HTTP_Server        |   10884 | Alive
LogLoader          | logloaderd         |     N/A | Down
dcm-daemon         | dcm-daemon         |     N/A | Down