Thinking Out Loud

December 17, 2017

Not Another adrci Examples

Filed under: 12c,adrci — mdinh @ 4:30 am

I know there are a lot of blog posts on ADRI, etc…

However, none of them solved what I was looking for, “How to show all specific problems?”


--- When there are more than 50 incidents
50 rows fetched (*** more available ***)
show incident -all

--- Show specific problem for ORA errors
show problem -all -p "problem_key='ORA 1578'"

---Generates the package for the problem id 100 in /tmp
ips pack problem 100 in /tmp

---Generates the package for the incident id 6439 in /tmp
ips pack incident 6439 in /tmp

---Generates the package for the problem with the problem_key 'ORA 1578'
ips pack problemkey "ORA 1578"

---Generates the package with the incidents occurred in last 8 seconds.
ips pack seconds 8

---Generates the package with the incidents occurred
---between the times '2007-05-01 10:00:00.00' and '2007-05-01 23:00:00.00'
ips pack time '2007-05-01 10:00:00.00' to '2007-05-01 23:00:00.00'
Advertisements

March 20, 2017

adrci purging

Filed under: adrci,oracle — mdinh @ 10:31 pm

I did not know this.

Is there a way to control Auto_Purge Frequency done by the MMON ? (Doc ID 1446242.1)

The automatic purge cycle is designed as follows.
(1) The first actual purge action will be 2 days after instance startup time
(2) The next automatic purge actions following this first purge is done once every 7 days

If you like to purge more often, then it will need to be done manually.

The blog below was every helpful for creating adrci scripts.
https://grepora.com/2016/08/03/adrci-retention-policy-and-ad-hoc-purge-script-for-all-bases/

Here is what I have created.

$ ./adrci_show_control.sh

SHOW CONTROL diag/crs/arrow1/crs:

ADR Home = /u01/app/oracle/diag/crs/arrow1/crs:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1344875867           720                  8760                 2016-11-24 19:05:55.164304 -08:00                                                 2017-02-28 19:56:23.753525 -08:00        1                    2                    82                   1                    2016-11-24 19:05:55.164304 -08:00
1 rows fetched

SHOW CONTROL diag/rdbms/hawka/HAWKA:

ADR Home = /u01/app/oracle/diag/rdbms/hawka/HAWKA:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1630649358           1                    1                    2017-03-04 10:01:39.568251 -08:00        2017-03-18 07:00:21.124556 -07:00        2017-02-28 19:55:26.148874 -08:00        1                    2                    80                   1                    2016-11-27 18:22:12.601136 -08:00
1 rows fetched

SHOW CONTROL diag/rdbms/test/test:

ADR Home = /u01/app/oracle/diag/rdbms/test/test:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
2768052777           720                  8760                 2017-03-04 18:10:18.197875 -08:00                                                                                          1                    2                    80                   1                    2017-03-04 18:10:18.197875 -08:00
1 rows fetched

$ ./adrci_set_control.sh

SET CONTROL diag/crs/arrow1/crs:
SET CONTROL diag/rdbms/hawka/HAWKA:
SET CONTROL diag/rdbms/test/test:

$ ./adrci_purge.sh

PURGE diag/crs/arrow1/crs:

ADR Home = /u01/app/oracle/diag/crs/arrow1/crs:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1344875867           2160                 2880                 2017-03-20 15:02:48.861513 -07:00                                                 2017-03-20 15:03:01.019503 -07:00        1                    2                    82                   1                    2016-11-24 19:05:55.164304 -08:00
1 rows fetched

PURGE diag/rdbms/hawka/HAWKA:

ADR Home = /u01/app/oracle/diag/rdbms/hawka/HAWKA:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1630649358           2160                 2880                 2017-03-20 15:02:48.879455 -07:00        2017-03-18 07:00:21.124556 -07:00        2017-03-20 15:03:01.348572 -07:00        1                    2                    80                   1                    2016-11-27 18:22:12.601136 -08:00
1 rows fetched

PURGE diag/rdbms/test/test:

