In a real time environment, we get the FRA usage threshold alert on a daily basis and we've to take action on those alert accordingly and if the environment is small then verifying FRA usage manually by typing the sql commands is fine but if the environment is big where thousands of databases are running then in that case it would always be better to use automation to fetch the FRA usage which reduce our manual effort and save our time too.
Obective Of Using this
script
To verify the FRA usage
, we basically check below things.
(1) The disk space's usage
(2) guarantee restore point info , if any
(3) How much space we can reclaim if we drop the restore point
after user's
confirmation
(4) PDB info and whether the restore point exist at CDB level
or PDB level , in case of
multitenant
architecture
(5) In real time environment , sometimes we also run the archive jobs to make some
if space , however
that's depends on multiple things like deletion policy , gg config
etc.
Main Script
Note A. If you're executing this script against RAC
database then please comment
the line
highlighted in red and uncomment the line highlighted in green ,
For
standalone database you're good to use this script without any changes.
This
script can be use against Primary & Standby Database both.
Note B. if you’re not using ASM DISK
then in RECO Disk group usage a blank
output
will come.
Note C. If you’re not using CDB then
you’ll get gv$pdbs view related error , although
rest of
the output will come.
Script Format
./<script_name.sh> DB_NAME
Note : Prior execute this script either you can set the database environment
or just pass the database name into the database server after login with
database’s user and this automation will automatically set the environment for
the database and fetch required FRA usage detail.
BODY OF SCRIPT
##########################################################
#!/bin/bash
#set -xv
#set -n
mkdir -p /u01/FRA_CHK
FRACHK=/u01/FRA_CHK
FRA_LOG=$FRACHK/fra_log.log
RSTRPNT=$FRACHK/rstrpnt.log
DBNAME=`ps -ef |grep -i ora_pmon_$1 | grep -v grep |
awk '{print $8}' | cut -d "_" -f3`
#DBNAME=`ps -ef |grep -i ora_pmon_$1 | grep -v
grep | awk '{print $8}' | cut -d "_" -f3 | sed 's/.$//'`
DB_STATUS=`ps -ef|grep -i ora_pmon_$1 | grep -v grep | wc -l`
if [ ${DB_STATUS} -eq 1 ]
then
export ORACLE_SID=`ps -ef | grep -i ora_pmon_$1 | grep -v grep
| cut -d "_" -f 3`
proid=`ps -ef | grep -i ora_pmon_$1 | grep -v grep | awk
'{print $2}'`
var=`ls -l /proc/$proid/cwd | cut -d ">" -f 2 |
sed 's/^ //'`
export ORACLE_HOME=`echo "${var%/*}"`
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=`ls -l /proc/$proid/cwd | cut -d
">" -f 2 | sed 's/^ //' | awk -F "/product" '{print
$1}'`
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<
EOF >$FRA_LOG
set lines 300 colsep '|'
select * from v\$flash_recovery_area_usage;
exit;
EOF
else
echo "Database $1 Is Not Running"
exit 1;
fra_output=`cat $FRA_LOG`
echo "$fra_output"
fi
########### DATABASE ROLE
###########
$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" <<
EOF >$FRACHK/db_unique_namme.log
set lines 300 colsep '|'
set heading off;
select database_role from v\$database;
show parameter db_unique_name;
exit;
EOF
db_role=`cat ${FRACHK}/db_unique_namme.log |sed /^$/d | awk
'FNR == 1 {print}'`
db_name=`cat ${FRACHK}/db_unique_namme.log |sed /^$/d | awk
'FNR== 2 {print}' | cut -d "|" -f3`
if [ "$db_role" == "PRIMARY" ]
then
echo "$db_name Is A Primary Database"
else
echo "$db_name Is A Physical Standby Database"
fi
######## Restore Point Info
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<
EOH > $RSTRPNT
Set lines 300 colsep '|'
Col con_id for 99999999
Col name for a40
Col time for a38
Col PRESERVED for a15
Col GUARANTEE_FLASHBACK_DATABASE for a30
Col STORAGE_SIZE for 99999.999999
set feedback off;
Select con_id, name, time, PRESERVED,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE/1024/1024/1024 "Reclaim Space
In GB" from v\$restore_point;
exit;
EOH
rstrpnt_info=`cat $RSTRPNT | sed /^$/d`
echo -e "\033[32m\nChecking Restore Point Info, If
Any\033[0m"
count=`cat $RSTRPNT | wc -l | sed /^$/d`
if [ $count -ge 1 ]
then
echo -e "\033[31m\n Restore Point exist\033[0m"
rstr_count=`cat $RSTRPNT`
echo -e "\033[32m\n$rstr_count\033[0m"
else
echo -e "\033[32m\n No Restore Point exist\033[0m"
fi
############# FUNCTIONS
PDB_INFO ()
{
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<
EOF >$FRACHK/PDB_INFO.log
set lines 300 colsep '|'
show pdbs;
exit;
EOF
while read LINE
do
echo "$LINE"
done < $FRACHK/PDB_INFO.log
}
##########
PDB INFO Functions Ends Here
RECO_AREA ()
{
$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" <<
EOF >$FRACHK/RECO_INFO.log
set lines 300 colsep '|'
SELECT
ROUND((A.SPACE_LIMIT / 1024/1024/1024), 2) AS FLASH_IN_GB, ROUND((A.SPACE_USED/1024/1024/1024),
2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE/1024/1024/1024), 2) AS
FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED)
AS PERCENT_OF_SPACE_USED,
(100-SUM(B.PERCENT_SPACE_USED)) "FRA_Free_Now_%",
ROUND((A.SPACE_USED/1024/1024/1024), 2)/0.6 NEEDed_size_IN_GB
--to be /0.6 NEEDed_size_IN_GB
FROM V\$RECOVERY_FILE_DEST A, V\$FLASH_RECOVERY_AREA_USAGE B
GROUP BY SPACE_LIMIT, SPACE_USED, SPACE_RECLAIMABLE;
exit;
EOF
while read LINE
do
echo -e "\033[33m$LINE\033m"
:
done < $FRACHK/RECO_INFO.log
}
####### Recover AREA DEST USAGE FUNCTION ENDS HERE
DISK_GROUP_SIZE_CHK()
{
$ORACLE_HOME/bin/sqlplus -S " as sysdba" <<
EOF >$FRACHK/DISK_SPACE.log
set lines 300 colsep '|'
col name format a60
SELECT
name group_name
,sector_size sector_size
,block_size block_size
,allocation_unit_size allocation_unit_size
,state state
,type type
,total_mb total_mb
,free_mb free_mb
,USABLE_FILE_MB USABLE_FILE_MB
,(total_mb - free_mb) used_mb
FROM
v\$asm_diskgroup
ORDER BY name
exit;
EOF
DISK_FREE_SPACE=`cat $FRACHK/DISK_SPACE.log | grep -i RECO |
sed '/^$/d' | cut -d '|' -f9|tr -d "[:blank:]"`
echo -e "\033[33mFREE USAGE MB IN RECO DISKGROUP
IS\033[0m : $DISK_FREE_SPACE"
}
####### DISK GROUP SIZE CHECK FUNCTION ENDS HERE
#### Main Function
PDB_INFO
RECO_AREA
DISK_GROUP_SIZE_CHK
### Removing FRA Check
Directory
echo -e "\033[33mRemoving $FRACHK Directory
Now\033[0m"
rm -rf $FRACHK
####
Line Of Code Ends Here
Sample
Output Of This Automation
[oracle@oratest tmp]$ ./script.ksh ORAPRIM
ORAPRIM Is A Primary
Database
Checking Restore Point
Info, If Any
Restore Point exist
CON_ID|NAME
|TIME |PRESERVED
|GUARANTEE_FLASHBACK_DATABASE |Reclaim
Space In GB
---------|----------------------------------------|--------------------------------------|---------------|------------------------------|-------------------
0|TEST |15-DEC-23 07.04.01.000000000 PM |YES
|YES |
0
3|TEMP |15-DEC-23 07.04.26.000000000 PM |YES
|YES |
.1953125
CON_ID|CON_NAME |OPEN MODE |RESTRICTED
----------|------------------------------|----------|----------
2|PDB$SEED
|READ ONLY |NO
3|PDB1 |MOUNTED
|
FLASH_IN_GB|FLASH_USED_IN_GB|FLASH_RECLAIMABLE_GB|PERCENT_OF_SPACE_USED|FRA_Free_Now_%|NEEDED_SIZE_IN_GB
-----------|----------------|--------------------|---------------------|--------------|-------
12.43| .7| .07| 5.61|
94.39| 1.16666667
FREE USAGE MB IN RECO
DISKGROUP IS :
Removing /u01/FRA_CHK
Directory Now