October 20, 2018

Update! - Unexpected Behaviour With V$DIAG_ALERT_EXT

Oops! Errormessages from a Test database in my Production-DB?!?


Story behind:

I wanted to know which "ORA-" Errors have occured in my Production-DB and issued a
select

  to_char(originating_timestamp,'DD.MM.YYYYHH24:MI:SS')

  ,message_text

from v$diag_alert_ext
where message_text like '%ORA-%'
and originating_timestamp > sysdate-31
order by originating_timestamp;
to have an overview about the current - let me say - error situation. So far - so good ...

Yesterday, I've found messages from a TEST database when querying the PRODUCTION db, which made me uncertain:
"Errors in file /u01/app/oracle/diag/rdbms/TEST/TEST/trace/TEST_j000_45562.trc"

Checked that - and discussed it with some collegues ... outcome is:

The View V$DIAG_ALERT_EXT does not only contain information from the current DB but from all databases on that system / from that diagnostic_dest:
select  distinct component_id,filename
from v$diag_alert_ext
order by 1,2;
COMPONENT_ID FILENAME
------------ -----------------------------------------------------------------------
apx          /u01/app/oracle/diag/apx/+apx/+APX1/alert/log.xml
asm          /u01/app/oracle/diag/asm/+asm/+ASM1/alert/log.xml
clients      /u01/app/oracle/diag/clients/user_oracle/host_4163035053_107/alert/log.xml
clients      /u01/app/oracle/diag/clients/user_oracle/host_4163035053_82/alert/log.xml
crs          /u01/app/oracle/diag/crs/host/crs/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest1/DBTEST1/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest10/DBTEST10/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest11/DBTEST11/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest12/DBTEST12/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest13/DBTEST13/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest14/DBTEST14/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest2/DBTEST2/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest3/DBTEST3/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest4/DBTEST4/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest5/DBTEST5/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest6/DBTEST6/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest7/DBTEST7/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest8/DBTEST8/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/dbtest9/DBTEST9/alert/log.xml
rdbms        /u01/app/oracle/diag/rdbms/rocrtest/rocrtest/alert/log.xml
tnslsnr      /u01/app/oracle/diag/tnslsnr/host/asmnet1lsnr_asm/alert/log.xml
tnslsnr      /u01/app/oracle/diag/tnslsnr/host/listener/alert/log.xml
tnslsnr      /u01/app/oracle/diag/tnslsnr/host/listener_511/alert/log.xml
tnslsnr      /u01/app/oracle/diag/tnslsnr/host/listener_576/alert/log.xml
tnslsnr      /u01/app/oracle/diag/tnslsnr/host/listener_580/alert/log.xml

Well - that was unexpected ... 

Question is: Bug or feature? I'll open a SR and keep You up to date ...
By the way: Made these tests on different oracle releases: 11.2, 12.1, 12.2.

Update (November 14th, 2018)

Opened a Service Request - and Oracle Support brought an explanation:

"We have verified it. It is an expected scenario. As stated earlier V$DIAG_ALERT_EXT displays trace file and alert file data for the current container (It means all PDB) in a CDB (Even though you connect as ALTER SYSTEM SET CONTAINER - it will show all the database because all database resides on ORACLE HOME). 

Actually, V$DIAG_ALERT_EXT read the logs of all databases and listeners from the ADR Location (ORACLE HOME Directory) So, one connection to a database is enough to see all the database alert files and listener logs registered inside the ADR structure. 

So, this is an expected one. And not a BUG! 
"


Share:

6 comments:

  1. Hi Robert,
    That seems to be common behavior.
    >>Question is: Bug or feature? I'll open a SR and keep You up to date ...
    Did you get a response from Oracle?

    ReplyDelete
  2. 'common behaviour' - well ... IMHO, this is a bug. When I query a database view, I do not expect results from other databases. Nevertheless - Oracle support is (still) working on that

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. Looks like this behaviour only holds true for multi-tenant architecture.
    We have not migrated to the CDB/PDB architecture yet, and we only see a single alert.log entry in v$diag_alert_ext - not even the trace files, etc, from the same database.

    ReplyDelete

Note: Only a member of this blog may post a comment.

Copyright © Robert Crames' Oracle Database and Middleware Blog | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by NewBloggerThemes.com