Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 | CREATE OR REPLACE DIRECTORY ALERT_LOG_DIR AS '/u01/app/oracle/diag/rdbms/orcl/ORCL/trace'; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE ORCL_ALERT_LOG ( MESSAGE VARCHAR2(4000 BYTE) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ALERT_LOG_DIR ACCESS PARAMETERS ( records delimited by newline logfile alert_log_dir:'orcl_alert_table.log' ) LOCATION (ALERT_LOG_DIR:'alert_ORCL.log') ) REJECT LIMIT UNLIMITED; |
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.
1 2 3 4 5 6 7 | SQL> set lines 1000 SQL> select * from DBA_ALERT_LOG where MESSAGE like '%ORA-12012%'; MESSAGE ---------------------------------------------------------------------------- ORA-12012: error on auto execute of job 2 ORA-12012: error on auto execute of job 2222 |
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:
1 2 3 4 5 6 7 8 9 | select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME ,message_text ,host_id ,inst_id ,adr_home from V$DIAG_ALERT_EXT A where component_id='rdbms' and message_text like '%ORA-12012%' order by 1 desc; |
1 2 3 4 5 6 7 8 9 10 | select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME ,message_text ,host_id ,inst_id ,adr_home from V$DIAG_ALERT_EXT A where A.ORIGINATING_TIMESTAMP > sysdate-1 and component_id='rdbms' and message_text like '%ORA-12012%' order by 1 desc; |
