Home » RDBMS Server » Server Administration » FRA Space Claim for Flashback Logs (Oracle 11.2.0.3, RedHat Enterprise Linux Server 6.7)
FRA Space Claim for Flashback Logs [message #658704] Fri, 23 December 2016 10:29 Go to next message
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 #658705 is a reply to message #658704] Fri, 23 December 2016 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
When I queried "v$flash_recovery_usage" I was able to find
Don't tell us, SHOW US, copy and paste your SQL*Plus session and don't forget to format it.
And show us parameters related to FRA (db_flash_cache_size, db_flashback_retention_target) and restore points.

Re: FRA Space Claim for Flashback Logs [message #658709 is a reply to message #658704] Fri, 23 December 2016 11:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
90% usage is not a problem. You have disc space, and Oracle is using it. That is the whole idea of the FRA automatic management.
Re: FRA Space Claim for Flashback Logs [message #658710 is a reply to message #658709] Fri, 23 December 2016 12:46 Go to previous messageGo to next message
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 #658711 is a reply to message #658710] Fri, 23 December 2016 12:48 Go to previous messageGo to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
John - Agreed that 90% usage is not a problem. But from the OS perspective, this is heavily utilized and would like to reduce it to at least 80%.
Re: FRA Space Claim for Flashback Logs [message #658712 is a reply to message #658711] Fri, 23 December 2016 12:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
That is ridiculous, man. Did you buy discs intending to leave them empty?
Re: FRA Space Claim for Flashback Logs [message #658713 is a reply to message #658710] Fri, 23 December 2016 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry, it was db_recovery_file_dest_size not db_flash_cache_size.

Anyway, as you have no restore points, Oracle will release space when it will need it.

Re: FRA Space Claim for Flashback Logs [message #658715 is a reply to message #658713] Fri, 23 December 2016 13:12 Go to previous messageGo to next message
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 #658716 is a reply to message #658715] Fri, 23 December 2016 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just decrease db_recovery_file_dest_size if you can't afford this size, then Oracle will clean the FRA.

Re: FRA Space Claim for Flashback Logs [message #658726 is a reply to message #658715] Sat, 24 December 2016 06:23 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
jesuisantony wrote on Fri, 23 December 2016 19:12
The 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 Wink

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 #659701 is a reply to message #658716] Fri, 27 January 2017 11:59 Go to previous messageGo to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Thanks Michel. We will work on changing the db_recovery_file_dest_size parameter to clear the required space. This will be an appropriate solution for us.
Re: FRA Space Claim for Flashback Logs [message #659825 is a reply to message #659701] Tue, 31 January 2017 07:10 Go to previous messageGo to next message
ibDBA
Messages: 2
Registered: January 2017
Location: Kolkata
Junior Member

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 #659826 is a reply to message #659825] Tue, 31 January 2017 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why not use a size which is the real one you can afford and get rid of any management or monitoring letting Oracle do it?

Re: FRA Space Claim for Flashback Logs [message #659827 is a reply to message #659826] Tue, 31 January 2017 08:11 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Michel Cadot wrote on Tue, 31 January 2017 14:05

Why not use a size which is the real one you can afford and get rid of any management or monitoring letting Oracle do it?
+1
Re: FRA Space Claim for Flashback Logs [message #660402 is a reply to message #659827] Wed, 15 February 2017 14:23 Go to previous messageGo to next message
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.

Re: FRA Space Claim for Flashback Logs [message #660407 is a reply to message #660402] Wed, 15 February 2017 15:53 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Monitor != "Help me to address this issue without not having to shut down or affecting the DB."

Previous Topic: ROLE not working as expected
Next Topic: checkpoint doesn't work
Goto Forum:
  


Current Time: Fri Mar 29 10:31:33 CDT 2024