Oracle warning, trace file (10046, deadlock, etc.)

Trace files are generated by each background process, and key actions are logged in the alert log, including:
• All start and close commands, including intermediate commands, such as ALTER DATABASE mount
• All internal errors for the instance (ORA-600 error, can only be reported to Oracle support solution)
• Any detected data file block corruption
• Any deadlocks that have occurred
• All operations that affect the physical structure of the database, such as creating or renaming data files and online redo logs
• Alter system command to adjust internal parameter values
• All log switches and log archive files

Take Oracle deadlock as an example:

Determine the storage directory for the warning log file

Sql>show parameter Dump_dest

The detailed TRC logs are as follows:

2015-12-15 08:47:31.756DEADLOCK detected (ora-00060 ) [Transaction Deadlock]The following deadlock is not a ORACLE error. It is a adeadlock due to user error in the design of a applicationor from issuing incorrect Ad-hoc SQL. The followinginformation may aid in determining the Deadlock:deadlock graph:---------blocker (s)--- --------------Waiter (s)---------Resource Name Process session holds waits process session holds waitstx-000a00 0D-0009FDF2745 X 34 817xtx-000b0012-00064744 817 X 31 745X Session745:did 0001-001f-001ba3cc Session 817:did 0001-0022-000E48FD Session817:did 0001-0022-000E48FD Session 745:did 0001-001f-001ba3cc Rows waited on:session745:obj-rowid =0001b049-aaabbjaahaaen4saaf (Dictionary Objn-110665, File-7, block-1105426, slot-5) Session817:obj-rowid =0001b049-aaabbjaahaaen4saae (Dictionary Objn-110665, File-7, block-1105426, slot-4)-----Information for the other waiting sessions-----Session817: Sid:817 ser:32301 audsid:7129480 user:93/lc019999 Flags: (0x100045) usr/-flags_idl: (0x1) bsy/-/-/-/-/-flags2: (0x40009)-/-/inc pid:O/S Info:user:SYSTEM, term:perftestdb66, ospid:21068image:oracle. EXE (SHAD) client details:o/s info:user:Administrator, Term:wangzg-inspur, ospid:2736:11836Machine:workgroup\wangzg-inspur program:plsqldev.exe application Name:pl/sql Developer, Hash value=1190136663Action Name:sql window-New, Hash value=2127054360Current sql:update tablenameset name=' tkk1215 ' where xxxxxxxxxxxxxxx-----End of information for the other waiting sessions-----Information for this session:-----Current SQL Stateme NT for this session (SQL_ID=01yxw4cmbqgg3)-----update Tablenameset name =' xyy1215 ' where xxxxxxx

===================================================

SQL statement Tracking
1. User-level self-tracking
Alter session set Sql_trace=true;
Executing the SQL statement in the same session will result in a trace file sid_ora_spid.trc (the SPID is the system process ID):
/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_spid.trc
SPID: The system process ID, which can be viewed by the statement:
Select S.username,s.sid,s.serial#,p.spid,s.program
From V$session s,v$process p
where s.paddr = P.addr
and S.sid = Userenv (' Sid ');

Alter session set Sql_trace=false;

2. System-Level tracking
Alter system set SQL_TRACE=TRUE scope=memory;
Executes the SQL statement in any session, producing the trace file Sid_ora_spid.trc (SPID is the system process ID):
/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_spid.trc

Alter system set SQL_TRACE=FALSE scope=memory;

3. User-level DBA tracking
Using Oracle's PL/SQL package Dbms_system
(1) identify the SID and serial# of the user from the session Performance view.

    Select S.username,s.sid,s.serial#,p.spid,s.program from       v$session s,v$process p      where s.paddr = P.addr         And s.username is not NULL         

USERNAME SID serial# SPID Program
------------------------------ ---------- ---------- ------------------------ -----------------------------
SYSTEM 35114 9172 Plsqldev.exe
SYSTEM 248 32295 9225 Plsqldev.exe
SYS 36202 9111 [email protected] (TNS v1-v3)
(2) Tracking user sessions
Execute sys.dbms_system.set_sql_trace_in_session (248,32295,true);
Execute the SQL statement in any session with ID 248, generating the trace file Sid_ora_spid.trc (the SPID is the system process ID):
/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_9225.trc
(3) Stop tracking
Execute sys.dbms_system.set_sql_trace_in_session (248,32295,true);

4.sql_trace Tracking SQL Statement execution
• Parse, execute, and fetch data counts
· CPU time and elapsed time
• Physical Read and logical reading
• Number of rows processed
• The user name that was parsed
• The case of each commit and rollback.

5. Format the trace file (tkprof)
Use the TKPROF program to convert the trace file to a readable text file, tkprof syntax:
Tkprof tracefile outputfile [Explain=user/password] [Table=schema.tablename]
[Print=integer] [Insert=] [sys=] [Sort=] ...
$ tkprof tracefile outputfile

Oracle warning, trace file (10046, deadlock, etc.)