Session Trace

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

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

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

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

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

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

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

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

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

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

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

unix level performane check

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

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

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

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



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

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

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

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

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

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

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

Important metalink

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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