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.<\/p>\n
The following command can be used to create a virtual directory that points to the trace directory in the database named ORCL.<\/p>\n
CREATE OR REPLACE DIRECTORY \nALERT_LOG_DIR AS \n'\/u01\/app\/oracle\/diag\/rdbms\/orcl\/ORCL\/trace';<\/pre>\nCreate an External Table<\/h1>\n
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.<\/p>\n
CREATE TABLE ORCL_ALERT_LOG\n(\n MESSAGE VARCHAR2(4000 BYTE)\n)\nORGANIZATION EXTERNAL\n ( TYPE ORACLE_LOADER\n DEFAULT DIRECTORY ALERT_LOG_DIR\n ACCESS PARAMETERS \n ( records delimited by newline logfile alert_log_dir:'orcl_alert_table.log' )\n LOCATION (ALERT_LOG_DIR:'alert_ORCL.log')\n )\nREJECT LIMIT UNLIMITED;<\/pre>\nBecause trace indexes are different for each instance on RAC systems, different tables must be created.<\/p>\n
For example, you can query if there is a ” ORA-12012 ” error in the alert log as follows.<\/p>\n
SQL> set lines 1000\nSQL> select * from DBA_ALERT_LOG where MESSAGE like '%ORA-12012%';\n\nMESSAGE\n----------------------------------------------------------------------------\nORA-12012: error on auto execute of job 2\nORA-12012: error on auto execute of job 2222<\/pre>\nRead Oracle Alert Log Using V$DIAG_ALERT_EXT<\/h1>\n
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.<\/p>\n
You can query V$DIAG_ALERT_EXT as follows:<\/p>\n
select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME\n,message_text\n,host_id\n,inst_id\n,adr_home \nfrom V$DIAG_ALERT_EXT A\nwhere component_id='rdbms'\nand message_text like '%ORA-12012%'\norder by 1 desc;<\/pre>\nselect TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME\n,message_text\n,host_id\n,inst_id\n,adr_home \nfrom V$DIAG_ALERT_EXT A\nwhere A.ORIGINATING_TIMESTAMP > sysdate-1\nand component_id='rdbms'\nand message_text like '%ORA-12012%'\norder by 1 desc;<\/pre>\n\n<\/div>\n