Database Reference
In-Depth Information
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 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. Instead, in this section we'll take a look at a specific
example of how to mine information from this alert log, in this case to create an uptime report.
In the past, I've used the alert log file for the http://asktom.oracle.com web site and to generate an uptime
report for my database. Instead of poking through the file and figuring that out manually (the shutdown and startup
times are in there), I decided to take advantage of the database and SQL to automate that work, thus creating a
technique for generating a dynamic uptime report straight from the alert log.
Using an EXTERNAL TABLE (which is covered in much more detail in Chapter 10 and Chapter 15), we can actually
query our alert log and see what is in there. I discovered that a pair of records was produced in my alert log every time
I started the database:
Thu May 6 14:24:42 2004
Starting ORACLE instance (normal)
That is, I always saw a timestamp record, in that constant, fixed-width format, coupled with the message Starting
ORACLE instance . I also noticed that before these records would be an ALTER DATABASE CLOSE message (during a
clean shutdown), or a shutdown abort message, or nothing—no message, indicating a system crash. But any message
would have some timestamp associated with it as well. So, as long as the system didn't crash, some meaningful
timestamp would be recorded in the alert log (and in the event of a system crash, some timestamp would be recorded
shortly before the crash, as the alert log is written to quite frequently).
I discovered that I could easily generate an uptime report if I
Collected all of the records like
Starting ORACLE instance %.
Collected all of the records that matched the date format (that were in fact dates).
Associated with each
Starting ORACLE instance record the prior two records (which would be
dates).
The following code creates an external table to make it possible to query the alert log. (Note: replace
/background/dump/dest/ with your actual background dump destination and use your alert log name in the
CREATE TABLE statement.)
EODA@ORA12CR1> create or replace
2 directory data_dir
3 as
4 '/home/ora12cr1/app/ora12cr1/diag/rdbms/ora12cr1/ora12cr1/trace/'
5 /
Directory created.
 
Search WWH ::




Custom Search