Apr 22, 2023
Trace File Wrap-Up- Files
You now know the two types of general trace files, where they are located, and how to find them. Hopefully, you’ll use trace files mostly for tuning and increasing the performance of your application, rather than for filing service requests.
As a last note, Oracle Support does have access to many undocumented “events” that are very useful for dumping out tons of diagnostic information whenever the database hits any error. For example, if you are getting an ORA-01555 Snapshot Too Old that you absolutely feel you should not be getting, Oracle Support can guide you through the process of setting such diagnostic events to help you track down precisely why that error is getting raised, by creating a trace file every time that error is encountered.
Alert Log File
The alert file (also known as the alert log) is the diary of the database. It is a simple text file written to from the day the database is “born” (created) to the end of time (when you erase it). In this file, you’ll find a chronological history of your database—the log switches; the internal errors that might be raised; when tablespaces were created, taken offline, put back online; and so on. It is an incredibly useful file for viewing the history of a database. I like to let mine grow fairly large before “rolling” (archiving) it.
The more information, the better, I believe, for this file. I will not describe everything that goes into an alert log—that’s a fairly broad topic. I encourage you to take a look at yours, however, and see the wealth of information it holds.
To determine the location of the text-based alert log for your database, run the following query:
SQL> select value from v$diag_info, v$instance where name = ‘Diag Trace’;
VALUE
/opt/oracle/diag/rdbms/cdb/CDB/trace
The name of the alert log will be of this format:
alert_.log
You can generate the location and name of the alert log with the following query:
SQL> select value || ‘/alert_’ || instance_name || ‘.log’ from v$diag_info, v$instance where name = ‘Diag Trace’;
VALUE||’/ALERT_’||INSTANCE_NAME||’.LOG’
/opt/oracle/diag/rdbms/cdb/CDB/trace/alert_CDB.log
If you want to view the location of the XML-based alert log, run this query:
SQL> select value from v$diag_info where name = ‘Diag Alert’;
It’s worth noting that there is an internal table, X$DBGALERTEXT, that you can query from SQL*Plus which derives its information from the alert log. This table requires SYS privileges to view. For example, as SYS, you can query the alert log for ORA- errors as follows:
$ sqlplus / as sysdba
SQL> select record_id,
to_char(originating_timestamp,’DD.MM.YYYY HH24:MI:SS’),
message_text
from x$dbgalertext
where message_text like ‘%ORA-%’;
Tip Oracle Support has a note on how to edit, read, and query the alert log (Doc ID 1072547.1).
In addition to using an external table to query the alert log, you can easily view the alert log using the ADRCI tool. That tool lets you find, edit (review), and monitor (interactively display new records as they appear in the alert log). Also, Enterprise Manager provides access to the alert log.
Generally speaking, when the need arises to view the alert log, most DBAs will navigate directly to the alert log location. Once there, they’ll use operating system utilities such as vi, tail, and grep to extract information.
Tip With Oracle 19c and above, there’s also an attention.log file that contains information regarding critical events in your database (startup, shutdown, invalid memory parameters, and so on). It is located in the $ORACLE_BASE/diag/ rdbms///log directory.
More Details