First of all, in order to find your log files location in the operating system where the database is located, you can query to V$DIAG_INFO view. It contains location details about all type of log files.
SQL> set lines 200
SQL> col name format a30
SQL> col value format a80
SQL> SELECT * FROM V$DIAG_INFO;
INST_ID NAME VALUE CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
1 Diag Enabled TRUE 0
1 ADR Base /opt/oracle 0
1 ADR Home /opt/oracle/diag/rdbms/oracdb/oracdb 0
1 Diag Trace /opt/oracle/diag/rdbms/oracdb/oracdb/trace 0
1 Diag Alert /opt/oracle/diag/rdbms/oracdb/oracdb/alert 0
1 Diag Incident /opt/oracle/diag/rdbms/oracdb/oracdb/incident 0
1 Diag Cdump /opt/oracle/diag/rdbms/oracdb/oracdb/cdump 0
1 Health Monitor /opt/oracle/diag/rdbms/oracdb/oracdb/hm 0
1 Default Trace File /opt/oracle/diag/rdbms/oracdb/oracdb/trace/oracdb_ora_24743.trc 0
1 Active Problem Count 0 0
1 Active Incident Count 0 0
INST_ID NAME VALUE CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
1 ORACLE_HOME /opt/oracle/product/19.0.0/dbhome_1 0
You can go to trace file directory now. You can use TAIL command in order to get the last N lines of the file. The -f parameter refreshes the screen when the file gets populated.
[root@dblive ~]# su - oracle
Last login: Sun Mar 28 15:01:46 +03 2021 on pts/0
[oracle@dblive ~]$
[oracle@dblive ~]$ pwd
/home/oracle
[oracle@dblive ~]$ cd /opt/oracle/diag/rdbms/oracdb/oracdb/trace
[oracle@dblive trace]$ find . -type f -iname "*alert*"
./alert_oracdb.log.save
./.alert_oracdb.log.swp
./.alert_oracdb.log.swo
./.alert_oracdb.log.swn
./.alert_oracdb.log.swm
./.alert_oracdb.log.swl
./alert_oracdb.log
[oracle@dblive trace]$ tail -100f alert_oracdb.log
Viewing alert log file via SQL
There is a fixed table called X$DBGALERTEXT. If you query it, Oracle reads the log.xml file, parses the data and returns as rows. You can also use V$DIAG_ALERT_EXT view to query XML alert log from the database. MESSAGE_TYPE and MESSAGE_LEVEL columns description is shown below.
SELECT * FROM X$DBGALERTEXT;
SELECT * FROM V$DIAG_ALERT_EXT;
SELECT * FROM V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > (SYSDATE - 1/24)
AND MESSAGE_TYPE IN (2, 3);
MESSAGE_TYPE
1: UNKNOWN - Essentially the NULL type
2: INCIDENT_ERROR - The program has encountered an error for some internal or unexpected reason, and it must be reported to Oracle Support
3: ERROR - An error of some kind has occurred
4: WARNING: An action occurred or a condition was discovered that should be reviewed and may require action
5: NOTIFICATION: reports a normal action or event. This could be a user action such as "logon completed"
6: TRACE: Output of a diagnostic trace
MESSAGE_LEVEL
1: CRITICAL: critical errors
2: SEVERE: severe errors
8: IMPORTANT: important message
16: NORMAL: normal message
SQL> desc X$DBGALERTEXT;
Ad² Bo■? T³r
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
CON_ID NUMBER
ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(64)
COMPONENT_ID VARCHAR2(64)
HOST_ID VARCHAR2(64)
HOST_ADDRESS VARCHAR2(46)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(64)
MESSAGE_GROUP VARCHAR2(64)
CLIENT_ID VARCHAR2(64)
MODULE_ID VARCHAR2(64)
PROCESS_ID VARCHAR2(32)
THREAD_ID VARCHAR2(64)
USER_ID VARCHAR2(128)
INSTANCE_ID VARCHAR2(64)
DETAILED_LOCATION VARCHAR2(160)
PROBLEM_KEY VARCHAR2(550)
UPSTREAM_COMP_ID VARCHAR2(100)
DOWNSTREAM_COMP_ID VARCHAR2(100)
EXECUTION_CONTEXT_ID VARCHAR2(100)
EXECUTION_CONTEXT_SEQUENCE NUMBER
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
VERSION NUMBER
MESSAGE_TEXT VARCHAR2(2048)
MESSAGE_ARGUMENTS VARCHAR2(512)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(512)
SUPPLEMENTAL_DETAILS VARCHAR2(4000)
PARTITION NUMBER
RECORD_ID NUMBER
CON_UID NUMBER
CONTAINER_NAME VARCHAR2(64)
ATTENTION_ID NUMBER
ID_SUFFIX VARCHAR2(64)
OPERATION_ID VARCHAR2(64)
CAUSE_TEXT VARCHAR2(2048)
ACTION_TEXT VARCHAR2(2048)
ORACLE_PROCESS_ID NUMBER
DATABASE_ID VARCHAR2(30)
SQL_ID VARCHAR2(13)
SESSION_ID VARCHAR2(16)
IMPACT_ID VARCHAR2(32)
IMPACT_SCOPE VARCHAR2(32)
CALL_STACK VARCHAR2(1024)
FLAGS NUMBER
Using ADRCI
You can also use ADRCI utility, which stands for Automatic Diagnostic Repository Command Interface. ADRCI is the command-line tool that enables you to view diagnostic data as well as the health monitor reports. The main purposes of ADRCI is, monitoring the diagnostic files, purging them and creating incident packages to send the Oracle support.
[oracle@dblive ~]$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Sun Mar 28 16:37:33 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
ADR base = "/opt/oracle"
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
ESTIMATE
EXIT
HELP
HOST
IPS
PURGE
RUN
SELECT
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW LOG
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
adrci>
If you want to get information about any of the ADR commands, you can use HELP. In order to get detailed explanation, type HELP at the beginning of the command as shown below.
adrci> help show alert
Usage: SHOW ALERT [-p <predicate_string>] [-term]
[ [-tail [num] [-f]] | [-file <alert_file_name>] ]
Purpose: Show alert messages.
Options:
[-p <predicate_string>]: The predicate string must be double-quoted.
The fields in the predicate are the fields:
ORIGINATING_TIMESTAMP timestamp
NORMALIZED_TIMESTAMP timestamp
ORGANIZATION_ID text(65)
COMPONENT_ID text(65)
HOST_ID text(65)
HOST_ADDRESS text(47)
MESSAGE_TYPE number
MESSAGE_LEVEL number
MESSAGE_ID text(65)
MESSAGE_GROUP text(65)
CLIENT_ID text(65)
MODULE_ID text(65)
PROCESS_ID text(33)
THREAD_ID text(65)
USER_ID text(65)
INSTANCE_ID text(65)
DETAILED_LOCATION text(161)
UPSTREAM_COMP_ID text(101)
DOWNSTREAM_COMP_ID text(101)
EXECUTION_CONTEXT_ID text(101)
EXECUTION_CONTEXT_SEQUENCE number
ERROR_INSTANCE_ID number
ERROR_INSTANCE_SEQUENCE number
MESSAGE_TEXT text(2049)
MESSAGE_ARGUMENTS text(513)
SUPPLEMENTAL_ATTRIBUTES text(513)
SUPPLEMENTAL_DETAILS text(4000)
PROBLEM_KEY text(551)
CON_UID number
CONTAINER_ID number
CONTAINER_NAME text(31)
[-tail [num] [-f]]: Output last part of the alert messages and
output latest messages as the alert log grows. If num is not specified,
the last 10 messages are displayed. If "-f" is specified, new data
will append at the end as new alert messages are generated.
[-term]: Direct results to terminal. If this option is not specified,
the results will be open in an editor.
By default, it will open in vi on Linux, but "set editor" can be used
to set other editors.
[-file <alert_file_name>]: Allow users to specify an alert file which
may not be in ADR. <alert_file_name> must be specified with full path.
Note that this option cannot be used with the -tail option
Examples:
show alert
show alert -p "message_text like '%incident%'"
show alert -tail 20
To show the last lines of the alert file, -tail parameter can be used. Also, -f is used to wait and report the new added lines. In the following example the last 50 lines are shown from alert log.
show alert -tail 50
Show the last 50 lines and wait any new lines.
show alert -tail 50 -f
Filter the alert logs which contains ‘ORA’ in MESSAGE_TEXT in the last one day.
show alert -p "MESSAGE_TEXT LIKE '%ORA%' AND ORIGINATING_TIMESTAMP > SYSTIMESTAMP - 1"
Show trace files that contains lgwr in the filename. -t is used to sort the filenames in the modified timestamp order.
show tracefile %lgwr% -t
Show the content of a specific trace file.
show trace diag/rdbms/tamlivec/tamlivec/trace/oracdb_lgwr_12007.trc
Show ADR purge settings.
show control
I hope this helps 🙂