Missing FNDSM and FNDIM Services After Cloning or CLEAR NONEXISTENT FND_NODES

AFTER CLEAR NONEXISTENT FND_NODES, FNDFS and FNDSM NOT COMMING UP

However, now the Concurrent Manager cannot be started. The following error is produced in the Internal Concurrent Manager log file :

Symptoms

Could not start Service Manager FNDSM_hostname.
The TNS alias could not be located, the listener process on hostname could not be contacted, or the listener failed to spawn the Service Manager process.

Process monitor session started : 28-MAY-2008 10:15:28

Could not contact Service Manager FNDSM_TEST_UAT_dev. The TNS alias could not be located, the listener process onTEST_UAT could not be contacted, or the listener failed to spawn the Service Manager process.

Could not contact Service Manager FNDSM_TEST_dev. The TNS alias could not be located, the listener process on TEST could not be contacted, or the listener failed to spawn the Service Manager process.

Could not contact Service Manager FNDSM_APPSTEST1_dev. The TNS alias could not be located, the listener process on APPSTEST1 could not be contacted, or the listener failed to spawn the Service Manager process.

Could not initialize the Service Manager FNDSM_CHEN_dev. Verify that APOLLO has been registered for concurrent processing.
Routine AFPEIM encountered an error while starting concurrent manager INVTMRPM with library /11i/applmgr/devappl/inv/11.5.0/bin/INCTM.



Check that your system has enough resources to start a concurrent manager process. Contact your system administra : 28-MAY-2008 17:34:44
Could not find service instance context for service instance number 1027
Could not find service instance context for service instance number 1035
Could not find service instance context for service instance number 1039
Could not find service instance context for service instance number 1036

Starting FNDCRM Concurrent Manager : 28-MAY-2008 17:34:44

Could not initialize the Service Manager FNDSM_CHEN_dev. Verify that APOLLO has been registered for concurrent processing.
Routine AFPEIM encountered an error while starting concurrent manager FNDCRM with library /11i/applmgr/devappl/fnd/11.5.0/bin/FNDCRM.

Check that your system has enough resources to start a concurrent manager process. Contact your system administrat : 28-MAY-2008 17:34:44

Starting INVMGR Concurrent Manager : 28-MAY-2008 17:34:44
Could not initialize the Service Manager FNDSM_CHEN_dev. Verify that CHEN has been registered for concurrent processing.
Routine AFPEIM encountered an error while starting concurrent manager INVMGR with library /11i/applmgr/devappl/inv/11.5.0/bin/INVLIBR.



Solution

Run the following select on the target node only to check trigger status :

SELECT trigger_name , status
FROM user_triggers
WHERE table_name = 'FND_NODES' ;

If status of the triggers show as DISABLED, then enable these following triggers as follows :

connect apps/

alter trigger UPNAME enable;
alter trigger FNDSM enable;

Take backup of FND_NODES table in Target :

create table FND_NODE_bk as select * from FND_NODES ;

1. Login into Oracle Application with System Administrator Responsibility.
2. Delete the rows from FND_NODES table in target using install -> node -> Define form.
3. Then re-Register TRAGET hostname.
4. This should register FNDIM_ and FNDSM_ concurrent managers in FND_CONCURRENT_QUEUE Table.

Then

Check the following Scripts it will give node name. If not give any out put please run that below sql scripts.

SQL> select concurrent_queue_name, node_name from fnd_concurrent_queues where
manager_type=6;

From the Application tier:

SQL> sqlplus apps/apps

1. Log in as applmgr
2. cd to $FND_TOP/patch/115/sql
3. Run the script: afdcm037.sql
4. This script make libraries for FNDSM and create Managers for Preexisting Nodes.


Now you check

ps -ef grep FNDSM
ps -ef grep FNDCRM


Note ID: Note:305986.1

Applying Developer patchset 18 and ATG RUP 6

ENVIRONMENT

OS: AIX Version 5.3
DB: 10g (10.2.0.1.0)
APPS: 11i (11.5.10.2)


1.0.0 Apply AD.I.6 Minipack

Apply patch 6502082 using adpatch

Instructions

1. Before Applying This 5985992 Patch

Perform the steps listed below:

1.2 From the location where this patch was unzipped, change directory to fnd/patch/115/bin .

1.3 Run the Validation script as follows:

------------------------------------------
NOTE:

Ensure that your current working directory is
[patch unzipped location]/fnd/patch/115/bin

------------------------------------------ on Unix:

./txkprepatchcheck.pl -script=ValidateRollup
-outfile=$APPLTMP/txkValidateRollup.html
-appspass=


1.4 Review the report generated by the validation script

2. How To Apply This Patch

This patch contains the following driver file to be applied with
AutoPatch: u5985992.drv (This is the unified driver).

2.1 After Applying this Patch Run Autoconfig

2.2 Update the RDBMS ORACLE_HOME file system with the AutoConfig files by
performing the following steps:

2.2.1 On the Application Tier (as the APPLMGR user):
2.2.1.1 Source the environment file.
2.2.1.2 Create the appsutil.zip file by executing:
$ADPERLPRG $AD_TOP/bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out .

2.2.2 On the Database Tier (as the ORACLE user):
2.2.2.1 Copy or FTP the appsutil.zip file to the
2.2.2.2 cd
2.2.2.3 unzip -o appsutil.zip
2.2.2.4 Run AutoConfig by executing:

2.3 Apply 11.5.10 INTEROP PATCH FOR 10GR2
Apply patch 4653225 using adpatch

Apply patch 4948577(patchset 18) For Oracle Developer

1.1 Apply Developer patchset 18

1. Apply patch 4948577 to 8.0.6 Home on app and admin tiers

Note:
Before moving ahead with patch application ensure following:

All processes, running out of the ORACLE_HOME being patched, are shutdown cleanly.
$PATH includes $ORACLE_HOME/bin and also it points to locations where following executables are present: make, ar, ld, and nm.
$ORACLE_HOME should point to 806 Oracle Home.
$ORACLE_HOME and $JDK_HOME environment variables are set.
Set JDK_HOME to the JDK directory (JDK 1.3.1 or above).
For example, if you have JDK version 1.3.1 installed under /usr/local/packages/jdk1.3.1 directory, then set JDK_HOME to /usr/local/packages/jdk1.3.1/jdk
Make sure to backup the current /etc/oraInst.loc file.
Point the inventory location to the correct path in the oraInst.loc with the directory having the required inventory information


Reference:

Unzip the patch in 8.0.6 $ORACLE_HOME. .
Run the install script contained in the patch:
cd $ORACLE_HOME/developer6i_patch18
./patch_install.sh 2>&1 tee patch_install_p18.log
Check patch_install_p18.log for errors.


2. Relink Procedure Builder

Reference:
cd $ORACLE_HOME/procbuilder60/lib;
make -f ins_procbuilder.mk install

3. Relink Forms
Reference:
cd $ORACLE_HOME/forms60/lib;
make -f ins_forms60w.mk install

4. Relink Graphics
Reference:
cd $ORACLE_HOME/graphics60/lib;
make -f ins_graphics60w.mk install


5. Relink Reports
Reference:
Reports has both link-time and run-time dependency with libjava.so, so you need to append $ORACLE_HOME/network/jre11/lib/sparc/native_threads in $LD_LIBRARY_PATH before linking Reports. The same $LD_LIBRARY_PATH should be used at run-time.
cd $ORACLE_HOME/reports60/lib;
make -f ins_reports60w.mk install

