| Lesson 5 | Recognizing severe errors in the alert log |
| Objective | Recognize severe errors in the alert log and identify the correct remedy. |
Recognizing severe errors
Alert logs and error messages
When we look at all of the information in the Oracle alert logs, it is easy to see how monitoring the alert log for messages could become a very time-consuming task. Fortunately, there are many ways to relieve yourself of this burden. There are several third-party products that you can purchase to alert you about messages such as Platinum's DB-vision and BMC's Patrol, but it is very easy to write your own monitor. No matter how you do it, it is imperative that you know about Oracle error messages as soon as possible. We will be discussing detailed techniques for this later in the course.
In addition to the routine messages that we find in the alert log, Oracle also reports unexpected and serious conditions in the alert log file. These errors are all serious in nature and require intervention by the Oracle DBA.
Database crash
These errors indicate an error that is severe enough to crash the Oracle instance. In these cases, a core dump or trace file is always produced, and these need to be gathered and sent to Oracle technical support. The alert log is the first place to look whenever an Oracle instance crashes, since the alert log will provide the names of all associated core dumps and trace files.
View the Code below to view the trace files dbwr_93556.trc and snp0_92808 would be sent to Oracle for analysis.
Wed Jan 27 14:44:09 1999 Errors in file /ora7/home/dba/oracle/product/7.3.3.0.0/rdbms/log/ora_66742.trc: ORA-00600: internal error code, arguments: [12700], [1090529546], [51], [], [], [], [], []
ORA-600 errors - unknown serious errors
These errors indicate a situation where Oracle has encountered an unexpected error. In most cases, an ORA-600 will not crash the Oracle database,
but it may produce a core dump of trace files. ORA-600 errors should always be reported to Oracle technical support. View the code below to see an example of this error message.
07445, 00000, "exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s]" // *Cause: An OS exception occurred which should result in the creation of a core file. // This is an internal error. // *Action: Contact your customer support representative.
ORA-7445 errors - exceptions with core dump
Oracle exception with core dump. This is a sever internal error that usually crashes the Oracle database and produces a core dump. These errors should always be reported to Oracle technical support. Click the View Code button to see an example of this error message.
ORA-1578 errors - corrupt data blocks
This message appears in Oracle when a data block is read that appears corrupt. This error should always provide the file number and block number of the questionable block.ORA-01578 ORACLE data block corrupted (file # 12345, block # 234)
When you encounter a corrupt block message in your alert log, you should immediately contact Oracle technical support. In most cases, Oracle support will instruct you to add a "set event" in your init.ora file to bypass the corrupt block. The init.ora entry looks like this:
event = ";xxxx trace name context forever, level 1024"
(where xxxx is supplied by Oracle support)
Once this is done, you can recover the corrupt table (except for the bad block) by using the export and import utilities.
Fri Feb 5 13:39:31 1999 ORA-1653: unable to extend table/cluster QDBA.Q$INSTAT_LOG by 64 in tablespace DBA_PERF
ORA-1653 - unable to extend object
These messages appear in the alert log whenever Oracle cannot extend an object within the tablespace. These errors cause the program that requested the extent to fail, and do not crash the database. The remedy for this error is to reduce the NEXT extent size for the object or add a data file to the tablespace. View the Code below to see an example.
Sun Feb 21 01:48:11 1999 ORA-1650: unable to extend rollback segment R06 by 64000 in tablespace ROLLBACK
ORA-1650 - unable to extend rollback segment
This error message is produced when a rollback segment has become full. The task associated with this task will terminate, but Oracle will not
crash. The remedy for this error is to dedicate a large rollback segment to the task, or have the task perform more frequent COMMIT statements.
Summary of severe error messages
The following table summarizes each severe error message and lists the recommended remedy.
Now that we have reviewed the most common serious alert log messages, let's look at how we can develop a mechanism for monitoring the alert log file.
Recognize alert Log Errors - Exercise
But first, before you go on, click the Exercise link below to match types of error messages with their characteristics and remedies.
Recognize alert Log Errors - Exercise
Recognize alert Log Errors - Exercise