Skip to Main Content
  • Questions
  • Querying V$DIAG_ALERT_EXT on another instance

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mike.

Asked: May 12, 2021 - 7:26 am UTC

Last updated: June 08, 2021 - 2:34 am UTC

Version: 19

Viewed 100+ times

You Asked

Our application connects to the database via the Oracle Instant Client using the scan address on RAC, we therefore end up connecting to an arbitrary instance. We would ideally like to be able to query the alert log directly from our application, there is a V$DIAG_ALERT_EXT but no corresponding GV$DIAG_ALERT_EXT. Is there any way that we can query V$DIAG_ALERT_EXT on the other instances that we are not connected to?

and we said...

Have you tried this?

1) Create an instance specific tnsnames.ora

DIRECT_TO_INSTANCE_DB1 = 
....
....
(connect_data= 
    (service_name=db.us.acme.com)
    (instance_name=db1)))


2) create a database link using the tnsnames entry

3) Then query the table via the database link

If you had several instances in the cluster, then its a case of one tns entry / db link per instance, and then a union all

Rating

  (3 ratings)

Comments

A reader, June 03, 2021 - 5:20 am UTC


Mike, June 03, 2021 - 7:30 am UTC

We were hoping for a solution that didn't require extra configuration outside of our application, but it does seem like that would be our only option. Is there a valid reason why Oracle doesn't provide a GV$DIAG_ALERT_EXT?
Connor McDonald
June 08, 2021 - 2:30 am UTC

I'll ask around internally and post any findings.

Possible Alternative

Victor Torres, June 03, 2021 - 11:40 am UTC

I think to remember this used to work, but I have not try it for a while and don't remember from where I got it from... so please test it first (no that we should mentioned that, right?)


set lines 500
set pages 50
col originating_timestamp for a40
col rownum for 999999
col Error for a150
-- SET PAUSE ON
-- SET PAUSE 'Press Return to Continue'
select inst_id, rownum "Line",TO_CHAR(originating_timestamp,'DD-MON-YYYY HH24:MI:SS'), message_text "Error"
from TABLE(gv$(cursor(select inst_id, originating_timestamp, message_text
from v$diag_alert_ext
where originating_timestamp >= (sysdate - 1)
AND regexp_like(message_text, '(ORA-|error)'))))
order by originating_timestamp asc;

Connor McDonald
June 08, 2021 - 2:34 am UTC

Nice touch. I'd forgotten about the GV$ function.