Transcript Troubleshooting 11i issues

Troubleshooting 11i issues
Adam Janbolat
www.dba4aday.com
[email protected]
DBA JOKE
• No one really knows what the Database
Administrator does, and no one is smart
enough to know if the DBA is doing it or not.
But every shop must have one DBA, because
no place can afford two of them.
Different issues with each user
• A user needs a trace file to upload to support
• A user is waiting on a payroll process that should not
take more than 10 min but it has been running for three
hours
• A user heard someone talk about locking issues and now
they have locking issues!
• A user is getting error ORA-1652: unable to extend temp
segment when running his/her code from sqlplus
• Your manager says the application is running slow; can
you improve performance
• A developer wants you to tune her code because it is a
dba thing to do
Overview
•
•
•
•
•
Tracing application forms
Tracing database processes
Tracing concurrent requests
Locking issues
Temp space issues
Diagnostic Tools
• Enterprise Manager
• unix/sql scripts
MIS:
DEMO
Tracing Forms
• Verify diagnostics is turned on in the Help
menu.
• You can turn it on for any user by setting
this profile option to No:
Hide Diagnostics menu entry
Turn diagnostics on for user
BROWN
Set the profile option to No
Tracing forms
Tracing forms
MIS:
DEMO
Tracing forms
• The form will produce a trace file in the
udump directory.
• The trace file will be ora_173xxx.trc
• Use tkprof on the trace file:
tkprof ora_173.trc ora_173.out explain=apps/password sort=prsela
• Which users have trace enabled:
Who has diagnostics privs
Run this query to find who has diagnostics privs:
select a.user_name
from fnd_user a,
fnd_profile_option_values b
where a.user_id=b.level_value and
b.profile_option_id=3214 and
b.profile_option_value='N' and
b.level_id= 10004
Tracing database processes
• Identify the process
• Trace the process
• Trace file will end up in the udump
directory
• Use tkprof on the trace file
Tracing concurrent request
• Find out the request_id
• Find out if the process has child processes
• Use this query to find which process it is:
select oracle_process_id from
fnd_concurrent_requests where
request_id=941206
• Oracle_process_id corresponds to the
spid in OEM.