Thinking Out Loud

March 27, 2021

Cleanup Trace Files For Multiple Oracle Homes

Filed under: adrci,awk_sed_grep,linux,oracle — mdinh @ 4:36 pm

I know what you are probably thinking. What’s the big deal and how many homes can there be?

For Exadata Cloud, I recalled seeing as many as 18 database homes.

As shown below, there are 5 database homes with version 12.2 and 1 database home with version 19.0.

# dbaascli dbhome info
DBAAS CLI version 21.1.1.0.1
Executing command dbhome info
Enter a homename or just press enter if you want details of all homes

1.HOME_NAME=OraHome101
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_4
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=
   OH Backup=NOT Configured 

2.HOME_NAME=OraHome100
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_7
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*****
   Agent DB IDs=d21b07df-20f2-439e-bc40-78a9597af362
 OH Backup=NOT Configured

3.HOME_NAME=OraHome105_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_6
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=******
   Agent DB IDs=f7d46615-a223-4002-9270-fa69465a7f2a
 OH Backup=NOT Configured

4.HOME_NAME=OraHome102_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_3
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*****
   Agent DB IDs=dceed071-9655-4c84-bef4-74b20180c99b
 OH Backup=NOT Configured

5.HOME_NAME=OraHome101_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_2
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*******
   Agent DB IDs=b2a5220d-844b-49b6-9351-7c72cf3c9d9b
 OH Backup=NOT Configured

6.HOME_NAME=OraHome100_19800_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/19.0.0.0/dbhome_2
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0
  DBs installed=********
   Agent DB IDs=feedb0e0-2d10-4db7-997a-a78e4ab083ef

Checking oratab for Oracle Homes

$ sort -u -t : -k 2,2 /etc/oratab | grep -v "^#" | awk -F ":" '{print $2}'
/u01/app/19.0.0.0/grid
/u02/app/oracle/product/12.2.0/dbhome_2
/u02/app/oracle/product/12.2.0/dbhome_3
/u02/app/oracle/product/12.2.0/dbhome_4
/u02/app/oracle/product/12.2.0/dbhome_6
/u02/app/oracle/product/12.2.0/dbhome_7
/u02/app/oracle/product/19.0.0.0/dbhome_2

Here is the crontab schedule:

00 01 * * * find /u01/app/grid/diag/crs/*/crs/trace -name "*.tr?" -mtime +30 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/audit -name "*.aud" -mtime +366 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "*.tr?" -mtime +200 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "cdmp*" -mtime +200 -exec rm -rf {} \;
00 04 * * * find /u02/app/oracle/diag/rdbms/*/*/cdump -name "core*" -mtime +200 -exec rm -rf {} \;

Here is the explanation for what (*) represents and examples:

00 01 * * * find /u01/app/grid/diag/crs/*/crs/trace -name "*.tr?" -mtime +30 -exec rm -f {} \;

ls -ld /u01/app/grid/diag/crs/*/crs/trace
* = hostname

Example:
$ ls -ld /u01/app/grid/diag/crs/*/crs/trace
drwxrwxr-x 2 grid oinstall 135168 Mar 26 18:40 /u01/app/grid/diag/crs/hostname/crs/trace

==============================

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/audit -name "*.aud" -mtime +366 -exec rm -f {} \;

ls -ld /u02/app/oracle/product/*/*/rdbms/audit
*/* = version/dbhome

Example:
$ ls -ld /u02/app/oracle/product/*/*/rdbms/audit
drwxr-xr-x 9 oracle oinstall  614400 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/audit
drwxr-xr-x 2 oracle oinstall  253952 Mar 26 18:40 /u02/app/oracle/product/12.2.0/dbhome_3/rdbms/audit
drwxr-xr-x 2 oracle oinstall  294912 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/audit
drwxr-xr-x 4 oracle oinstall   94208 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_6/rdbms/audit
drwxr-xr-x 2 oracle oinstall    4096 Mar  1 02:31 /u02/app/oracle/product/12.2.0/dbhome_7/rdbms/audit
drwxr-xr-x 3 oracle oinstall 5783552 Mar 26 18:32 /u02/app/oracle/product/19.0.0.0/dbhome_2/rdbms/audit

==============================

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "*.tr?" -mtime +200 -exec rm -f {} \;

ls -l /u02/app/oracle/product/*/*/rdbms/log/*.tr?
*/* = version/dbhome

Example:
$ ls -l /u02/app/oracle/product/*/*/rdbms/log/*.tr?
-rw-r----- 1 oracle asmadmin 868 Feb 19 17:41 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/log/*******2_ora_57506.trc
-rw-r----- 1 oracle asmadmin 868 Dec  4 18:06 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/log/*******2_ora_66404.trc
-rw-r----- 1 oracle asmadmin 862 Mar 24 19:38 /u02/app/oracle/product/12.2.0/dbhome_3/rdbms/log/*****2_ora_217755.trc
-rw-r----- 1 oracle asmadmin 869 Feb 18 21:51 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/log/*****2_ora_351349.trc
-rw-r----- 1 oracle asmadmin 867 Feb 19 17:41 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/log/*****2_ora_57519.trc
-rw-r----- 1 oracle asmadmin 866 Mar  1 20:01 /u02/app/oracle/product/12.2.0/dbhome_6/rdbms/log/******2_ora_167170.trc
-rw-r----- 1 oracle asmadmin 831 Mar  1 02:31 /u02/app/oracle/product/12.2.0/dbhome_7/rdbms/log/*****2_ora_314160.trc

==============================

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "cdmp*" -mtime +200 -exec rm -rf {} \;

ls -ld /u02/app/oracle/diag/rdbms/*/*/cdump
*/* = db_unique_name/db_name

Example:
$ ls -ld /u02/app/oracle/diag/rdbms/*/*/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep  3  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep  2  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 21  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Feb 17 02:35 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 21  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Feb 18 21:51 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 25 07:13 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump

It’s also possible to use adrci to configure SHORTP_POLICY and LONGP_POLICY.

If new homes are created, then would SHORTP_POLICY and LONGP_POLICY need up be updated for the new homes?

Alternatively, can download and use purgeLogs: Cleanup traces, logs in one command (Doc ID 2081655.1)

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'

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.

Blog at WordPress.com.