| FRA Space Claim for Flashback Logs [message #658704] |
Fri, 23 December 2016 10:29  |
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
|
Senior Member |
|
|
Hello,
I am having a challenge with my flash recovery area. The entire file system for allocated for flashback logs and the file system has crossed 90%. The overall disk space is 500G.
When I queried "v$flash_recovery_usage" I was able to find that 90% of the usage (percent_space_used) was by "Flashback Logs" alone. However the "percent_space_reclaimable" was 80%. Be informed that parameter value for "db_flashback_retention_target" is 1440 (1 day).
The "v$flashback_database_log" query shows that the oldest flashback time is 5 days before.
I understand that there are flashback logs which can be reclaimed (as per "v$flash_recovery_usage"). But I am not sure how to proceed further. The online documentations say that these logs should not be deleted manually as Oracle will take care of the same.
I can see that the old logs are getting deleted automatically on a daily basis however the file usage still remains at 90%. I am getting threshold alerts even though this will not affect my DB.
Help me to address this issue without not having to shut down or affecting the DB.
Thank You.
|
|
|
|
|
|
|
|
| Re: FRA Space Claim for Flashback Logs [message #658710 is a reply to message #658709] |
Fri, 23 December 2016 12:46  |
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
|
Senior Member |
|
|
Michel - Find the information. There are no restore points.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 0 0 0
BACKUP PIECE 0 0 1
IMAGE COPY 0 0 0
FLASHBACK LOG 90.2 80.4 724
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
SQL> sho parameter db_flash_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 0
SQL> sho parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
|
|
|
|
|
|
|
|
|
|
| Re: FRA Space Claim for Flashback Logs [message #658715 is a reply to message #658713] |
Fri, 23 December 2016 13:12 |
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
|
Senior Member |
|
|
John - You are just talking from FRA perspective. The System Admin team sees this as a concern and would want to either reduce the space or increase the disk space. As you might know adding disc will incur cost. Hence that is not an option.
When I can the see that there is 80% of space that is reclaimable why shouldn't I think about clearing it? It will be happy for a DBA to know that there is more to space to grow and that we are less prone to space issue.
|
|
|
|
|
|
| Re: FRA Space Claim for Flashback Logs [message #658726 is a reply to message #658715] |
Sat, 24 December 2016 06:23 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
The whole point is that you do NOT have a space issue. You have enabled database flashback, and the FRA self-tuning mechanism manages the space to maximize the flashback time. If you look at V$FLASHBACK_DATABASE_LOG you'll see how far back you can go. If you reduce your FRA you'll see in that view that you can't flashback as far. You would be compromising recoverability to keep your system administrators happy (though I fail to see what their problem is, or that it is any of their business).
|
|
|
|
| Re: FRA Space Claim for Flashback Logs [message #659012 is a reply to message #658715] |
Wed, 04 January 2017 03:14 |
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
|
Senior Member |
|
|
jesuisantony wrote on Fri, 23 December 2016 19:12The System Admin team sees this as a concern and would want to either reduce the space or increase the disk space.
Tell your System Admin team to increase their monitoring threshold on the disk containing the Oracle FRA to 90% and to stop worrying themselves, you, or us about it 
Some people simply do what they've been told is right rather than thinking for themselves or asking advice from people more experienced.
[Edit: typo]
[Updated on: Wed, 04 January 2017 03:16] Report message to a moderator |
|
|
|
|
|
| Re: FRA Space Claim for Flashback Logs [message #659825 is a reply to message #659701] |
Tue, 31 January 2017 07:10 |
|
|
|
One thing I have noticed during last 4 years working on FRA, that db_recovery_file_dest_size can be a virtual number which can be bigger than available disk space. Have tweaked this value manytimes to make DB think that space is there. Just need to monitor the actual used space and get rid of restore points and logs in agreed routine.
|
|
|
|
|
|
|
|
| Re: FRA Space Claim for Flashback Logs [message #660402 is a reply to message #659827] |
Wed, 15 February 2017 14:23 |
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
|
Senior Member |
|
|
Michel - The point which you raised makes sense. But I need to go with the option specifications of the client. The expectation is to monitor everything through the available monitoring tool. There is no customization to ignore specific file system. Need to check on that.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
