Tracing files for Oracle (trace file)

Trace files are typically caused by the dbms_monitor (in Oracle Database 9i Relese 2) and earlier versions of Altersession SET sql_trace=true enable tracing results. Or it is generated by using the extended Trace tool with 10046 events, as follows

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/96/2E/wKioL1kdoCXRHFuBAAAMgwxEAvE932.png "title=" 1.png "alt=" Wkiol1kdocxrhfubaaamgwxeave932.png "/>

These trace files contain information related to diagnostics and performance. They are of great significance for understanding the internal work of database applications. In a healthy database, you will often see these trace files, and much more than you can see other types of trace files.

1 File Location

either using the dbms_monitor Sql_trace ,oracle

• Span style= "FONT-FAMILY:SIMSUN;FONT-SIZE:10.5PT;" lang= "ZH-CN" xml:lang= "ZH-CN" > If you use a dedicated server connection, the user_dump_dest parameters are generated in the directory specified in the trace file.

• If you use a shared server connection , the background_dump_ Dest parameters are generated in the directory specified in the trace file.

note Meaning: 11g version background_dump_dest and user_dump_dest parameters will be discarded, although you can also query using these two parameters but you can use the new parameters or views to query.

Use Show Parameterdump_dest command to view the directory, or you can directly query V$parameter view, or query the new V$diag_info View .

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/96/2E/wKiom1kdoDPzRDwUAAAkLQFFwO8264.png "title=" 2.png "alt=" Wkiom1kdodpzrdwuaaaklqffwo8264.png "/>

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/96/2E/wKioL1kdoEOR_X3KAAB0Jx43F4A448.png "title=" 3.png "alt=" Wkiol1kdoeor_x3kaab0jx43f4a448.png "/>

V$diag_info is a Oracle Database 11g The new View , this has not been in the earlier version. It is an easier interface for accessing trace information used by new ADR tools.

Oracle Database 11g adjust the default storage location for many files, make them better organized, This makes it easier to record to oracle service request. The most important of these lines are the following two items:

• diag Trace : This is oracle Database     11g trace file ( where.

• Default Trace File : This is the trace file name for the current session. In earlier versions, this file name might be difficult to get. In Oracle Database 11g , only a simple query to v$diag_info can return the fully qualified file name of the document.

2 Naming Conventions

Oracle The naming conventions for trace files in are always changing, as shown in the following example:

Trace file name	Database version
Ora_10583.trc	9i Release 1
Ora9ir2_ora_1905.trc	9i Release 2
Ora10gr2_ora_6793.trc	10g Release 2
Ora11gr2_ora_1990.trc	11g Release 2

The trace file name can be divided into the following sections.

the first part of the file name is Oracle_sid ( except 9i Release 1 )

the next part of the file name has only one Ora .

The number in the trace file name is the process of the dedicated server ID , you can from v$process view to get.

Oracle Database 11g easy to use v$diag_info view, before which the actual ( ) requires access to 4

V$parameter : Found user_dump_dest Specifies the location of the trace file, which may be used in the trace filename to find an optional Tracefile_identifier .

v$process : Find Process ID .

v$session : correctly identifies the session information in other views.

V$instalce : Get Oracle_sid .

Use the following query to generate a trace file name:

SELECT C.value | | ' /' | | D.instance_name | | ' _ora_ ' | | A.spid | | Case

When e.value are not NULL and then

' _ ' | | E.value

END | | '. TRC ' TRACE

From V$process A, V$session B, V$parameter c,v$instance D, V$parameter E

WHERE a.addr = b.paddr

and B.sid = USERENV (' SID ')

and c.name = ' user_dump_dest '

and e.name = ' tracefile_identifier ';

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/96/2E/wKiom1kdoJei7auHAAAuzhqcrJ4904.png "title=" 5.png "alt=" Wkiom1kdojei7auhaaauzhqcrj4904.png "/>650) this.width=650; src=" https://s3.51cto.com/wyfs02/M01/96/2E/ Wkiol1kdokqwz4g2aaa9v4jvrl8122.png "title=" 4.png "alt=" Wkiol1kdokqwz4g2aaa9v4jvrl8122.png "/>

If the file exists, it can be accessed by name. Trace files will not appear until tracing is enabled. On the Windows platform to change / replace \.

3 Tagging trace Files

there is a way to "tag" a trace file so that even if you do not have access v$process and the v$session , you can also find trace files. Assuming you can read the user_dump_dest directory, you can use the session parameter tracefile_identifier. With this method, you can add a string that uniquely identifies the trace file name:

you can see that the trace file is still in the standard <ORACLE_SID>_ora_<PROCESS_ID> The format is named, but there is a unique string specified for it, which makes it easy to find the trace file name.

Refer to Oracle 9i 10g 11g Programming Art in-depth database architecture

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1927326

Tracing files for Oracle (trace file)