Java Mailer Setup Diagnostic Test

Source $APPL_TOP/$APPLFENV on the Current Manager node assigned to the Workflow Mailer.
unzip ATGSuppJavaMailerSetup12.zip
./ATGSuppJavaMailerSetup12.sh
It needs following parameters to pass
1)apps user
2)apps user password and following
3)user paswd
PARAMETER_ID PARAMETER_NAME VALUE
------------ --------------- -------------------------
10018 ACCOUNT oracle
10033 INBOUND_SERVER smtp.corp.mycompany.com
10043 OUTBOUND_SERVER smtp.xyz.mycompany1.net
10053 REPLYTO oracle@mycompany.com

Use the following script to get above listed parameters
sql script:
col PARAMETER_NAME format a15
col VALUE format a25
select p.parameter_id,
p.parameter_name,
v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER', 'ACCOUNT', 'REPLYTO')
order by p.parameter_name;

Ref: Oracle Workflow ATG Support: R12 Java Mailer Setup Diagnostic Test [ID 748421.1]

Unable to connect Discoverer Admin using sysadmin

1. You need to create a folder called SECURE under your ORACLE folder:

eg. make a directory like c:\oracle\secure

2. Copy the DBC File from the E-Business Suite database and place it in the secure directory. It will be located in the FND_SECURE directory.

3. Select the Environment tab and create new System Variables

FND_SECURE =\secure in step 1 above: eg. c:\oracle\secure

4. Update your %ORACLE_HOME%/network/admin/tnsnames.ora to include an entry for the E-Business Suite database. The database name must match the two_task entry in the dbc file.

5. You may have to rename the dbc file so that it matches the name by which you are referring to the database in your TNS Names file

6. Make sure that TNSPING works and that you can get a connection to the database

7. You should be able to log in ok now

Ref:Connecting to an E-Business Suite EUL Using Any Discoverer Component Fails With "Authentication Failed" or "ORA-01017: invalid username/password;logon denied" [ID 364665.1]

Script to monitor jdbc connections

set pause off
REM JDBC Scripts used to clear JDBC INACTIVE connections:

REM Script1: It will capture the complete JDBC Connection repot:

spool prod_jdbc_update_jan12_11.log
select count(*),
substr(machine, 6, instr(machine,'.')-6) machine, process, module from gv$session where program like 'JDBC%'
group by machine, process, module
order by 1 asc ;

prompt 'executing Script2: Capture the all INACTIVE sessions list:...'

set lines 200
set head off
column machine format a35
column logon_time format a19
column spid format a10
column process format a10
column module format a18
column action format a10

select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
set head on
set pagesize 1000
SELECT
s.sid||','||s.serial# sid_ser,
s.process
,substr(s.machine, 6, instr(s.machine,'.')-6) machine,
s.program,
s.MODULE,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
round(s.last_call_et/3600) Last_Call_ET
FROM gv$session s, gv$process p
WHERE s.module like 'JDBC%'
and s.paddr = p.addr
and s.username is not null
AND s.module = s.program
and s.inst_id=p.inst_id
and s.status='INACTIVE'
and s.type != 'BACKGROUND'
ORDER BY round(last_call_et),logon_time, s.process,s.machine, s.program, s.MODULE;

prompt 'executing Script3: It will capture the JDBC Connections list older than 12 hours:..'

set lines 200
set head off
column machine format a35
column logon_time format a19
column spid format a10
column process format a10
column module format a18
column action format a10
select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
select 'Following is the JDBC Connections list older than 12 hours , are killed' from dual;
set head on
set pagesize 1000
SELECT
s.sid||','||s.serial# sid_ser,
s.process
,substr(s.machine, 6, instr(s.machine,'.')-6) machine,
s.program,
s.MODULE,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
round(s.last_call_et/3600) Last_Call_ET
FROM gv$session s, gv$process p
WHERE s.module like 'JDBC%'
and s.paddr = p.addr
and s.username is not null
AND s.module = s.program
and s.inst_id=p.inst_id
and s.status='INACTIVE'
and s.type != 'BACKGROUND'
and s.last_call_et/3600 > 12
ORDER BY round(last_call_et),logon_time, s.process,s.machine, s.program, s.MODULE;

