General Issues

1)Assigned responsibilities to that user are reflecting.

Solution :
1) Bounce the apache
and check it and other solution is
2)You need to run the following concurrent programs if you have any missing responsibilities

- "Sync responsibility role data into the WF table."
- "Synchronize WF LOCAL tables"
- "Workflow Directory Services User/Role Validation"

3) bounce the Apache after running the above programs
*********************************************************************************
Error while lauching the forms
APP-FND-01542 THe application server is not authorized to acess the database
And the modplsql log says
[Wed Sep 2 17:21:59 2009] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:22:33 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:22:41 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:39:03 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017

Solution:
Change the parameter "appserverid_authentication oa_var="s_appserverid_authentication">SECURE/appserverid_authentication"

To
"appserverid_authentication oa_var="s_appserverid_authentication">OFF/appserverid_authentication"
In the xml file

and run the autoconfig.

************************************************************************************
3) Forms not coming up
Solution
check the enteries of hostname and virtual name
"oacore_trusted_oproc_nodes oa_var="s_oacore_trusted_oproc_nodes">vis02,hostname.abcd.comhostname ************************************************************************************

How to Enable the Concurrent Program Trace

How to find the Trace file generated for a concurrent program? (Doc ID 967966.1)
Enabling Concurrent Program Traces (Doc ID 123222.1)

Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.

After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.

SQL to identify the trace file

select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];

or

select value from v$parameter where name = 'user_dump_dest';

select value from v$diag_info where name = 'Diag Trace'


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;


DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*

P.S: Dont forget to disable the trace

Query to find the child requests from parent requests

SELECT req.* FROM (SELECT con.user_concurrent_program_name ,req.request_id ,parent_request_id ,req.phase_code ,req.status_code FROM apps.fnd_concurrent_requests req ,apps.fnd_concurrent_programs_vl con WHERE 1 = 1 AND con.concurrent_program_id = req.concurrent_program_id ) req_set, (SELECT con.user_concurrent_program_name ,req.request_id ,req.phase_code ,req.request_date ,req.requested_start_date ,req.actual_completion_date ,req.status_code, parent_request_id FROM apps.fnd_concurrent_requests req ,apps.fnd_concurrent_programs_vl con WHERE 1 = 1 AND con.concurrent_program_id = req.concurrent_program_id ) req WHERE req_set.parent_request_id = 5337352 -- Request id of the submitted request set. AND req.parent_request_id = req_set.request_id order by requested_start_date desc;

CPU Utilization check

1) % ulimit -Sa ,SVMON,

2)CPU utilization
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: sar -u
IBM: ps av (or) iostat 3 20
Digital: /usr/sbin/pset_info
Linux: xosview

In addition of monitoring the CPU usage you should monitor the runqueue to determine if
processes are waiting for an available processor.
You can use "sar -q" to monitor the runqueue.

3)CPU usage by processor
To see how much CPU time is being used by each processor on a multiprocessor machine:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /usr/bin/mpstat
HP: /usr/sbin/sar -M 5 5
IBM: vmstat -> Under cpu, if "us" is a very high number you have a cpu intensive process

4)Number of CPU's
To see the number of CPUs there are in the machine, and their status:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /usr/sbin/mpstat
HP: /usr/sbin/sar -M 2 2
IBM: /usr/sbin/bindprocessor -q
Digital: /usr/sbin/psrinfo -v
Linux: xosview
5)Swap space
To see the amount of swap space is on the machine and the usage:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /etc/swap -l and /etc/swap -s
HP: /etc/swapinfo -m ( must be root Unix id)
IBM: lsps -a
Digital: /usr/sbin/swapon -s
Linux: free -t
6)Shared memory
To see the current usage of shared memory & semaphores:
"ipcs -b"

ADPATCH Part-2