1.2 Install Additional patches for Developer6i patchset 18

1.2.1 Apply patch 5713544

Unzip the patch. Change the directory to where the patch was unzipped.
cd 5713544
If you don't have genshlib in $ORACLE_HOME/bin directory then copy this file over there.
cp genshlib $ORACLE_HOME/bin
sh patch.sh


1.2.2 Apply patch 4261542

Copy the patch file to 6i Oracle Home
cd $ORACLE_HOME
Unzip the patch
Backup the Forms class files, i.e.$ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class
%cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class
$ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG4261542
%cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
$ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG4261542

Copy this file into ORACLE_HOME/forms60/java/oracle/forms/engine
%cd $ORACLE_HOME/4261542/oracle/forms/engine
%cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
%cd $ORACLE_HOME/4261542/oracle/forms/handler
%cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class


1.2.3 Apply patch 5216496

Copy the zip in $ORACLE_HOME and Unzip the zip file. This will unzip into 5216496 folder.
• If you don't have genshlib in $ORACLE_HOME/bin directory then copy this file over there.
cp genshlib $ORACLE_HOME/bin
• Copy your original libraries in case you ever need it:
cd $ORACLE_HOME/lib
cp libiff.a libiff.a.ORG
cp libiff.so.0 libiff.so.0.ORG
cp libiffw.a libiffw.a.ORG
cp libiffw.so.0 libiffw.so.0.ORG
• Archive .o into iwf and iwfw library
cd $ORACLE_HOME/5216496/opt
ar -rv $ORACLE_HOME/lib/libiff.a ifzprs.o
cd $ORACLE_HOME/5216496/optweb
ar -rv $ORACLE_HOME/lib/libiffw.a ifzprs.o
• Generate All *.so's
cd $ORACLE_HOME/lib
$ORACLE_HOME/bin/genshlib iffw 0
$ORACLE_HOME/bin/genshlib iff 0
• Generate forms executables
cd $ORACLE_HOME/forms60/lib
make -f cus_forms60w.mk install

1.2.4 Apply patch 5753922
Unzip Patch file into 6i Oracle Home
cd 5753922
sh patch.sh

1.2.5 Apply patch 6195758

Unzip the patch into 6i Oracle Home.
cd $ORACLE_HOME
unzip p6195758_600_GENERIC.zip

Unzip the java class files and regenerate your JAR files

Backup the Forms class files,

%cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
$ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG6195758

%cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class
$ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class.PRE_BUG6195758

Inside folder 6195758 in step-2 has class files in oracle\forms\handler directory. Copy this file into ORACLE_HOME/forms60/java/oracle/forms/handler

cd $ORACLE_HOME/6195758/oracle/forms/handler
cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
%cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class

1.2.6 Apply Patch 5938515

Unzip Patch file in 6i Oracle Home
cd 5938515
sh patch.sh

1.3 Apply Forms Interoperability Patch

Apply patch 4888294 using adpatch

1.3.1 APPLICATIONS INTEROPERABILITY PATCH

Apply patch 3830807 using adpatch

1.3.2 UNABLE TO RELINK LIBIFJAPI60.SO IN RHAS3.0

Apply patch 4586086 using adpatch


1.4 Generate JAR Files

Generate JAR files using adadmin

1.5 Apply Security Fixes to Developer 6i
1.5.1 Apply patch 5687261 to forms server


Unzip patch in 6i oracle home
cd 5687261
sh patch.sh 2>&1 tee patch.log
Please inspect patch.log for any errors

1.5.2 Apply patch 5686997
Unzip the patch in 6i Oracle Home
cd 5686997
sh patch.sh 2>&1 tee patch.log
Please inspect patch.log for any errors.

1.5.3 Apply patch 5078711

• Make sure that all processes running from the ORACLE_HOME being patched are shutdown before installing this patch.
• Ensure that ORACLE_HOME is set.
• Set JDK_HOME to the JDK directory (JDK 1.3.1 or later).
• Ensure $ORACLE_HOME/bin is included in your PATH environment variable, and also ensure that the following executables must be present in the $PATH: make, ar, ld, nm
• Set your current directory to the directory where the patch is located and then run the installcpu.sh script by entering the following UNIX command:
sh installcpu.sh
Inspect the following install log file for any errors:
installcpu.sh log file $ORACLE_HOME/cpu/5078711/install.log

***********************************



Apply ATG_PF RUP6

Apply Technology Stack Patch

CPUApr2008 6831988 EBS11i:Technology Stack (TXK) Components For Critical Patch Update April 2008

1.6 Apply Pre-Req Patches

1.6.1 Apply AD.I.6 Minipack
Apply patch 6502082 using adpatch
1.6.2 Post AD.I.6 patch Tasks

1.6.2.1 Grant privileges and create PL/SQL profiler objects
Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.

• Create $ORACLE_HOME/appsutil/admin on the database server.
• Copy adgrants.sql from $APPL_TOP/admin to $ORACLE_HOME/appsutil/admin.
• Set the environment to point to ORACLE_HOME on the database server.
• Run following commands
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql
1.6.2.2 Run Adconfig
Run adconfig to instantiate the templates used by AD Utilities
1.6.2.3 Perform Adadmin Tasks
• Regenerate JAR files using adadmin.
• Update current view snapshot

1.7 Apply ATG_PF RUP6

1.7.1 Apply pre-req patches
Apply following pre-req patches

1.7.1.1 Apply patch 5989593

Apply patch using adpatch
Create $ORACLE_HOME/appsutil/admin on the database server.
Copy adgrants.sql (UNIX) from the patchtop(5989593)/admin directory to
$ORACLE_HOME/appsutil/admin.
Set the environment to point to ORACLE_HOME on the database server.
Use SQL*Plus to run the script:

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql

1.7.1.2 Apply patch 4291053

Apply patch using adpatch
Run adadmin and
Compile APPS Schema
Compile Flexfield data in AOL Tables

1.7.1.3 Apply patch 3218526 – BNE.D

Apply patch using adpatch
Run adadmin and
Compile APPS Schema
Compile Flexfield data in AOL Tables

Perform following post patch steps

Once you have successfully applied minipack 11i.BNE.D, you must complete these additional product-specific tasks. This section describes these tasks.

 The Web server must be restarted after the successful application of the Patch Set.
 Review the list of profile options in the Implementation and User Documentation specified in the Additional Product Information section.
 Review the seeded form functions, menu, and responsibility in the Implementation and User Documentation specified in the Additional Product Information section. Give Oracle Application Users access to Web ADI form functions. Simply assigning the seeded Desktop Integration Responsibility to a user can do this. See the Oracle Applications System Administrator's Guide for more information on giving users access to form functions on the Personal Home Page.
 If your site is using the General Ledger - Journals Integrator:
o Make sure the GL: Set of Books Name profile option is set for all user profiles.
o Assure that at least one of the following form functions is associated with the menu that contains the form function they use to access Web ADI: Enter Budget Journals, Enter Journals, Enter Encumbrances, Import Journals, and Create Document.
 After BNE.D has been applied, then apply the latest ATG RUP patchset - currently that is ATG RUP6. All of the Web ADI fixes that have been made on top of BNE.D have been rolled up in the the latest ATG RUP patchset. In order to get these latest updates, you should apply the latest ATG RUP patch 6. Reference Patch 5903765 11i.ATG_PF.H. RUP6 and Note 444524.1 for more information
 Sites using Internal Controls Manager need to apply Patch 4483674 after applying BNE.D Minipack.
 Sites using Financial Intelligence need to apply Patch 3857216 after applying BNE.D Minipack.
 Sites using HRMS need to apply Patch 4199321 after applying BNE.D Minipack. Please consult the HRMS Metalink page for any additional patches which may also need to be applied.

