首页 » ORACLE 9i-23ai » Debug oracle 常用的一些trace命令

Debug oracle 常用的一些trace命令

当oracle遇到问题时, 当表面的现象和现有的log无法为我们诊断问题提供足够的信息时, 希望可能通过打开oracle 的debug开关,生成更详细的trace 文件提供更多的信息, 这里整理了一些trace的命令.

列出数据库的事件列表

SET SERVEROUTPUT ON

DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/

http://www.anbob.com/archives/1620.html

RMAN
To enable tracing is RMAN, you have to switch rman is “debug” mode.

RMAN> debug on
or
[oracle@anbob:/export/home/oracle]# rman target / debug trace=tracefile.trc

DGMGRL
Dgmgrl can be switched to debug mode to get more information about a failed command.

[oracle@anbob:/export/home/oracle]# dgmgrl -debug sys/oracle@primdb

EXPDP / IMPDP
To trace a datapump process, you can do it at different levels, How to create a Data Pump trace file ? Parameter: TRACE , more refer Doc ID 286496.1

expdp system/oracle directory=dir1 dumpfile=tt.dmp logfile=tt.log tables=ANBOB.TT  trace=FFF0300

-- Summary of Data Pump trace levels:
-- ==================================

  Trace   DM   DW  ORA  Lines
  level  trc  trc  trc     in
  (hex) file file file  trace                                         Purpose
------- ---- ---- ---- ------ -----------------------------------------------
  10300    x    x    x  SHDW: To trace the Shadow process (API) (expdp/impdp)
  20300    x    x    x  KUPV: To trace Fixed table
  40300    x    x    x  'div' To trace Process services
  80300    x            KUPM: To trace Master Control Process (MCP)      (DM)
 100300    x    x       KUPF: To trace File Manager
 200300    x    x    x  KUPC: To trace Queue services
 400300         x       KUPW: To trace Worker process(es)                (DW)
 800300         x       KUPD: To trace Data Package
1000300         x       META: To trace Metadata Package
------- 'Bit AND'
1FF0300    x    x    x  'all' To trace all components          (full tracing)

SRVCTL
srvctl is the program that control clusterware resources.If srvctl reports error, the following SRVM tracing can be turned on before executing the srvctl command:

$ script /tmp/out.1
$ SRVM_TRACE=true
$ export SRVM_TRACE
$ $RESOURCE_HOME/bin/srvctl <command> <RESOURCE_TYPE> <option>
$ exit
turn off trace
unset SRVM_TRACE

OPATCH
This variable is OPATCH_DEBUG and it has to be set to TRUE

[oracle@anbob:/export/home/oracle]# export OPATCH_DEBUG=TRUE
[oracle@anbob:/export/home/oracle]# $ORACLE_HOME/OPatch/opatch lsinv

Oracle Kernel Tracing
Oracle Kernel tracing can be done by enabling some events.Event is a number between 10000 and 10999. The most popular events are 10046 (SQL Trace) and 10053 (CBO debug trace), As of Oracle11g, a new event syntax has been introduced, which makes it easier to perform tracing of processes. This can be used especially for environments such as Datapump, where multiple processes are started and the lifetime of these processes is volatile. you can use “oradebug doc” get detail .

SQL TRACE
TO diag SQL performance problems

ALTER SESSION SET sql_trace=true;
or 
oradebug event 10046 trace name context forever ,level N;
or
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(:sid, :serial#, true);
or
EXEC DBMS_SUPPORT.START_TRACE_IN_SESSION(:sid, :serial, true, true);
or
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true)

11g Events++ Syntax:
alter system set events 'sql_trace {process : ospid = 2345} level=12';

TRACE SQL CBO
We can capture a 10053 trace for a specific sql_id that way:

alter system set events ‘trace[RDBMS.SQL_Optimizer.*][sql:<YOUR_SQL_ID>]’;
or
execute DBMS_SQLDIAG.DUMP_TRACE(-
    p_sql_id=>'cjk13xfm8ybh7', -
    p_child_number=>0, -
    p_component=>'Optimizer', -
    p_file_id=>'TRACE_10053');
or
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

HANGANALYZE dump
When a database has severe performance problems, the cause of the problem may be a HANG. Using trace files produced by HANGANALYZE, you will be able to quickly determine if two or more processes are deadlocked and how many other processes may be affected.The “HANGANALYZE” command is available since Oracle Release 8.1.6. In Oracle9i it was enhanced to provide “cluster wide” information in Real Application Cluster (RAC) environments on a single sho
HANGANALYZE may be executed using the following syntax:

ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level <level>';

Systemstate Dump
a method of capturing a systemstate dump using the Linux utility ‘gdb’.? While this method can be used to capture a trace of any process, this is especially useful when a connection to the database cannot be made.\

