The default alert log directory path has this structure:
$ORACLE_HOME/rdbms/log
Or find it easily with the show parameter command:
SQL> show parameter background
You can override the default directory path for the alert log by setting the DIAGNOSTIC_DEST initialization parameter. Usually, the db_unique_name is the same as the instance_name, but it depends on the environment.
In RAC and Data Guard environments, however, the db_unique_name is often different from the instance_name. You can verify the directory path with this query:
SQL> select value from v$diag_info where name = ‘Diag Trace’;
The name of the alert log follows this format:
Alert_<ORACLE_SID>.log
You can also locate the alert log from the OS (whether the database is started via these OS commands):
$ cd $ORACLE_HOME
$ find . -iname alert_<ORACLE_SID>.log $ find . -iname alert_mmdb23c.log
Check the alert log for any messages, errors, and activity that might help with troubleshooting the issue at hand. As we had already discussed with the sizing of redo, there might be too frequent checkpoints and other details that show up in the alert log. Scan or search the alert log for error messages and warnings.
Tip trace and log files might also need to be cleared out, and setting up an automated job to remove these older files will help keep the ORACLE_HOME directories at a good size to keep this file system from filling up and causing issues. Consider using the automatic diagnostic repository Command Interpreter (adrCI) utility to purge old trace files.
Inspecting the Alert Log
When dealing with database issues, the alert.log file should be one of the first files you check for relevant error messages. You can use either OS tools or the ADRCI utility to view the alert.log file and corresponding trace files.
Viewing the Alert Log via OS Tools
After navigating to the directory that contains the alert.log, you can see the most current messages by viewing the end (furthest down) of the file (in other words, the most current messages are written to the end of the file). To view the last 50 lines, use the tail command:
$ tail -50 alert_<ORACLE_SID>.log
You can continuously view the most current entries by using the f switch:
$ tail -f alert_<ORACLE_SID>.log
You can also directly open the alert.log with an OS editor (such as vi):
$ vi alert_<ORACLE_SID>.log
Sometimes, it is handy to define a function that will allow you to open the alert.log, regardless of your current working directory.
When inspecting the end of the alert.log, look for errors that indicate these types of issues:
• Archiver process hung, owing to inadequate disk space
• File system out of space
• Tablespace out of space
• ORA- 600 or 7445 errors
• Running out of memory in the buffer cache or shared pool
• Media error indicating that a data file is missing or damaged
• Error indicating an issue with writing an archivelog
For a serious error message listed in the alert.log file, there is almost always a corresponding trace file. For example, here is the accompanying message for the prior error message:
Errors in file $ORACLE_HOME/rdbms/mmdb23c/trace/mmdb23c_ora_5272.trc
Inspecting the trace file will often (but not always) provide additional insight into the issue.
Leave a Reply