1.7.1.4 Apply patch 3761838 – FRM.G
1.7.1.5 Apply patch 4206794 – FRM.H


Apply patch 4206794 using adpatch
Perform following adadmin task after successful application of patch
• Generate message files
• Compile APPS schema(s)
• Compile flexfield data
• Recreate grants and synonyms for APPS schema(s)
• Gather schema statistics

1.7.2 Run the technology stack validation utility

• Source the environment logged in as applmgr
• Ensure that APPLRGF environment is set, if not then set it to the value of APPLTMP
• From the location where this patch(ATG_PF>RUP6) was unzipped, change directory to fnd/patch/115/bin.
• Run the tech stack validation utility as follows:
• ./txkprepatchcheck.pl -script=ValidateRollup
-outfile=$APPLTMP/txkValidateRollup.html
-appspass=
• Check the report filegenerated by this utility for any actions required to perform

1.7.3 Apply ATG_PF.RUP6 patch using adpatch.
Apply patch 5903765 using adpatch
1.7.4 Post ATG_PF.RUP6 steps
Apply following patches using adpatch
3865683


Complete Adadmin Tasks
Complete following adadmin tasks
• Generate message files
• Compile apps schema
• Compile flexfields
• Compile menus
• Run “Gather Schema Statistics” concurrent program

Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES

After completing the ADCLONE process, or implementing Autoconfig,
the FND system tables have incorrect values

SQL> select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES;


NODE_NAME N S S S S
------------------ - - - - -
TESTSERVER O N Y N Y
APPSTEST01 O N Y N Y
APPSDEVP01 O N Y N Y
DEVP O Y Y Y Y
AUTHENTICATION O N N N N
GOLD_UAT O
GOLDTS O Y Y Y Y

7 rows selected.


NOTE:

Do not directly Delete from FND_NODES table because the
'support_' columns will be affected and will not
incorporate the correct information througout in OAM


Run The following Script.

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;


After Commit check the following Scripts. Now you got output no rows.
Then you run Autuconfig both Tiers

SQL> select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES;


no rows selected


Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers,
to repopulate the required system tables.

SQL> select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES;



NODE_NAME N S S S S
-------------------- - - - - -
AUTHENTICATION O N N N N
GPLDTS O Y Y Y Y


Note:260887.1

Invalid Objects after Database Upgraded to 10.2.0.1

ERROR at line 1:
ORA-04063: package body "SYSTEM.AD_INVOKER" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYSTEM.AD_INVOKER"
ORA-06512: at line 2

sqlplus -s APPS/***** @/11i/progs/applmgr/devappl/ad/11.5.0/admin/sql/adinvset.pls &systempwd 8 0 FALSE FALSE


The Oracle DB's stored procedures have not been invalidated and recompiled for Oracle 10g.


Solutions
==========


1. Change directory to $Portal_DB_HOME/rdbms/admin
2. Login to sqlplus as sys as sysdba.
3. SQL>shutdown
4. SQL>startup upgrade
5 SQL>@utlirp.sql
6. SQL>shutdown
7. SQL>startup
8. SQL>@utlrp.sql
9. Check for invalid objects.

Use the following queries to track recompilation progress

1. Query showing jobs created by UTL_RECOMP

SELECT job_name FROM dba_scheduler_jobs
WHERE job_name like 'UTL_RECOMP_SLAVE_%';

2. Query showing UTL_RECOMP jobs that are running

SELECT job_name FROM dba_scheduler_running_jobs
WHERE job_name like 'UTL_RECOMP_SLAVE_%';


1. Query returning the number of invalid objects remaining. This
number should decrease with time.


SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

2. Query returning the number of objects compiled so far. This number
should increase with time.


SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

select count(*) from dba_objects where status='INVALID';

Important Apps Scripts

/* cmtoday.sql
see info on jobs run today
can specify an individual job name to limit the list
*/

clear columns
set pages 23
set lines 125
col os form A6
col program head "Program Name" form A70 trunc
col shrtnm head "Shortname" format a9
col time head Elapsed form 9999.99
col request_id head "Req Id" form 9999999
col cprogid head "Prg Id" form 999999
col "Started On" format a10
col "Finished On" format a11
col "Submitted By" format a30 trunc
col argument_text head "Arguments" format a50 trunc
col statustxt head Status format a10 trunc
col phasetxt head Phase format a10 trunc
set recsep wrap
set verify off
accept trgtprog char default all prompt 'What is the concurrent program short name : '
accept offsetdays num default 0 prompt 'How many days back (0 for today, 1 for yesterday etc) : '
select a.request_id
,c.concurrent_program_name shrtnm
,c.concurrent_program_id cprogid
,ctl.user_concurrent_program_name "program"
,l2.meaning phasetxt
,l1.meaning statustxt
,to_char(a.actual_start_date,'hh:mi:ssAM') "Started On"
,to_char(a.actual_completion_date,'hh:mi:ssAM') "Finished On"
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,a.argument_text
,u.user_name || ' - ' || u.description "Submitted By"
from APPLSYS.fnd_Concurrent_requests a
,applsys.fnd_user u
,applsys.fnd_lookup_values l1
,applsys.fnd_lookup_values l2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
where u.user_id = a.requested_by
and (upper(c.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
and trunc(a.actual_start_date) = trunc(sysdate) - &offsetdays
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and nvl(l1.start_date_active,sysdate) <= sysdate
and (l1.end_date_active > sysdate or l1.end_date_active is null)
and l2.lookup_type = 'CP_PHASE_CODE'
and l2.lookup_code = a.phase_code
and l2.language = 'US'
and l2.enabled_flag = 'Y'
and nvl(l2.start_date_active,sysdate) <= sysdate
and (l2.end_date_active > sysdate or l2.end_date_active is null)
and a.concurrent_program_id = c.concurrent_program_id
and ctl.concurrent_program_id = c.concurrent_program_id
and ctl.language = 'US'
and a.program_application_id = c.application_id
and ctl.application_id = c.application_id
order by actual_start_date;

/

/* cmlong.sql
monitor script to list the ccmgr jobs running longer than 10 minutes
and exceeding the average historical runtime for this particular
job
*/

col program form A35 trunc head "Program Full Name"
col intprog format a20 trunc head "Internal Name"
col time form 9999.99
col "Req Id" form 9999999
col qname head "Concurrent Manager Queue" format a25 trunc
rem select q.concurrent_queue_name qname
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,a.phase_code,a.status_code
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
ctl.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and ctl.concurrent_program_id = c.concurrent_program_id
and ctl.language = 'US'
and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 10
and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >
( select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440
from APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
where c2.concurrent_program_id = c.concurrent_program_id
and a2.concurrent_program_id = c2.concurrent_program_id
and a2.program_application_id = c2.application_id
and a2.phase_code || '' = 'C'
)
order by 5 desc;
/

Daily Total Count Concurrent

select sysdate -1, count(*)
from apps.fnd_concurrent_requests
where to_char(actual_completion_date,'YYYYMMDD') = (select to_char(sysdate -1,'YYYYMMDD') from dual);

/

currently Running Concurrent Rrequests:

SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);

/

prompt To Find Sessions Generating Lots of Redo
prompt

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 50
ORDER BY 5 desc, 1, 2, 3, 4
/

/* formusers.sql
show the applications sessions currently using a specified form
can optionally limit it by active only
if you need to limit by a user, use appsid.sql
*/

set lines 132
set verify off
set feedback off
col user_name head "Apps Signon" format a12 trunc
col description head "Real Name" format a25 trunc
col user_form_name head "Form Name" format a30 trunc
col time head "Elapsed" format a7
col startedat head "Logon At" format a19
col spid head DBProc format a6
col sidctr head "#Sess" format 99999
col actstat head "Status" format a1
col currform format a30 trunc head CurrentForm
col lastcallet format a11
col sid format 99999
break on startedat on time on user_name on description on sidctr on actstat on spid
accept trgtuser char default ALL prompt 'What is the Apps short username you are interested in : '
accept trgtform char default ALL prompt 'What is the Apps form you are interested in : '
accept trgtstat char default N prompt 'Limit to active users only [y/n] : '
select /*+ rule */
to_char(s.logon_time,'mm/dd hh24:mi:ss') startedat,
a.time,
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "LastCallET",
u.user_name,
u.description ,
count(s.sid) sidctr,
s.sid,
decode(sum(decode(s.status,'ACTIVE',1,0)),0,'I','A') actstat,
p.spid,
a.form_id || ' - ' || a.user_form_name currform
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
-- and l.start_time = u.last_logon_date
-- and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and ((upper(a.user_form_name) like upper('%&trgtform%')) or (upper('&trgtform') = 'ALL'))
and ((s.status = 'ACTIVE' and upper('&trgtstat') = 'Y') or (upper('&trgtstat') != 'Y'))
and ((upper(u.user_name) like upper('%&trgtuser%')) or (upper('&trgtuser') = 'ALL'))
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by to_char(s.logon_time,'mm/dd hh24:mi:ss'),
s.sid,u.user_name, u.description,a.time,a.form_id || ' - ' || a.user_form_name,p.spid,
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60)
order by 8 desc,5,to_char(s.logon_time,'mm/dd hh24:mi:ss'),a.time;
/

