Query_to_get_Resize_Details

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno) where tablespace_name in ('APPS_TS_TX_DATA','APPS_TS_TX_IDX') order by tablespace_name
 )
select
 case when autoextensible='NO' or autoextensible='YES' -- and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by file_name --bytes-hwm_bytes desc

Database Growth

The below script lists the details of database growth per month:
--------------------------------------------------------------------

select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by  to_char(creation_time, 'MM-RRRR');

Below script is useful for tablespace level database growth:
--------------------------------------------------------------

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb 
from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;



oracle script to check the database growth
----------------------------------------------

SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM    (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;


SQL script that gives you the current size of database and average disk space consumed in each day.
--------------------------------------------------------------------------------------------------


SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
SELECT a.days,a.tsname
, used_size_mb
, used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
FROM (
SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
,ts.tsname
,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days
) a
) b GROUP BY b.tsname ORDER BY b.tsname;


The below script lists the details of database growth per month:
--------------------------------------------------------------------

select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from   v$datafile
group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')
order by   1, 2;


Query to find DB Growth Per Month:
-----------------------------------

COLUMN month FORMAT a75
COLUMN growth FORMAT 999,999,999,999,999

SELECT
TO_CHAR(creation_time, ‘RRRR-MM’) “Month”,
SUM(bytes/1024/1024) “growth in MB”
FROM sys.v_$datafile
GROUP BY TO_CHAR(creation_time, ‘RRRR-MM’)
ORDER BY TO_CHAR(creation_time, ‘RRRR-MM’);

Query to find DB Growth per tablespace, average growth per day and average week growth:
-------------------------------------------------------------------------------------------

select min(creation_time) “Create Time”, ts.name, round(sum(df.bytes)/1024/1024) curr_size_mb,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)),1) growth_per_day,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 7,1) growth_7_days
from v$datafile df ,v$tablespace ts where df.ts#=ts.ts# group by df.ts#,ts.name order by df.ts#;


Database Growth History and Forecast
----------------------------------------

set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_count number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
v_db_begin_size number := 0;
v_db_end_size number := 0;
v_db_begin_allocated_space number := 0;
v_db_end_allocated_space number := 0;
v_db_growth number := 0;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';
BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
v_ts_begin_allocated_space := 0;
v_ts_end_allocated_space := 0;
v_ts_begin_size := 0;
v_ts_end_size := 0;
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
select count(*) into v_count from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF v_count=0 THEN
RAISE not_in_awr;
END IF;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF UPPER(v_rec.tablespace_name)='SYSTEM' THEN
v_numdays := v_end_snap_date - v_begin_snap_date;
END IF;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_db_begin_allocated_space := v_db_begin_allocated_space + v_ts_begin_allocated_space;
v_db_end_allocated_space := v_db_end_allocated_space + v_ts_end_allocated_space;
v_db_begin_size := v_db_begin_size + v_ts_begin_size;
v_db_end_size := v_db_end_size + v_ts_end_size;
v_db_growth := v_db_end_size - v_db_begin_size;


