How to check alert log in Oracle 11g

About Alert log in Oracle 11g
Note that, in Oracle 11g if you want to look for alert log file just like plain text file not in XML file then navigate to
[diagnostic_dest]/diag/rdbms/[dbname]/[instname]/trace or ORACLE_HOME/log/trace folder and then open the alert_[instance_name].log

If you have worked with Oracle 10g then you might see several parameters like background_dump_dest, user_dump_dest, core_dump_dest which determine the location of oracle alert, user dump and core dump directory. Beginning with Release 11g of Oracle Database, because all diagnostic data, including the alert log, are stored in the ADR, the initialization parameters BACKGROUND_DUMP_DEST and USER_DUMP_DEST are deprecated. They are replaced by the initialization parameter DIAGNOSTIC_DEST, which identifies the location of the ADR.

If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE. It takes value from Oracle Universal Installer.

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

In case of Oracle RAC diagnostic_dest parameter can be set on each instance. Oracle recommends that each instance in a cluster specify a DIAGNOSTIC_DEST directory location that is located on shared disk and that the same value for DIAGNOSTIC_DEST be specified for each instance.

The structure of the directory specified by DIAGNOSTIC_DEST is as follows:

[diagnostic_dest]/diag/rdbms/[dbname]/[instname]

This location is known as the Automatic Diagnostic Repository (ADR) Home. For example, if the database name is proddb and the instance name is proddb1, the ADR home directory would be [diagnostic_dest]/diag/rdbms/proddb/proddb1.

The following files are located under the ADR home directory:

i)Trace files - located in subdirectory [adr-home]/trace

ii)Alert logs - located in subdirectory [adr-home]/alert. In addition, the alert.log file is now in XML format, which conforms to the Oracle ARB logging standard.

iii)Core files - located in the subdirectory [adr-home]/cdumd

iv)Incident files - the occurrence of each serious error (for example, ORA-600, ORA-1578, ORA-7445) causes an incident to be created. Each incident is assigned an ID and dumping for each incident (error stack, call stack, block dumps, and so on) is stored in its own file, separated from process trace files. Incident dump files are located in [adr-home]/incident/[incdir#]. You can find the incident dump file location inside the process trace file.

How to view alert log in Oracle 11g
You can view the alert log with a text editor, with Enterprise Manager, or with the ADRCI utility.
To view the alert log with Enterprise Manager:

i) Access the Database Home page in Enterprise Manager.

For Oracle Enterprise Manager Grid Control, go to the desired database target.

ii) Under Related Links, click Alert Log Contents.

The View Alert Log Contents page appears.

iii) Select the number of entries to view, and then click Go.

To view the alert log with a text editor,

i) Connect to the database with SQL*Plus or another query tool, such as SQL Developer.

ii) Query the V$DIAG_INFO view.

iii) To view the text-only alert log, without the XML tags, complete these steps:

- In the V$DIAG_INFO query results, note the path that corresponds to the Diag Trace entry, and change directory to that path.

- Open file alert_SID.log with a text editor.

iv) To view the XML-formatted alert log, complete these steps:

- In the V$DIAG_INFO query results, note the path that corresponds to the Diag Alert entry, and change directory to that path.

- Open the file log.xml with a text editor.

To find the trace file for your current session issue,

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

To find all trace files for the current instance issue,

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

To determine the trace file for each Oracle Database process issue,

SQL> SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

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.

To invoke adrci issue,

$ adrci
adrci> set editor vi (Setting editor. )
adrci> show alert ( Alert log file will be opened in your editor. )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -n 200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )
adrci> show alert -tail 100 -f ( Similar to tail -n 100 -f )

If you want to see the lists of all the "ORA-" error, then run following command.

$ adrci
adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"