ps -ef | grep? orcl (LOCAL=NO)
gdb $ORACLE_HOME/bin/oracle <above pid>
(gdb) print ksudss(10)
Review the trace file which is found in the user_dump_dest directory
or

SQL>oradebug dump systemstate <level>
 --for RAC
SQL>oradebug -g all dump systemstate 266

http://www.anbob.com/archives/2132.html or http://www.anbob.com/archives/1912.html

ERRORSTACK dump
Errorstack tracefiles are very useful for troubleshooting ORA-600’s, crashes, hangs and even just bad performance.

oradebug dump errorstack 3
or
oradebug event 942 trace name errorstack level 3

Tracing Parallel Execution

alter session set "_px_trace"=[[Verbosity,]area],[[Verbosity,]area],..,[time];
Verbosity:
high
medium
low
Area:
scheduling - ( equivalent to some of event 10384 and some of 10390)
execution - (equivalent to some of event 10390)
granule - (equivalent to some of event 10390 and some of 10391)
messaging - (equivalent to event 10392 and event 10393)
buffer - (equivalent to event 10399)
compilation - ( no equivalent event)
all - all of the above
none - none of the above.
Timing
time

as example
alter session set "_px_trace"=high,execution,medium,execution,time;
or
alter session set "_px_trace"="compilation","execution","messaging";

To switch off the trace :
SQL> alter session set "_px_trace"="none";

Enabling Debugging for Oracle Clusterware Resources
You can enable debugging for Oracle Clusterware resources by issuing crsctl set log and crsctl set trace commands, using the following syntax:

crsctl set {log | trace} resource "resource_name=debugging_level"
resource_name—The name of the resource to debug.
debugging_level—A number from 1 to 5 to indicate the level of detail you want the debug command to return.
e.g.
crsctl set log gpnp “GPNP=5”

Enable component-level debugging

You can enable debugging for the CRS, CSS, and EVM modules and their components, and for resources, by setting environment variables or by issuing crsctl set log commands, using the following syntax:

crsctl set {log | trace} module_name "component:debugging_level[,component:debugging_level][…]"
e.g.
To set debugging levels on specific cluster nodes, include the -nodelist keyword and the names of the nodes, as follows:
crsctl set log crs "CRSRTI:1,CRSCOMM:2" -nodelist node1,node2

Enable debug gpnp GPNP_TRACELEVEL
In order to get more log and trace information there is a tracing environment variable GPNP_TRACELEVEL which range is from [0-6].

export GPNP_TRACELEVEL=6

DBMS_STATS Tracing

After some diligent searching, I found that dbms_stats has its own built in tracing routines.

Tracing is enabled by calling dbms_stats.set_global_prefs(‘trace’,<trace flags>)
Following are the possible values for the trace flags:
1 = use dbms_output.put_line instead of writing into trace file
2 = enable dbms_stat trace only at session level
4 = trace table stats
8 = trace index stats
16 = trace column stats
32 = trace auto stats – logs to sys.stats_target$_log
64 = trace scaling
128 = dump backtrace on error
256 = dubious stats detection
512 = auto stats job
1024 = parallel execution tracing
2048 = print query before execution
4096 = partition prune tracing
8192 = trace stat differences
16384 = trace extended column stats gathering
32768 = trace approximate NDV (number distinct values) gathering

Enable Oracle SQLNet trace
Sometimes we need to enable sql*net trace to diagnose sqlplus connection problems such as ora-125*

Enabling Dynamic Listener Tracing
LSNRCTL> set current_listener listener
Current Listener is listener
LSNRCTL> set trc_level 16

Listener trace

TRACE_UNIQUE_CLIENT = on
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT = /tmp

It was true up until 11GR2. In order to see the trace files in the specified location you should specify yet another parameter:
DIAG_ADR_ENABLED=OFF

Client trace

TRACE_LEVEL_CLIENT = SUPPORT
TRACE_DIRECTORY_CLIENT = c:\oracle\trace
TRACE_UNIQUE_CLIENT = ON
TRACE_FILE_CLIENT = SESS
TRACE_LEVEL_CLIENT can be one of the following four values:
0 or OFF
4 or USER
10 or ADMIN
16 or SUPPORT

http://www.anbob.com/archives/2073.html
http://www.anbob.com/archives/1973.html
http://www.anbob.com/archives/2095.html

Note: debug is danger, 不了解的trace生产上不要乱用, 如果启用注意磁盘空间不要被tracefile 占满.

references https://laurent-leturgez.com/2016/08/29/tracing-oracle-binaries-and-tools/

打赏

对不起,这篇文章暂时关闭评论。