I was testing the process of retrieving the Alert Log and other diagnostic features from the Oracle Database and GoldenGate into Grafana for improved observability. There are several ways to do this, but I think parsing and querying the alert.log file directly from the Oracle Database is a solid option for some cases. Additionally, Oracle has an internal view starting with Oracle 12c that really helps out.

V$DIAG_ALERT_EXT

This view allows querying recent entries in the alert log using SQL, without needing access to the operating system or the physical log file.

---

1. Query the Alert Log (Basic Example)

SELECT originating_timestamp, message_text
FROM   V$DIAG_ALERT_EXT
WHERE  originating_timestamp > SYSDATE - 1
ORDER  BY originating_timestamp DESC;

This shows alert log messages from the past 24 hours.

---

2. Filter Specific Messages (e.g., ORA- errors)

SELECT originating_timestamp, message_text
FROM   V$DIAG_ALERT_EXT
WHERE  message_text LIKE '%ORA-%'
ORDER  BY originating_timestamp DESC;

---

3. Search for Startup or Shutdown Events

SELECT originating_timestamp, message_text
FROM   V$DIAG_ALERT_EXT
WHERE  message_text LIKE '%Starting ORACLE instance%' 
   OR  message_text LIKE '%Shutting down instance%'
ORDER  BY originating_timestamp DESC;

---

4. Look for Deadlock Detected

SELECT originating_timestamp, message_text
FROM   V$DIAG_ALERT_EXT
WHERE  message_text LIKE '%deadlock detected%'
ORDER  BY originating_timestamp DESC;

---

Notes:

The underlying table is populated in near-real-time.

It is available only if ADR is enabled.

Automatic Diagnostic Repository (ADR) is enabled by default in Oracle 11g and later. However, you can verify whether ADR is enabled for your Oracle instance using the following steps:

SHOW PARAMETER diagnostic_dest;
SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';

Option: Use Oracle REST Data Services (ORDS) to Expose a Custom View

1. Create a REST endpoint over V$DIAG_ALERT_EXT.
2. Use the Grafana JSON or REST plugin to pull logs.

This works if direct DB access is not allowed, but you can expose Oracle data securely via REST APIs.