Transcript errorstack_shortstack

Errorstack –Shortstack
By Rishi Raj Srivastava (Oracle Certified Master)
Download this from geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
About Me
• My name is Rishi , I have 11 years of DBA experience.
• An Oracle Certified Master
• My key skill sets are AWS, Dataguard, Golden Gate, Performance
Tuning , RAC
• I have worked as Country SME in IBM for Oracle technology.
• Worked for Metalink Support in performance and core database
team.
• Current Employer HPE – SME database Technology
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
After this session you should be able to
understand.
• Define Errorstack and short_stack.
• When to use Errorstack and short_stack.
• How to enable the Errorstack and short_stack.
• How to disable the Errorstack.
• How to read the Errorstack trace file (Only Relevant)
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Errorstack – What and Why
• A triggering event/ Debugging Tool
• To generate the trace file
• To know the failing SQL
• To extract the information about database user
• To get the call stack or failing functions.
• To know the bind values.
• To know if Oracle is looping in its code.
geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Errorstack – What and Why ………….Continue
• Any Error where you need more information about session... Like 942,
1031 etc.
• Any Session which is hanging e.g. Drop user, drop table etc.
• Any single process taking huge amount of CPU, to confirm if its
spinning
geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
How to Set The Errorstack
• Alter system set events ‘XXX trace name errorstack, level 3’;
• In Parameter file..
event=’XXX trace name errorstack, level 3′
• Alter session set events ‘XXX trace name errorstack level 3’;
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Continue ..
If You need errorstack for any existing session with the help of
oradebug.
connect / as sysdba
oradebug setospid <<OS ID>>
Oradebug errorstack level 3;
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Trace File Analysis & Important Sections..
• Header of Trace file
Dump file /ora/admin/ABCDEFGH/udump/YYYY_ora_2851.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
ORACLE_HOME = /ora/product/v102 <<Oracle Home>>
System name: SunOS <<OS Detail >>
Node name: XXXXXX <<Machine Name>>
Release: 5.10 <<OS VERSION>>
Version: Generic_147440-05
Machine: sun4u
Instance name: YYYYY
Rishi Raj Srivastava (Oracle Certified Master)
geopdata.com
[email protected]
Continue …
• To find the current sql , you need to search with below keyword in
trace file
“Current SQL statement for this session”
• Another important Section to check is “Call Stack Trace”.
geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Continue …
• Session/Process Information:
To know who the running is the failing statement, search the trace file with
“(session” keyword. It takes you to the piece of trace where we get the session
detail.
Process Group: DEFAULT, pseudo proc: 3bc7fbd80
O/S info: user: oracle, term: pts/1, ospid: 2851
OSD pid info: Unix process pid: 2851, image: oracle@CLIENT_MACHINE(TNS V1-V3)
—————————————SO: 3bb33b428, type: 4, owner: 3bc523d90, flag: INIT/-/-/0x00
(session) sid: 1984 trans: 0, creator: 3bc523d90, flag: (41) USR/- BSY/-/-/-/-/DID: 0001-002E-00005D31, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 3, prv: 0, sql: 3ab153708, psql: 3bd6647c8, user: 0/SYS
service name: SYS$USERS
O/S info: user: oracle, term: pts/1, ospid: 2802, machine: XXXXXX
program: sqlplus@XXXXXX (TNS V1-V3)
application name: sqlplus@XXXXXX (TNS V1-V3), hash value=4195484827
OTHER SECTIONS
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Continue …
• Bind Values – To find the bind values below keywords are useful
1.Search the “Current Cursor” and find the cursor#
2.Take the cursor number and search “Cursor#Num”
3.Search the “Bind Info” in same cursor
• Plan Table - Search with the keyword “Current Cursor”
or “Plan Table”
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Shortstack
• Is quick way to get the ‘call stack tree’
SQL>Connect / as sysdba
SQL> oradebug setospid XXXX
SQL> oradebug short_stack
Wait for 1 minute and run again
SQL> oradebug short_stack
Wait for 1 minute and run again
SQL> oradebug short_stack
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Shortstack Continue …
• Do you see same call stack in all run ?
• Oracle Support will give you direct hit
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
FAQ – A Quick Recap
• Do I need to pay extra for this tracing ?
• How to find out SPID from trace file.
• How to know the detail of end users who is generating error ?
• How to know failing query ?
• Will it impact database performance if I enable Errorstack ?
• Trace file is text or binary ?
• Why Shortstack ?
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Any Questions ??
geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]
Thank You !!!
Have more questions ??
91-9986076730 [email protected]
GEOPDATA.COM
Geopdata.com
Rishi Raj Srivastava (Oracle Certified Master)
[email protected]