1. Using the adctrl utility, shutdown the workers. a. adctrl
b. Select option 3 "Tell worker to shutdown/quit"
2. Backup the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema
sqlplus applsys/apps
create table fnd_Install_processes_back as select * from
fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
3. Backup the .rf9 files located in $APPL_TOP/admin//restart directory. At this point, the
adpatch session should have ended and the cursor should be back at the UNIX prompt.
cd $APPL_TOP/admin/DEVL
mv restart restart_back
mkdir restart
4. Drop the FND_INSTALL_PROCESSES table and the AD_DEFFERED_JOBS table.
sqlplus applsys/apps
drop table FND_INSTALL_PROCESSES;
drop table AD_DEFERRED_JOBS;
5. Apply the new patch.
6. Restore the .rf9 files located in $APPL_TOP/admin//restart_back
cd $APPL_TOP/admin/DEVL
mv restart restart_3263588
mv restart_back restart
7. Restore the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema.
sqlplus applsys/apps
create table fnd_Install_processes as select * from
fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
8. Re-create synonyms
sqlplus apps/apps
create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
9. Start adpatch, it will resume where it stopped previously.



How do you determine what patch set you are currently utilizing?
select pa_server_pkg.get_patch_level from dual;

• Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application*/ 
  
  select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = '' 
  
  To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id 
  
  select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3; 
  
  To find the base application version  
  
  select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y' 
  
  
   To find all available application version 
  
  select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES 
  
  
  To get file version of any application file which is changed through patch application
  
  select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME 
  
  To get information related to how many time driver file is applied for bugs
• select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = '' 
  
  To find latest patchset level for module installed
  
  select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME 
  
  To find what is being done by the patch
  
  select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE 
  
  
  To find Merged patch Information from database in Oracle Applications
  
  select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) ); 
  
  Second Query to know, what all has been done during application of PATCH
  
  Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
  AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and 
  B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID 
  and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and 
  I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and 
  G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
  in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
  GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR, 
  D.FILENAME, E.ACTION_CODE 
  
  
  Script to find out Patch level of mini Pack
  
  Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%'; 
  
  Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex. 
  AD,PA,…
• Script to find the patch timeings
  
  SELECT to_char(drivers.LAST_UPDATE_DATE, 'yyyy-mm-dd') install_date,
           bug.BUG_NUMBER,
           patches.patch_type,
           drivers.PLATFORM,
           langs.language,
           drivers.DRIVER_FILE_NAME,
           to_char(runs.start_date, 'HH24":"MI":"SS') start_time,
           to_char(runs.end_date, 'HH24":"MI":"SS') end_time,
           to_char(to_date('00:00:00','HH24:MI:SS') + (runs.end_date - runs.start_date), 'HH24:MI:SS') time_difference,
           (to_number(to_char(to_date('00','HH24')  + (runs.end_date - runs.start_date), 'HH24'))*60) +
           (to_number(to_char(to_date('00','MI')    + (runs.end_date - runs.start_date), 'MI'))) +
           (to_number(to_char(to_date('00','SS')    + (runs.end_date - runs.start_date), 'SS')) / 60) time_dif_min
    FROM apps.ad_bugs bug,
         apps.ad_patch_drivers drivers,
         apps.ad_patch_driver_langs langs,
         apps.ad_applied_patches patches,
         apps.ad_patch_runs runs
    WHERE langs.patch_driver_id = drivers.patch_driver_id AND
          runs.patch_driver_id=drivers.patch_driver_id AND
          patches.APPLIED_PATCH_ID=drivers.APPLIED_PATCH_ID AND
          patches.patch_name=bug.bug_number AND
          drivers.orig_patch_name = 'bug_' || bug.bug_number
          and bug_number like '%patch_number%'
    ORDER BY install_date ASC, start_time ASC;
  
  

to merge the patch

admrgpch -s /patches/madhu/M2/source -d /patches/madhu/M2/dest -merge_name M2P

Script to find long running requests

#!/bin/ksh
echo "Program Start Date: " `date`

if [ `whoami` != 'oracle' ]
then
echo "Error: You must be oracle to execute the script. Exiting."
exit
fi


########################################################################
# Get the list of programs running for more than 4 hours
########################################################################

export ORACLE_HOME=/opt/oracle/products/9.2.0.x
export ORACLE_SID=ERPPROD
export PATH=$ORACLE_HOME/bin:$PATH

