appsdba4all: February 2013

Thursday, February 28, 2013

Concurrent Manager Request Status Remains Terminating Indefinitely

symptom: Concurrent Manager request status remains terminating indefinitely

cause: Concurrent manager tables needed to be cleared.

fix: To Clear the Concurrent Manager Tables:

This can be done through SQL*PLUS as APPLSYS using the following commands:

SQL> update fnd_concurrent_queues

Set running_processes=0, max_processes=0;

IMPORTANT NOTE: It is very important to NEVER delete/truncate this table

because this holds all the concurrent manager data

SQL> delete from fnd_concurrent_processes;

SQL> truncate table fnd_concurrent_processes;

IMPORTANT NOTE: It is important to note that ALL processes will be deleted.

This table keeps track of the processes that the concurrent managers currently

are running. If the concurrent managers are down, there should be no concurrent

manager processes running. This is the only time it is acceptable to delete

from this table.

SQL> delete from fnd_concurrent_requests where status_code = 'T';

('T' is for terminating requests and when they are left in this status can

prevent the managers from coming up.)

SQL> Update fnd_concurrent_requests

set status_code = 'X', phase_code = 'C'

where status_code = 'T';

a). Set Terminating or Running to Completed/Terminated

SQL> UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'X'

WHERE status_code ='T'

OR phase_code = 'R'

b) Place Pending/(Normal/Standby) to On Hold

UPDATE fnd_concurrent_requests

SET hold_flag = 'Y'

WHERE phase_code = 'P'

AND status_code in ('Q','I')

This will change the terminating requests to terminated.

SQL>delete from fnd_concurrent_requests where status_code = 'C';

This will clean up the completed requests as well. This will delete all

completed requests.

The PURGE CONCURRENT REQUEST AND/OR MANAGER DATA report can be used to

reduce the amount of information held in this table. When the number

exceeds 5000, performance of the concurrent managers can be impacted.

SQL>select * from fnd_dual;

This table should only contain one row. If you have more than one row in

this table do the following:

SQL> delete from fnd_dual

where rownum < (select max(rownum) from fnd_dual);

++++++++++++++++++++++++++++++++++++++

ID 182575.1

[Understanding Cost Manager [ID 304313.1 & 373207.1]]

How to mass change Mail Preferences for FND USERS

How to change all existing users to 'HTML Mail' as their preference?

Direct table updates are not supported. Please ensure to perform a backup prior to making any changes reflected in this document.

Using sqlplus as the apps user:

Step 1:

SQL> update wf_local_roles

set notification_preference = 'MAILHTML'

where user_flag='Y'

Step 2:

SQL> update fnd_user_preferences

set preference_value = 'MAILHTML'

where module_name = 'WF'

and preference_name = 'MAILTYPE'

step 3:

SQL> commit

++++++++++451929.1 ,728483.1++++++++++++

Wednesday, February 27, 2013

How to troubleshoot untranslated menus after implementing NLS

Troubleshoot untranslated menus after implementing NLS

++++++++++++++++++++++++++++++++++++++++

1). Check the database character set use:

SQL> select value "Character Set" from V$NLS_PARAMETERS where parameter='NLS_CHARACTERSET';

Character Set

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

UTF8

2). you need to enable French & German from OAM

Enabling the Arabic from OAM, folllow the below steps

3). Validate the language activated:

SQL> Select NLS_LANGUAGE from FND_LANGUAGES where INSTALLED_FLAG in ('B','I');

NLS_LANGUAGE

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

GERMAN

FRENCH

AMERICAN

SQL> col PARAMETER for a30;

SQL> col VALUE for a30;

SQL> select * from V$NLS_PARAMETERS;

4). Stop the application servers

cd $ADMIN_SCRIPTS_HOME

./adstpall.sh apps/pwd

5). Run Maintain Multi-lingual Tables: adadmin

