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-%'"

What will happen if oracle unable to write Alertlog, Core Dump Or Tracefiles

In this I will show what can happen if Oracle is unable to write to the trace/alert.log to bdump or cdump & udump directories. It can happen if you loss your background_dump_dest or user_dump_dest or core_dump_dest ormay the the partition containing bdump or udump folder is full.

The answer is if oracle is unable to write to the trace/alert.log to bdump & udump directories then the oracle behavior depends on which process is attempting to write to alert.log/trace/coredump file. Based on the process the instance may or may not crash immediately.

If a foreground process corresponding to user process wants to write, but is unable to do so, the process may hang/terminate but there will not be any impact on database.

But if the background process wants to write it may hang and eventually crash the instance if that background process terminates.

In the following section I demonstrate this behavior on 10.2g

1.Start database with spfile Create one bdump directory
SQL> !mkdir /oradata2/bdump

2.Set the background_dump_dest to this location.
SQL> alter system set background_dump_dest='/oradata2/bdump';
System altered.

SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oradata2/bdump

3.Switch logfile. It will generate one trace file in the background_dump_dest.
SQL> alter system switch logfile;
System altered.

SQL> !ls /oradata2/bdump
arjudba_arc0_10688.trc

4.Move the bdump location to a new location. Here bdump_bak.
SQL> !mv /oradata2/bdump /oradata2/bdump_bak

SQL> !ls /oradata2/bdump
/oradata2/bdump: No such file or directory

5.Now you may face one of two scenario a) or b).

a)If database is open then Instance will crash
SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

b)If database is shutdown startup will fail with ORA-00444 and ORA-07446.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-00444: background process "MMAN" failed while starting
ORA-07446: sdnfy: bad value '' for parameter .

So if you try to write alert log file while instance is up and running it may fail and eventually crush the instance.

However, for a foreground process the database will not crash, if it is unable to write to alert.log.

Stop Alert Notification for a specified period of time

In database you probably has notification settings-whenever you have problem in database you will be notified by e-mail.

However, when you plan to bring your database down for maintenance, you can indicate that you do not want alert notifications to be sent to you by defining a blackout period.

Blackouts also allow you to suspend monitoring in order to perform other maintenance operations.

You can easily do it from Enterprise Manager.

The Steps for stopping Notifications for a period of time:
---------------------------------------
1)Log on to the Enterprise Manager with SYSTEM user.
2)In database homepage at the top right corner click the "setup".
3)Click "Blackouts" at the left pane.
4)Click "create" at the right side of the page.
5)The Create Blackout wizard appears. Fill up the required fields.
6)Fill up the five steps like, when you want to set time period for blackout or will it make it repeatedly.
7)You receive a confirmation message that your blackout has been defined whenever you finish.

What is Alert Log?

Each database has a special file named alert_sid.log. The alert log of a database is a chronological log of messages and errors, and includes the following items:


1)All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur.

2)Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements.

3)Messages and errors relating to the functions of shared server and dispatcher processes.

4)Errors occurring during the automatic refresh of a materialized view.

5)The values of all initialization parameters that had nondefault values at the time the database and instance start.


Oracle Database uses the alert log to record these operations as an alternative to displaying the information on an operator's console.

The alert log file destination is specified by BACKGROUND_DUMP_DEST.

What is Server-Generated Alerts?

A server-generated alert is a notification from the Oracle Database server of an impending problem.

The notification may contain suggestions for correcting the problem.
Notifications are also provided when the problem condition has been cleared.

Server-generated alerts can be based on threshold levels or can issue simply because an event has occurred.

A)Threshold-based alerts:
-----------------------------
Threshold-based alerts can be triggered at both threshold warning and critical levels.

The value of these levels can be customer-defined or internal values, and some alerts have default threshold levels which you can change if appropriate.

For example, by default a server-generated alert is generated for tablespace space usage when the percentage of space usage exceeds either the 85% warning or 97% critical threshold level.

Threshold-based alerts are,
1)Physical Reads Per Second

2)User Commits Per Second

3)SQL Service Response Time

B)Event based Alerts:
----------------------------
Event based alerts are generated because an event has occurred.Some example of event based alerts are,

1)Snapshot Too Old

2)Resumable Session Suspended

3)Recovery Area Space Usage