ADR Home = /u01/app/oracle/diag/rdbms/test/test:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
2768052777           2160                 2880                 2017-03-20 15:02:48.894455 -07:00                                                 2017-03-20 15:03:01.442372 -07:00        1                    2                    80                   1                    2017-03-04 18:10:18.197875 -08:00
1 rows fetched

$ cat adrci_show_control.sh

for f in $( adrci exec="show homes" | grep -v "ADR Homes:" );
do
echo "SHOW CONTROL ${f}:";
adrci exec="set home $f; show control;" ;
done

$ cat adrci_set_control.sh

for f in $( adrci exec="show homes" | grep -v "ADR Homes:" );
do
echo "set control ${f}:";
adrci exec="set home $f; set control \(SHORTP_POLICY=2160, LONGP_POLICY=2880\);" ;
done

$ cat adrci_purge.sh

for f in $( adrci exec="show homes" | grep -v "ADR Homes:" );
do
echo "purge ${f}:";
adrci exec="set home $f; show control; purge" ;
done

ADRCI Retention Policy and Ad-Hoc Purge Script for all Bases

Filed under: adrci,oracle — mdinh @ 10:31 pm

GREPORA

As you know, since 11g we have a Automatic Diagnostic Repository (ADR). To better manage it, we also have a Command-line Interface, called ADRCI.
ADR contains all diagnostic information for database (logs, traces, incidents, problems, etc).

adr1

ADR Structure

View original post 349 more words

June 2, 2011

Alert Log Monitoring using ADRCI

Filed under: 11g,adrci,oracle — mdinh @ 1:48 am

This has been tested on: SunOS 5.10 Generic_142909-17 sun4u sparc SUNW,SPARC-Enterprise

If my memory serves me correctly, you will need to change “ps -eo args” for Linux and other OS accordingly.

Note: db_unique_name = lax_${sid} (primary) / san_${sid} (standby), which you might have already guessed.

Here is the script:

#!/bin/sh

. /home/oracle/.common.conf > /dev/null

DN=`dirname $0`
BN=`basename $0`
EXCEPTIONS=$SCRIPT_DIR/alert_exceptions

DCP=lax # primary data center
DCS=san # standby data center
for sid in `ps -eo args|grep ora_smon|grep -v grep|awk -F_ '{print $3}'`
do
  OUTF=$LOG/${sid}.alert.log
  ORACLE_SID=$sid
  . oraenv
  PRIM=diag/rdbms/${DCP}_${sid}/$sid
  STBY=diag/rdbms/${DCS}_${sid}/$sid
  if [ -r $ORACLE_BASE/$PRIM ]; then
    DIAG=$PRIM
    DB=${DCP}_${sid}
  else
    if [ -r $ORACLE_BASE/$STBY ]; then
      DIAG=$STBY
      DB=${DCS}_${sid}
    fi
  fi
  adrci > $OUTF << EOF   
  set home ${DIAG}   
  SHOW ALERT -term -P "MESSAGE_TEXT like 'ORA-%' and originating_timestamp > systimestamp-1/1440"
  exit
EOF
  if [ `egrep '^ORA-' $OUTF|egrep -cvf $EXCEPTIONS` != 0 ]; then
    mailx -r oracle@domain.com -s "OERR: $BN $DB" $EMAIL < $OUTF
  fi
done
exit

Here is an example of email notification:

From: oracle@domain.com
Sent: Wednesday, June 01, 2011 1:43 PM
To: dba
Subject: OERR: monitor_alert.sh dw02

ADRCI: Release 11.2.0.2.0 - Production on Wed Jun 1 13:43:01 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> adrci>
ADR Home = /u01/app/oracle/diag/rdbms/lax_dw02/dw02:
*************************************************************************
2011-06-01 13:42:10.542000 -07:00
ORA-1652: unable to extend temp segment by 4 in tablespace                 TEMP
ORA-1652: unable to extend temp segment by 4 in tablespace                 TEMP
adrci>

UPDATE: There was an error in the original cut & paste. Improved script.

Create a free website or blog at WordPress.com.