Alert Log Information

The alert log is a text file used by Oracle to record significant events while a database is running. The log is written in chronological order, and the most recent information appears at the bottom of the file. Alert logs contain the following types of information:

1. A record of all redo log switches.
2. Log entries that record each startup and shutdown. Startup entries include lists of all initialization parameters having values other than their default.
3. Messages about internal errors related to problems within the Oracle software.
4. Messages about other significant errors.
5. Records of changes to the database's physical structure.

Question:

1. What is the rule to find the location of the Oracle alert log.
2. I am on an Oracle 11g server and I cannot locate the alert logs.
3. How do I quickly find the location of the Oracle alert logs on an unfamiliar server?

Answer: The Oracle alert log location is determined by

1. the background dump destination and specified by the background_dump_dest parameter.
2. You can display the location of the alert log directory using the following SQL*Plus command:

SQL > show parameter background

Some of the information in the alert log is routine in nature and some is not. You should check the alert log on a daily basis and many DBAs check it more frequently to see if any errors or unusual events have been recorded. Then you need to act on that information.
In the next lesson, you will locate the text file containing the alert log for your database.

The Oracle **alert log** is a key diagnostic tool that captures critical events and background information about the database. When running Oracle 12c, you should regularly review the alert log for specific types of information that help maintain database health and troubleshoot issues. Here’s a categorized list of what to look for:

1. Startup and Shutdown Events
   • Ensure proper database startup and shutdown operations.
   • Look for:
     • Instance startup messages:

       Starting ORACLE instance (normal)...
                   

     • Database shutdown messages:

       Shutting down instance (immediate)...
       ORACLE instance shut down.
                   

2. Errors and Warnings
   • Critical errors such as ORA-00600 (internal errors) and ORA-07445 (unexpected errors).
   • Deadlocks (ORA-00060).
   • Resource exhaustion messages:

     ORA-01555: Snapshot too old
     ORA-01653: Unable to extend table
             

   • Errors related to control files, redo logs, or archive logs.
3. Redo Log Switches
   • Review log switch events to ensure they occur at expected intervals:

     Thread 1 advanced to log sequence <n>
             

   • Investigate if frequent "checkpoint not complete" messages appear, which indicate performance issues:

     Thread 1 cannot allocate new log, sequence <n>
     Checkpoint not complete
             

4. Checkpoint and Archiving Information
   • Check for successful checkpoint events:

     Completed checkpoint up to RBA [File#, Block#, Seq#]
             

   • Verify successful archiving of redo logs in ARCHIVELOG mode:

     Archived Log entry <n> added for thread <m>, sequence <s>
             

5. Tablespace and Datafile Issues
   • Look for tablespace space issues or auto-extend warnings:

     ORA-01652: Unable to extend temp segment
             

   • Monitor datafile resizing or errors:

     File #<n> needs recovery.
             

6. Recovery and Backup Events
   • Automatic Recovery (e.g., when the database is restarted after an unexpected shutdown):

     Media Recovery Waiting for thread <n>
             

   • Check RMAN backup status or errors during backup operations.
7. Resource Usage Warnings
   • Look for resource usage messages, such as:
     • Processes or sessions exceeding limits.
     • Memory pressure or swapping warnings:

       ORA-04031: Unable to allocate <n> bytes of shared memory.
                   

8. Database Configuration Changes
   • Identify recent parameter changes or configuration modifications:

     ALTER SYSTEM SET <parameter_name>...
             

9. Listener and Connectivity Issues
   • Look for messages related to connectivity or listener status:

     TNS-12560: Protocol Adapter Error
             

10. Automatic Diagnostic Repository (ADR) Messages
    • ADR is tightly integrated in Oracle 12c and reports on critical incidents:

      Incident <n> created in ADR
              

    • Look for any core dumps or trace file generation.
11. Patching and Upgrade Information
    • If you recently applied a patch or performed an upgrade, monitor the alert log for:
      • Post-upgrade initialization or errors.
      • Compatibility mode changes.
12. Flashback and Undo Tablespace Monitoring
    • Ensure that undo tablespace operations complete successfully:

      Undo tablespace is full: ORA-01555
              

    • Monitor flashback logs if using flashback features:

      Flashback Database log switch.
              

Best Practices

• Use Tools: Use the ADRCI tool to query the alert log:
  

  adrci> show alert -tail
  

• Automate Monitoring: Set up scripts to parse the alert log for errors or warnings and generate alerts.
• Retain Logs: Regularly archive old logs to prevent disk space issues.

By proactively monitoring these entries in the Oracle alert log, you can quickly identify and resolve potential problems to ensure optimal database performance. Let me know if you need further assistance with alert log management!

Click the Quiz link to answer a few questions about routine monitoring.
Routine Monitoring - Quiz