prompt 'executing Script 4 : It will capture the list of JDBC Connection list and spool to /tmp/db_prod_kill_jdbc_sessions.lst which has the list to clear sid,serial#...'

set head off
set feedback off
spool /tmp/db_prod_kill_jdbc_sessions.lst

select 'alter system kill session '''||s.sid||','||s.serial#||''';'
FROM gV$SESSION s , gV$PROCESS p
WHERE s.paddr = p.addr
AND s.module = s.program
and s.inst_id=p.inst_id
and s.username IS NOT NULL
and s.type != 'BACKGROUND'
AND round(s.last_call_et/3600) > 12
and s.module like 'JDBC%'
and s.status='INACTIVE';

spool off

prompt 'executing Script5: It will capture the list of OS processes corresponding to JDBC Connection list (script 4) and spool to /tmp/jdbc_inactive_session.sh ..'


spool /tmp/jdbc_inactive_session.sh
SELECT ' kill -7 '||s.process
FROM gV$SESSION s ,gV$PROCESS p
WHERE s.paddr = p.addr
AND s.module = s.program
and s.inst_id=p.inst_id
and s.username IS NOT NULL
and s.username is not null
and s.type != 'BACKGROUND'
AND round(s.last_call_et/3600) > 12
and s.module like 'JDBC%'
and s.status='INACTIVE';
spool off

exit

The users that fails do not have access to the Business Area (BA) used by the worksheet

Cause
The users that fails do not have access to the Business Area (BA) used by the worksheet.
Solution
In Discoverer Administrator :
Use Tools / Security pull down menu.
Search for user(s) that fail, move required BA(s) from Available to Selected.
Select OK button (or Apply and then OK button)

or

Refer Note : Unable To Use EULAPI to Grant Privilege to Multiple Responsibilities [ID 430945.1]
Syntax Eg:$ORACLE_HOME/bin/eulapi -CONNECT eul4_us/eul4_us@PRDN -GRANT_PRIVILEGE -ROLE "All INV Super User" -PRIVILEGE all_user_privs -log /tmp/eul_14.log

Query for Appliation Products Installed

col application_short_name format a23
col APPLICATION_NAME format a27
col PATCH_LEVEL format a15
select fav.application_short_name
,fav.application_name
,fpi.patch_level
,decode(fpi.status,'N','None','I','Installed','S','Shared')
from fnd_product_installations fpi
,fnd_application_vl fav
where fav.application_id=fpi.application_id
order by 1;

how to know oracle forms patchset

- Login to the application
- Navigate to Help > About Oracle Applications

Eg:
Forms Server
----------------------------------------
Oracle Forms Version : 6.0.8.27.0

use the following note to know the corresponding Developer 6i Patchset
Note: 125767.1 - Upgrading Developer 6i with Oracle Applications 11i
Note: 232313.1 - Information on Previous Versions of Developer 6i Patchsets
Eg:
Forms: 6.0.8.27.x --> Developer 6i Patch 18
Forms: 6.0.8.26.x --> Developer 6i Patch 17
Forms: 6.0.8.25.x --> Developer 6i Patch 16
Forms: 6.0.8.24.x --> Developer 6i Patch 15

ORA-12152 "Unable To Send Break Message" on Connection

When trying to log on to the database from a client when using a Cisco GSS and a CSM (Content Switch Module) the connection fails to complete or fails before receiving any data with a network related error:
ORA-12152: TNS:unable to send break message

Solution:
To resolve such an issue, make sure all switches or firewalls are set to the highest possible value and test. Once all connections are successful, the idle time limit can be tuned downward to a value that won't cause an interruption. This type of configuration would normally be handled by the Network Administrator of a system so this information should be passed on to them for actioning.

Ref:ORA-12152 "Unable To Send Break Message" on Connection [ID 363638.1]