AD Administration Main Menu à Maintain Applications Database Entities menu (4) à Maintain multi-lingual tables (3).

Note: If any work failed please update with details to support

6). Run Update current view snapshot : adadmin

Maintain Applications Files menu -> Maintain snapshot information -> Update current view snapshot

7). Run the Translation Synchronization Patch utility (adgennls.pl)

perl $AD_TOP/bin/adgennls.pl

Above command will genertate the manifest file which you need to upload to support. Menifist file will be on following location:

$APPL_TOP/admin/$TWO_TASK/out/adgennls.txt

8). Upload the manifest file to oracle support.

contact Oracle Support for assistance , list of Translation Synchronization patches you requested

https://updates.oracle.com/TransSync

9). Apply the Translation Synchronization patch: latest translations from Oracle

Follow the instructions in the README file to apply the patch

10). grep "ias_version" $CONTEXT_FILE

<ias_version oa_var="s_ias_version">10.1.3.5.0</ias_version>

grep "JRE_TOP" $CONTEXT_FILE

<JRE_TOP oa_var="s_jretop">/d01/oracle/SUPT/apps/tech_st/10.1.3/appsutil/jdk/jre</JRE_TOP>

The German font files from cd $FND_TOP/resource need to been copied to

$JRE_TOP/jre/lib/fonts directory on all web and concurrent nodes.

Copy the fonts ALBAN*.ttf to $JRE_TOP/jre/lib/fonts. Steps:

• Copy the font files to the Java default font directory for the external JDK on the HTTP Server node.

$ cp /resource/ALBAN*.ttf /jre/lib/fonts

• Remove Image Files from Cache Directory

Remove all existing non-English language image files from the image cache directory.

$ cd $OA_HTML/cabo/images/cache

$ cd

$ rm *.gif *.imx

11). AD Administration utility to generate message files from the Generate Applications Files menu.

12). Compile the invalid objects using utlrp.

13). Compile Forms and Reports

14). Compile Apps schema and validate apps schema

15). Bring up the applications services,

16). do a complete sanity check and release the instance.

Note:
Please verify JRE_TOP

dbldxml.pl is not finding the java executable in the expected directory that is pointing the env variable:

JRE_TOP ==> $ORACLE_HOME/jre/1.1.8

The 'jre' is not shipped in 11g Database :

To implement the solution, please execute the following steps:

1. JRE_TOP should be set to $ORACLE_HOME/jre/1.4.2 :

export JRE_TOP=$ORACLE_HOME/jre/1.4.2

2. Start the creation of the Contextfile (XML-file) again :

perl adbldxml.pl tier=db appsuser=apps appspasswd=xxxx

The utility can be run by issuing the following command on the database tier:

perl $ORACLE_HOME/appsutil/bin/adbldxml.pl tier=db appsuser=apps appspass=apps

Note: In R12, this utility is supported only on the database tier.

For the appTier Under $AD_TOP/bin/

1). cd $AD_TOP/bin/perl adclonectx.pl --> May not work

2). (Or)

$ cd $APPL_TOP/ad/12.0.0/bin

$ perl adconfig.pl contextfile= <INST_TOP>/appl/admin/<STNDBY CONTEXT>.xml run=INSTE8

++++++++++++++++++++++++++++++++++++++++

1478859.1, 1333985.1, 333785.1, 252422.1, 399789.11070033.1 & 788053.1.

++++++++++++++++++++++++++++++++++++++++

What to install? NLS media or TSP (Translation Synchronization Patch)?

TSP patch is basically a customized patch to pull together all the delta files between US (the source of truth) and any activated languages.

See the Adding and Maintaining NLS Languages section in Chapter 1.

Follow the instructions for the business requirement to add a language.

The Post-installation Tasks section of this document.

Adding and Maintaining NLS Languages:

Perform the following steps:

1. From Oracle Applications Manager, go to License Manager and activate or change your base language to a new one.

