Useful Oracle E-Business Suite Scripts Part - 2

Various scripts used with Concurrent Manager/Requests
a.     To list all running programs
SELECT FCR.REQUEST_ID REQUEST_ID
       ,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
       ,FCR.ACTUAL_START_DATE START_DATE
       ,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
                               'I', 'Inactive',
                               'P', 'Pending',
                               'R', 'Running') PHASE
       ,DECODE(FCR.STATUS_CODE, 'A', 'Waiting',
                               'B', 'Resuming',
                               'C', 'Normal',
                               'D', 'Cancelled',
                               'E', 'Error',
                               'F', 'Scheduled',
                               'G', 'Warning',
                               'H', 'On Hold',
                               'I', 'Normal',
                               'M', 'No Manager',
                               'Q', 'Standby',
                               'R', 'Normal',
                               'S', 'Suspended',
                               'T', 'Terminating',
                               'U', 'Disabled',
                               'W', 'Paused',
                               'X', 'Terminated',
                               'Z', 'Waiting') STATUS
      ,FU.USER_NAME REQUESTED_BY
FROM  FND_CONCURRENT_PROGRAMS FCP,
      FND_CONCURRENT_PROGRAMS_TL FCPT,
      FND_CONCURRENT_REQUESTS FCR,
      FND_USER FU
WHERE    --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
       FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND    FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND    FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND    FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND    FU.USER_ID = FCR.REQUESTED_BY
AND    FCPT.LANGUAGE = USERENV('Lang')
AND    FCR.PHASE_CODE = 'R'
ORDER BY FCR.ACTUAL_START_DATE DESC

b. To list all pending programs
SELECT FCR.REQUEST_ID REQUEST_ID
       ,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
       ,FCR.ACTUAL_START_DATE START_DATE
       ,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
                               'I', 'Inactive',
                               'P', 'Pending',
                               'R', 'Running') PHASE
       ,DECODE(FCR.STATUS_CODE, 'A', 'Waiting',
                               'B', 'Resuming',
                               'C', 'Normal',
                               'D', 'Cancelled',
                               'E', 'Error',
                               'F', 'Scheduled',
                               'G', 'Warning',
                               'H', 'On Hold',
                               'I', 'Normal',
                               'M', 'No Manager',
                               'Q', 'Standby',
                               'R', 'Normal',
                               'S', 'Suspended',
                               'T', 'Terminating',
                               'U', 'Disabled',
                               'W', 'Paused',
                               'X', 'Terminated',
                               'Z', 'Waiting') STATUS
      ,FU.USER_NAME REQUESTED_BY
FROM  FND_CONCURRENT_PROGRAMS FCP,
      FND_CONCURRENT_PROGRAMS_TL FCPT,
      FND_CONCURRENT_REQUESTS FCR,
      FND_USER FU
WHERE    --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
       FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND    FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND    FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND    FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND    FU.USER_ID = FCR.REQUESTED_BY
AND    FCPT.LANGUAGE = USERENV('Lang')
AND    FCR.PHASE_CODE = 'P'
ORDER BY FCR.ACTUAL_START_DATE DESC

c.  To list all programs that are scheduled and yet to run
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date, cr.PHASE_CODE, cr.STATUS_CODE
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
--AND trunc(cr.requested_start_date) >= trunc(SYSDATE)
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id
AND cr.release_class_id is not null
order by name, requested_start_date;

d.    To list avg / min / max Execution time / Wait time and number of executions for a given program for a given date range