END;
END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_db_end_allocated_space||' MB'||' ('||round(v_db_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_db_end_size||' MB'||' ('||round(v_db_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_db_end_size/v_db_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_db_begin_allocated_space||' MB'||' ('||round(v_db_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_db_end_allocated_space||' MB'||' ('||round(v_db_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_db_begin_size||' MB'||' ('||round(v_db_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_db_end_size||' MB'||' ('||round(v_db_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_db_growth||' MB'||' ('||round(v_db_growth/1024,2)||' GB)');
IF (v_db_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for the Database');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_db_growth/v_numdays,2)||' MB'||' ('||round((v_db_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_db_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_db_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_db_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*90)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\');
END IF;
EXCEPTION
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('====================================================================================================================');
DBMS_OUTPUT.PUT_LINE('!!! ONE OR MORE TABLESPACES USAGE INFORMATION NOT FOUND IN AWR !!!');
DBMS_OUTPUT.PUT_LINE('Execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT, or wait for next AWR snapshot capture before executing this script');
DBMS_OUTPUT.PUT_LINE('====================================================================================================================');
END;
/
/##############################################

Performance issues check list for Oracle EBS applications

Performance issues check list for Oracle EBS applications

1. Verify all the servers that are part of the applications are accessible.
-> login to the servers that confirms servers are up and accessiable.
-> verify the load on the servers to see if any hung processes.
top  - command to verify the processes and resource utilization
-> verify disk spaces.
$df -h
-> verify if disks are accessible.

2. Verify all application services are up and running normal.

3. Verify database health check.
-> check any blocking locks in the database.
-> check any tablespace running out of space.
-> check Gather schema statistics running periodically and verify if any scheduled job failed.
set linesize 200
col table_name for a30
select table_name, last_analyzed from dba_tables order by last_analyzed desc;
-> check alert log for errors.
-> check temporary space, undo retention.
-> verify if any recent changes to database configuration parameters.
-> verify if any recent changes to database Oracle home like patching/upgrade ..
-> If any changes to Oracle home consider applying CPU/PS patches to Oracle home to make sure latest bugs are addressed.
-> verify if any indexes status is invalid
select index_name , status, last_analyzed from dba_indexes where  STATUS != 'VALID';
-> verify if any recent jobs scheduled in the database like Mviews refresh
-> Verify SGA configuration is appropricate.
-> Check if sufficient database sessions, processes, cursors are configured. It not it may throw error into alert log.

4. Concurrent Managers performance issues and Oracle notes:
Note 1075684.1 Concurrent Managers are consuming high CPU and memory
Note 1360118.1 Concurrent Manager Slow Performance Leaving Requests in Pending Status.
Note 1060736.1 Deadlock Error During Concurrent Request Termination
Note 866298.1 Concurrent Processing - ORA-00060: Deadlock Detected - UPDATE FND_CONCURRENT_QUEUES
Note 822368.1 Concurrent Processing - How To Run the Purge Concurrent Request FNDCPPUR, Which Tables Are Purged, And Known Issues Like Files Are Not Deleted From File System or Slow Performance
Note 1060707.1 Purge Concurrent Requests/Manager Data, FNDCPPUR, Not Removing Files From Filesystem
Note: Also reference Note 986272.1 "How To Performance Tune The Concurrent Processing Server"
EBPERF FAQ - Collecting Statistics in Oracle EBS 11i and R12 (Doc ID 368252.1)

5. Forms issues
11i: Troubleshooting Tips For Spinning/Hanging F60WEBMX Processes [ID 457381.1]
11i: Troubleshooting Tips For Spinning/Hanging F60WEBMX Processes (Doc ID 457381.1)


6. Turn on debugging for specific issue:
I.
a. cd to your $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config directory.
b. Make a back up your oc4j.properties (e.g. cp -p oc4j.properties oc4j.properties_oss)
c. Add the values to your oc4j.properties file
 a. AFLOG_ENABLED=true
 b. AFLOG_LEVEL=statement
 c. AFLOG_MODULE=%
 d. AFLOG_FILENAME=/tmp/aflog_oss.log

2. Enable http ODL logging
II.
 a. Backup your [ $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf ] file
 Edit the httpd.conf file and add the following to the end of file
 OraLogMode oracle
 OraLogSeverity TRACE:32
 OraLogDir $LOG_HOME/ora/10.1.3/Apache/oracle
 Please use the full path to $LOG_HOME
 (e.g. OraLogDir /oracle/v1204/inst/apps/V1204_bertha/logs/ora/10.1.3/Apache/oracle)
 b. Make the following directory which will be where the Apache ODL log files are created
 mkdir $LOG_HOME/ora/10.1.3/Apache/oracle

III. Increase OPMN Logging
 -------------------------------
 Backup of your [ $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml ] file.
 Edit the opmn.xml file and make the following change.
 ( i.e.
 BEFORE:
 <debug path="/oracle/v1204/inst/apps/V1204_bertha/logs/ora/10.1.3/opmn/opmn.dbg"
 comp=""
 rotation-size="1500000"
 rotation-hour="0" />

 AFTER:
 <debug path="/oracle/v1204/inst/apps/V1204_bertha/logs/ora/10.1.3/opmn/opmn.dbg"
 comp="internal;ons;pm"
 <<-- [ debug statements.. ]
 rotation-size="1500000"
 rotation-hour="0" />

IV. Shutdown middle (web) tier ( $ADMIN_SCRIPTS_HOME/adopmnctl.sh stopall )

V. Clean out the Apache/oc4j log files for middle tier iAS 10g (10.1.3):
 ( From an Unix shell where the Apps environment has been sourced )

 a-1. $ cd $LOG_HOME/ora/10.1.3/Apache/
 a-2. $ pwd (verify that you are in the "Apache logs directory")
 a-3. $ rm -r *
 b-1. $ cd $LOG_HOME/ora/10.1.3/opmn/
 b-2. $ pwd (verify that you are in the "opmn logs directory")
 b-3. $ rm -r *

 [ Note: ] For EACH j2ee log group cleaning below:
 Could have multiple jvms per each type of log groups so
 "default_group_#" refers to generic #. Whereby the steps
 below for cleaning should be for EACH jvm.

 c-1. $ cd $LOG_HOME/ora/10.1.3/j2ee/forms/[ forms_default_group_# ]
 c-2. $ pwd (verify that you are in the "forms_default_group_#" directory)
 c-3. $ rm -r *

 d-1. $ cd $LOG_HOME/ora/10.1.3/j2ee/oacore/[ oacore_default_group_# ]
 d-2. $ pwd (verify that you are in the "oacore_default_group_#" directory)
 d-3. $ rm -r *
 e-1. $ cd $LOG_HOME/ora/10.1.3/j2ee/oafm/
 e-2. $ pwd (verify that you are in the oafm directory)
 e-3. $ rm -r *

 f-1. $ cd $LOG_HOME/ora/10.1.3/j2ee/oafm/[ oacore_default_group_# ]
 f-2. $ pwd (verify that you are in the "oafm_default_group_#" directory)
 f-3. $ rm -r *

VI. Startup middle (web) tier ( $ADMIN_SCRIPTS_HOME/adopmnctl.sh startall )

 a. Reproduce the problem

 b. Run the following (aoljtests) tests as well.
 Note(s):
 Upload screen shots from each of the tests below.
 -or-
 Save the pages from your browser. (File --> Save Page)

 Go to..
 http://[machine:port]/OA_HTML/jsp/fnd/aoljtest.jsp
 Enter in the following Info...
 Environment Type: non-RAC ( unless you have a RAC system,
 which I do not believe to be true )
 Apps User Name:
 Apps Password:
 Oracle SID
 Host Name: <-- this is the database server- (fully qualified domain)
 Port Number: <-- Database listener port

VII. The tests we want you to run are the following -
 *Connection Test
 Locate DBC File
 Verify DBC Settings
 Verify Guest User
 *APPS_WEB_AGENT
 Virtual Directory Settings
 *APPS_SERVLET AGENT
 Virtual Directory Settings
 Servlet Ping
 *APPS_FRAMEWORK_AGENT
 Virtual Directory Settings
 OA Framework System Info
 *Tool Launcher Profile Settings
 ICX_FORMS_LAUNCHER <<-- Actually click on the link that comes up and see if forms launches
 *Application Login
 Login Page <<-- Try and log in, if possible then click on the two tests below
 Show Responsibilities
 Show Current Session Settings

VIII. Shutdown middle (web) tier ( $ADMIN_SCRIPTS_HOME/adopmnctl.sh stopall )

IX. Run the following from a Unix shell with the Apps environment sourced and upload the resulting zip file
 under your [ tmp ] directory.
 zip -r /tmp/`uname -n`_`date +%m%d%y.%H%M`_iAS1013.zip \
 $ORA_CONFIG_HOME/10.1.3/Apache/* \
 $ORA_CONFIG_HOME/10.1.3/j2ee/* \
 $ORA_CONFIG_HOME/10.1.3/opmn/* \
 $ORA_CONFIG_HOME/10.1.3/javacache/* \
 $LOG_HOME/ora/10.1.3/Apache/* \
 $LOG_HOME/ora/10.1.3/opmn/* \
 $LOG_HOME/ora/10.1.3/j2ee/* \
 $APPLRGF/javacache.log \
 /tmp/aflog_oss.log \
 $CONTEXT_FILE

Oracle Apps 11i and R12 Environment Variables list

Oracle Apps 11i and R12 Environment Variables list:

Important File Locations

File Oracle Apps 11i Oracle Apps R12
Environment Source file APPSORA.env APPS<SID>_<hostname>.env
Context File (Middle tier) $APPL_TOP/admin/$TWO_TASK.xml $INST_TOP/appl/admin/$TWO_TASK_<hostname>.xml
tnsnames.ora (OH) $ORACLE_HOME/network/admin/<CONTEXT> $INST_TOP/ora/10.1.2/network/admin
listener.ora $ORACLE_HOME/network/admin/<CONTEXT> $INST_TOP/ora/10.1.2/network/admin
appsweb.cfg $OA_HTML/bin $INST_TOP/ora/10.1.2/forms/server
tnsnames.ora (Apache) $IAS_ORACLE_HOME/network/admin/<CONTEXT> $INST_TOP/ora/10.1.3/network/admin
jsev.properties $IAS_ORACLE_HOME/Apache/Jserv/etc $INST_TOP/ora/10.1.3/opmn/conf/opmn.xml
httpd.conf $IAS_ORACLE_HOME/Apache/Apache/conf $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf
apps.conf $IAS_ORACLE_HOME/Apache/Apache/conf $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf
formservlet.ini $IAS_ORACLE_HOME/Apache/Jserv/etc $ORACLE_HOME/forms/server/default.env
topfile.txt $APPL_TOP/admin $APPL_CONFIG_HOME/admin
adovars.env $APPL_TOP/admin $APPL_CONFIG_HOME/admin
adjborg2.txt $APPL_TOP/admin $APPL_CONFIG_HOME/admin
SSL Certificates $COMMON_TOP/admin/certs $INST_TOP/certs
AD scripts logs $COMMON_TOP/admin/log/<SID_hostname> $LOG_HOME/appl/admin/log
Concurrent Request logs $APPLCSF $APPLCSF
Apache logs $IAS_ORACLE_HOME/Apache/Apache/logs $LOG_HOME/ora/10.1.3/Apache
Jserv logs $IAS_ORACLE_HOME/Apache/Jserv/logs $LOG_HOME/ora/10.1.3/j2ee
javacache.log $COMMON_TOP/rgf/<SID_hostname> $LOG_HOME/appl/rgf
Environmental variables

Variable Oracle Apps 11i Oracle Apps R12
APPL_TOP $HOME/<SID>appl $HOME/apps/apps_st/appl
COMMON_TOP $HOME/<SID>comn $HOME/apps/apps_st/comn
ORACLE_HOME (applmgr) $HOME/<SID>ora/8.0.6 $HOME/apps/tech_st/10.1.2
IAS_ORACLE_HOME $HOME/<SID>ora/iAS $HOME/apps/tech_st/10.1.3
ORACLE_HOME (oracle) $HOME/<SID>db/10.2.0 $HOME/db/tech_st/10.2.0
ORADATA $HOME/<SID>data $HOME/db/apps_st/data
JAVA_TOP, OA_JAVA $COMMON_TOP/java $COMMON_TOP/java/classes
OA_HTML $COMMON_TOP/html $COMMON_TOP/webapps/oacore/html
FND_SECURE $FND_TOP/secure/<SID> $INST_TOP/appl/fnd/12.0.0/secure
ADMIN_SCRIPTS_HOME $COMMON_TOP/admin/scripts/<SID> $INST_TOP/admin/scripts
LOG_HOME – $INST_TOP/logs
FORMS_WEB_CONFIG_FILE – $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg
AF_JLIB – $COMMON_TOP/java/lib
JAVA_BASE – $COMMON_TOP/java
INST_TOP – $HOME/inst/apps/<CONTEXT>
ORA_CONFIG_HOME – $INST_TOP/ora
APPLCSF $COMMON_TOP/admin $LOG_HOME/appl/conc

SQL id from Request id

set lines 1000
col USER_CONCURRENT_PROGRAM_NAME for a30
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30
select /*+ ordered */
fcr.request_id,
fcp.user_concurrent_program_name
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      inst.inst_name
,      sa.SQL_ID
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      gv$process pro
,      gv$session sess
,      gv$sqlarea sa
,      sys.v_$active_instances inst
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    sess.inst_id = inst.inst_number (+)
and    request_id='&req_id';