View Oracle Database diagnostic files (logs)

DiagnosticFile (diagnostic file) 1: Role of the diagnostic file Diagnosticfiles: contains information about major events in the background. Used to resolve issues and

Diagnostic File (Diagnostic File) 1: Role of the Diagnostic File Diagnostic files: contains information about Major Events encountered in the background. Used to resolve issues and

Diagnostic File (Diagnostic File)

1: Role of diagnostic files

Diagnostic files:

Contains information about major events in the background.

Used to resolve problems,

Used for daily log file management.

2: Classification of diagnostic file logs

There are two types:

1: alterSID. log

----- Background trace files (background process trace files)

2: trace files ---

----- User trace file (user trace file)

1: Name the Background trace files file:

Naming method: SID_processname_PID.trc

2: Name the user trace files file as follows:

SID_ora_PID.trc explanation: SID_ora _ process number. trc

3: alertSID. log description:

This file is for record:

1: record some operation commands

2: record the results of major events

3: routine operation information

4: used to diagnose database errors

Each entry has a time stamp (timestamp) associated with it.

This file must be managed by Oracle DBA

The file is located at: BACKGROUND_DUMP_DEST.

Run show parameter dump to view the location of the file:

This file also contains the database startup information equivalent to pfile or spfile.

Log on as an administrator:

2: The following are actual operations:

Log On With sysdba and run the following command:

SQL> show parameter dump

NAME TYPE VALUE
-----------------------------------------------------------------------------
Background_core_dump string partial
Background_dump_dest string d: \ app \ topwqp \ diag \ rdbms \ orcl \
Orcl \ trace
Core_dump_dest string d: \ app \ topwqp \ diag \ rdbms \ orcl \
Orcl \ cdump
Max_dump_file_size string unlimited
Shadow_core_dump string none
User_dump_dest string d: \ app \ topwqp \ diag \ rdbms \ orcl \
Orcl \ trace

You can see the path information of these files.

Based on the explicit information, I found my file location:

The directory structure is as follows:

The following describes how to record information to these log files. Some switches are required.

A little bit of information:

There are two ways for the user to tracing

1: session level:

Run the following command:

Alter sesssion set SQL _TRACE = TRUE

The second is to execute the following stored procedure:

Dbms_system.SET_ SQL _TRACE_IN_SESSION

The second method is instance-level:

Set the initialization parameter: SQL _TRACE = TRUE

Generally, the session level is used. If the instance level is set, the log file is too large;

You can use the information in the alterSID. log File to create a pfile or spfile file to start the database.

Modify SQL _trace to true at the session level to view the corresponding information in the corresponding file of user_dump_dest.

SQL> conn/as sysdba
Connected.
SQL> alter session set SQL _trace = true;

The session has been changed.

After execution: View

Orcl_ora_7188.trc File Information PS: if you do not know which file to delete, delete all the files in this directory, and then execute the SQL statement to see the generated file:

View the file information as follows:

Detailed execution information:

For example, a statement is: select * from dual

The following information is generated in this file:

* ** 2013-06-13 22:58:20. 776
==================================
Parsing in cursor #1 len = 18 dep = 0 uid = 0 oct = 3 lid = 0 tim = 9184375464 hv = 942515969 ad = '232363f8' sqlid = 'a5ks9fhw2v9s1'
Select * from dual
END OF STMT
PARSE #1: c = 0, e = 32, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 9184375458
EXEC #1: c = 0, e = 50, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 9184376205
FETCH #1: c = 0, e = 109, p = 0, cr = 3, cu = 0, mis = 0, r = 1, dep = 0, og = 1, tim = 9184376423
STAT #1 id = 1 cnt = 1 pid = 0 pos = 1 obj = 115 op = 'table access full dual (cr = 3 pr = 0 pw = 0 time = 0 us cost = 2 size = 2 card = 1)'
FETCH #1: c = 0, e = 2, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 0, tim = 9184376893