2. From AD Administration, run Maintain Multi-lingual Tables (AD Administration Main Menu > Maintain Applications Database Entities Menu).

3. From AD Administration, run Maintain Snapshot Information

4. Run the Translation Synchronization Patch utility (adgennls.pl). .

If you have already run the Translation Synchronization Patch utility by following the Oracle E-Business Suite NLS Release Notes as mentioned in Step 3,

you can omit this step and step 5.

Requesting Translation Synchronization Patches note 252422.1

5. When you are notified that it is available, apply your Translation Synchronization Patch (TSP) for all languages you requested.

6. Install the Release 12 NLS Help, if available (optional).

+++++++++++++Manjunatha++++++++++++++++++++

How to compile the untranslated menus after implementing NLS

How to compile the untranslated menus after implementing NLS:

1. Login to Applications as System Administrator, select "Security -> Responsibility -> Define", and query the Responsibility that has the problem

2. Locate the "Menu" field, and note the MENU_NAM.

3. Exit Define Responsibility form.

Invoke Define Menu form and then enter query.

Put the menu details identified in step 2 in the "User Menu Name" field and execute query. This will give menu name which could be used in step 4.

4. Run the following SQL:

select menu_name, menu_id from fnd_menus

where menu_name IN ('<MENU_NAMES identified with step 3>);

5. Once the MENU_ID's are located, run the following SQL:

set pages 100 col prompt format a30

select PROMPT,SOURCE_LANG from FND_MENU_ENTRIES_TL

where MENU_ID IN (<menu id's identified in step 4);

The values should return a SOURCE_LANG row for each installed language and their translated prompt Verify the untranslated prompts

6. Now having the MENU_NAME, locate the loader file tha delivers these definitions :

On UNIX session (assuming it's UNIX), run the following command :

cd $PRODUCT_TOP/patch/115/import/NLS (related to the Responsibility or MENU)

Then substitue the MENU_NAME below with those identified earlier

find ./ -name "*.*" -exec grep -Hsi 'menu in specific language' {} \;

7. Once the file(s) is (are) identified, check the content, and verify the translated prompts, and compare this file with the one supplied with the patch.

8. For those identified loader files from step 6, run the FNDLOAD command to load the above mentioned ldts manually .

The syntax for the fndload command is as below :

FNDLOAD apps/<Password> 0 Y UPLOAD $FND_TOP/patch/115/import/<filename>.lct $PRODUCT_TOP/patch/115/import/<NLS Directory>/<filename>.ldt CUSTOM_MODE=FORCE UPLOAD_MODE=NLS WARNINGS=TRUE

9. To find the lct file for a specific ldt file, open the ldt file. It will contain the lct file name to use. Find the location of that lct in the system and use it in fndload command

10. Check whether the menus are appearing properly

+++++++++++++++++++++++++++

MOS ID 417340.1 , 393861.1 & 299080.1

+++++++++++++++++++++++++++

Number of Users Connected with Oracle Apps/Count Concurrent_users in Oracle Apps

how many users are connected to Oracle Apps/Count Concurrent_users?

Solution A:

1. One can use this SQL statement to count concurrent_users in Oracle apps:

select count(distinct d.user_name) from apps.fnd_logins a,

v$session b, v$process c, apps.fnd_user d

where b.paddr = c.addr

and a.pid=c.pid

and a.spid = b.process

and d.user_id = a.user_id

and (d.user_name = 'USER_NAME' OR 1=1);

select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility

from fnd_user fu, fnd_responsibility fr, icx_sessions ic

where fu.user_id = ic.user_id AND

fr.responsibility_id = ic.responsibility_id AND

ic.disabled_flag='N' AND

ic.responsibility_id is not null AND

ic.last_connect like sysdate;

2. In the Oracle Applications Manager, go to the site map, and select the "Applications Usage"

option in the Activity region. There one will be able to report on many usage questions, such as:

Products Installed

Application Users Per Module Summary

Applications Usage Reports

Suppliers

Purchase Line Items Processed - Internet Supplier Portal

Purchase Line Items Processed - Purchasing Intelligence

Order Entry Lines Processed - Ordering Application

Purchase Line Items Processed - iProcurement

Expense Reports Processed - Internet Expense

Invoice Line Items Processed - Accounts Receivables

3. Run the following queries:

This will give the number of users on the system in the past 1 hour.

SQL> select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';

This will give the number of users on the system in the past 1 day.

SQL> select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';

This will show the activity in the last 15 minutes.

SQL> select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time",

user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;

Solution B:

1. Enable Profile Option "Sign-On Audit" at "Form" level.

2. Run "Purge Signon Audit" request.

3. Security:Users -> Monitor" option

++++++++++++++++++++++++++++++++

MOS ID: 295206.1

++++++++++++++++++++++++++++++++

Temp Tablespace Usage/Free

TEMP Usage per session ?

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) sort_ops

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

Temp Space used/free?

SQL> select tablespace_name, bytes_used/1024/1024, bytes_free/1024/1024 from v$temp_space_header;

Users Using Temp tablespace?

SQL> select b.tablespace, (b.blocks*p.value)/1024/1024 , a.sid, a.username from sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p

where p.name=b.session_addr;

To get which user is using temp tablespace ?

SQL> select username,tablespace,extents from v$sort_usage;

Free and Used Space in Temp Tablespace ?

SQL> SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

Temp Usage By statement ?

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

T.sqladdr address, Q.hash_value, Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address (+)

AND T.tablespace = TBS.tablespace_name

ORDER BY S.sid;

User quotas on all tablespaces :

SQL> col quota format a10

select username

, tablespace_name

, decode(max_bytes, -1, 'unlimited'

, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"

from dba_ts_quotas

where tablespace_name not in ('TEMP');

Temp Usage:

SQL> set pagesize 10000;

set linesize 10000;

select

srt.tablespace,

srt.segfile#,

srt.segblk#,

srt.blocks,

a.sid,

a.serial#,

a.username,

a.osuser,

a.status

from

v$session a,

v$sort_usage srt

where

a.saddr = srt.session_addr

order by

srt.tablespace, srt.segfile#, srt.segblk#,

srt.blocks;

SQL> select sum(free_blocks) from gv$sort_segment where tablespace_name ='TEMP';

SQL> select file_id, tablespace_name, bytes from dba_temp_files;

SQL> select inst_id,tablespace_name,total_blocks,used_blocks,free_blocks from gv$sort_segment;

SQL> select username, tablespace, blocks from v$sort_usage;

SQL>select tablespace_name, total_blocks,used_blocks, free_blocks from v$sort_segment

SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header ;

SQL> select tablespace_name,extent_size,total_extents,total_blocks,max_size,max_blocks from v$sort_segment;

SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

++++++++++++++++++++

FRD TRACE in R12 :

Enable FRD Trace for Application User:

Enter profile option : ICX: Forms Launcher
Enter User Name : Manjunatha

Copy the url from site level and append ?record=collect to the url and paste the url at user level

http://waseem.learning.com:8000/forms/frmservlet?record=collect

save it.

Provide the url to the user tell user to login using the url mentioned. Reproduce the issue.
once the Issue is reproduced revert back the changes.

location of trace file : $FORMS_TRACE_DIR is the place where trace file will be generated.
trace file name will be with collect*
uploade the collect* to oracle.
$FORMS_TRACE_DIR = $INST_TOP/logs/ora/10.1.2/forms

After enabling FRD TRACE : User may get the below error messages after entering login credentials :

Application is not authorized to access the database
Take backup of the “context_file”
search for the parameter s_appserverid_authentication change the value of this parameter from SECURE to OFF as shown below:

change the value of "s_appserverid_authentication" to OFF from SECURE and run autoconfig
e.g
<appserverid_authentication oa_var="s_appserverid_authentication">SECURE</appserverid_authentication>
to
<appserverid_authentication oa_var="s_appserverid_authentication">OFF</appserverid_authentication>

Stop the Application Services.
Run Autoconfig.
Start the Application Services again.
Inform the user to login and test.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
To Enable/Disable Trace :

To start tracing:

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', TRUE);

/* execute your selects to be traced */

To stop tracing:

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', FALSE);

To start tracing:

exec sys.dbms_monitor.session_trace_enable(session_id=>18,serial_num=>226, waits=>true, binds=>true);

/* execute your selects to be traced */

To stop tracing:

exec sys.dbms_monitor.session_trace_disable(session_id=>18,serial_num=>226);

Disable Trace :

exec dbms_monitor.session_trace_disable;

begin

dbms_monitor.session_trace_disable(18,226);

end;

++++++++++++++++++++++++++++++++++++++++++++++

This post covers overview of How to troubleshoot long running concurrent request in Oracle
Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 1145)

Step 2 : Run below command to find SPID, provide concurrent request ID (1145 in my case)
when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
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;

Step 3.1 : Check and confirm SPID on Database Node
$ ps-ef | grep 2987
proddb 2987 1 0 13:30:43 ?
0:03 oracledbr12 (LOCAL=NO)

Step 3.2 : Set OSPID (2987 in my case) for ORADEBUG
SQL> oradebug setospid 2987

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12
LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name
/oracle/apps/proddb/10.2.0/admin/prod_CSDoracle/udump/ prod _ora_2987.trc
Wait for 15-20 minutes

Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like
$ tkprof prod _ora_2987.trc prod _ora_2987.txt explain=apps/pwd sort=(exeela,fchela)
sys=no

Step 6 : Check TKPROF file to find root cause of slow concurrent request

++++++++++++++++++++++++++++++++++

MOS ID 296559.1

++++++++++++++++++++++++++++++++++

Concurrent Manager Issue’s after Refresh/Cloning EBS R12 :

The Oracle Applications are as followings:

Solution :

1. Stop all middle tier services including the concurrent managers and verify that all APPS-Tier processes are down :

2. Stop the Database and verify, that all Database processes are down.

3. Start the database.

sqlplus apps/pwd

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

SQL>COMMIT;

SQL> EXIT;

Make sure following two sqls returns no rows

SQL> select node_name "Node Name", node_mode "Mode", support_cp "C",support_web "W", support_admin "A", support_forms "F" from FND_NODES;

SQL> select * from FND_OAM_CONTEXT_FILES;

4. Execute AutoConfig on all Tiers - first on the DB Tier and then on the APPS Tier(s) to repopulate the required system tables.

SQL> sqlplus APPS/<Password>

SQL> select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

Note: If the above SQL does not returning any value then please do the following :

a. cd $FND_TOP/patch/115/sql

b. sqlplus APPS/<Password>

c. @afdcm037.sql;

d. This script will create libraries for FNDSM and create Managers for Preexisting Nodes.

Note: Service Manager "FNDSM" cannot be created from form:

Concurrent> Manager> Define under Sysadmin Responsibility.

Check again if the FNDSM entries exist now :

a. sqlplus APPS/<Password>

b. select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

5. Relink the binaries FNDLIBR, FNDSM FNDFS and FNDCRM :

a. cd $FND_TOP/bin

b. adrelink.sh force=y "fnd FNDLIBR"

c. adrelink.sh force=y "fnd FNDSM"

d. adrelink.sh force=y "fnd FNDFS"

e. adrelink.sh force=y "fnd FNDCRM"

6. Execute the CMCLEAN.SQL script from the referenced note below (don't forget to commit).

Run cmclean.sql script .

7. Start the Middle Tier Services including your Concurrent Manager.

8. Retest the issue
+++++++++++++++++++++++++++++++++++++