grant select on V$DIAG_ALERT_EXT to MYUSER The next step is to get records from the standby’s alert log associated with redo log apply. These include “Media Recovery Log” in the MESSAGE_TEXT columns of both X$DBGALERTEXT and V$DIAG_ALERT_EXT. The alert log record needs to be joined to a matching row in the standby’s V$ARCHIVED_LOG table.
NOTE: The Oracle: DB Log Alerts Config Dynamic Application requires that the database administrator grant SELECT privileges in the v$diag_alert_text to the monitoring user. Oracle: DB Logswitch Rate Stats Oracle: DB Open Cursors per Session Stats Oracle: DB Performance Stats Oracle: DB RAC Disk Group Space Stats, SELECT * FROM GV$LOCK WHERE INST_ID = 2 OR INST_ID = 5 retrieves the information from the V$ views on instances 2 and 5. The GV$ views can be used to return information on groups of instances defined with the OPS_ADMIN_GROUP parameter. For more information see OPS_ADMIN_GROUP on page 1-86 and Oracle8 Parallel Server Concepts and Administration.
V$DIAG_ALERT_EXT Oracle DB: Alert Log (default behavior) successfully on all recent •Standard view, will work databases Slow to return results on some databases SQL access referencing custom view V$ALERT_LOG Oracle DB: Alert Log • (enabled with custom credential) Performs well • No special privileges required • Requires custom view, A new role APPLICATION_TRACE_VIEWER grants to select on views that show only SQL and Optimizer traces. Alert.log. This is not new in 12.2, you can query V$DIAG_ALERT_EXT to see the alert.log content. Of course, from the PDB you will see only PDB related content: Endian type of dictionary set to little Autotune of undo retention is turned on.
v$diag_alert_ext aka x$diag_alert_ext should be useful for querying the oracle alert log. Unfortunately with comon conditions in the where clause, queries against it run unusably slow. This is covered in Mos doc 1684140.1 “Selects from v$diag_alert_ext run slowly with large alert logs“, but that ends with the unhelpful “Solution: There is no solution.“.
