How to find trace files in Oracle

What is the tracking document used for? The trace file contains a large amount of detailed diagnostic and debugging information. Through the interpretation and analysis of the tracking file, we can locate the problem, analyze the problem and solve the problem. From the source of trace files, trace files can be divided into two categories: one is the database operators intentionally generated, and the other is due to an exception error, generated automatically by the database. For the latter category, it is only useful for technical support staff within Oracle, but for us, it is mostly not understood. The first category, which we often use, helps us analyze, adjust, and optimize application performance, process and solve problems. second, how is the tracking file named? The name of a trace file generally consists of the following parts: Oracle_sid fixed character "ora" server's process ID number file suffix name. TRC The first three parts are underlined between the connections. The following example: Orcl_ora_6210.trc where "ORCL" is the SID of the database in this environment, "6210" is the server process ID number used to generate the trace file session. How do I know the server process ID used by my oracle_sid and session?      It will be discussed in detail later. The naming rules for the trace files mentioned above apply to the first type of trace file We said earlier, that is, a trace file that the database operator intentionally generates. If this is the second type of trace file, the name changes slightly. This type of trace file changes the fixed character "Ora" to the abbreviation for the Oracle daemon name. As shown in the following example: Orcl_lgwr_30019.trc orcl_dbw0_2664.trc third, where is the tracking file stored?         The location of tracking files is different depending on the database version. The following are described in 10g and 11g respectively.        10g        Usually, when we install the database, we set an environment variable $oracle_base (note that it is BASE, not home), The main directory for Oracle software installation is specified in this variable. Assuming we set the directory as "/oracle/app/oracle", then the trace file will be placed in the following directories:     /oracle/app/oracle/admin/orcl/bdump   This directory is used to store the output files generated by the Oracle background process. Database auto-generated trace files are stored here by default, in addition, we often refer to the alarm log, also put here.      /oracle/app/oracle/admin/orcl/cdump   This directory is used to store output files generated by kernel processes      /oracle/app/ Oracle/admin/orcl/udump   This directory is used to store user-generated output files. The trace files that we intentionally generate are stored here by default.       Above is said by default, if the administrator of the database changes this setting, our more insurance method is to query the relevant parameters to determine, the query method is as follows:      Sqlplus as Administrator login , enter show parameter Dump_dest and return. For example:     SQL> show parameter dump_dest name                                            type          value-----------------------------------------------------------------------------background_dump_dest             & nbsp String      /oracle/app/oracle/admin/orcl/bdump

Core_dump_dest                            string      /ORACLE/APP/ORACLE/ADMIN/ORCL/CDUMP 

User_dump_dest                          string &NB Sp    /oracle/app/oracle/admin/orcl/udump         In addition, these settings are also documented in the parameters file. It can also be obtained by querying the parameter file.              11g       11G introduces a new feature called the Automated Diagnostics Knowledge Base (Automatic Diagnostic Repository abbreviation for ADR). It is the central storage point for all diagnostic information, and it includes a variety of dump files, trace files, logs, and health state monitoring reports. All instances (RDBMS instances and ASM instances) are created with their own directory structure in the ADR. The location of        ADR is specified by the parameter diagnostic_dest, and the default location is the same as $oracle_base. The structure is as follows:      oracle_base/diag/database_name/instance_name      Assuming that the $oracle_base we set is still "/ Oracle/app/oracle ", the database name and instance name are all" ORCL ", the actual directory structure is as follows:     /oracle/app/oracle/diag/orcl/orcl      This location, also known as the adr_home of the DB instance ORCL in this example.     In this directory, you will also find a subdirectory named Trace, which centrally stores the database background process and the user-generated trace files as well as the alarm logs.       But this is also by default, we can get the actual location by querying the V$diag_info view. :      select * from V$diag_info as shown below;

