Hello,
on a daily / hourly basis I need need to check the size for the Flash Recovery Area (v$flash_recovery_area_usage)
I was not in work yesterday and when I arrived today I checked the alert log only to find that the usage was 100%. This means the overnight backups will not run.
I was wondering if someone could provide a script that gives a warning at 80%
The SQL that I use to determine the ASM Flash Recovery Usage is -->
SQL> COL % FORMAT 99.0
SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "%" FROM v$asm_diskgroup;
NAME FREE_MB TOTAL_MB %
------------------------------ ---------- ---------- ----------
DATA 36054 40960 88.0224609
REDOLOG 40859 40960 99.753418
BACKUP 17885 20480 87.3291016
OCRVOTE 10145 10240 99.0722656
At the moment the usage is fine, but I'd like a shell script that I can schedule via cron, on an hourly basis and send an email, warning me that any of the ASM disks usage has gone above 80%
Thanks in advance
You could consider adding this new functionality to the script related to the monitoring of the ORA- alerts: the Oracle database instance processes log a warning in the alert log when the usage of the fast recovery area (a.k.a. flash recovery area) reaches a certain threshold.
Hi,
Yes that is true. However the alert script runs once a day at 4am and I'd like to run this particular script on an hourly basis. Otherwise the first sign I am aware of the space issue is in the morning, after the backups have been scheduled to run.
Thanks again
OK,
so you could create and schedule a new script (copy the original one, change the message parameter and the mail subject handling).
OK,
tested and worked so thanks
I did that - but if there are any other errors within the hour will it also mail these?
For example I could have ORA-600 or other meaningless errors that I do not need to know about.
Regards
If you remove the message like '%ORA-%' part, you won't receive a notification for an ORA- alerts.
? The ORA-00600 alerts could be more than important ..., they could be critical.
Hi,
thanks and that is true what you say, and I was just using the ORA-600 as an example. But I wanted to be able to act on the Flash back area, if the disk was say 80% full.
Thanks
---------- Post updated at 04:11 PM ---------- Previous update was at 03:54 PM ----------
Hi,
does this make it easier?
When it generates an alert for Disk usage, I get an email like this
ADR Home = /u01/app/oracle/diag/rdbms/dctmprod/DCTMPROD1:
*************************************************************************
2011-06-07 08:36:24.865000 +01:00
Errors in file /u01/app/oracle/diag/rdbms/dctmprod/DCTMPROD1/trace/DCTMPROD1_m000_31563.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 97.17% used, and has 304087040 remaining bytes available.
2011-06-07 10:21:17.155000 +01:00
Errors in file /u01/app/oracle/diag/rdbms/dctmprod/DCTMPROD1/trace/DCTMPROD1_ora_17971.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
So in my script can I do something which only searches for ORA-19815 errors
On that line it displays the percentage - so if it is above 80% (threshold) then it can send an email.
Thanks again
You could use something like this:
#!/bin/bash
ORACLE_HOME=<your_oracle_home>
ORACLE_SID=<your_oracle_sid>
ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
. "$ORACLE_HOME"/bin/oraenv
_limit=80
_mailto=<your_email_address>
_mail_subject="FRA usage for $ORACLE_SID on $HOSTNAME"
_today=$(
date '+%Y-%m-%d %H:%M:%S'
)
_yesterday=$(
date -d yesterday +'%Y-%m-%d 00:00:00'
)
_my_result=$(
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA-19815%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"
"
)
_my_percentil=$(
sed -rn '/ORA-19815/s/.*is ([0-9]+).?[0-9]*% used.*/\1/p' <<< "$_my_result"
)
((${_my_percentil:-0} > _limit)) &&
mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
The interval is the day before from 00:00:00 until the moment the script is executed, you may need to adjust it.
Thanks,
I will test and let you know
---------- Post updated at 05:17 PM ---------- Previous update was at 04:45 PM ----------
Hi,
it nearly worked, thanks, just a small error at the end - see results below
[oracle@UKEDXDTMDBS01A scripts]$ bash -xv test1.sh
#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
+ ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=DCTMPROD1
+ ORACLE_SID=DCTMPROD1
ORAENV_ASK=NO
+ ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
+ export ORACLE_HOME ORACLE_SID ORAENV_ASK
PATH=/usr/sbin:$PATH; export PATH
+ PATH=/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/db_1/bin
+ export PATH
export ORACLE_SID=DCTMPROD1
+ export ORACLE_SID=DCTMPROD1
+ ORACLE_SID=DCTMPROD1
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
+ ORACLE_HOME=/product/11.2.0/db_1
+ export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
+ PATH=/product/11.2.0/db_1/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/db_1/bin
+ export PATH
USER=oracle
+ USER=oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
+ ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
#. "$ORACLE_HOME"/bin/oraenv
_limit=80
+ _limit=80
_mailto=name@company.com
+ _mailto=name@company.com
_mail_subject="FRA usage for $ORACLE_SID on $HOSTNAME"
+ _mail_subject='FRA usage for DCTMPROD1 on UKEDXDTMDBS01A.company.com'
_today=$(
date '+%Y-%m-%d %H:%M:%S'
)
date '+%Y-%m-%d %H:%M:%S'
++ date '+%Y-%m-%d %H:%M:%S'
+ _today='2011-06-08 17:12:34'
_yesterday=$(
date -d yesterday +'%Y-%m-%d 00:00:00'
)
date -d yesterday +'%Y-%m-%d 00:00:00'
++ date -d yesterday '+%Y-%m-%d 00:00:00'
+ _yesterday='2011-06-07 00:00:00'
_my_result=$(
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA-19815%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"
"
)
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA-19815%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"
"
++ adrci 'exec=
set home DCTMPROD1;
show alert -term -P \"MESSAGE_TEXT like '\''%ORA-19815%'\'' and ORIGINATING_TIMESTAMP between '\''2011-06-07 00:00:00'\'' and '\''2011-06-08 17:12:34'\''\"
'
+ _my_result='
ADR Home = /u01/app/oracle/diag/rdbms/dctmprod/DCTMPROD1:
*************************************************************************
2011-06-07 08:36:24.865000 +01:00
Errors in file /u01/app/oracle/diag/rdbms/dctmprod/DCTMPROD1/trace/DCTMPROD1_m000_31563.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 97.17% used, and has 304087040 remaining bytes available.
2011-06-07 10:21:17.155000 +01:00
Errors in file /u01/app/oracle/diag/rdbms/dctmprod/DCTMPROD1/trace/DCTMPROD1_ora_17971.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.'
_my_percentil=$(
sed -rn '/ORA-19815/s/.*is ([0-9]+).?[0-9]*% used.*/\1/p' <<< "$_my_result"
)
sed -rn '/ORA-19815/s/.*is ([0-9]+).?[0-9]*% used.*/\1/p' <<< "$_my_result"
++ sed -rn '/ORA-19815/s/.*is ([0-9]+).?[0-9]*% used.*/\1/p'
+ _my_percentil='97
100'
((${_my_percentil:-0} > _limit)) &&
mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
+ (( 97
100 > _limit ))
test1.sh: line 38: ((: 97
100 > _limit: syntax error in expression (error token is "100 > _limit")[oracle@UKEDXDTMDBS01A scripts]$
Yep,
I didn't think of multiple entries.
And which one do you want to get in this case: the first one, the last one, all of them?
Got to go now, I'll check it later.
Hi,
I would like to get the ORA-19815 entries for the past 24 hrs and then only mail
if it breaches the 80% threshold
thankyou
You can try the following code. I removed some parts as the ORA-19815 is raised when the free space in the FRA is less than 15% (usage at 85%) so no additional conditionals are needed.
The script checks for the presence of ORA-19815 from its last run, so you can schedule it every 24h. The script will report all the ORA-19815 alerts present in the alert log only on its first run (or if you manually delete the ._my_last_date hidden file in its current working directory)
#!/bin/bash
ORACLE_HOME=your_oracle_home
ORACLE_SID=your_oracle_sid
ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
. "$ORACLE_HOME"/bin/oraenv
_mailto=your_email_address
_mail_subject="FRA usage for $ORACLE_SID on $HOSTNAME"
_my_first_date='1990-01-01 00:00:00'
_my_last_date_file=._my_last_date
[ -f "$_my_last_date_file" ] ||
printf > ./"$_my_last_date_file" '%s\n' "$_my_first_date"
_last_date=$( <./"$_my_last_date_file" )
printf > ./"$_my_last_date_file" '%s\n' "$(
date '+%Y-%m-%d %H:%M:%S'
)"
_my_result=$(
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA-19815%' and ORIGINATING_TIMESTAMP >= '$_last_date'\\\"
"
)
[[ $_my_result == *ORA-* ]] &&
mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
Many thanks,
now up and running fine via cron