select c.CONCURRENT_PROGRAM_ID,
       q.concurrent_queue_name qname,
       c.concurrent_program_name||' - '||
       ctl.user_concurrent_program_name "Program"
      ,to_char(min(actual_start_date),'mm/dd/rr') earlieststart
      ,sum(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(tot)"
      ,avg(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(avg)"
      ,min(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(min)"
      ,max(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(max)"
      ,count(*) "Jobs"
      ,ls.meaning lsm
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_programs c,
      APPLSYS.fnd_concurrent_programs_tl ctl,
      APPLSYS.fnd_lookup_values ls,
      APPLSYS.fnd_concurrent_processes b,
      applsys.fnd_concurrent_queues q    
where c.concurrent_program_id = &cmprogid
  and a.concurrent_program_id = c.concurrent_program_id
  and a.controlling_manager = b.concurrent_process_id
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and ctl.concurrent_program_id = c.concurrent_program_id
  and ctl.language = 'US'
  and a.program_application_id = c.application_id
  and ctl.application_id = c.application_id
  and ls.lookup_type = 'CP_STATUS_CODE'
  and ls.language = 'US'
  and ls.enabled_flag = 'Y'
  and (ls.start_date_active <= sysdate and ls.start_date_active is not null)
  and (ls.end_date_active > sysdate or ls.end_date_active is null)
  and a.status_code || '' = ls.lookup_code
  and a.phase_code || '' = 'C'
group by c.CONCURRENT_PROGRAM_ID ,q.concurrent_queue_name, c.concurrent_program_name
        ,ctl.user_concurrent_program_name
        ,ls.meaning
order by 4 desc

e.   To get the input the Concurrent request number and if it is running then give the Oracle Sid, serial# , unix process id, along with program or module it is linked with
SELECT d.sid, d.serial# ,d.process , c.SPID, fcpv.user_concurrent_program_name, a.REQUEST_ID, fa.APPLICATION_NAME,
d.osuser, replace(d.machine,'HEADLANDS\',null) user_srvr, a.REQUESTED_START_DATE
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d,
fnd_concurrent_programs_vl fcpv,
fnd_application_tl  fa
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R'
AND fcpv.concurrent_program_id = a.CONCURRENT_PROGRAM_ID
AND a.RESPONSIBILITY_APPLICATION_ID = fa.APPLICATION_ID

f.     List all programs that can be run by a given userSELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application,
FCPT.USER_CONCURRENT_PROGRAM_NAME
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r,
FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE g.user_id(+) = u.user_id
AND u.USER_NAME = '&USER_NAME'
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND FCP.APPLICATION_ID = A.APPLICATION_ID
AND FCP.APPLICATION_ID = FCPT.APPLICATION_ID
ORDER BY responsiblity, application, FCPT.USER_CONCURRENT_PROGRAM_NAME

g.     List all programs that are defined as incompatible for a given programSELECT FAL.APPLICATION_NAME INCOMPATIBLE_APPLICATION , FCPT.USER_CONCURRENT_PROGRAM_NAME INCOMPATIBLE_PROGRAM
FROM FND_CONCURRENT_PROGRAM_SERIAL FCPS,
FND_CONCURRENT_PROGRAMS FCP,
FND_APPLICATION_TL FAL,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPS.TO_RUN_APPLICATION_ID = FCP.APPLICATION_ID
AND FCPS.TO_RUN_CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCPS.RUNNING_CONCURRENT_PROGRAM_ID IN (SELECT FCPT.CONCURRENT_PROGRAM_ID  FROM FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPT.USER_CONCURRENT_PROGRAM_NAME = :USER_CONCURRENT_PROGRAM_NAME)
AND FAL.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FCPS.TO_RUN_CONCURRENT_PROGRAM_ID

h.      Given a program name in which manager it will runselect  ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
  from Fnd_Concurrent_Requests t,
       FND_CONCURRENT_PROCESSES k,
       Fnd_Concurrent_Queues_TL QTL,
       Fnd_Concurrent_Programs_TL PTL
  where k.concurrent_process_id = t.controlling_manager
    and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
    and ptl.concurrent_program_id=t.concurrent_program_id
    and qtl.language='US'
    and PTL.USER_CONCURRENT_PROGRAM_NAME = :CONCURRENT_PROGRAM_NAME
ORDER BY ptl.user_concurrent_program_name DESC



To Identify the manager for a given concurrent program The script given below identifies which manager is going to execute a given Concurrent program:

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl fcp, apps.fnd_concurrent_queue_content fcqc, apps.fnd_concurrent_queues_tl cq
WHERE
fcqc.type_application_id(+) = fcp.application_id AND
fcqc.type_id(+) = fcp.concurrent_program_id AND
fcqc.type_code(+) = 'P' AND
fcqc.include_flag(+) = 'I' AND
fcp.LANGUAGE = 'US' AND
fcp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND
NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND
NVL (cqc.queue_application_id, 0) = cq.application_id AND
cq.LANGUAGE = 'US'

To identify long running concurrent requests for more than 1 hour.


set line 200
set pagesize 500
col PROGRAM_NAME format a30
col concreq format a8
col Username format a10
col opid format a4
col dbuser format a6
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
           fcp.USER_CONCURRENT_PROGRAM_NAME "Program_Name",
                fu.user_name "Username",
               round((sysdate - actual_start_date) * 24 ,2) "Running_Hrs",
           SUBSTR(proc.os_process_id,1,15) clproc,
           SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
           SUBSTR(vsess.username,1,10) dbuser,
           SUBSTR(vproc.spid,1,10) svrproc,
           vsess.sid sid,
           vsess.serial# serial#
    FROM   fnd_concurrent_requests req,
           fnd_concurrent_processes proc,
           fnd_lookups look,
           fnd_lookups look1,
           V\$process vproc,
           V\$session vsess,
           fnd_concurrent_programs_vl fcp,
        fnd_user fu
    WHERE  req.controlling_manager = proc.concurrent_process_id(+)
    AND    req.status_code = look.lookup_code
    AND    look.lookup_type = 'CP_STATUS_CODE'
    AND    req.phase_code = look1.lookup_code
    AND    look1.lookup_type = 'CP_PHASE_CODE'
    AND    look1.meaning = 'Running'
    and    req.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
    AND    proc.oracle_process_id = vproc.pid(+)
    AND    vproc.addr = vsess.paddr(+)
    AND    fu.user_id = req.requested_by
    AND    round((sysdate - actual_start_date) * 24) > 1;
 

Useful Oracle E-Business Suite Scripts Part - 1

Below are the various Oracle R12 E-Business Suite scripts that can be handy during support,upgrade,etc.,

Query for Scheduled reqeuts start on prior run less than 5 mins
select cs.request_id,cs.concurrent_program_id,cs.program,cs.argument_text,cr.resubmit_interval_type_code,cs.requestor,resubmit_interval,resubmit_interval_unit_code from fnd_concurrent_requests cr,fnd_conc_req_summary_v cs where
 cs.phase_code='P'
and cs.status_code in ('I','Q')
and
resubmit_interval_type_code='START' and cr.request_id=cs.request_id
and resubmit_interval_unit_code='MINUTES' and resubmit_interval<5

Script to disable all scheduled requests - After cloning
update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and (status_code = 'I' OR status_code = 'Q');
and requested_start_date >= SYSDATE
and hold_flag = 'N';

Number of times Purge Concurrent Request execution was run in last 30 days
set pages 1000
set line 132
set head on
select  r.requested_start_date,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like 'Purge Concurrent Request and/or Manager Data'
and  r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Number of times gather schema stats was run in last 30 days
set pages 1000
set line 132
set head on
select  r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like '%Gather Schema Statistics%'
and  r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Concurrent Requests Average/Max/Min hours runtime details
set linesize 200
col username for a10
col status for a10
col phase  for a10
col PNAME for a70
col request_id for 99999999999
col PNAME  for a40
select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME  as PNAME,
avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) avg_Hrs_running,  
max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user  b
where
phase_code = 'C' and status_code = 'C' and
a.REQUESTED_START_DATE > sysdate-30 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;  


Oracle EBS user level scripts
Given a user name list all the responsibilities assigned to this user
SELECT UNIQUE u.user_id, u.user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE u.USER_NAME = :USER_NAME
AND g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)


 This is to list the  Apps User who are assigned a particular responsibility
select a.user_name,b.responsibility_name 
from  fnd_user a,fnd_responsibility_vl b ,FND_USER_RESP_GROUPS c
where a.user_id = c.user_id
and c.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
and lower(b.responsibility_name) like lower('&Responsibility_name%')
and a.END_DATE is null
and (c.END_DATE is null
or c.end_date > sysdate)
group by a.USER_NAME,b.RESPONSIBILITY_NAME

List Responsibilities That Can Run a Given Concurrent Program
This script helps in identifying the list of responsibilities that can run a given concurrent request

SELECT responsibility_name RN
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND (UPPER(fcpv.concurrent_program_name) = UPPER('&1')
OR
UPPER(fcpv.user_concurrent_program_name) = UPPER('&1'))
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
/


How to create to Oracle DB User which is like APPS but has only SELECT privileges
(this user should not have to use schema name.objectname and all SELECT qeries run from APPS user should work from this DB User as well)
a.       Create a user using
Create user ‘username’ identified by ‘password’;
b.      Then execute the following command in the sql prompt as the sys user,
spool create_grants.sql
set echo off
set pagesize 0
set linesize 300
set feedback off
SELECT 'Grant select on APPS.'||object_name||' to A;' FROM user_objects WHERE object_type IN ('TABLE','VIEW');
spool off;
exit;
c.       Executing the script  would create a sql file name create_grants.sql that has all the ‘SELECT priveleges’ of all the objects that the APPS user has access to..
d.      Finally run the create_grants.sql script.
 

Autoconfig failed with error AC-50480: Internal error occurred:

Autoconfig failed with error AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.

Issue:
Running autoconfig failed during execution of adgentns.pl file.
below is the error message seen in adconfig.log file.

##########################################################################
Logfile:  /u01/orcl/inst/apps/orcl_oradb/admin/log/08201702/NetServiceHandler.log
        Classpath                   : /u02/orcl/apps/apps_st/comn/java/lib/appsborg2.zip:/u02/orcl/apps/apps_st/comn/java/classes
Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated
adgentns.pl exiting with status 2....
....
[CVM Error Report]
The following report lists errors encountered during CVM Phase
      <filename>  <return code where appropriate>
  /u02/orcl/apps/apps_st/appl/ad/12.0.0/bin/adgentns.pl  2
No of scripts failed in CVM phase: 1

--> Verifying NetServiceHandler.log file it showed below errors while running script admk80ln.sh which is used to create tnsnames.ora and listener.ora file by retrieving node details from database.

Enter value for 1: Enter value for 2: ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Unable to generate listener.ora from database
Using default listener.ora file
Error while generating listener.ora.

StackTrace:
java.lang.Exception: Error while generating listener.ora.

Cause:

When queried FND_NODES table only applicaiton tier node information is retrieved. Database tier information is missing.
Upon investigation it is identified that node clean is performed on the ebs database and autoconfig on database tier is not done.

Solution:
1) Run autoconfig on database tier
2) Once autoconfig on database tier is successfull run again on application tier and this time it should be successful.

FRM-92101 - There was a failure in the Forms Server during startup. This could happen due to invalid configuration.

FRM-92101 - Unable to Launch EBS & Discoverer in Same Browsers

Issue :

Getting error frm-92101: There was a failure in the Forms Server during startup. This could happen due to invalid configuration.

Unable to launch EBS and Discoverer plus applications together from Internet Explorer. This issue is occurring only if both Discoverer and EBS services are running on same server.

Issue occurs when we launch them together as below

1. Launch EBS URL and launch any forms
2. Then now open a new tab and launch discoverer URL
3. Then EBS Forms fail with below error message.

And we can see below error messages in java console

oracle.forms.net.ConnectionException: 500

    at oracle.forms.net.ConnectionException.createConnectionException(Unknown Source)

    acache:  Read manifest for https://ebs.domain.com:4443/OA_JAVA/oracle/apps/fnd/jar/fndewt.jar: read=45 full=87491

java.io.EOFException

    at java.io.DataInputStream.readUnsignedByte(Unknown Source)

   

4. Then later when we close and retry to launch forms it still fails with frm-92101 message as below

5. Below is the error recorded in application.log file

Unable to switch to Working Directory: /u01/oravis /apps/tech_st/10.1.3/forms

14/08/28 18:37:22.839 formsweb: Forms session <5> failed during startup: no response from runtime process

Cause

This issue is occurring because of improper handling path variable when a discoverer is launched. When EBS application is launched  it sets JSESSIONID cookie with a restriction of path= /OA_HTML so that this is restricted to EBS Applications. But when the discoverer application is launched it is setting JSESSIONID without any restriction.

This makes that the DISCOVERER JSESSIONID cooke is also passed with request send to EBS server and since DISCOVERER lacks the "path=/discoverer" when setting the cookie the HTTP requests for Forms Servlet receive an 'unexpected' JSESSIONID cookie value and fail with the error.

EBS http:

Set-Cookie: JSESSIONID=3c3046608609362f0bfe2e0ffc36096ef65ada33c71207aac7f9a4cc881ea06a.e3qPc3mMa34Ne34TahyLa3iKaN50; path=/OA_HTML; secure

Discoverer http:

Set-Cookie: JSESSIONID=hHq6TnsdMJhJlQvTk4nRTjSh2x00BKphgT6ktw376Xx1V30L1DfX!1397667650; domain=.domain.com; path=/; secure; HttpOnly

Solution:

There are two workarounds to prevent this issue

Workaround 1:

This workaround fixes issue of launching both forms and discoverer together but this restricts users launching more than one forms for their activity

1. In the $CONTEXT_FILE change
   <forms_tracking_cookies oa_var="s_forms_tracking_cookies">disabled</forms_tracking_cookies>
   to
   <forms_tracking_cookies oa_var="s_forms_tracking_cookies">enabled</forms_tracking_cookies>
2. Run autoconfig to reflect changes and bounce application services.

(or)

1. If autoconfig execution cannot be performed rightway, update orion-web.xml file under $INST_TOP/ora/10.1.3/j2ee/forms/application-deployments/forms/formsweb

<session-tracking cookies="disabled" />

to

<session-tracking cookies="enabled" />

2. Bounce opmn services using adopmnctl.sh

Workaround 2:

This workaround is most suggestable than prior and this is performed on Discoverer side and this changes make the discoverer to restrict usage of its JSessionid cookie.

1. Need to add following entries to weblogic.xml file of discoverer.war file.

<session-descriptor>
<cookie-name>JSESSIONID</cookie-name>
<cookie-path>/discoverer</cookie-path>
</session-descriptor>

2. The below are the files that needs to be modified with above changes. The paths may not be exact but should be similar as below

$DOMAIN_HOME/servers/<DomainName>/tmp/_WL_user/oracle.discoverer/yn4f2j/WEB-INF/weblogic.xml
$DOMAIN_HOME /servers/<DomainName>/tmp/_WL_user/discoverer_11.1.1.2.0/51oeh7/war/WEB-INF/weblogic.xml

3. Once the changes are done reploy discoverer.ear using fusion middleware console
4. Login FMW Console, click on “Lock & Edit” and go to Deployments -> Select Discoverer (11.1.1.X.0) -> Click on Update -> Select “Redeploy this application using the following deployment files” -> Next -> Finish -> “Activate Changes”
5. Bounce opmnctl services

Now you should be able to launch both EBS and discoverer without any restrictions

 

SSL for EBS using Oracle Wallet

How to setup SSL for EBS??

Follow the below document on how to configure SSL for EBS using OWM (Wallet)

SSL for EBS R12 using OWM

As an alternative you can use orapki, keytool utilities to perform SSL setup..

SSL for Weblogic Server

Hi,

Below are the high level steps of configuring Weblogic admin server and managed servers with SSL.

1) Create a keystore
2) Generate Certificate Request file using keytool
3) Export keystore file and upload to CA authority
4) Upload the Digital certificates obtained by Trusted CA to Key store
5) Login Weblogic console and go to configuration page of admin server
6) Enable SSL port under General, Modify Keystore details with Cutsom keystores and and keystore password
7) Update the same keystore under SSL Tab.
8) Save the changes and activate.
9) Shutdown admin server and restart it

