Lab Assignment #1 - Oracle Database Tracing
1. Alert Log Files
Description:
The Oracle alert log file contains critical information related to the Oracle database, such as
database startup, shutdown, errors, and internal system messages.
Location:
The alert log file is usually located in the Oracle database's `alert_log` directory (typically located in
the Oracle database's `oracle_home`).
SQL Command to view alert log:
You can query the alert log files using Oracle's v$diag_info view:
SELECT * FROM v$diag_info WHERE name = 'Alert Log';
2. Background Trace Files
Description:
Background trace files are generated by Oracle background processes (like DBWR, LGWR, etc.).
These files provide information about the background operations such as the writing of logs,
backups, etc. It is useful for diagnosing issues related to database performance or failure.
Location:
These files are stored in the Oracle diagnostic destination directory.
SQL Command to view background trace files:
You can query background trace files using v$diag_info view:
SELECT * FROM v$diag_info WHERE name = 'Diag Trace';
3. User Trace Files
Description:
User trace files are generated when a user session is traced using tools like DBMS_SESSION or
ALTER SESSION. These files contain session-specific information such as SQL queries, errors, and
resource usage. This helps to troubleshoot performance issues.
SQL Command to enable tracing for a session:
ALTER SESSION SET SQL_TRACE = TRUE;
SQL Command to disable tracing for a session:
ALTER SESSION SET SQL_TRACE = FALSE;
4. Enable and Disable User Tracing
Enable User Tracing:
To trace a session in Oracle, you use the DBMS_SESSION package. Tracing can be enabled at the
session level:
EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE);
Disable User Tracing:
Tracing can be disabled for a session as follows:
EXEC DBMS_SESSION.SET_SQL_TRACE(FALSE);
