Important NOTE IDs:
apscheck.sql from note Note ID: 246150.1
Apache Clearing Cache in R12 Note ID: 742107.1
New utility to change passwords from 12.1.2 -- AFPASSWD Note ID: 437260.1
How to change from default Servlet mode (in apps R12) to Socket mode ? Note # 384241.1
Utility /Script To Check The Techstack Component Versions (Forms, Http Server, JDK, Framework, Database, etc) (Doc ID 601736.1)
A Detailed Approach To Purging Oracle Workflow Runtime Data (Doc ID 144806.1)
Concurrent Processing - Settings for Profile Option "Concurrent Report Access Level" (Doc ID 736547.1)
Symptoms 567389.1
=========
make: Fatal error: Don’t know how to make target 'install'
SOLUTION
============
This bug will be fixed in the 10.2.0.5 patchset
The bug is also fixed in 10.2.0.4 by Patch 6826281
============
1. Rename the $ORACLE_HOME/odbc/lib/ins_odbc.mk file
2. Run the "relink all" command again
TROUBLESHOOT LOGIN PAGE / APACHE
================================
For related Apache issues One can go through the following logs
$ cd $LOG_HOME
$ find . -name 'application.log'
./ora/10.1.3/j2ee/oacore/oacore_default_group_2/application.log
./ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
./ora/10.1.3/j2ee/oafm/oafm_default_group_1/application.log
$FND_TOP/admin/template/custom/httpd_conf_1013.tmp
$INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf
When the issue occur check the log files apart from access and error_log
sec_case_sensitive_logon boolean FALSE
==============================================================
Related metalink notes to enable additional debugging
How to enable Apache, OC4J and OPMN logging in Oracle Applications R12 419839.1
R12 How To Enable and Collect Debug for HTTP, OC4J and OPMN 422419.1
============================================================================
Concurrent Manager Status
=======================
set pages 999
col user_concurrent_queue_name for a48
select user_concurrent_queue_name,max_processes,running_processes from fnd_concurrent_queues_vl where concurrent_queue_name in ('FNDCRM','FNDICM','FNDSCH','STANDARD','PODAMGR','RCVOLTM','PASMGR','INVMGR','INVTMRPM','WFALSNRSVC','WFMLRSVC','IEU_SH_CS','IEU_WL_CS','OAMCOLMGR','WFWSSVC','FNDCPOPP') or concurrent_queue_name like 'FNDSM%';
==========================================================
select * from DBA_NETWORK_ACLS;
select * from DBA_NETWORK_ACL_PRIVILEGES;
select * from USER_NETWORK_ACL_PRIVILEGES;
================================
376700.1 ===>SSL Creation Setup
==========================
Query To find any JAVA class Versions
====================================
SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE UPPER(filename) LIKE UPPER('PoGenerateDocument%')
AND ve.file_id = fi.file_id
AND version = (SELECT MAX (version) FROM apps.ad_file_versions ven
WHERE ven.file_id = fi.file_id)
SQL> /
FILE_ID FILENAME VERSION
---------- ------------------------------ ----------------------------------------
435163 PoGenerateDocument.class 115.18.11510.2
435164 PoGenerateDocumentCP.class 115.8.11510.2
435197 PoGenerateDocument.class 120.18.12010000.9
============================================================================
select decode(nvl(a.APPLICATION_short_name,'Not Found'),
'SQLAP','AP','SQLGL','GL','OFA','FA','Not Found','id '||to_char(fpi.application_id),
a.APPLICATION_short_name) apps, decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,
fpi.product_version,nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2;
=============================================================
### What are the steps to reproduce the problem ###
1. Click on Notification Summary to open worklist
2. Click 1 PO and view for approval, but font is not correct so cant read
3. Do again with Requisition, font is correct, can read it.
Solution:
System Profile Option - 'ICX: Client IANA Encoding' is matching the character set from the NLS_LANG setting
This resolved the issue…….
******************************************************************************************************************************
1. When Inventory Corrupted.
Rebulding inventory:
$ORACLE_HOME/oui/bin/attachHome.sh -silent -local -invPtrLoc $ORACLE_HOME/oraInst.loc
******************************************************************************************************************************
Invalid Objects Issue
When patches applied. New Invalids come
Startup database in Upgrade Mode
RUN $ORACLE_HOME/rdbms/admin/utlirp.sql
This invalidates all the objects
Shutdown DB and Startup in Normal Mode
RUN $ORACLE_HOME/rdbms/admin/utlrp.sql
Some times utl_recomp gone invalid. You need to compile first
=================================================================================
ENABLE TRACE FROM FRONT END R12
================================
In order to analyze the performance issue we will need a trace file for the validation
process for a SINGLE DAY. To get the R12 Trace:
1. Navigate Responsibility: System Administrator > Profile > System > Query
User: User submitting the Report Profile: Initialization SQL Statement - Custom
2. Click on User column - Edit Field and enter
begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS='||''''||'10046
TRACE NAME CONTEXT FOREVER,LEVEL 12'||''''); end; 3. Save.
4. Upload the raw and tkprof'd trace file created. Use the following SQL to locate the file:
select value from v$parameter where name = 'user_dump_dest';
<<Parameter:TIMED_STATISTICS>>
Enable/Disable the collection of timed statistics, such as CPU and elapsed times.
TRUE - Enable timing FALSE - Disable timing (Default value). Basic Syntax of TKProf
TKPROF filename_source filename_output EXPLAIN=[username/password] SYS=[YES/No] TABLE=[tablename]
filename_source The trace file that needs to be formatted
SOLARIS OS INFO
Nr.Of CPU psrinfo
Memory Conf /usr/sbin/prtconf
Swap /usr/sbin/swap -s
Bit Version /usr/bin/isainfo -kv
Extensible uname -X
AIX
No.Of CPU lscfg -vs|grep proc | wc -l
Bit version ls -l /unix
LINUX
No.Of CPU: /proc/meminfo, /proc/cpuinfo
To Find Oracle Binary Version
==========================
cd $ORACLE_HOME/bin
file oracl*
If The Application Broken BY doing
MO: Operating Unit fix it is remove from site level.
Download the profile option from Any Cloned Instance
FNDLOAD apps/**** O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct fmga_op_un.ldt PROFILE PROFILE_NAME="ORG_ID" APPLICATION_SHORT_NAME="FND"
Download the profile option from FMGT
FNDLOAD apps/**** O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct fmgt_op_un.ldt PROFILE PROFILE_NAME="ORG_ID" APPLICATION_SHORT_NAME="FND"
Run diff on it
diff fmgt_op_un.ldt fmga_op_un.ldt
7c7
< #Source Database FMGT
---
> #Source Database FMGA
126a127,132
> BEGIN FND_PROFILE_OPTION_VALUES "10001" "** Site **" ""
> OWNER = "HABBASI"
> LAST_UPDATE_DATE = "2009/02/23"
> PROFILE_OPTION_VALUE = "106"
> END FND_PROFILE_OPTION_VALUES
>
2579,2581c2585,2587
< OWNER = "ORACLE"
< LAST_UPDATE_DATE = "2009/05/08"
< PROFILE_OPTION_VALUE = "106"
---
> OWNER = "FSARWAR"
> LAST_UPDATE_DATE = "2008/11/21"
> PROFILE_OPTION_VALUE = "107"
usrns214:/home/applfmgt>
upolad the Cloned Instance ldt into Production
FNDLOAD apps/**** O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct fmga_op_un.ldt
Test the application make sure you log off and log back in to instance.
select request_id, CONCURRENT_QUEUE_NAME from fnd_concurrent_worker_requests where request_id in '12934727';
select
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';
How to Trace a Concurrent Request And Generate TKPROF File
==========================================================
Enable Tracing For The Concurrent Manager Program
• Responsibility: System Administrator
• Navigate: Concurrent > Program > Define
• Query Concurrent Program
• Select the Enable Trace Checkbox
Turn On Tracing
• Responsibility: System Administrator
• Navigate: Profiles > System
• Query Profile Option Concurrent: Allow Debugging
• Set profile to Yes
Run Concurrent Program With Tracing Turned On
• Logon to the Responsibility that runs the Concurrent Program
• In the Submit Request Screen click on Debug Options (B)
• Select the Checkbox for SQL Trace
Run the following SQL to find out the Raw trace name and location for the concurrent program. The SQL prompts the user for the request id
SELECT ’Request id: ’||request_id , ‘Trace id: ’||oracle_Process_id, ‘Trace Flag: ’||req.enable_trace, ‘Trace Name: ‘||dest.value||’/'||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’, ‘Prog. Name: ’||prog.user_concurrent_program_name, ‘File Name: ’||execname.execution_file_name|| execname.subroutine_name , ‘Status : ’||decode(phase_code,’R',’Running’) ||’-'||decode(status_code,’R',’Normal’), ‘SID Serial: ’||ses.sid||’,'|| ses.serial#, ‘Module : ’||ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request and req.oracle_process_id=proc.spid(+) and proc.addr = ses.paddr(+) and dest.name=’user_dump_dest’ and dbnm.name=’db_name’ and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;
$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no
The trace file by default is post fixed with oracle Process_id which helps us to identify which trace file belongs to which concurrent request. The below SQL Query returns the process_id of the concurrent request:
Select oracle_process_id from fnd_concurrent_requests where request_id='2768335'
(This query displays Process Id)
Ls –ltr *oracle_processid*.trc
********************************************
SID: MPDR1I
Customer is unable to get forms. Issues are intermittent.
Forms seession establishing form not launching at all
********************************************
We saw below message appearing in the logfile.
********************************************
Warning: Shared server clients will not be able to connect because SHARED_SERVERS is 0
Mon May 02 15:42:59 2011
ALTER SYSTEM SET dispatchers='(protocol=tcp)(dispatchers=3)' SCOPE=MEMORY;
Mon May 02 15:43:00 2011
dispatcher 'D000' encountered error setting up listening port
********************************************
Based on this we tried checking all listener parameters & observed that “remote_listener” was set to "MPDR1I_REMOTE".
As this parameter is specific to RAC environment for registering PMON with listener running on remote nodes, we commented
out this parameter on MPDR1I which is non-rac environment.
Yes the problem was mainly with all remote DB connections listening to incorrect listener parameter (as set above).
********************************************
Httpd_server down id=754838.1
$ grep -i SSLSessionCache ssl.conf
#SSLSessionCache none
#SSLSessionCache dbm:/polymi/inst/apps/POLYMI_auohsolym08/logs/ora/10.1.3/Apache/ssl_scache
#SSLSessionCache shmht:/polymi/inst/apps/POLYMI_auohsolym08/logs/ora/10.1.3/Apache/ssl_scache(512000)
SSLSessionCache shmcb:/var/opt/TOLYMI_auohsolym02/Apache/logs/ssl_scache(512000)
SSLSessionCacheTimeout 300
Issue is not with NFS, some times it comes due to Wrong NFS mount.
This is on Local File system.Ssl.conf has wrong Entry,
When You get the following ISSUE
=================================
SQL> set time on
09:56:46 SQL> exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10, 32, 'NOBACKUP', NULL, 'LASTRUN', 'GATHER AUTO', 10, 'N');
BEGIN FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10, 32, 'NOBACKUP', NULL, 'LASTRUN', 'GATHER AUTO', 10, 'N'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 14254
ORA-06512: at "SYS.DBMS_STATS", line 14277
ORA-06512: at line 1
ORA-06512: at "APPS.FND_STATS", line 711
ORA-06512: at "APPS.FND_STATS", line 1836
ORA-06512: at "APPS.FND_STATS", line 1183
ORA-06512: at "APPS.FND_STATS", line 1275
ORA-06512: at "APPS.FND_STATS", line 815
ORA-06512: at line 1
SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;
OWNER TABLE_NAME STATT
------------------------------ ------------------------------ -----
SYS SCHEDULER$_REMDB_JOBQTAB ALL
SYS AQ_PROP_TABLE ALL
SYS KUPC$DATAPUMP_QUETAB ALL
SYS SYS$SERVICE_METRICS_TAB ALL
APPLSYS FND_SOA_JMS_IN ALL
APPLSYS FND_SOA_JMS_OUT ALL
APPLSYS FND_CP_GSM_IPC_AQTBL ALL
SYS WRH$_EVENT_HISTOGRAM ALL
SYS WRH$_FILESTATXS ALL
SYS WRH$_SQLSTAT ALL
SYS WRH$_SYSTEM_EVENT ALL
SYS WRH$_WAITSTAT ALL
SYS WRH$_LATCH ALL
SYS WRH$_LATCH_MISSES_SUMMARY ALL
SYS WRH$_DB_CACHE_ADVICE ALL
26 rows selected.
SQL> SQL> exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_IN');
PL/SQL procedure successfully completed.
==================================================
Core issue is that the time it takes to create the lob index is huge.
The reason for this is that this is a context index which actually indexes the data in free form text to enable search capability.
The problem is that the FND_LOBS table is used for more than just help files even though the FND_LOB_CTX index is only relevant for helpfiles.
In looking at the lob it turns out this table actually holds 62GB of data. To see this we can run the following sql :
SQL> l
1* select count(*) , SUM(DBMS_LOB.GETLENGTH(FILE_DATA))/1024/1024
lob_size from fnd_lobs
SQL> /
COUNT(*) LOB_SIZE
---------- -----------
262555 61487.04
When we build the context index it tries to scan through all 62GB in
order to build the search functionality. However if we look at where all
this data is coming from we see this :
1 select program_name,count(*),SUM(DBMS_LOB.GETLENGTH(FILE_DATA))/1024/1024 lob_size from fnd_lobs
group by program_name order by 3 asc;
PROGRAM_NAME COUNT(*) LOB_SIZE
-------------------------------- ---------- -----------
...
FND_HELP 90489 573.77
16181 3691.65
FNDATTCH 155748 57201.69
57GB of the 62GB are attachments and not help files - another 4GB have
no program name attached. All we really want to index are the FND_HELP
files. According to Note 397757.1 this is fixed by doing the following :
update fnd_lobs
set FILE_FORMAT = 'IGNORE'
where NVL(PROGRAM_NAME,'NULL') != 'FND_HELP' ;
WHich will tell the system to only build the index on FND_HELP files.
FND_LOB_CTX is now fixed.
======================================================================
Check Conflicts for any RDBMS Patch#
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./6196748
Level 12 trace for a concurrent prog Doc ID 295963.1
select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from applsys.FND_CONCURRENT_REQUESTS where REQUEST_ID=13787589;
update APPLSYS.fnd_Concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where REQUEST_ID=13787589;
commit;
1.Login as system owner and run:
alter profile AD_PATCH_MONITOR_PROFILE limit failed_login_attempts unlimited;
alter profile default limit failed_login_attempts unlimited password_lock_time 1/1440;
alter user apps account unlock;
==================================================================================
select text from dba_source where name='<PACKAGE NAME>' and type='PACKAGE';
select text from dba_source where name='<PACKAGE body NAME>' and type='PACKAGE BODY';
***************************************************************************************************
package version
===============
select text from all_source where name = '&pkg_name' and line < 7;
select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;
SET MARKUP HTML ON
SPOOL RESP_MF_BUY_file.xls;
====================================
Update override address: afsvcpup.sql
select COMPONENT_ID,COMPONENT_NAME from FND_SVC_COMPONENTS where COMPONENT_NAME like '%Workflow Notification%';
select PARAMETER_ID,DISPLAY_NAME from FND_SVC_COMP_PARAMS_TL where DISPLAY_NAME like '%Test%Address%';
update FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE='glsrk.lir@worth.com' where COMPONENT_ID=10006 and PARAMETER_ID =10057;
select PARAMETER_VALUE from FND_SVC_COMP_PARAM_VALS where COMPONENT_ID=10006 and PARAMETER_ID =10057;
-- XLA status --
SQL> select fpi.application_id, fpi.status, fpi.patch_level from fnd_product_installations fpi where fpi.application_id = 602;
APPLICATION_ID S PATCH_LEVEL
-------------- - ------------------------------
602 S R12.XLA.A.6
*************************************************************************************
Select a.APPLICATION_ID, i.STATUS
from fnd_application ,fnd_product_installations i
where a.APPLICATION_ID=i.APPLICATION_ID
and a.APPLICATION_SHORT_NAME ='&applshortname'
**************************************************************************************
1. FND Message Log
1) Enable the FND Debug Log
a) Navigate to System Administrator responsibility
b) Set the following profiles at the USER level:
FND: Debug Log Enabled = YES
FND: Debug Log Filename = NULL
FND: Debug Log Level = STATEMENT
FND: Debug Log Module = %
==============================================================
Compiling a Form in R12
frmcmp_batch module=CYPPORCT.fmb userid=apps/apps output_file=CYPPORCT.fmx compile_all=special
Creating a DB Link / Database Link
CREATE PUBLIC DATABASE LINK R11toR12 CONNECT TO apps IDENTIFIED BY xxxxx USING '<Target SIE>';
SQL> select HOST_NAME,INSTANCE_NAME from v$instance@R11TOR12;
SQL> select sysdate from dual@r11tor12;
adcfgclone.pl dbTechStack
=========================
When we run adcfgclone.pl dbTechStack, it doesn't recreate the control
file and doesn't try to start the database. The only things it does
is recreate the database inventory and relink the database oracle
home.
When we want to manually create the control file and bring up the
database on our own, instead of relying on adcfgclone to do this for
us, we use adcfgclone.pl dbTechStack
Issue With AutoConfig Desc
===========================
We are seeing a peculiar issue where the values we modify using vi are reverted during autoconfig.
For e.g.
We modified "s_applcsf" value to null in xml file and ran autoconfig.
Now we see that the xml file has reverted back to old value, we confirmed this from the backup xml file system took during autoconfig that while autoconfig
started it had the correct value (null) when it started.
But after autoconfig we see that the value with the directory came back !
We know the fix is to update the values from OAM, but wanted to get to the core of the issue.
This is on 12.1.3.4
=============
This has been known problem from long time & the below could be a possibility in your case too.
Cause
*****
Each Contextfile version has a serial number. When autoconfig is run, this serial number is written to the table:
FND_OAM_CONTEXT_FILES against the version of the Context file:
However it is possible that the Apps Tier Contextfile serial number can become unsynchronised with the serial number information in the table.
If the serial number in the filesystem reads a lower value compared with that showing in the table for the same contextfile version, then each time Autoconfig is run, AutoConfig will see that the table is showing the higher value and it will replace the Contextfile in the filesystem with the parameter values from the contextfile associated with the serial number in the table.
This results in any changes made to the filesystem Contextfile being ignored.
Solution
*******
1. Manually change the serial number parameter "s_contextserial" in the Apps Tier Contextfile on the Filesystem to a value which is one more than the highest version of the serial number in the FND_OAM_CONTEXT_FILES table, for the relative Contextfile version.
2. Run AutoConfig.
===========================================================================
REVIEW :
========
As per Data Collected, we see that the same error is being reproduced by
$ORACLE_HOME/OPatch/opatch util LoadXML -xmlInput /talsec/oracle/product/oraInventory/ContentsXML/comps.xml
You entered: "/talsec/oracle/product/oraInventory/ContentsXML/comps.xml".
Failed to load XML file due to SAXException, msg=Whitespace required.
UtilitySession failed to check the XML file.
UtilSession::process() failed. Detail: Failed to load XML file due to SAXException, msg=Whitespace required.
===> The above confirms that the file "/relsec/oracle/product/oraInventory/ContentsXML/comps.xml" under Oracle Central Inventory (oraInventory) is CORRUPTED.
ACTION PLAN :
=============
1. Please restore the Central Inventory File "/relsec/oracle/product/oraInventory/ContentsXML/comps.xml" from previously taken good backup or identical setup.
OR
2. If Backup is not available for the file "/relsec/oracle/product/oraInventory/ContentsXML/comps.xml" please try recreating the Central Inventory itself with below steps :
=> Remove the existing Central inventory (oraInventory)
Use the following command to rename the oraInventory in Linux
mv /relsec/oracle/product/oraInventory /relsec/oracle/product/oraInventory-ORIG
=> Change the directory to the oui location.
cd /relsec/oracle/product/102/oui/bin
=> Attaching the Home for Oracle Database 10.2.0.2.0
Execute runInstaller with -attachHome option for Oracle Home.
./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/relsec/oracle/product/102" ORACLE_HOME_NAME="APPS9I_TALSEC"
3. The above should recreate the oraInventory with appropriate Oracle Home entries.
4. Please use the latest opatch utility version 10.2.0.5.1 and try applying the Patch 5632264 and let us know the results.
=======================================
To Get the Entire EBSO Environment Info:
=======================================
Generate concurrent processing environment information (Submit Concurrent Programe)
=======================================
However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING
after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate,
and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requests set status_code='X', phase_code='C' where status_code='T';
Changing Dispatching Priority within the Concurrent Manager
If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.
Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.
Using data Dictionary Scripts with the Concurrent Manager
Few Oracle Applications DBAs understand that sophisticated data dictionary queries can be run to reveal details about the workings within each Concurrent Manager. Oracle provides several internal tables that can be queried from SQL*Plus to see the status of the concurrent requests, and the most important are FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS.
Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:
afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql Displays the requests that are pending, held, and scheduled.
afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if
you suspect the ICM is in a gridlock with another oracle process.
In addition to these canned scripts you can skill write custom Concurrent Manager scripts. For example, the following query can be executed to identify requests based on the number of minutes the request ran:
conc_stat.sql
set echo off
set feedback off
set linesize 97
set verify off
col request_id format 9999999999 heading "Request ID"
col exec_time format 999999999 heading "Exec Time|(Minutes)"
col start_date format a10 heading "Start Date"
col conc_prog format a20 heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"
spool long_running_cr.lst
SELECT
fcr.request_id request_id,
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
fcr.actual_start_date start_date,
fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
WHERE
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
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
fcpt.language = USERENV('Lang')
ORDER BY
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
spool off
Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.
Enter value for min: 60
Exec Time
Request ID (Minutes) Start Date Conc Program Name User Program Name
----------- ---------- ---------- -------------------- --------------------------------------
1445627 218 01-SEP-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
444965 211 03-JUL-01 CSTRBICR5G Cost Rollup - No Report GUI
1418262 208 22-AUG-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
439443 205 28-JUN-01 CSTRBICR5G Cost Rollup - No Report GUI
516074 178 10-AUG-01 CSTRBICR6G Cost Rollup - Print Report GUI
1417551 164 22-AUG-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
1449973 160 03-SEP-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
520648 159 13-AUG-01 CSTRBICR5G Cost Rollup - No Report GUI
446007 122 03-JUL-01 CSTRBICR5G Cost Rollup - No Report GUI
392996 120 01-JUN-01 BMCOIN Bill and Routing Interface
====================================================================================
Check for unused columns that might cause problem to export direct=y (refer to notes 279327.1)
SQL>select * from dba_unused_col_tabs where table_name='FND_FLEX_VALIDATION_TABLES';
If it returns any row like the following:
OWNER TABLE_NAME COUNT
------------------------------
A FND_FLEX_VALIDATION_TABLES 1
Run this sql:
SELECT OBJ#,COL#,SEGCOL#,name,type# FROM COL$ WHERE OBJ# in(select object_id from dba_objects where object_name='FND_FLEX_VALIDATION_TABLES');
OBJ# COL# SEG COL# NAME TYPE#
2732 15 16 E_NAME 1
.....
2732 0 6 SYS_C00006_03071317:21:10$ 2
.....
2732 20 21 ID_CON_TYPE 1
SYS_C00006_03071317:21:10$ is the column that is unused.
Please drop the unused columns by running the following command:
ALTER TABLE FND_FLEX_VALIDATION_TABLES DROP UNUSED COLUMNS ;
If you are on 9.2.0.3, please repeat the same steps above for table FND_TABLES, and drop unused columns for FND_TABLES if need to.
Check SSO installation status
=============================
SELECT 'SSO: '||decode(instr(v.profile_option_value,'SSO'),0,'NOT ','')||'enabled ('||v.profile_option_value||')'
FROM apps.fnd_profile_options_vl p,apps.fnd_profile_option_values v
WHERE v.level_id=10001 AND (p.profile_option_name='APPS_SSO')
AND (p.profile_option_id=v.profile_option_id);
'SSO:'||DECODE(INSTR(V.PROFILE_OPTION_VALUE,'SSO'),0,'NOT','')||'ENABLED('||V.PROFILE_OPTION_VALUE||')'
------------------------------------------------------------------------------------------------------------------------------------
SSO: NOT enabled (SSWA)
CHECK ATG Version
==========
sqlplus apps/#APPS_PWD#
SET head off Lines 120 pages 100
col p_num format A65
col bug_number format A10
col patch_name format A10
select bug_number, decode(bug_number,
'3438354', '11i.ATG_PF.H'
,'4017300' ,'11i.ATG_PF.H.RUP1'
,'4125550' ,'11i.ATG_PF.H.RUP2'
,'4334965' ,'11i.ATG_PF.H RUP3'
,'4676589' ,'11i.ATG_PF.H RUP4'
,'5382500' ,'11i.ATG_PF.H RUP5 HELP'
,'5473858' ,'11i.ATG_PF.H.5'
,'5674941' ,'11i.ATG_PF.H RUP5 SSO Integrat'
,'5903765' ,'11i.ATG_PF.H RUP6'
,'6117031' ,'11i.ATG_PF.H RUP6 SSO 10g Integration'
,'6330890' ,'11i.ATG_PF.H RUP6 HELP'
,'6241631','11i.ATG_PF.H RUP 7'
,'6936696','11i.ATG_PF.H RUP7 SSO 10g Integration'
,'8248307','11i.ATG_PF.H RUP7 HELP'
) p_num, last_update_date
FROM ad_bugs
WHERE bug_number
IN ( '3438354', '4017300', '4125550', '4334965', '4676589', '5382500', '5473858', '5674941', '5903765', '6117031', '6330890','6241631', '6936696', '8248307' );
The following query can be used to assist in detecting types which have multiple versions: Doc ID 748109.1
select object_id, owner, object_name, object_type, status
from dba_objects
where object_name in
(select distinct object_name
from dba_objects
where object_type='TYPE' and status='INVALID')
order by owner, object_name;
Check Jinitiator version:
=======================
egrep 's_jinit|s_sun' $CONTEXT_FILE
? Check if s_jinit_ver_dot and s_sun_plugin_ver is already on latest version. If so no need to continue further.
egrep 's_jinit_ver_comma|s_jinit_ver_dot|s_jinit_clsid|s_sun_plugin_type|s_sun_plugin_ver|s_sun_clsid' $CONTEXT_FILE
$ egrep 's_jinit|s_sun' $CONTEXT_FILE
<sun_plugin_ver oa_var="s_sun_plugin_ver">1.4.2_04</sun_plugin_ver>
<sun_plugin_type oa_var="s_sun_plugin_type">jinit</sun_plugin_type>
<sun_clsid oa_var="s_sun_clsid">CAFEEFAC-0014-0002-0004-ABCDEFFEDCBA</sun_clsid>
<jinit_ver_dot oa_var="s_jinit_ver_dot">1.3.1.28</jinit_ver_dot>
<jinit_ver_comma oa_var="s_jinit_ver_comma">1,3,1,28</jinit_ver_comma>
<jinit_clsid oa_var="s_jinit_clsid">CAFECAFE-0013-0001-0028-ABCDEFABCDEF</jinit_clsid>
SQL> select USER_CONCURRENT_QUEUE_NAME, concurrent_queue_name, node_name, node_name2 from fnd_concurrent_queues_vl where USER_CONCURRENT_QUEUE_NAME like '%PCP2%' order by 1;
USER_CONCURRENT_QUEUE_NAME CONCURRENT_QUEUE_NAME NODE_NAME NODE_NAME2
--------------------------------------- ------------------------------ ------------------------------ ------------------------------
FastFormula Transaction Manager PCP2 FFTM2 VMOHSNOIL011
INV Remote Procedure Manager PCP2 INVTMRPM2 VMOHSNOIL011
NOV Custom Manager PCP2 XXNOV_CUSTMGR2 VMOHSNOIL011 VMOHSNOIL008
NOV Payroll Child Manager PCP2 XXNOV_PAYCHLD2 VMOHSNOIL011 VMOHSNOIL008
NOV Payroll Manager PCP2 XXNOV_PAYMGR2 VMOHSNOIL011 VMOHSNOIL008
Output Post Processor PCP2 FNDCPOPP2 VMOHSNOIL011 VMOHSNOIL008
PO Document Approval Manager PCP2 PODAMGR2 VMOHSNOIL011
Receiving Transaction Manager PCP2 RCVOLTM2 VMOHSNOIL011
Standard Manager PCP2 STANDARD2 VMOHSNOIL011 VMOHSNOIL008
9 rows selected.
To Check whether forms mode:
more $FORMS60_WEB_CONFIG_FILE|grep connectMode
connectMode=socket
more $FORMS60_WEB_CONFIG_FILE|grep serverName=
serverName=devbimboespana
; serverName=%LeastLoadedHost%
$ more $FORMS60_WEB_CONFIG_FILE|grep domainName=
domainName=.oracleoutsourcing.com
$ more $FORMS60_WEB_CONFIG_FILE|grep serverURL=
serverURL=