After restart you should be able to launch Console with https (SSL Enabled).

The detailed primer of this activity will be published soon.. Wait for the updates

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

Hi All,

Changing Apps password using FNDCPASS.

Trying to password using FNDCPASS and it errored as below
bash-4.1$ FNDCPASS apps/apps123 0 Y system/system123 SYSTEM APPLSYS welcome

bash-4.1$ cat L8041552.log
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
 module:
+---------------------------------------------------------------------------+
Current system time is 30-MAY-2014 15:19:18
+---------------------------------------------------------------------------+
Working...
APP-FND-02704: Unable to alter user APPS_MRC to change password.
APP-FND-02704: Unable to alter user APPS to change password.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 30-MAY-2014 15:19:19
+---------------------------------------------------------------------------+

Issue 1:
APP-FND-02704: Unable to alter user APPS_MRC to change password.
As APPS_MRC is no longer in use from 11.5.10.2 We can delete this entry
Solution
*********
You need to do the following steps:

1.) Stop all application services.
2.) Start only database and database listener.
3.) Take backup of FND_USER and FND_ORACLE_USERID table first.
4.) Connect to SQLPLUS as APPS user and delete the row from FND_ORACLE_USERID table for "APPS_MRC".
     SQL> delete from FND_ORACLE_USERID where ORACLE_USERNAME='APPS_MRC';
     SQL> commit;