Input Request ID and get Output

set long 10000000
select sql_text, sid, SERIAL# from v$session a,v$sqltext b,applsys.fnd_concurrent_requests f
where a.sql_hash_value=b.hash_value
and f.ORACLE_SESSION_ID=a.AUDSID
and f.request_id = '2722865'
/

Input SID and Get output

column osuid format a8
column dbacct format a20
select s.sid, s.process pid, p.spid orapid, s.osuser osuid, s.username dbacct,
s.serial#, s.program, s.machine
from v$session s, v$process p where s.sid = &&1
and p.addr(+) = s.paddr
/


/* showappuser.sql
show the applications user based on the apps logon username
*/


set lines 132
col user_name head "Apps Signon" format a12 trunc
col description head "Real Name" format a25 trunc
col user_form_name head "Form Name" format a35 trunc
col time head "Elapsed Time" format a10
col zoom_level head "Zoom Level"
col startedat head "Logon At" format a19
col spid head DBProc format a6
col svrstat head S format a1 trunc
col elapsedtime head "Elapsed Time" format a12
col program format a30 trunc
break on user_name,description,spid,sid,svrstat nodup
accept trgtusr char prompt 'What is the Apps Logon User ID : '
prompt Here are the sessions for user id &trgtusr....
select to_char(l.start_time,'mm/dd/yy hh:mi:ssAM') startedat,
a.time,
u.user_name, u.description, l.SERIAL#,
p.spid, s.sid,s.status svrstat,
s.program
,a.user_form_name
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where u.user_name = upper('&trgtusr')
and s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
and l.start_time = u.last_logon_date
and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
order by to_char(l.start_time,'mm/dd/yy hh:mi:ssAM');
-- group by u.user_name,u.description,s.sid,s.status,p.spid,s.program
-- group by u.user_name,u.description,s.sid,s.status,p.spid,a.time,a.user_form_name
-- order by to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM'),a.time;

break on elapsedtime nodup
prompt Here are the Apps forms currently in use by &trgtusr.....
select a.user_form_name, max(a.time) elapsedtime
from apps.fnd_signon_audit_view a,
v$process p,
applsys.fnd_logins l,
v$session s,
applsys.fnd_user u
where a.pid = p.pid
and p.addr = s.paddr
and a.user_id = u.user_id
and u.user_name = upper('&trgtusr')
and l.user_id = u.user_id
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by a.user_form_name;

/

/* showappsid.sql
show the applications user based on the sid
*/


rem to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM') startedat,
set lines 132
set verify off
col user_name head "Apps Signon" format a12 trunc
col description head "Real Name" format a25 trunc
rem col user_form_name head "Form Name" format a30 trunc
col forminfo head "Form Name" format a40 trunc
col time head "Elapsed Time" format a10
col zoom_level head "Zoom Level"
col startedat head "Logon At" format a19
col lastcallet format a11
accept trgtsid number prompt 'What is the SID : '
select /*+ rule */
to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM') startedat,
a.time,
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "LastCallET",
u.user_name, u.description ,
s.module || ' - ' || a.user_form_name forminfo
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where s.sid = &trgtsid
and s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
-- and l.start_time = u.last_logon_date
-- and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60),
u.user_name, u.description,a.time,s.module || ' - ' || a.user_form_name
order by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),a.time;
/

show current per trans

SELECT s.logon_time, t.start_time, oc.sql_text, s.username "User Name",
s.osuser "OS User", s.status "Status", lockwait "Lock Wait",
s.program "Program", s.logon_time "Connect Time", p.program "P Program",
si.physical_reads "Physical Reads", si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets", si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes", s.process "Process",
p.spid, p.pid, s.serial#, si.sid
FROM sys.v_$sess_io si, sys.v_$session s, sys.v_$process p ,
v$open_cursor oc, v$transaction t
WHERE s.username is not null
AND si.sid(+)=s.sid
AND s.sid = oc.sid
AND p.addr(+)=s.paddr
AND s.saddr(+)= t.ses_addr
ORDER BY s.status, si.consistent_gets+si.block_gets desc
/

prompt Temporary Tablespace Usage in terms of alloacted,used,free space
prompt
column tablespace_name format a10 heading "temporary|tablespace"
column allocated_mb format 99999.99 heading "allocated|in mb"
column used_mb format 99999.99 heading "used|in mb"
column free_mb format 99999.99 heading "free|in mb"
select *
from (select a.tablespace_name
, sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name ) x,
(select sum(b.bytes_used/1024/1024) used_mb
, sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name) y
/


set Head off
column USER_NAME format a10
column RESPONSIBILITY_NAME format a20
spool /oracle/scripts/sysadminresponse.log
SELECT A.USER_NAME , B.RESPONSIBILITY_NAME FROM FND_USER A,FND_RESPONSIBILITY_VL B
WHERE B.RESPONSIBILITY_NAME='System Administrator';
spool off
exit;
/

Oracle Applications Tablespace Model - OATM

Oracle Applications Tablespace Model - OATM