inst_id NAME                  VALUE-----------------------------------------------------------------------------------------      1 Diag Enabled          TRUE      1 ADR Base              /u01/oracle      1 ADR Home              /u01/oracle/diag/rdbms/orclbi/orclbi      1 diag Trace            / U01/oracle/diag/rdbms/orclbi/orclbi/trace      1 diag Alert            /u01/oracle/diag/rdbms/orclbi/orclbi/alert      1 Diag Incident         /u01/oracle/diag/rdbms/orclbi/orclbi/incident      1 Diag cdump            /u01/oracle/diag/rdbms/ Orclbi/orclbi/cdump      1 Health Monitor        /U01/ORACLE/DIAG/RDBMS/ORCLBI/ORCLBI/HM

Iv. How to find a specific trace file?       With the introduction above, we already know how the trace file is named and where it is stored. But for a busy system, in the same directory, may be in a similar time to generate multiple trace files, then how to find the trace file we want it. The core is to determine the server process ID number we are using. With this ID number, we can know for sure which trace file is what we want. The following describes how to query the server process ID number.       The server process ID number that is currently running is stored in the v$process view. But because of the limited information available in this view, it's hard to pinpoint which server process ID (SPID) We're looking for most of the time. For example, the following output is basically the same.       sql> select Addr,spid,username,terminal,program from v$process; ADDR                          spid       & nbsp   USERNAME   TERMINAL                             Program------------------------------------------------------------------------------------------------- -------------------------------000000007fe6d7a8   2447          oracle             UNKNOWN                           [email protected] (MMNL) 000000007fe6dfa0   2449         Oracle              unknown                         &NB Sp [Email protected] (D000) 000000007fe6e798   2451          oracle             &NBS P UNKNOWN                           [email protected] (S000) 000000007fe6ef90   2453          oracle             &NBS P;PTS/1                                 &N Bsp    [email protected] (TNS v1-v3) 000000007fe6f788   2455          oracle &nbs P            unknown                           [email protected] (ARC0) 000000007fe6ff80   2457          or Acle              unknown                 &NBSP ;         [email protected] (ARC1) 000000007fe70778   2459          or Acle              unknown                 &NBSP ;         [email protected] (CTWR) 000000007fe70f70   2461          or Acle              unknown                 &NBSP ;        [email protected] (QMNC) 000000007fe71768   5703         Oracle &N Bsp             PTS/2                     &NBS P   &nbsp           [email protected] (TNS v1-v3) 000000007fe71f60   5839       &NB Sp Oracle              unknown                 &NB Sp         [email protected]000000007fe73f40   2475         Oracle   & nbsp          unknown                       &NB Sp   [email protected] (q000)        At this point, we use the v$session view, which is to find the server process through the session (a process can have multiple sessions, but one session, Can belong to only one process). As mentioned earlier, there are two types of trace files, and we need the trace files that we actively generate. That is, we are actively generating it, which must be generated in our current session. Therefore, we can use the following statement to find the server process ID (SPID) that our current session belongs to.     Sql> Select a.spid from v$process a,v$session b where B.sid=sys_context (' Userenv ', ' Sid ') and A.addr=b.pad Dr; spid------------5922        sys_context (' Userenv ', ' Sid ') in the above statement is to use the system's built-in function to fetch the current session ID (SID), which can also be replaced with Userenv (' Sid '), the resultis the same. Only Oracle recommends using the former. Since this function is only retained for the sake of maintaining version compatibility, the function will probably be canceled in the newer version.       With this spid, we can go to the corresponding trace file storage location, to find the current time generated, the file name contains "5922" Trace files.       In addition, we can use the following SQL statement to query directly in the current session to obtain the absolute path and name of the trace file.  select C.value | | '/' | | D.instance_name | | ' _ora_ ' | | A.spid | | '. TRC ' Trace from v$process A, v$session B, V$parameter C, v$instance dwhere a.addr = B.paddr and B.SID = Sys_co ntext (' userenv ', ' Sid ')  and c.name = ' user_dump_dest ';

How to find trace files in Oracle