5.) Change the APPS password using FNDCPASS.
6.) Check the FNDCPASS logfile for any error. If the password has changed successfully then run
autconfig in application tier. Otherwise do not run autconfig.
7.) Start the application and let me know the outcome.

Issue 2:APP-FND-02704: Unable to alter user APPS to change password.
It throwed error stating apps password couldn;t be changed but it was able to make change to applsys password.

1) This failed because the default profile assigned to APPS schema is EXEMPT_POLICY(custom profile which is preventing the passowrd to be chnaged) as below
SQL> select profile from dba_users where username='APPS';
PROFILE                      
------------------------------
EXEMPT_POLICY

2) Change the profile to DEFAULT
SQL> alter user apps profile default;

then now reset password again using FNDCPASS.. It works

Enable SSL for EBS R12

Hi All,

This post assists in configuring SSL for EBS R12 from command line using orapki rather than owm utility. This illustrates example for self signed certificates.

This involves below series of steps to setup SSL.

1. Create New Wallet
2. Create a Certificate Request
3. Upload Certificate to Wallet
4. Modify the OPMN wallet
5. Import certs to cacerts
6. Update the Context File
7. Settings for DB Tier

. 6Create New Wallet

Login EBS Application server and source using environment file (APPS<sid_machine>.env) located in the APPL_TOP directory.

