How to check Oracle DB flash recovery area usage?

How to check Oracle DB flash recovery area usage?

Issue:

How to check and monitor Oracle DB flash recovery area usage?

Solution:

SELECT x.*, round(100 * (x.SPACE_USED / x.SPACE_LIMIT), 3) fra_pct_used
FROM V$RECOVERY_FILE_DEST x

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

Example:

Sample Linux shell script to monitor Oracle DB flash recovery area usage.

flashback_recovery_area_usage_monitoring.sh

#!/bin/bash

#################################################################
#
# Script to monitor Flashback Recovery Area (FRA) usage by Oracle DB
#
# Desc: Script to monitor Flashback Recovery Area (FRA) usage by Oracle DB
#
# Date: 30-Jun-2014
#
#################################################################

. ~/.bash_profile
OP_TIMESTAMP=`date +%Y%m%d_%H%M`
CONNECT_STRING=system/mypwd@MYORA
ENVIRO=”Production”
REP_NAME=”Monitor FRA Usage”
EMAIL_LIST1=”someone@somewhere.net”
EMAIL_SUBJ=”$REP_NAME – $OP_TIMESTAMP”
REP_FILE_NAME1=monitor_fra_usage_$OP_TIMESTAMP.out
OUTPUT_PLACE1=/tmp/monitor_fra_usage_$OP_TIMESTAMP.out
REP_FILE_NAME2=monitor_fra_usage2_$OP_TIMESTAMP.out
OUTPUT_PLACE2=/tmp/monitor_fra_usage2_$OP_TIMESTAMP.out
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
PATH=$PATH:$ORACLE_HOME/bin
export NLS_LANG=.AL32UTF8
export from=myschema@MYORA
export ORACLE_HOME PATH

sqlplus -s $CONNECT_STRING <<!

set markup html on
set verify off feedback off heading on echo off pages 100 termout off

set lines 1200
spool $OUTPUT_PLACE1

SELECT x.name,
round(x.SPACE_LIMIT / 1024 / 1024 / 1024, 3) space_limit_gb,
round(x.SPACE_USED / 1024 / 1024 / 1024, 3) space_used_gb,
round(x.SPACE_RECLAIMABLE / 1024 / 1024 / 1024, 3) space_reclaimable_gb,
number_of_files,
round(100 * (x.SPACE_USED / x.SPACE_LIMIT), 3) fra_pct_used
FROM V\$RECOVERY_FILE_DEST x;

set termout on
spool off;
exit
!

sqlplus -s $CONNECT_STRING <<!

set markup html on
set verify off feedback off heading on echo off pages 100 termout off

set lines 1200
spool $OUTPUT_PLACE2

SELECT * FROM V\$FLASH_RECOVERY_AREA_USAGE;

set termout on
spool off;
exit
!

(
echo “To: $EMAIL_LIST1”
echo From: oracle@10.20.30.40
echo “Content-Type: text/html; “
echo Subject: “$EMAIL_SUBJ”
echo
echo “<center><b>Monitor FRA Usage</b></center><br/>”
cat $OUTPUT_PLACE1
echo “<br>”
cat $OUTPUT_PLACE2
) | /usr/sbin/sendmail -t

---