ORA_TEST=`sqlplus "/as sysdba" <set linesize 200
set pagesize 60
set feed off
set head off
SELECT 'Concurrent Program: '||t.user_concurrent_program_name||' ,With Req ID: '||r.REQUEST_ID||' ,Started at: '||to_char(v.actual_start_date,'dd/mm/yy hh24:mi:ss')||' ,With Parameters: '||v.argument_text||' ,Running for: '||round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24))||'Hours'
FROM apps.fnd_concurrent_requests r , apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl t, apps.fnd_conc_req_summary_v v
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate -1)
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.status_Code='R' and r.phase_code='R'
AND v.request_id=r.request_id
AND round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24)) > 4
order by v.actual_start_date
.
spool $ORACLE_HOME/rdbms/log/LongRunningJobs.lst
/
spool off
exit
EOF`

JobExists=`grep "Concurrent Program" $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | wc -l `

##########################################################################
# Send mail if there are programs which are running for more than 4 hours
##########################################################################


if [ $JobExists -ne 0 ]
then
cat $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | /usr/bin/mailx -s "Long Running Jobs in ERPPROD" ...
fi

STATS PACK

•Statspack collects more data, including high-resource SQL.
•Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. (Many of these ratios must be calculated manually when using BSTAT/ESTAT).
•Permanent tables owned by PERFSTAT store performance statistics. Instead of creating/dropping tables each time, data is inserted into the preexisting tables. This makes it easier to compare historical data.
•Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected.
•Data collection is easy to automate using either DBMS_JOB or an operating system utility.
•Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as 'user commits' + 'user rollbacks.' BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = 'user commits.' For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios.
The installation steps for STATSPACK are relatively simple and straightforward. This chapter will walk you, step by step, through the installation and configuration of STATSPACK. We will be including a complete description of the following steps:
•Creating the perfstat tablespace
•Creating the perfstat user
•Creating the tables
•Installing the packages
•Collecting data
•Scheduling data collection snapshots with dbms_job
•Testing your installation
Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy:
•spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts:
•spcsr.sql This script creates a user called PERFSTAT with the required permissions.
•spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.
•spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.
•spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.
•spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:
•spdusr.sql This script drops the PERFSTAT user.
•spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility.
•spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.
•sprepins.sql This is the actual SQL that produces the STATSPACK report.
•sppurge.sql This is a script to delete older unwanted snapshots.
•spuexp.par This is a export parameter file to export all of the STATSPACK data.
•sptrunc.sql This is a script to truncate all STATSPACK tables.
•spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.
•spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6

How to run the report
sql >variable snapno number; (eg :run the script at 6 clk )
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 1 if you are executing the
statspack procedure in the database for the first time..)

runit again as per time required time ( run it at 7 clk)
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 2 )

Now we have two snap numbers (these are called as begin and end snap numbers
which you will be entering when you are running the spreport.sql.)

sql >oracle_home/ora90/rdbms/admin/spreport.sql;

this will ask you to enter the begin and end snap numbers.(enter as 1 and 2
for the begin and end snap numbers..)

How does one determine the table and column name for a field displayed in a form

1. Use Help > Record History
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Record History
- The name of the base view used displays as Table Name:
- Go to the Sql*Plus
- Type desc , for example, desc mtl_system_items_fvl
- The columns of the table are displayed
- Review the columns and see if one matches the field that you are reviewing

2. Use Help > Diagnostics > Examine
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Diagnostics > Examine
- The block and field name that contain the data in the form are displayed as
well as the value in the field.
- Many times the block name is similar to the table name in the database
- Many times the field name is similar to the column name in the database
- Also you can go back to the view in #1 and see if the column can be found

3. Use the eTRM
- Find the table name from the record history in #1
- You may still require additional information about the columns
- Goto http://metalink.oracle.com/ > Top Tech Docs > Applications Electronic
Technical Reference Manuals
- Choose your version (ie., 11.5.9)
- Type the view or table name as the search criteria
- Press search
- The view or table appears
- Press the link and get more details and descriptions about the columns in the table

4. Research the view
- Sometimes the steps in #1 and #2 lead you to a view
- Get more details about the view as well as the underlying tables and columns
with the following SQL:
set long 100000
col text format a70
set pages 100
spool view.lst
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_NAME')
and owner = 'APPS';
spool off

5. Use SYSTEM.LAST_QUERY
- Query the records in a given form
- Choose Help > Diagnostics > Examine > Block = SYSTEM, Field= LAST_QUERY
- The query run to get the data shown is displayed.
- You can look at the various columns and tables used and research further

