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/
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。