B.6 Oracle Server
How to output the error information of the Oracle server, and the server status, in an Oracle server are described below:
-
Acquiring alert files
-
Acquiring user traces
-
Acquiring Net server trace files
-
Using dynamic performance view
- Organization of this subsection
(1) Alert file
In an alert file, you can confirm the following information with an instance name of a trace based performance analysis as the key.
-
All internal errors, block corruption errors, and dead lock errors that have occurred
-
Management and operation of CREATE, ALTER, or DROP statements, STARTUP or SHUTDOWN statements, and ARCHIVELOG statement
-
Messages and errors related to the shared server and dispatcher process functionality
-
Errors that occurred during automatic refresh of the dataized view
-
Values of all initialization parameters during invocation of a database and instances
Alert files are output to the following locations:
-
In Windows
location-specified-with-BACKGROUND_DUMP_DEST#\ALERT_instance-name.LOG
-
In UNIX
location-specified-with-BACKGROUND_DUMP_DEST#/ALERT_instance-name.LOG
# Set with the initialization parameter USER_DUMP_DEST of Oracle. For details about USER_DUMP_DEST, see the documentation on Oracle.
Of the output alert files, specify the desired alert file by comparing the instance name output to a trace based performance analysis, and the instance name included in the alert file name.
(2) User trace
In the user trace, you can check the following information with an instance name of a trace based performance analysis, OS process ID, session ID, and session serial number as the key:
-
Information of error that occurred in a server process
-
Execution plan and the statistics of an SQL
(3) Net server trace file
In Oracle server, the details of the executed network events are output to a Net server trace file. The OS process ID of the server is included in the file name of the Net server trace file.
Net server trace files are output to the following locations:
-
In Windows
location-specified-by-sqlnet.ora-file#\CLI_OS-process-ID.TRC
-
In UNIX
location-specified-by-sqlnet.ora-file#/CLI_OS-process-ID.TRC
# The output destination of the Net server trace file is set with the Oracle sqlnet.ora file. The coding format of the sqlnet.ora file is described below. For details about the sqlnet.ora file, see the documentation on Oracle.
TRACE_LEVEL_CLIENT=16 TRACE_DIRECTORY_CLIENT=directory-name TRACE_UNIQUE_CLIENT=ON TRACE_TIMESTAMP_CLIENT=ON
Of the output Net server trace files, specify the desired Net server trace file by comparing the OS process ID of a trace based performance analysis and the OS process ID name included in the Net server trace file name.
For details about the output contents of the Net server trace file, see the documentation on Oracle.
- Important note
-
The Net server trace file consumes a large amount of disk space, as a result, it may lead to a decline in the system performance. Reference the Net client trace only when required.
(4) Dynamic performance view
You can reference the details of a process by studying the dynamic performance view or identifying a session. Study the dynamic performance view by comparing it with the connection IDs output to trace based performance analysis files.
The following table describes the relationship between the connection IDs of the dynamic performance view and trace based performance analysis.
Item No. |
Item displayed in the dynamic performance view |
Connection ID item of trace based performance analysis |
---|---|---|
1 |
V$INSTANCE INSTANCE_NAME |
instance-name |
2 |
V$SESSION SID |
session-ID |
3 |
V$SESSION SERIAL# |
session-serial-number |
4 |
V$PROCESS SPID |
OS-process-ID |
For details about the dynamic performance view, see the documentation on Oracle.