The Oracle Applications Tablespace Model was another long awaited feature that got introduced in 11.5.10.Prior to 11.5.10 by default each of the oracle applications product would have two dedicated tablespace holding the data element and the other for storing the index eg GLD (For General Ledger base tables) and GLX (For indexes relation to the General Ledger product).This easily resulted in some 300 odd tablespaces to manage apart from the system, temp and the rollback tablespaces.

In the new Oracle Applications Tablespace Model (OATM) all these product related tablespaces have been consolidated in two main tablespaces one for holding the base tables and the other for holding the related indexes. Apart from these two tablespace you have an additional ten tablespaces including system tablespace undo tablespace and the temporary tablespace. Thereby reducing the total number of tablespace in the OATM to twelve.

Apart from the obvious ease of management and administration with a reduced number of tablespace being involved the OATM also provides benefits like efficient space utilization. This is achieved by supporting locally managed tablespaces as opposed to the dictionary managed tablespace in the previous model.

OATM also supports uniform extent allocation and auto allocate extent management. In uniform extent management all the extents have the same size and result in less fragmentation. Auto allocate extent management allows the system to determine the extent sizes automatically.

OATM also provides additional benefits when implementing Real Application Clusters (RAC) in Oracle Applications.

Under the OATM the following twelve tablespaces are created as a default.
APPS_TS_TX_DATA - This tablespace hold the translational tables of all Oracle Applications products. For example the GL_JE_HEADERS will be a part of APPS_TX_DATA.
APPS_TS_TX_IDX - All the indexes on the product tables are kept under this tablespace.
APPS_TS_SEED - The seeded data that is setup and reference data tables and indexes form this tablespace. For example your FND_DATABASES table would reside in the APPS_TS_SEED tablespace.
APPS_TS_INTERFACE - All the interface tables are kept in this tablespace for example the GL_INTERFACE table.
APPS_TS_SUMMARY - All objects that record summary information are grouped under this tablespace.
APPS_TS_NOLOGGING - This tablespace contains the materialized views that are not used for summary purposes and other temporary
object that do not require redo log entries.
APPS_TS_QUEUES - With the support for advanced queuing in Oracle Applications, the advanced queue tables and related objects form a part of this tablespace.
APPS_TS_MEDIA - This tablespace holds multimedia objects like graphics sound recordings and spital data.
APPS_TS_ARCHIVE - Tables that are obsolete in the current release of Oracle Applications 11i are stored here. These tables are preserved to maintain backward compatibility of custom programs or migration scripts.
UNDO - The undo tablespace is used as automatic undo management is enabled by default in 11.5.10.This acts as a replacement to red log files.
TEMP - The Temp tablespace is the default temporary tablespace for Oracle Applications.
SYSTEM - This is the SYSTEM tablespace used by the Oracle Database.

For all new installation of 11.5.10 OATM is available as a default install. For prior applications system you can migrate your existing tables spaces to the oracle applications tablespace model.

For this purpose you have the Tablespace Migration Utility(TMU) which is available as a patch (3381489).

The TMU is a Perl based interactive utility that helps in migrating the oracle applications schemas to implement the OATM.

For additional information refer to the Oracle Applications Tablespace Migration Utility User Documentation.

Kill long running forms / inactive forms processes

kill_forms_db_long.txt

#!/bin/ksh
# ============================================================================
# Program kill_forms_db_long.ksh - Kill long running/inactive forms processes
# History:
# Date Who Description
# -------- ------------- -----------------------------------
# 04/09/07 Navdeep Saini Initial Creation
# 04/20/07 Navdeep Saini Hard coded the forms: INVTTMTX, INVTVQOH, INVTOTRX
# Usage:
# kill_forms_db_long.ksh [SID]
# ============================================================================
##############################################################
# userror: echos the usage error line and exits with an exit code of 1
#############################################################
# Set environmental variables
#
command=$(print $0 | awk -F/ '{print $NF }')
typeset -xu ORACLE_SID
LOG=${HOME}/local/log/kill_forms_db_long.log
PW=/usr/local/bin/GetPass.ksh
typeset -x HOSTNAME=$(hostname)
typeset -x DATE=$DATE
function userror
{
print "Usage: $command []"
exit 2
}
#############################################################
# check that applmgr is running this script
#
if [[ $(/usr/bin/whoami) != applmgr ]]; then
echo "You must be applmgr to run this script."
exit 1
fi
############################################################
# if there are no command line parameters the script is in error
#
if [[ $# = 0 ]]; then
print "Please enter the proper number of command line parameters"
userror
fi
ORAENV_ASK=NO
typeset -x ORACLE_SID=$1
. /usr/local/bin/db
. ${PW} system
# Check to see if the SYSTEM password is available
file:///C|/blog/kill_forms_db_long.txt (1 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
if [[ -z ${SYSTEM_PASS} ]]; then
print "${PW} does not have the SYSTEM password for $ORACLE_SID"
userror
fi

#############################################################
#Main
#############################################################
sqlplus -s <system/${SYSTEM_PASS}@${ORACLE_SID}
set lines 200
set head off
column machine format a10
column logon_time format a19
column spid format a10
column process format a10
column module format a18
spool /tmp/kill_forms_db.lst
select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
select 'Following user forms session, inactive for more than 60 min, are killed' from dual;
set head on
set pagesize 1000
SELECT
p.spid,
s.process,
s.status,
s.machine,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et/3600 Last_Call_ET,
s.action,
s.module,
s.sid,
s.serial#
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE' order by logon_time;
spool off
file:///C|/blog/kill_forms_db_long.txt (2 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
set head off
set feedback off
spool /tmp/db_sessions.lst
Select
'alter system kill session '''||s.sid||','||s.serial#||''';'
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE';
spool off
spool /tmp/forms_session.sh
SELECT
'remsh '||s.machine||' kill -7 '||s.process
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE';
spool off
EOF

#cat /tmp/kill_forms_db.lst |awk '{print "alter system kill session ('" $7 "\,'" $8}'
#mail -s "Long running forms sessions in "$HOSTNAME navdeep.saini@echostar.com < /tmp/kill_forms_db.lst

cat /tmp/kill_forms_db.lst >> $LOG

file:///C|/blog/kill_forms_db_long.txt (3 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
cat /tmp/db_sessions.lst >> $LOG
cat /tmp/forms_session.sh >> $LOG
#Check to see if any rows returned and run the kill db and forms
NUMROWS=`cat /tmp/kill_forms_db.lst|grep "no rows selected"`
#echo $NUMROWS
if [[ $NUMROWS != "no rows selected" ]] then
sqlplus -s <system/${SYSTEM_PASS}@${ORACLE_SID}
@/tmp/db_sessions.lst
EOF
#echo "inside if condition"
sh /tmp/forms_session.sh
cat /tmp/kill_forms_db.lst | mailx -s "Ran kill long running forms in "$ORACLE_SID DBATeam@yourdomain.com
fi

Database Important Scripts

Hit Ratios

select Round(100*(cg.value+db.value-pr.value)/(cg.value+db.value),2) "Buffer Hit Ratio"
from v$sysstat db, v$sysstat cg, v$sysstat pr
where db.name = 'db block gets'
and cg.name = 'consistent gets'
and pr.name = 'physical reads'
/

Check the active process

ps -fu applmgr

Kill the Active process

kill -9 `ps -u applmgr -o "pid="`


Clear the whole content and remove all lines inside the file then :

Code:
cat /dev/null > your_file

Total TBS Size

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc
/

Free Space Size

select tablespace_name, bytes/1024/1024 from dba_free_space
/

Last Analyzed

select max(last_analyzed) from dba_tables
/

TEMP TBS Size

select file_name, sum(bytes)/(1024*1024) from dba_temp_files
group by file_name
/

select tablespace_name, sum(bytes)/(1024*1024) TEMPSIZE from dba_temp_files
group by tablespace_name
/

Script: Listing Memory Used By All Sessions

select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/

SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts

select 'INIT.ORA sort_area_size: 'value
from v$parameter
where name like 'sort_area_size'
/

select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')
/

This script lists all jobs that are currently running in the local database.

select
djr.sid sess,
djr.job jid,
dj.log_user subu,
dj.priv_user secd,
dj.what proc,
to_char(djr.last_date,'MM/DD') lsd,
substr(djr.last_sec,1,5) lst,
to_char(djr.this_date,'MM/DD') nrd,
substr(djr.this_sec,1,5) nrt,
djr.failures fail
from
sys.dba_jobs dj,
sys.dba_jobs_running djr
where
djr.job = dj.job
/

Lists all jobs that have been submitted to run in the local database job queue.

select
job jid,
log_user subu,
priv_user secd,
what proc,
to_char(last_date,'MM/DD') lsd,
substr(last_sec,1,5) lst,
to_char(next_date,'MM/DD') nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,'Y','N','Y') ok
from
sys.dba_jobs
/

Compile

for
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;
select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','PACKAGE BODY','VIEW') AND STATUS ='INVALID';
spool off;

for running the script
@c:\dba\compile.sql

Section – A
This scripts is used to take a index rebuild

The following tables we have to recreate it, (FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)


1) This scripts is used to take a index rebuild on following tables
(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)
(spool the following output)

