FRA Check Script
Niel:orprod75> cat fra_check.sh
#!/bin/ksh
loc=/oracle/$ORACLE_SID/UPGRADE_19C
. $loc/.env_ora
sqlplus -s / as sysdba <<EOF
-- Use (MB) of FRA
set lines 100
col name format a60
select
name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used / 1024 / 1024) "Used MB"
from v\$recovery_file_dest;
-- FRA Occupants
SELECT * FROM V\$FLASH_RECOVERY_AREA_USAGE;
-- Location and size of the FRA
show parameter db_recovery_file_dest
-- Size, usage, Reclaimable space used
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
FROM
V\$RECOVERY_FILE_DEST A,
V\$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;
show parameter reco
--After that you can resize the FRA with:
--ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both;
-- Or change the FRA to a new location (new archives will be created to this new location):
--ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u001/${ORACLE_SID}/arch' scope=both;
show parameter reco
prompt ----------------------------------------------------------------------
prompt
prompt
archive log list
EOF
echo
echo ===========================================================================
echo "If the LOCATION NOT SET To following Value then ples use this syntax "
echo
echo "Mkdir -p /oracle/${ORACLE_SID}/oraarch/oraflash ...if No Directory found"
echo "ALTER SYSTEM SET db_recovery_file_dest_size=30G scope=both;"
echo "ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/oracle/${ORACLE_SID}/oraarch/oraflash' scope=both;"
echo
echo =============================================================================
Fra Cleanup Step
cat clean_fra_arch_logs.sh
export ORACLE_SID=PROD
export ORAENV_ASK=NO
. oraenv
$ORACLE_HOME/bin/rman target / << EOF
run
{
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt expired archivelog all;
}
exit
EOF