1. Navigate to the $INST_TOP/ora/10.1.3 and source the env file to set your 10.1.3 ORACLE_HOME variables.
2. Navigate to the $INST_TOP/certs/Apache directory.
3. Take backup of existing wallet files to a backup directory.
4. Create New wallet using orapki utility as below

bash-4.1$ cd $INST_TOP/Apache

bash-4.1$ orapki wallet create -wallet . -auto_login -pwd apps123

bash-4.1$ ls -ltr

total 16

-rw------- 1 oracle dba 7912 Apr 15 13:33 ewallet.p12

-rw------- 1 oracle dba 7940 Apr 15 13:33 cwallet.sso

Create a Certificate Request
Create new certificate request file by passing appropriate details as input which is used for generating a SSL Certificate.

1. Below are the details required to be passed to generate csr file.

Common Name name of server with domain, e.g. mylinux.domain.com
Organizational Unit: The unit within your organization, e.g. HR
Organization: is the name of your organization, e.g. Home
Locality/City: is your locality or city, e.g. New York
State/Province: is the full name of your State or Province - do not abbreviate,

Country: Select country from drop down list, e.g. USA

Keysize: Encryption level and min is 1024, recommended value - 2048

3. Use command as below to generate certificate request file and export it.

$ orapki wallet add -wallet . -dn "CN=mylinux.domain.com,OU=HR,O=Home,L=New york,ST=Newyork,C=USA" -keysize 2048 -pwd apps123

 