select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'
FROM DBA_INDEXES a
WHERE
a.TABLE_NAME in('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')
and partitioned= 'NO'
union
select 'ALTER INDEX ' a.OWNER '.' b.INDEX_NAME ' REBUILD PARTITION 'b.partition_name' NOLOGGING;'
from dba_indexes a,dba_ind_partitions b
where a.indeX_name = b.index_name
and a.TABLE_NAME in ('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')
and partitioned= 'YES'

2) ALTER TABLE APPLSYS.FND_CONCURRENT_REQUESTS move;
3) ALTER TABLE APPLSYS.FND_FILE_TEMP move;
3) run the index rebuild outuput scripts.
4) run the gather table statistics for mentioned tables
(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)




TABLE

select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'TABLE'
and (extents +1) >= max_extents;

ALTER TABLE .table STORAGE ( MAXEXTENTS x);

where x is greater than max_extents and lesser than unlimited
(2147483645);

ALTER TABLE .table STORAGE ( MAXEXTENTS UNLIMITED);



INDEX

select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'INDEX' and
(extents +1) >= max_extents;



ALTER INDEX .index STORAGE ( MAXEXTENTS integer);

ALTER INDEX .index STORAGE ( MAXEXTENTS UNLIMITED);



Section B

The following script's output is used to run a index rebuild on weekly basis


select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'
FROM DBA_INDEXES a
WHERE OWNER NOT IN ('SYS','SYSTEM')
AND a.INDEX_TYPE='NORMAL'
AND NOT EXISTS (SELECT INDEX_NAME FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER NOT IN ('SYS','SYSTEM')
AND INDEX_NAME = a.INDEX_NAME)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME from dba_tables c
where TEMPORARY='Y'
and a.table_name = c.table_name)
ORDER BY OWNER,INDEX_NAME;
/



for
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;

select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','VIEW') AND STATUS ='INVALID';
spool off;
for running the script
select 'alter ' 'PACKAGE ' OBJECT_NAME ' compile body' ';' from dba_objects where object_type in ('PACKAGE BODY') AND STATUS ='INVALID';
/



