Visit Our UNIX and Linux User Community


Script for V$flash_recovery_area_usage


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script for V$flash_recovery_area_usage
# 8  
Old 06-08-2011
You could use something like this:

Code:
#!/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.
# 9  
Old 06-08-2011
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


Code:
[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]$


Last edited by radoulov; 06-08-2011 at 02:19 PM.. Reason: Please use code tags.
# 10  
Old 06-08-2011
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.
# 11  
Old 06-08-2011
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

Last edited by jnrpeardba; 06-08-2011 at 04:47 PM..
# 12  
Old 06-08-2011
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)

Code:
#!/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"


Last edited by radoulov; 06-08-2011 at 06:23 PM..
# 13  
Old 06-13-2011
Many thanks,

now up and running fine via cron

Previous Thread | Next Thread
Test Your Knowledge in Computers #830
Difficulty: Medium
JSON-RPC is a remote procedure call (RPC) protocol built on JSON, as a replacement for XML-RPC or SOAP.
True or False?

5 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to block first bash script until second bash script script launches web server/site?

I'm new to utilities like socat and netcat and I'm not clear if they will do what I need. I have a "compileDeployStartWebServer.sh" script and a "StartBrowser.sh" script that are started by emacs/elisp at the same time in two different processes. I'm using Cygwin bash on Windows 10. My... (3 Replies)
Discussion started by: siegfried
3 Replies

2. Shell Programming and Scripting

Shell script works fine as a standalone script but not as part of a bigger script

Hello all, I am facing a weird issue while executing a code below - #!/bin/bash cd /wload/baot/home/baotasa0/sandboxes_finance/ext_ukba_bde/pset sh UKBA_publish.sh UKBA 28082015 3 if then echo "Param file conversion for all the areas are completed, please check in your home directory"... (2 Replies)
Discussion started by: ektubbe
2 Replies

3. UNIX for Dummies Questions & Answers

Calling a script from master script to get value from called script

I am trying to call a script(callingscript.sh) from a master script(masterscript.sh) to get string type value from calling script to master script. I have used scripts mentioned below. #masterscript.sh ./callingscript.sh echo $fileExist #callingscript.sh echo "The script is called"... (2 Replies)
Discussion started by: Raj Roy
2 Replies

4. Shell Programming and Scripting

Script will keep checking running status of another script and also restart called script at night

I am using blow script :-- #!/bin/bash FIND=$(ps -elf | grep "snmp_trap.sh" | grep -v grep) #check snmp_trap.sh is running or not if then # echo "process found" exit 0; else echo "process not found" exec /home/Ketan_r /snmp_trap.sh 2>&1 & disown -h ... (1 Reply)
Discussion started by: ketanraut
1 Replies

5. Shell Programming and Scripting

create a shell script that calls another script and and an awk script

Hi guys I have a shell script that executes sql statemets and sends the output to a file.the script takes in parameters executes sql and sends the result to an output file. #!/bin/sh echo " $2 $3 $4 $5 $6 $7 isql -w400 -U$2 -S$5 -P$3 << xxx use $4 go print"**Changes to the table... (0 Replies)
Discussion started by: magikminox
0 Replies

Featured Tech Videos