$ orapki wallet add -wallet . -dn "CN=mylinux.domain.com,OU=HR,O=Home,L=New york,ST=Newyork,C=USA" -keysize 2048 -request server.txt -pwd apps123

4. Verify the certificate request file from wallet as below.

 

$ cd $INST_TOP/certs/Apache/

$ orapki wallet display -wallet .

Requested Certificates:

Subject:        CN=mylinux.domain.com,OU=HR,O=Home,L=New york,ST=Newyork,C=USA  ß File requested

User Certificates:

Trusted Certificates:

Subject:        CN=GTE CyberTrust Root,O=GTE Corporation,C=US

Subject:        CN=Entrust.net Certification Authority (2048),OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.),O=Entrust.net

Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS incorp. by ref. (limits liab.),O=Entrust.net,C=US

Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net

Then submit the generated request file(server.txt) to Certifying authority to request a self-signed certificate.

Upload Certificate to Wallet

1. As the certificate provided is self signed, once Certifying authority provides Self-signed Server Certificate we need additional certificates to import them to the wallet. We need to download root certificate, intermediate certificates to import server certigficate to wallet.

Note: Save certificate in base-64 format.

2. Once certificate is received save the file as server.crt in ($INST_TOP/certs/Apache). Also import all the root certificates of this certificate and save certificates with necessary conventions as below.

