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 ato have an overview about the current - let me say - error situation. So far - so good ...select
to_char(originating_timestamp,'DD.MM.YYYYHH24:MI:SS')
,message_text
from v$diag_alert_extwhere message_text like '%ORA-%'and originating_timestamp > sysdate-31order by originating_timestamp;
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!
"
Hi Robert,
ReplyDeleteThat 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?
'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
ReplyDeleteHope you got answer for this :)
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteYou can use the x$dbgalertext
ReplyDeleteLooks like this behaviour only holds true for multi-tenant architecture.
ReplyDeleteWe 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.