6. Run a trace
- Open the form that you are interested in but do not run a query
- Choose Help > Diagnostics > Trace > Trace with binds
- The trace file name and location are listed. Note them down
- Query the records
- Immediately shut trace off with Help > Diagnostics > Trace > No Trace
- The trace file name and location are listed
- Go to the database machine and get the trace file
- Create a tkprof of the trace file with the command, tkprof sys=no
- The various queries run are displayed.
- You can look at the various columns and tables used and research further

7. Open the form in Oracle Developer
- Open the form that you are interested in the Oracle Applications
- Choose Help > About
- Note the form name
- You have to first configure Oracle Developer to open forms with their
associated libraries
- Once this is done Open Developer
- Open the form in question
- Review the block, pl/sql procedures and libraries associated to the form

Profile option for change of settings like color etc

Change the "Corporate Branding Image for Oracle Applications / FND_CORPORATE_BRANDING_IMAGE" profile option to achieve this.

Refer to the following notes for more details:

Note: 551795.1 - How to change the default branding on the homepage which shown as "E-Business Suite" ?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=551795.1

Note: 395445.1 - Oracle Application Framework Profile Options Release 12
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=395445.1

Note: 421636.1 - How to replace the default Oracle Logo with a Customized Logo?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=421636.1

Note: 459350.1 - R12 - Image Size Problem On Login Page
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=459350.1

Session Trace

How to enable trace at database level?
Ans : set init.ora parameter sql_trace

How to enable trace for a session?
Ans: Alter system set sql_trace=true;
Execute the sql query
Alter system set sql_trace=false;
This will create a trace file at $RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.

How to enable trace for other session?
Ans : exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)
Eg: To enable trace for sql session with sid 8
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,true);
PL/SQL procedure successfully completed.

To disable trace
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,false);

What is the location of inint.ora ?
Ans : $RDBMS_ORACLE_HOME/dbs

What is that trace files contains and the utiliy used to read them?
Ans : Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.

How to find trace file for a given concurrent request id?
Ans : Go to $RDBMS_ORACLE_HOME/admin//udump
Grep “ “ *

How does one enable trace in the Oracle Application screens / forms?
One can enable trace through the forms by using the Help menu, choosing the daignostics menu,
trace and then selecting the appropriate trace for your needs. Most commonly if debugging an error,
you should at least provide trace with binds.
When debugging a performance issue,
you may consider using trace with binds and waits.

For example, the following is the navigation to enable trace in a form:
Goto the Oracle Applications
Login
Open the form where the error occurs but do not yet cause the error.
Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds
A message appears indicating that the trace will be recorded
Note the file name and location of the file
Now reproduce the error.
Once the error occurs, disable trace as soon as possible.
Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
The same file name and location will be noted in case you need it again.
Retrieve the trace file.

How does one enable trace for a concurrent program?
A simple way to enable trace in a concurrent program is
to review the concurrent program definition and select trace enabled.
This will enable trace without binds for all users that run the program.

For example, the following steps could be used.
Goto Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace

unix level performane check

vmstat
$ vmstat 5 3
Displays system statistics (5 seconds apart; 3 times):
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy id
0 0 0 28872 8792 8 5 172 142 210 0 24 3 11 17 2 289 1081 201 14 6 80
0 0 0 102920 1936 1 95 1936 302 1264 235 12 1 0 3 240 459 211 0 2 97
0 0 0 102800 1960 0 0 0 0 0 464 0 0 0 0 0 107 146 29 0 0 100