spool runts.sql
select 'alter database datafile '''file_name''''' autoextend on;' from dba_data_files;
/
@runts



The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:


select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Putting it all together into a single query:

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c
/

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')

/


SESSION_WAITS

SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
/


To select the username and the process status

select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224


selecta.requested_start_date,a.last_update_date,a.status_code,b.user_name ,a.argument_text from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224


To select the username,process,status,Terminal name using SID

select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c,v$process p
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and p.addr = a.paddr
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
/


To see the currently updated archive log files

SQL>select name from v$archived_log where trunc(completion_time) >= trunc(sysdate)-5;

To find the BDUMP,UDUMP directory

select value from v$parameter where name = 'background_dump_dest'
select value from v$parameter where name = 'user_dump_dest'
select value from v$parameter where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest')
/


Identify the user and session ID for a UNIX process

This quick process identifies the Oracle user and session ID of a UNIX process that is using up a large amount of CPU. It can also be used to help find inefficient queries. This query is placed inside of a script that I pass the UNIX process ID to.

select s.sid, s.username, s.osuser, s.serial#
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = &1;
/

Tracing an Oracle session by SID

This code accepts an Oracle session ID [SID] as a parameter and will show you what SQL statement is running in that session and what event the session is waiting for. You simply create a SQL file of the code and run it from the SQL prompt.

prompt Showing running sql statements ...........................

select addr from v$process where spid='8419'

select * from v$session where PADDR='00000003B29F9388'


select a.sid Current_SID, a.last_call_et ,b.sql_text
from v$session a
,v$sqltext b
where a.sid = 14
and a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by a.last_call_et,a.sid,b.piece;

prompt Showing what sql statement is doing.....................

select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,
d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
/

Check all active processes, the latest SQL, and the SQL hit ratio

select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;

Monitoring Oracle processes

select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr
order by s.status,1;

/


Displays concurrent requests that have run times longer than one hour (3600 seconds)

SELECT REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') starttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
OUTCOME_CODE,phase_code,status_code,
printer,print_style,description,
SUBSTR(completion_text,1,20) compl_txt
FROM fnd_concurrent_requests
WHERE to_date(ACTUAL_START_DATE,'DD-MON-RRRR') = to_date(sysdate,'DD-
MON-RRRR')
ORDER BY 2 desc
/

This script will map concurrent manager process information about current concurrent managers.

SELECT proc.concurrent_process_id concproc,
SUBSTR(proc.os_process_id,1,6) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(vproc.spid,1,10) svrproc,
DECODE(proc.process_status_code,'A','Active',
proc.process_status_code) cstat,
SUBSTR(concq.concurrent_queue_name,1,30) qnam,
-- SUBSTR(proc.logfile_name,1,20) lnam,
SUBSTR(proc.node_name,1,10) nnam,
SUBSTR(proc.db_name,1,8) dbnam,
SUBSTR(proc.db_instance,1,8) dbinst,
SUBSTR(vsess.username,1,10) dbuser
FROM fnd_concurrent_processes proc,
fnd_concurrent_queues concq,
v$process vproc,
v$session vsess
WHERE proc.process_status_code = 'A'
AND proc.queue_application_id = concq.application_id
AND proc.concurrent_queue_id = concq.concurrent_queue_id
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+)
ORDER BY proc.queue_application_id,
proc.concurrent_queue_id

Show currently running concurrent requests

SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);
/

To find the CPU consumption

select ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time, w.seq#, q.sql_text,command
from
v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
and se.paddr=p.addr
and se.sql_address=q.address
order by ss.value desc,ss.sid
/

Script to show problem tablespaces

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc


Oracle space monitoring scripts table space wise
This scripts gives warning indicator for all tablespaces that have less then 90% free space in them (with an asterisk in the last column).


select tbs.tablespace_name,
tot.bytes/(1024*1024) "Total Space in MB",
round(tot.bytes/(1024*1024)- sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) Pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1 ;


Oracle space monitoring scripts (grand total table space)

select
sum(tot.bytes/(1024 *1024))"Total size",
sum(tot.bytes/(1024*1024)-sum(nvl(fre.bytes,0))/(1024*1024)) Used,
sum(sum(nvl(fre.bytes,0))/(1024*1024)) Free,
sum((1-sum(nvl(fre.bytes,0))/tot.bytes)*100) Pct
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
/


What's holding up the system?

Poorly written SQL is another big problem. Use the following SQL to determine the UNIX pid:

Select
p.pid, s.sid, s.serial#,s.status, s.machine,s.osuser, p.spid, t.sql_text
From
v$session s,
v$sqltext t,
v$process p
Where
s.sql_address = t.address and
s.paddr = p.addr and
s.sql_hash_value = t.hash_value and
s.sid > 7 and
s.audsid != userenv ('SESSIONID')
Order By s.status,s.sid, s.osuser, s.process, t.piece ;
/

Script to display status of all the Concurrent Managers
select distinct Concurrent_Process_Id CpId, PID Opid,
Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,
P.process_status_code Status,
To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
where Q.Application_Id = Queue_Application_ID
and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
and Spid = Os_Process_ID
and Process_Status_Code not in ('K','S')
order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name

Get current SQL from SGA

select sql_text
from V$session s , V$sqltext t
where s.sql_address=t.address
and sid=
order by piece;

You can find the SID from V$session.

What SQL is running and who is running it?


select a.sid,a.serial#,a.username,b.sql_text
from v$session a,v$sqltext b
where a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by 1,2,b.piece;

---
select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",
s.sid "Session ID",s.status "Status",
s.username "Username", RTRIM(s.osuser) "OS User",
b.spid "OS Process ID",s.machine "Machine Name",
s.program "Program",c.sql_text "SQL text"
from v$session s, v$session_longops l,v$process b,
(select address,sql_text from v$sqltext where piece=0) c
where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
group by s.sid,s.status,s.username,s.osuser,s.machine,
s.program,b.spid, b.pid, c.sql_text order by s.status,s.sid

TO FIND THE SORTING DETAILS

SELECT a.sid,a.value,b.name from
V$SESSTAT a, V$STATNAME b
WHERE a.statistic#=b.statistic#
AND b.name LIKE 'sort%'
ORDER BY 1;
/

Long running SQL statements

SELECT s.rows_processed, s.loads, s.executions, s.buffer_gets,
s.disk_reads, t.sql_text,s.module, s.ACTION
FROM v$sql /*area*/ s,
v$sqltext t
WHERE s.address = t.address
AND ((buffer_gets > 10000000) or
(disk_reads > 1000000) or
(executions > 1000000))
ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece
/
Move a table from one tablespace to another

There are many ways to move a table from one tablespace to another. For example, you can create a duplicate table with dup_tab as select * from original_tab; drop the original table and rename the duplicate table as the original one.

The second option is exp table, drop it from the database and import it back. The third option (which is the one I am most interested in) is as follows.

Suppose you have a dept table in owner scott in the system tablespace and you want to move in Test tablespace.

connect as sys

SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT SYSTEM

Elapsed: 00:00:00.50

You want to move DEPT table from system to say test tablespace.

SQL :> connect scott/tiger
Connected.
SQL :> alter table DEPT move tablespace TEST;

Table altered.


SQL :> connect
Enter user-name: sys
Enter password:
Connected.
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT TEST
/


To compile the procedure

Alter PROCEDURE JA_IN_BULK_PO_QUOTATION_TAXES compile

To compile the form

F60gen userid=apps/metroapps@dev module=

.fmb
output_file=/forms/US/form name.fmx
module_type=form batch=no compile_all=special




/

Run this sql statement to get package version :

select text from user_source where name='&package_name'
and text like '%$Header%';

prompt asks you the package name, in return it gives you two lines
corresponding to specifications and body creation files

You can also get pls version on database by running:

select name , text
from dba_source
where text like '%.pls%'
and line <>views

Sometimes version information is available in view definition.
Try the following sql statement :

col TEXT for a40 head "TEXT"
select VIEW_NAME, TEXT
from USER_VIEWS
where VIEW_NAME = '&VIEW_NAME';

workflow

Run wfver.sql (see §5.e) to get version of workflow packages and views.




Finding active and inactive sessions :


set linesize 132
col started format a15
col osuser format a10
col username format a13
col sid format 99999
select d.physical_reads, p.spid,a.sid, a.serial#, a.username, a.osuser,
TO_CHAR(a.logon_time, 'MM/DD HH24:MI') started, a.sql_hash_value,status
from sys.v_$session a, sys.v_$process p, sys.v_$sess_io d
where a.sid = d.sid and a.paddr = p.addr and a.type <> 'BACKGROUND'
and a.status = 'INACTIVE'
order by a.username,a.logon_time




Select 'alter system kill session '''sid','serial#''';' from
V$session where status='INACTIVE';

select p.spid,s.status,s.username,s.machine,s.sid,s.serial#,s.program,
s.osuser,s.sql_address from v$process p,v$session s
where s.paddr = p.addr and s.sid in(&sid)



Finding Locks


select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc

Finding Blocking sessions :

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0
and l1.id1=l2.id1 and l1.id2=l2.id2

select s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;



-- sessions with highest CPU consumption

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;


-- sessions with the highest time for a certain wait

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

-- sessions with highest DB Time usage

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;

db file scattered read
Systemwide Waits:
If the TIME spent waiting for multiblock reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at where BLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).


It can also be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions may be worth tracing:


SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file scattered read' and total_waits>0 ORDER BY 3,2;


One can also look at:
Statements with high DISK_READS in Sessions with high table scans blocks gotten in

db file sequential read


Systemwide Waits:IO is a normal activity so you are really interested in unnecessary or slow IO activity. If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the "Tablespace IO" and "File IO" sections of the ESTAT or STATSPACK reports to get information on which tablespaces / files are servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at .

It can also be useful to see which sessions are performing reads and trace them to see if the IOs are expected or not. This statement can be used to see which sessions may be worth tracing:


SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file sequential read' and total_waits>0 ORDER BY 3,2;


One can also look at:
Statements with high DISK_READS in Sessions with high "physical reads" in


Undo Tablespace Check

set linesize 150
col username format a18
col sid format 99999
col object_name format a18
select s.username,s.sid,rn.name,rs.rssize/1024/1024 "UsedSize",rs.status,t.used_ublk,t.used_urec,do.object_name
from V$TRANSACTION t,V$SESSION s,V$ROLLNAME rn,V$ROLLSTAT rs,V$LOCKED_OBJECT lo,DBA_OBJECTS do
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
and t.xidusn = lo.xidusn(+)
and do.object_id = lo.object_id;

Temp Tablespace Check

prompt
prompt +----------------------------------------------------+
prompt TEMP TABLESPACE USAGE BY SESSION
prompt +----------------------------------------------------+

--Temp TS usage by each session:

select b.tablespace
,a.sid,
sum(round(((b.blocks*p.value)/1024/1024),2)) size_mb
from v$session a
,v$sort_usage b
,v$process c
,v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr and
a.paddr=c.addr
group by b.tablespace,a.sid
order by sum(round(((b.blocks*p.value)/1024/1024),2)) desc






