Read Oracle alert log From SQL

It is possible to query the contents of the alertSID.log file in Oracle databases, such as a table in the database. To do this, you must first create a virtual directory that points to the trace directory that contains the alertSID.log file. The contents of the log file can then be queried with an external table.

Create a virtual directory

The following command can be used to create a virtual directory that points to the trace directory in the database named ORCL.

Create an External Table

We can create an external table as follows. The log file (orcl_alert_table.log) required for the external table will be created under the trace directory.

Because trace indexes are different for each instance on RAC systems, different tables must be created.

For example, you can query if there is a ” ORA-12012 ” error in the alert log as follows.

Read Oracle Alert Log Using V$DIAG_ALERT_EXT

You can query any alert.log file line by line with the external table above. It is also possible to access the same information with the V$DIAG_ALERT_EXT view. With this view, you can view not only the database, but also the error messages related to different components (rdbms, diagtool, clients, asm, tnslsnr). Also, XML-based alert log files in the Automatic Diagnostic Repository (ADR) can be queried.

You can query V$DIAG_ALERT_EXT as follows:

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *