Over the years I have learned that common sense goes a long way as a DBA. The other day I was running an ETL process when this error occurred. The following link outlines step to perform to recover from this error.
I have always trusted Burleson Consulting web site for great explanation’s of what to check. So I went through the steps outlined on the post. Prior to altering the archivelog destination with LOG_ARCHIVE_DEST_.. = ‘location=/archivelogpath reopen’; I decided to look at my flashback_retention_target and discovered that my setting was 24 hours. Since I was in the middle of an ETL run and really didn’t need 24 hours of flashback recovery my problem was as simple as running.
ALTER SYSTEM SET db_flashback_retention_target = 180 SCOPE=BOTH
This dropped the flashback required to 3 hours and freed space in the flashback area to continue archiving. I guess I could have also probably increased my flashback db_recovery_file_dest_size as well to solve my issue.
My long running ETL process continued on its way, and I was a happy camper.
When the ETL process completed I just reset the retention level to my disaster recovery objectives.
Long story short, Oracle-managed directories like the Fast Recovery Area are great at reclaiming space when needed, and not panicking over stuck archive log processing should be the norm. Although we deep down are always nervous on what we might walk into on any given production day. That’s just being a DBA that doesn’t fly by the seat of his pants!