Alert Log Files in Oracle

Back to: Oracle DBA Tutorials

Alert Log Files in Oracle with Examples

In this article, I am going to discuss Alert log Files in Oracle with Examples. Please read our previous article where we discussed the Automatic Diagnostic Repository in Oracle with Examples.

What is an Alert Log File in Oracle?

The alert log is a chronological log of messages and errors, and includes the following items:

• Any non-default initialization parameter used at startup.
• All internal errors (ORA-00600), block corruption errors (ORA-01578), and deadlock errors (ORA-00060) occur.
• Administrative operations, such as some CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements. You can see create databases, alter databases, and not all of the DDL operations but some of the DDL operations. But you can enable other DDL operations. Using the alert log we can see the last time the database had started, the last time the database had rebooted, and the last time a new pluggable database was created.
• Messages and errors relating to the functions of shared server and dispatcher processes.
• Errors occur during the automatic refresh of the materialized view.
• The locations of the various diagnostics directories can be displayed using the V$DIAG_INFO view. For example select name, the value from v$diag_info; Using this query, we can able to see all the directories present in the database of diagnostics like alert log files and trace files.
• The alert log file can be viewed using a text editor or using ADRCI. ADRCI is a little bit complicated compared to the text editor. So, we can mostly use the text editor to view alert log files.

Examples to understand Alert log Files in Oracle:

Now, let us do an exercise on the alert log concept. Open the Linux machine and open the terminal and login into the database as “sqlplus / as sysdba”.

Use the column formatting to organize the query

Query:
Col name format a25
Col value format a50
Select name, value from v$diag_info;

We can see the directories as

• ADR BASE is /u01/app/oracle/
• ADR Home as /u01/app/oracle/diag/rdbms/jayadb/jayadb.
• Diag Trace as /u01/app/oracle/dig/rdbms/jayadb/jayadb/alert/

There are other folders for cdump, incident, health monitor, and the default trace files. We use the above query to find the locations of diagnostic directories. We can see Active Problem Count is 0 and the Active Incident count is also 0 which indicates that until now there are no problems indicated in the database so that we can reach oracle support. If the count is not 0 then we have to connect to Oracle support. We have a folder for each and every diagnostic. There will be more information about their respective directories.

Examples to understand Alert log Files in Oracle:

From the above query, we can see all the diagnostic directories. We will select the location of the alert log file. The alert log can be viewed in two places. In the folder called alert and, in the folder, called Trace.

So, now we will be checking the alert folder /u01/app/oracle/dig/rdbms/jayadb/jayadb/alert/. This has the XML version of the alert log.

We have only one file log.xml. Let us open the file using the gedit application.

Once the file below is opened. The file will represent below.

The above-marked part of the file is the starting portion of the log.xml file. From the above-marked field, we can find out the time the database started, hostname, IP Address, and much more.

So, we can see all the alerts that occurred to the database in this log.xml file. We can even search for any specific type of alerts that occurred to the database. Now, let’s try to search how many times the database has started in the alert log file. We will be using ctrl+F to find any specific term here.

We can see the total number of terms “Starting Oracle instance” occurred 43 times. This indicates we have started the oracle instance 43 times.

Note: Searching in the editor is case-insensitive.

Let us try to search for the term “alter database”.

We can see there are 238 entries for alter database term. Here from the results, we have made the command “alter database mount” and the time the command is executed and the IP Address and the hostname. This information will be useful in the case of restoration.

Now, that we got a little bit idea about the XML version of the alert log. Let us open the original version of the alert log file in /u01/app/oracle/dig/rdbms/jayadb/jayadb/trace/

We can see there are many files. But the file with alert_jayadb.log is the alert log file. This is the text version of the alerts. Let’s go ahead and open with gedit application.

This is the entire file and what it looks like.

We will find the same information but in the text version. Now, let’s try to search for the shutdown, and let’s see how many times we have shut down the database.

We can see that the total count of shutdowns is 90. We can search for the entire file and find out the time when the shutdown and much more information such as what is the exact process of shutting down the database. Let us go to the bottom of the file and scroll a bit to the top until you find the term “system parameter with non-default values”.

In our case, we found it on 23-10-2022. We can see the parameters here and these are the parameters that are used to start the database. The Number of processes is 300 and the open_cursors is 410. Previously we have changed the value of the parameter open_cursors to 410. After that, we restarted the database and that is the reason, we can see the value 410 for the open_cursors parameter. If in case if the database is crashed, we can use these parameters to recover the database and restart the database again.

Note: Let us recall what we have discussed until now, we have two folders to find the alert log of the database. The first file is the XML version of the alert log and another version is the text version of the alert log file. We can open the file with any text editor and search for the terms we are looking.

In the next article, I am going to discuss Alert Log using ADRCI in Oracle with Examples. Here, in this article, I try to explain Alert log Files in Oracle with Examples and I hope you enjoy this Alert log Files in Oracle with Examples article.