Extract the DDL Scripts for the existing database links:


SELECT
'create 'DECODE(U.NAME,'PUBLIC','public ')'database link 'CHR(10)
DECODE(U.NAME,'PUBLIC',Null, U.NAME'.') L.NAMEchr(10)
'connect to ' L.USERID ' identified by '''
L.PASSWORD''' using ''' L.host ''''
chr(10)';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;



Please change the oracle directories appropriately:

select 'create or replace directory 'OWNER'.'DIRECTORY_NAME ' as ' ''''DIRECTORY_PATH''''';' from DBA_DIRECTORIES;

How to check database bit like 32bit or 64bit

SELECT distinct('This is a ' (length(addr)*4) '-bit database') "WordSize" FROM v$process;
select PLATFORM_ID, PLATFORM_NAME from v$database;


Check the PROFILE OPTIONS VALUE

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
‘10001′, ‘SITE’,
‘10002′, ‘APP’,
‘10003′, ‘RESP’,
‘10005′, ‘SERVER’,
‘10006′, ‘ORG’,
‘10004′, ‘USER’, ‘???’) "LEV",
decode(to_char(pov.level_id),
‘10001′, ”,
‘10002′, app.application_short_name,
‘10003′, rsp.responsibility_key,
‘10005′, svr.node_name,
‘10006′, org.name,
‘10004′, usr.user_name,
‘???’) "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE ‘%&&profile%’
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
AND decode(to_char(pov.level_id),
‘10001′, ”,
‘10002′, app.application_short_name,
‘10003′, rsp.responsibility_key,
‘10005′, svr.node_name,
‘10006′, org.name,
‘10004′, usr.user_name,
ORDER BY "NAME", pov.level_id, "VALUE";



***************


SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE 'ICX_FORMS_LAUNCHER'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value


*********

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE '%&&profile%'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
AND decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') LIKE '%&&username%'
ORDER BY "NAME", pov.level_id, "VALUE";

Find Concurrent manager for a particular concurrent request


select
c.user_name,
a.Request_id,
to_char(a.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start",
decode(a.status_code,
'A','Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'P', 'Scheduled',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') status_code,
b.user_concurrent_queue_name' - 'b.target_node "queue_name",
a.user_concurrent_program_name
from fnd_concurrent_worker_requests a,
fnd_concurrent_queues_vl b,
fnd_user c
where a.concurrent_queue_id=b.concurrent_queue_id
and a.phase_code='R'
and a.requested_by=c.user_id
and c.user_name='RMANI'
Order by 5;

Here is the list of Important oracle Apps Scripts

Here is the list of few important tables.
Concurrent Manager


FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND

FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD / Patches

AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

/* To find the latest application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

/* 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 - for Applications DBA
GL - for General Ledger
PO - Purchase Order



Where is Concurrent Manager Node ? If you don't know where is your CM here are few ways to find your it .

SQLPLUS> select node_name from apps.fnd_nodes where support_cp='Y';


Here I am listing Unix/Linux commnads which might be useful while troubleshooting Oracle Apps.


Enable Trace on any Executable to find out whats happening at O.S. Level

truss -eafl -o output.trc -o truss.txt
for example for Apache
truss -eafl -o output.trc -o truss.txt apachectl

This command will trace any system calls and will help you to find out errors.



How to find a "word" or pattern in all files in a directory & subdirectories

find . -name "*" -exec grep -l {} \; -print
for example I want to search for word oracle
find . -name "*" -exec grep -l oracle {} \; -print



How to delete files older than N number of days , Useful to delete old log files

find . -name '*.*' -mtime + -exec rm {} \;
for example if I want to delete all files older than 7 days
find . -name '*.*' -mtime +7 -exec rm {} \;
*Check carefully & run it first from logs or dump directory


How to find a class inside a set of Jar files


for i in 'find .-name *.jar'
do
if test 'jar -tvf $i|grep QualityObject.class'
then
ls $i
fi
done


How to find the symbolic links that point to the old path in your oracle_home and appl_top.

This command is useful in cloning after restore from source to target that symbolic link are not pointing to source.

ls -al `find . -type l` | grep $OLD_PATH



To find all the text files that contains the old path at the UNIX level.

find . -type f -print|grep -v ":"|xargs file|grep "text"|awk '{FS=":"}{print $1}'|xargs grep -il $OLD_PATH



How to Sort files based on Size of file in a Directory
Useful in finding out spaces issues


ls -l | sort -nrk 5 | more


How to check if a Port is listening for any Service

netstat -an | grep $PORTNO

How to schedule a Job in Unix

Use cronjob
crontab -l ( list current jobs in cron)
crontab -e ( edit current jobs in cron )
_1_ _2_ _3_ _4_ _5_ $Job_Name
1 - Minutes (0-59)
2 - Hours ( 0-24)
3 - day of month ( 1- 31 )
4 - Month ( 1-12)
5 - A day of week ( 0- 6 ) 0 -> sunday 1-> monday
e.g. 0 0 1 * 5 Means run job at Midnight on 1st of month & every friday



Profile Options Useful for Oracle Apps DBA

Here is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options, it depends on your implemnetation. I am going going to update more about Profile Options.

Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text


Below you will find commonly asked questions about various component Version in Oracle Applications & way to find them which might be helpful in Apps DBA Job. Most of these are for Unix / Linux.


Q: How to find Oracle Applications Web Server Version ?
Q: How to find Version of Apache used with oracle apps 11i ?


Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command
./httpd -version
You will see output like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19


Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server

Above is output If you have installed 10g Application Server with 11i


Q: How to find Jinitiator Version ?

Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin defined by environment variable FORMS60_WEB_CONFIG_FILE & search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ; if your version is 1.3.1.18 you will see entry like 1,3,1,18


Q: How to find Forms Version in 11i ?

Login to forms from frontend , on top menu bar of forms click on "Help" & Select "About Oracle Applications" go to "Forms Server " section. You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 . If you want to know whats your forms patchset level then subtract 9 from fourth digit which means for above case form patchset 17 is applied.


Q: How to find Forms Version in Apps from command Line ?

Enter "f60gen" on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit - 9)


Q: How to find Version of any file in Oracle Apps 11i ? or
Q: How to find any Reports Version 11i ? or


In Oracle Applications under ad utilities there is utility called as adident Used for Identification purpose or to find out file version use
adident Header
for ex. inorder to to find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp. Similarly you can use adident to find version of any report in 11i.


Q: How to find Operation System Version (Unix/Linux) ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?

For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like


64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications


Q: How to find Operating System version ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?
For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like


64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications



Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.

Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?


execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1


Q. How to find Version of Apps 11i ?

Run following SQL from apps user

SQL> select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
-----------------------
11.5.10.2
Which means you are on Apps Version 11.5.10.2

Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.


Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?

execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1
Now you know what should be bit of patch to download



Q How to Discoverer Version installed with Apps ?

Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i. To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute
strings dis4ws | grep -i 'discoverer version'
You should see output like
Discoverer Version:Session 4.1.47.09.00

Q. How to find Workflow Version embedded in Apps 11i ?

Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0


You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.


Q: How to find version of JDK Installed on Apps ?

There might be multiple JDK installed on Operating System . Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var="s_jdktop" what so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like

java version "1.4.2_10"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)
Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)