Alert Log in oracle11g

Alert Log in oracle11g

What is Alert Log?

-The alert log (is also referred as alert_dbname.log)file is a chronological log of message and errors written by oracle database

The file should be constantly used to monitor the db to identify error message and corruptions

-It includes the information of following items

*db startup ,shut down,create,alter ,drop and archive log statements(Administrative Operations)

*log switches

*Internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur.

*Error occurring messages during the automatic refresh of the materialized view

*at the time of db instance startup it records values of all initialization parameters which had nondefault values

Note: this file will be created automatically whenever the old file was deleted

About alert log:

-In oracle10g there are several parameters like user_dump_dest,background_dump_dest,core_dump_dest which determine the location of alert log, user dump, and core dump directory.

-with Release 11g of Oracle Database, because all diagnostic data, including the alert log, are stored in the ADR, and it’s located in the directory and the parameter is “DIAGNOSTIC_DEST” which means the directory of background_dump_dest and user_dump_dest are deprecated.

*If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.

*If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.

SQL> show parameter diagno

-if this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:

I)ADRCI (Automatic Diagnostic Repository Command Interpreter(ADRCI)):

-You can also use the ADR Command Interpreter (ADRCI) is a utility that enables you to investigate problems, view health check reports, and package and upload first-failure diagnostic data to Oracle Support, all within a command-line environment. ADRCI also enables you to view the names of the trace files in the ADR, and to view the alert log with XML tags stripped, with and without content filtering.

II)ADR base directory:

ADR_BASE/diag/product_type/product_id/instance_id

-For example, for a database with a SID and database name both equal to ora11g, the ADR home would be in the following location:

ADR_base/diag/rdbms/ora11g/ora11g/

-Within the ADR home directory are subdirectories where the database instance stores diagnostic data.

1)alert ( The XML-formatted alert log )

2)trace ( Background and server process trace files and SQL trace files and text alert.logfiles)

3)cd ump ( Core files )

4)Incident files (Serious of each error ex: ORA-600, ORA-1578, ORA-7445 causes an incident to be created)

View alert logs:

1)trace

SQL> show parameter BACKGROUND_DUMP_DEST

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest                 string      C:\oraclexe\app\oracle\diag\rbms\dhoni\dhoni\trace

SQL>  select value from v$diag_info where name ='Diag Trace';

VALUE

C:\ORACLEXE\APP\ORACLE\diag\rdbms\dhoni\dhoni\trace

Note: to open the alert_dbname.log file within trace folder

2)xml file

SQL>  select value from v$diag_info where name ='Diag Alert';

VALUE

--------------------------------------------------------------------------------

C:\ORACLEXE\APP\ORACLE\diag\rdbms\dhoni\xe\alert

Note: to open the log.xml file from alert folder

To work with ADRCI:

$adrci (Linux)

1)Alert log file will be opened in your editor.

adrci> show alert