Re: ADRCI

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sat, 16 Jan 2016 01:23:56 +0300
Message-ID: <CAOVevU4tycKYBTNeSUqng6pECt_=gBu=waBya5EGQK9nyyz3Bg_at_mail.gmail.com>

v$diag_alert_ext calls x$diag_ALERT_EXT, that's different function and it's heavier than X$DBGALERTEXT.

SQL> set timing on
SQL> select count(*) from X$DBGALERTEXT;

  COUNT(*)

---

     90615

Elapsed: 00:00:02.40
SQL> select count(*) from v$diag_alert_ext;

  COUNT(*)

---

   6238249

Elapsed: 00:03:26.04

Btw, x$diag_ALERT_EXT has fixed index(although it's not very useful), but X$DBGALERTEXT
hasn't.

SQL> _at_fixed_indexes.sql x$diag_ALERT_EXT

TABLE_NAME                     INDEX_NUMBER COLUMN_POSITION COLUMN_NAME
------------------------------ ------------ ---------------
------------------------------
X$DIAG_ALERT_EXT                          3               0 ADR_PATH_IDX

On Sat, Jan 16, 2016 at 1:06 AM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Hi Nassyam,
> Why do you use X$ table and not v$diag_alert_ext? The latter is documented
> and accessible from any schema with enough privilege, not just SYS.
> V$DIAG_ALERT_EXT is just another name for x$dbgalertext, those two "tables"
> are essentially equivalent.
> Regards
>
>
> On 01/15/2016 12:16 PM, Nassyam Basha wrote:
>
> I use SQL Query for same information to avoid using various utilities.
> Here is the query below.
> Again if you are looking only from ADRCI then like above Rajest mentioned.
>
>
> col ORIGINATING_TIMESTAMP for a40
> col MESSAGE_TEXT for a80
> set linesize 500
> SELECT
> originating_timestamp,
> message_text
> FROM
> sys.x$dbgalertext
> WHERE
> message_text LIKE '%&Text%';
>
> Thank You.
>
> On Fri, Jan 15, 2016 at 10:32 PM, Rajesh Aialavajjala <
> <r.aialavajjala_at_gmail.com>r.aialavajjala_at_gmail.com> wrote:
>
>> Woody,
>>
>> Don Seiler (a frequent contributor to this list) had this example in
>> <http://www.nocoug.org/download/2011-08/Seiler_Pythian_Learning_to_Love_the_ADR.pdf>
>> http://www.nocoug.org/download/2011-08/Seiler_Pythian_Learning_to_Love_the_ADR.pdf
>>
>> "show alert -p "message_text like '%ORA%' and originating_timestamp >=
>> systimestamp-30" -term" indicating you can use "AND" in adrci...
>>
>> Thanks,
>>
>> --Rajesh
>>
>> On Fri, Jan 15, 2016 at 11:59 AM, Seth Miller < <sethmiller.sm_at_gmail.com>
>> sethmiller.sm_at_gmail.com> wrote:
>>
>>> The doc is terrible in this case. I had to figure this one out myself.
>>>
>>> show alert -p "MESSAGE_TEXT LIKE 'ORA-%' and ORIGINATING_TIMESTAMP >
>>> systimestamp - 1"
>>>
>>> Seth Miller
>>>
>>> On Fri, Jan 15, 2016 at 10:28 AM, Woody McKay <woody.mckay_at_gmail.com>
>>> wrote:
>>>
>>>> Hi, (12.1.0.2 in win64)
>>>>
>>>> Does anyone have an example of using adrci to view ORA- errors after a
>>>> specific date/time?
>>>>
>>>> I have a good command for seeing all ORA- errors in the alert log, but
>>>> would like to check it after a specific data and time. The docs don't show
>>>> if AND or OR can be used for two predicates
>>>>
>>>> adrci> show alert -p "MESSAGE_TEXT LIKE 'ORA-%'"
>>>>
>>>> Was wondering if somehow I can add -p originating_timestamp or
>>>> something like that.
>>>>
>>>> My overall goal is to monitor the DB alert log for ORA- errors and send
>>>> a notification on new errors. I started a perl, but got excited when I saw
>>>> ADRCI. OEM cost more than leadership will spend.
>>>>
>>>> Thoughts?
>>>>
>>>> --
>>>> Sincerely,
>>>>
>>>> Woody
>>>>
>>>
>>>
>>
>
>
> --
> Nassyam Basha.
> *Oracle Database Consultant*| *Pythian * <http://www.pythian.com/>
> [image: ACED Profile]
> <https://apex.oracle.com/pls/apex/f?p=19297:4:::NO:4:P4_ID:13140>
> Oracle 11g Certified Master
> <http://education.oracle.com/education/otn/NassyamBasha.htm>
> Co-Author: Oracle Data Guard 11gR2
> <http://www.amazon.in/Oracle-Guard-11gR2-Administration-Beginners/dp/1849687900>
> Co-founder of Oraworld-team <http://www.oraworld-team.com>
>
> [image: Facebook] <https://www.facebook.com/nassyambasha> [image:
> Twitter] <https://twitter.com/oracle_ckpt> [image: LinkedIn]
> <https://in.linkedin.com/in/nassyambasha> [image: Google +]
> <https://plus.google.com/+NassyamBasha> [image: CKPT Blog]
> <http://www.oracle-ckpt.com/>
>
>
>
>
> --
> Mladen Gogala
> Oracle DBAhttp://mgogala.freehostia.com
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 15 2016 - 23:23:56 CET