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



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;
------------ -----------------------------------------------------------------------
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 There!

in my blogpost "http://robertcrames.blogspot.com/2018/10/oda-bug-and-poor-support-from-oracle.html" I described a problem with the impossibility of creating databases as a result of an incredebly long DBID and the 'poor' oracle support in that specific case.

Well - I contacted Oracle support and had a call with the Support Manager and then ... it worked smoothly 😏. Provided a few more information and Oracle support had an idea:

'Remove any changes from the .bash_profile and try again' ... Unfortunately, we made no changes on root's .bash_profile (which is the 'initiator' of the create database process) :-( - but we made some changes on oracle's .bash_profile. One of these changes was, to set oracle's primary environment to Grid Infrastructure' instance +ASM1. Removed that entry, retried the 'odacli create-database' - and ... it works - DBID is correct and plausible, everything runs fine. :-)


October 12, 2018

DBID Problem @ ODA ... Bug (?)

long story told short: 

  • Oracle Database Appliance X6-2M and X7-2M, 
  • oak up to

We were not able to create databases anymore on one of our 15 ODAs. Detailled analysis has shown that the dbid - result from an 'odacli describe-database -i <DB identifier>' - grows and grows and, when it reaches a size of more than 255 characters, any subsequent try to create a database will fail and leave the database in status 'creating'. No more databases can be created, neither can You delete the database which is left in status 'creating'.

This is what a correct DBID (result from a describe-database) looks like: 1653395885 - btw: this is exactly the real dbid found in the database (select dbid from v$database).

An - imho - 'corrupted' DBID looks like this: 2791133100113200112102253200112102127320011210212832001121021533200112102154320011210215632001121021573200112102159320011210217323200112102177232001121021793200112102181320011210218323200112102186320011210215800112102576011210251101121021196170968 - the last 'n' numbers represent the real dbid (select dbid from v$database).

Side effect, when the database creation has failed: 
odacli list-databases
DCS-10001:Internal error encountered: could not execute query.

Would be great if You could do this:

  • If You have more than five databases on an ODA, issue an 'odacli list-databases'
  • take the id from the last created database and issue an 'odacli describe-database -i <id>'
  • Post the result as a comment in this blog

Regarding oracle support:

The Service Request is now open since weeks. Started as a Sev 2, relatively early changed sev to 1. The SR is updated from oracle support very rarely, usually no answer at all when I update the SR. In the meantime I escalated the SR, asked for a recall from a manager - no result and no recall. Today asked for another recall from another manager - will see how long that will take. 

@OracleSupport: that is very poor ... leaving customers helpless and uninformed with such severe Problems. Very disappointing ...

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