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;

/

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

$ 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:]’;

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 ';

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

(gdb) print ksudss(10)

Review the trace file which is found in the user_dump_dest directory

or

SQL>oradebug dump systemstate

--for RAC

SQL>oradebug -g all dump systemstate 266

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’,)

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

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

打赏

微信扫一扫，打赏作者吧～