We have received an alert that FRA usaage has crossed 85%. Upon looking found that at OS level only 15 files but it shows as 220 files in v$flash_recovery_area_usage.
**************************************** Step By Step Procedure ******************************************************
#########################################
# 1) Check Flashback Usage
#########################################
select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 1
REDO LOG 0 0 0
ARCHIVED LOG 2.9 0 10
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 83.62 71.62 220
FOREIGN ARCHIVED LOG 0 0 0
=====================================================================================================================
#########################################
# 2) Check Recovery File Dest
#########################################
Select name,space_limit/1024/1024/1024 "Total Size",space_used/1024/1024/1024 "GB Used",round((space_used/space_limit)*100,2) "Percentage_Used",
(space_limit-space_used)/1024/1024/1024 "GB Free", space_reclaimable/1024/1024/1024 "Reclaimable",number_of_files from v$recovery_file_dest;
NAME Total Size GB Used Percentage_Used GB Free Reclaimable NUMBER_OF_FILES
------------------------- ---------- ---------- --------------- ---------- ----------- ---------------
+RECO 1000 865.613281 86.56 134.386719 720.175781 220
=====================================================================================================================
#########################################
# 3) Database Parameters
#########################################
SQL> sho parameter reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 1000G
SQL> sho parameter retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 43200
=====================================================================================================================
#########################################
# 4) Flashback Logs
#########################################
Let us see the Flashback logs which is present at the OS level.
ASMCMD [+RECO/db01/flashback] > ls -l
Type Redund Striped Time Sys Name
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_173.4531.837219837
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_199.41359.837295107
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_200.22599.837298645
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_201.32376.837302495
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_202.24492.837306331
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_203.25970.837309665
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_204.6831.837313373
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_205.7999.837317745
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_206.47642.837322305
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_207.75437.837325047
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_208.65550.837326775
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_209.21280.837328813
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_210.84547.837330497
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_211.72511.837332819
FLASHBACK MIRROR COARSE JAN 20 09:00:00 Y log_37.21464.834689357
ASMCMD [+RECO/db01/flashback] >
=====================================================================================================================
If we can see the number of flashback logs present at OS level is only 15, but v$flash_recovery_AreA_usage & v$recovery_file_dest shows as 220
=====================================================================================================================
##########################
## Solution
##########################
Flashback Retention Target set at database level is 43200 minutes, which is 30 days.
Changing the database retention target from 30 days to 1 days
SQL> Alter system set db_flashback_retention_target=1440 scope=both sid='*';
System altered.
After changing this parameter, we have to wait for atleast 30 minutes to get this info updated in v$flash_recovery_Area_usage.
In case if v$flash_recovery_area_usage is still not updated then this seems to be a Bug 4911954.
A Workaround is to manually refresh v$flash_recovery_area_usage by running the below procedure.
SQL> execute dbms_backup_restore.refreshagedfiles;
PL/SQL procedure successfully completed.
Note :
Please monitor the alert log file for the changes happening after flashback retention target.
=====================================================================================================================
Comments Are Always welcome
=====================================================================================================================
No comments:
Post a Comment