OraFAQ Forum: Server Administration » FRA Space Claim for Flashback Logs

Home » RDBMS Server » Server Administration » FRA Space Claim for Flashback Logs (Oracle 11.2.0.3, RedHat Enterprise Linux Server 6.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #658705 is a reply to message #658704]

Fri, 23 December 2016 10:38

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #658709 is a reply to message #658704]

Fri, 23 December 2016 11:34

John Watson
Messages: 8976
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.

Report message to a moderator

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.

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #658711 is a reply to message #658710]

Fri, 23 December 2016 12:48

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%.

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #658712 is a reply to message #658711]

Fri, 23 December 2016 12:50

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

That is ridiculous, man. Did you buy discs intending to leave them empty?

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #658713 is a reply to message #658710]

Fri, 23 December 2016 12:54

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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.

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #658716 is a reply to message #658715]

Fri, 23 December 2016 13:14

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #658726 is a reply to message #658715]

Sat, 24 December 2016 06:23

John Watson
Messages: 8976
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).

Report message to a moderator

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: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

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

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.

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

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.

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #659826 is a reply to message #659825]

Tue, 31 January 2017 08:05

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #659827 is a reply to message #659826]

Tue, 31 January 2017 08:11

gazzag
Messages: 1119
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

Report message to a moderator

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.

Report message to a moderator

Re: FRA Space Claim for Flashback Logs [message #660407 is a reply to message #660402]

Wed, 15 February 2017 15:53

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 09:56:05 CDT 2025