Root Certifciate - ca.crt

Intermediate Certificate - intermediate_base64.cer

Server.crt - server_base64.cer

 

Root & Intermediate certificates can be obtained from Internal Certifying authority if you are using self signed certificates.

 

3. Ftp the certificate files to the server and place them in $INST_TOP/certs/Apache directory.
4. Upload these certificates to Wallet using commands as below.

bash-4.1$ cd $INST_TOP/certs/Apache

 

$ orapki wallet add -wallet . -trusted_cert -cert SSL/ca.crt -pwd apps123

$ orapki wallet add -wallet . -trusted_cert -cert SSL/intermediate_base64.cer -pwd apps123

$ orapki wallet add -wallet . -user_cert -cert SSL/123080_base64_certificate.cer -pwd apps123

5. Display Wallet contents and you should see all the certificates uploaded to wallet.

bash-4.1$ orapki wallet display -wallet .

Requested Certificates:

User Certificates:

Subject:        CN=mylinux.domain.com,OU=HR,O=Home,L=New york,ST=Newyork,C=USA

Trusted Certificates:

Subject:        CN=GTE CyberTrust Root,O=GTE Corporation,C=US

Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        CN=Intermediate Authority,O=Home,C=US

Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject:        CN=Entrust.net Certification Authority (2048),OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.),O=Entrust.net

Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS incorp. by ref. (limits liab.),O=Entrust.net,C=US

Subject:        CN=Home Root Certificate Authority,O=Home

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net

6. We need to import root & intermediate certificates to b64InternetCertificate.txt file located in the 10.1.2 ORACLE_HOME/sysman/config directory.

$ cd $INST_TOP/certs/Apache/

$ cat SSL/ca.crt >>$ORACLE_HOME/sysman/config/b64InternetCertificate.txt ß root cert

$ cat SSL/infra_intermediate_base64.cer >>$ORACLE_HOME/sysman/config/b64InternetCertificate.txt  ß Inter cert

$ cat SSL/Infra_East_Base64.cer  >> $ORACLE_HOME/sysman/config/b64InternetCertificate.txt  ßInter cert

Modify the OPMN wallet

As default opmn wallet comes with demo wallet files, these should be now replace with newly created wallets as below.

1. Navigate to the $INST_TOP/certs/opmn directory.
2. Create a new directory named BAK
3. Move the ewallet.p12 and cwallet.sso files to the BAK directory just created.
4. Copy the ewallet.p12 and cwallet.sso files from the $INST_TOP/certs/Apache directory to the $INST_TOP/certs/opmn directory.

$ cd $INST_TOP/certs/opmn

$ mkdir BAK

$ mv * BAK/

$ cp ../Apache/ewallet.p12 .

$ cp ../Apache/cwallet.sso .

Import certs to cacerts
Now the new certificates files needs to be uploaded to cacerts under $OA_JRE_TOP/security/lib for proper functioning of XML Piublisher, OPP, etc.,

1. Navigate to the $OA_JRE_TOP/lib/security directory
2. Backup the existing cacerts file.

cd $OA_JRE_TOP/lib/security

cp cacerts cacerts.orig

4. Copy your ca.crt and server.crt files to this directory and issue the following command to insure that cacerts has write permissions:

$ chmod u+w cacerts