An alert message is sent to the predefined persistent queue ALERT_QUE owned by the user SYS.

Oracle Enterprise Manager reads this queue and provides notifications about outstanding server alerts, and sometimes suggests actions for correcting the problem.

The alerts are displayed on the Enterprise Manager console and can be configured to send email or pager notifications to selected administrators.

If an alert cannot be written to the alert queue, a message about the alert is written to the Oracle Database alert log.

How to view and change threshold settings of Threshold based Alerts:
---------------------------------------------------------------------------
1)You can view and change threshold settings for the server alert metrics using the SET_THRESHOLD and GET_THRESHOLD procedures of the DBMS_SERVER_ALERTS PL/SQL package.

2)The DBMS_AQ and DBMS_AQADM packages provide procedures for accessing and reading alert messages in the alert queue.

Setting Threshold Levels
-----------------------------

The following example shows how to set thresholds with the SET_THRESHOLD procedure for CPU time for each user call for an instance:

DBMS_SERVER_ALERT.SET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, DBMS_SERVER_ALERT.OPERATOR_GE, '8000',
DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2, 'arju',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'arju.arjubd.com');


In this example, a warning alert is issued when CPU time exceeds 8000 microseconds for each user call and a critical alert is issued when CPU time exceeds 10,000 microseconds for each user call.


Retrieving Threshold Information
-----------------------------------------
SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';
Related Documents
Stop Alert Notification for a specified period of time

How to Notify or send email Event in Oracle from EM

In this post I will show about how e-mail can be send from oracle if any critical events happen.
With Enterprise Manager the task is very simple. With this you can easily send email if any oracle related problem happens. Like Tablespace full, Any oracle error, listener issue, performance problem etc.

Step 1:
At first step you need to setup sender mail address, your SMTP server address and sender identifier.

i)On the Enterprise Manager Home page click setup link which is on the upper most right corner.
ii)..../em/console/admin/rep/userAdmin window appears. On the Setup tab click on the Notification Methods.
iii)The several boxes appears.
Outgoing Mail (SMTP) Server: Here post your SMTP server address. On my system I gave 192.168.1.1.

Identify Sender As: This is the identity of sender. I gave in this box Arju.

Sender's E-mail Address: This is thrid box. Define from whom the mail will be sent. I gave here prothoma@....com

iv)On the right side click on Test Mail Servers button A new window will come. If it display message similar like
Test Results
192.168.1.1: Test succeeded - You will also need to verify that a test e-mail has been received by prothoma@ya....com
Then this step is correct.

Step2:
In this step you will assign the mail address of the users to whom notification will be sent. To do this,
i)On the Enterprise Manager page click on preferences link which is on the upper most right corner.

ii)Under general tab type SYS password and confirmed password. Then under E-mail Addresses menu click add another row button and specify the email address to whom notification will be send.

ii)Then click on Test. A new window will appear displaying messge
.....@....com: Test succeeded - You will also need to verify that a test e-mail has been received
Now check the mail address to see whether actually mail is sent or not.

If you got mail to .....@....com address from prothoma@ya....com then you have successfully configured notification.

Step3:
i)On the Enterprise Manager page click on preferences link which is on the upper most right corner.
ii)You then specify rules and schedules in of the notifications.
iii)Click on rules and then select Listener Availability or Host Availability and Critical States or Database Availability and Critical States and then click assign methods button and check the box send me e-mail and click ok.

iv)Notification Schedule
Next, you will need to define your notification schedule. EM will NOT send you email notifications
if you do not have a schedule defined.
A notification schedule is used to represent your on-call schedule. It tells EM two things:
(a) the day and time you should be contacted and
(b) the email addresses to be used during those times.
Any time slot that is left empty in the schedule means that EM should NOT send you email
notifications, even though alert may occur during that time.
It is important to note that the schedule you specify will automatically repeat.

In a nutshell I can say "Setting up email notifications for alerts" needs following steps.

Step I. Setup the mail servers
Step II. Setup EM user accounts for your administrators
Step III. Each EM user should define their own notification settings
a) E-mail addresses -From Preferences
b) Notification Schedule
Step IV. Define and subscribe to Notification Rules

Related Documents
How to get port number list of EM and isqlplus
In EM connection fails with ERROR: NMO not setuid-root (Unix-only)
Connect to EM though normal user