Monitoring Alert Log File

• It is important for a DBA to check alert log file regularly to detect problems before they become serious.
• Internal errors like ORA-600 and block corruption errors like ORA-1578 or ORA-1498.
• Locations of alert log file is given as $ORACLE_HOME/admin/TEST/bdump (background_dump_dest).
• Alert log file consist of
  a) checkpoint start and end times
  Note: the values are written into alert log file only if LOG_CHECKPOINTS_TO_ALERT parameter is TRUE.
  b) Incomplete checkpoints
  c) Time to perform archiving
  d) Instance recovery start and complete times
  e) Deadlock and timeout errors
• Along with alert log file in bdump, some other trace files created by background process which in turn helps us to diagnose and troubleshoot.
• Tracing can be enabled at Instance Level and at Session Level.
• Instance Level: This can be enabled or disabled by SQL_TRACE parameter. Default value will be False.
• Session Level: Following statement enables writing into a trace file
  Execute dbms_system.set_sql_trace_in_session (10, 22, TRUE);
  Where 10 and 22 are the SID and SERIAL# in v$session view.