5.     Add your Apache ca.crt and server.crt to cacerts as below.

bash-4.1$ cd -

/oracle/orainst1/d112uk/inst/apps/d112uk_camcfnldap01/certs

bash-4.1$ cd Apache/SSL/

bash-4.1$ ls

Server_base64_certificate.cer  ca.crt  intermediate_base64.cer

bash-4.1$ cat intermediate_base64.cer >> ca.crt

bash-4.1$ cp -pr ca.crt $OA_JRE_TOP/lib/security

bash-4.1$ cp -pr Server_base64_certificate.cer $OA_JRE_TOP/lib/security

 

bash-4.1$ cd $OA_JRE_TOP/lib/security

 

$ keytool -import -alias ApacheRootCA_2048 -file ca.crt -trustcacerts -v -keystore cacerts

When prompted enter the keystore password (default password is changeit).

$ keytool -import -alias ApacheServer_2048 -file 123080_base64_certificate.cer -trustcacerts -v -keystore cacerts

Trust this certificate? [no]:  Yes

Certificate was added to keystore

 

Update the Context File

1. Backup existing context file under $INST_TOP/appl/admin/<db>_<server>.xml
2. Modify all below variables in the context file and save it.

SSL Related Variables in the Context File
Variable	Non-SSL Value	SSL Value
s_url_protocol	http	https
s_local_url_protocol	http	https
s_webentryurlprotocol	http	https
s_active_webport	same as s_webport	same as s_webssl_port
s_webssl_port	not applicable	4443 (port pool 0)
s_https_listen_parameter	not applicable	same as s_webssl_port
s_login_page	http://mylinux.domain.com:8000/OA_HTML/AppsLogin	https://mylinux.domain.com:4443/OA_HTML/AppsLogin
s_external_url	http://mylinux.domain.com:8009	https://mylinux.domain.com:4443

3. Run autoconfig using adautocfg.sh file.
4. Restart the middle tier services.

Settings for DB Tier

To enable SSL on the Database Tier, We need to import server certificate,ca.crt (and intca.crt if it exists) by creating an empty wallet.

1. Connect to database and find the wallet location as below.

SQL> select fnd_profile.value('FND_DB_WALLET_DIR') from dual

SQL> /

 

FND_PROFILE.VALUE('FND_DB_WALLET_DIR')

--------------------------------------------------------------------------------

/oracle/u011/mydb/db/tech_st/11.2.0/appsutil/wallet

3. After setting your environment for the database tier, navigate to the $ORACLE_HOME/appsutil directory.
4. Verify for directory waller, if It doesn’t exist create now.
5. Navigate to the newly created wallet directory.
6. Create a new empty wallet in this directory

orapki wallet create -wallet $ORACLE_HOME/appsutil/wallet -auto_login -pwd apps123

7. FTP the certificate files from application server to database server and import them into this newly created wallet as below.

bash-4.1$ pwd

/oracle/u011/mydb/db/tech_st/11.2.0/appsutil/wallet

 

$ orapki wallet add -wallet $ORACLE_HOME/appsutil/wallet -trusted_cert -cert root_base64.cer -pwd apps123

$ orapki wallet add -wallet $ORACLE_HOME/appsutil/wallet -trusted_cert -cert intermediate_base64.cer -pwd apps123

 

$ orapki wallet add -wallet $ORACLE_HOME/appsutil/wallet -trusted_cert -cert 123080_base64_certificate.cer -pwd apps123

8. Test the wallet created from above steps as below and it should return page content.

SQL> select UTL_HTTP.REQUEST

( url => fnd_profile.value('APPS_FRAMEWORK_AGENT')||'/OA_HTML/IRCRESUMEUK1.xsl',

  proxy => hr_util_web.proxyForURL(fnd_profile.value('APPS_FRAMEWORK_AGENT')),

  wallet_path=>'file:'||fnd_profile.value('FND_DB_WALLET_DIR'),  wallet_password=>fnd_preference.eget('#INTERNAL','WF_WEBSERVICES','EWALLETPWD','WFWS_PWD'))

from dual;

 

The above query should return HTML output.

 -- This completed SSL Setup