Having any processes in the b or w columns is a sign of a problem system.
Having an id of 0 is a sign that the cpu is overburdoned.
Having high values in pi and po show excessive paging.
• procs (Reports the number of processes in each of the following states)
• r : in run queue b : blocked for resources (I/O, paging etc.)
• w : runnable but swapped
• memory (Reports on usage of virtual and real memory)
• swap : swap space currently available (Kbytes)
• free : size of free list (Kbytes)
• page (Reports information about page faults and paging activity (units per second)
• re : page reclaims mf : minor faults pi : Kbytes paged in po : Kbytes paged out
• fr : Kbytes freed de : anticipated short-term memory shortfall (Kbytes)
• sr : pages scanned by clock algorith
• disk (Reports the number of disk operations per second for up to 4 disks
• faults (Reports the trap/interupt rates (per second)
• in : (non clock) device interupts si : system calls
• cs : CPU context switches
• cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
• us : user time si : system time cs : idle time
CPU Usage
sar
$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):
Time %usr %sys %wio %idle
11:57:31 72 28 0 0
11:57:41 70 30 0 0
11:57:51 70 30 0 0
11:58:01 68 32 0 0
11:58:11 67 33 0 0
11:58:21 65 28 0 7
11:58:31 73 27 0 0
11:58:41 69 31 0 0
Average 69 30 0 1

%usr: Percent of CPU in user mode
%sys: Percent of CPU in system mode
%wio: Percent of CPU running idle with a process waiting for block I/O
%idle: Percent of CPU that is idle
mpstat
$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 8 times):
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 6 8 0 438 237 246 85 0 0 21 8542 23 9 9 59
0 0 29 0 744 544 494 206 0 0 95 110911 65 29 6 0
ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system.
%CPU PID USER COMMAND
78.1 4789 oracle ora_dbwr_DDDS2
8.5 4793 oracle ora_lgwr_DDDS2
2.4 6206 oracle oracleDDDS2 (LOCAL=NO)
0.1 4797 oracle ora_smon_DDDS2
0.1 6207 oracle oracleDDDS2 (LOCAL=NO)
etc. etc. etc. etc.

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:
SELECT a.username,a.osuser,a.program,spid,sid,a.serial#
FROM v$session a,v$process b
WHERE a.paddr = b.addr AND spid ='&pid';



tar
The tar command can be used to backup and restore files to another filesystem or an offile storage device:
# Create archive.
cd /u01/app/oracle
tar -cvf /tmp/admin.tar admin

# Restore archive.
cd /tmp
tar -xvf admin.tar
If a full path is used during the archive creation the extract locations are fixed rather than relative. The process is similar when accessing a tape device except the destination is the mounted device:
# Mount and rewind the tape.
mt -f /dev/rmt/2m rew

# Create archive.
tar -cvf /dev/rmt/2m /u01/*

# Restore archive.
tar -xvf /dev/rmt/2m
dd
The dd command is similar to the tar command:
# Mount and rewind the tape.
mt -f /dev/rmt/2m rew

# Create archive.
dd if=/u01/app/oracle/* of=/dev/rmt/2m BS=32K

# Restore archive.
dd if=/dev/rmt/2m of=/u01/app/oracle BS=32K
cpio
The cpio command deals with the standard input so filesystem paths must be piped to it:
# Create archive.
cd /u01/app/oracle
find admin | cpio -oc > /tmp/admin.cpio

# Restore archive.
cd /tmp
cpio -idmv < admin.cpio
If a full path is used during the archive creation the extract locations are fixed rather than relative:
find /u01/app/oracle/admin | cpio -oc > /tmp/admin.cpio
vdump, rvdump, vrestore and rvrestore
Full level 0 backup of a local filesystem (/u01) to a local device (/dev/tape/tape1_d6):
/sbin/vdump -0 -u -f /dev/tape/tape1_d6 /u01
Full level 0 backup of a local filesystem (/u01) to a remote device (server2:/dev/tape/tape1_d6):
/sbin/rvdump -0 -u -f server2:/dev/tape/tape1_d6 /u01
Restore a vdump or rvdump archive from a local device (/dev/tape/tape1_d6) to a local filesystem (/u01):
/sbin/vrestore -xf /dev/tape/tape1_d6 -D /u01
Restore a vdump or rvdump archive from a remote device (server2:/dev/tape/tape1_d6) to a local filesystem (/u01):
/sbin/rvrestore -xf server2:/dev/tape/tape1_d6 -D /u01

Important metalink

1. How to run OATM migration utility ---- Note:404954.1

2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1

3. Upgrading Oracle Applications ---- Note:289788.1

4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1

5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1

6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1

7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1

8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1

9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1

10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1

11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1

12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1

13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1

14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1

15.Shared APPL_TOP FAQ ---- Note:243880.1

16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1

17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1

18.Release 11i Adpatch Basics ---- Note:181665.1

19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1

20.How to Create a Custom Concurrent Manager ---- Note:170524.1

21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1

22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1

23.**** Oracle Applications DBA SQL utilites ------------ 108207.1