Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vishnu.

Asked: September 16, 2017 - 2:13 pm UTC

Last updated: October 20, 2017 - 1:04 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I see FRA size is Filling up and when checked Flashback Logs are occupying most of it as seen below,

SQL> select * from v$recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE .01 0 1
REDO LOG 0 0 0
ARCHIVED LOG .18 0 1
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 66.23 0 331
FOREIGN ARCHIVED LOG 0 0 0

There are no RESTORE POINTS however,
SQL> SELECT NAME, TO_CHAR(SCN) SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE/1024/1024/1024 GB FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected

SQL>
Here is my flashback setting
SQL> sho parameter db_recover

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FLASH
db_recovery_file_dest_size big integer 1000G

initially FRA was set to 800G but due to alerts we had it moved to 1000G. earlier 280 files were there now flashback logs are at 331 and keep on increasing...

We have retention set to one day only...
SQL> sho parameter retention

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

Also I verified under ASM that flashback logs are there even when there are no Restore Points...
ASMCMD> pwd
+flash/history/flashback
ASMCMD> du
Used_MB Mirror_used_MB
695784 695784

I see one day files are being showed up. Here is the sample of them...
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
FLASHBACK UNPROT COARSE SEP 16 07:00:00 Y log_231.6021.954567147
FLASHBACK UNPROT COARSE SEP 16 07:00:00 Y log_229.5684.954565529
FLASHBACK UNPROT COARSE SEP 16 07:00:00 Y log_181.862.954830959
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_77.2813.954735113
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_75.5558.954724029
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_72.6281.954709067
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_67.5141.954699885
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_61.6439.954807321
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_53.1503.954698753
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_49.567.954698271
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_325.4818.954607441
FLASHBACK UNPROT COARSE SEP 16 06:00:00 Y log_323.7230.954607257

How can we solve this situation Tom?

I have read in oracle docs that we can bounce the database to clear these flashback logs. Is it So Tom?

Also is there a way without bouncing the database, we can clear these logs?

Earlier also we had this situation and my colleagues told they did manually deleted the flashback logs under ASM using rm as there were no restore points. Could this be one of the possible ways to handle this?

Please let me know if any more details are required..

Thanks
Vishnu

and Connor said...

Earlier also we had this situation and my colleagues told they did manually deleted the flashback logs under ASM using rm as there were no restore points. Could this be one of the possible ways to handle this?

Dont do this. Manually deleting flashback logs can get you into nasty states where you cannot open the database after a shutdown. Not fun.

When using flashback logs, the recovery area is *meant* to get full, ie, we will hold onto flashback logs as long as we can to give the best flashback capability. This might seem alarming but as space is needed for archive logs (which are obviously more important) we will delete flashback logs as required.

There is one thing to be careful with however - if we need to delete some old flashback logs, we will delete only a certain amount of them so if you are really cranking out archive logs extremely quickly you *could* get to a state where you did not clear out enough flashback logs and hence get into problems. See MOS note 1481739.1 for details and workarounds.

If you want to clear up some space, set db_flashback_retention_target to a lower number and we'll clean out logs, and then set it back to your desired value.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up

Ram Dittakavi, September 18, 2017 - 6:54 am UTC

Hi Connor,

Have gone through the Doc ID 1481739.1.

Say, this is my config:

db_recovery_file_dest is +DG_FLASH
db_recovery_file_dest_size is 100GB
db_flashback_retention_target 90
log_archive_dest_1 is +DG_ARC

So, i'm using +DG_FLASH only for flashback logs.

There are no GRTs as of this moment. Once the flashback logs size usage reached 100GB, then which of the following is correct:

1) Logs that are not needed to go beyond 90 mins are all purged.

2) Logs that are not needed to go beyond _MINIMUM_DB_FLASHBACK_RETENTION are all purged.

3) Logs that need to be purged to accommodate the new flashback logs are only purged.
Connor McDonald
October 06, 2017 - 6:02 am UTC

My reading of the note is that _MINIMUM_DB_FLASHBACK_RETENTION is in effect a lower limit on db_flashback_retention_target. (ie, saying that you cannot go below 60).

So my understanding is we will commence purging logs in order to release pressure on DG_FLASH. So the logs that are oldest will be purged - lets say that log caters for a flashback back to 70mins ago. As logs are purged, eventually the oldest log is now 60mins old. Even if you are still under space pressure, we will not go beyond that.

I'm in an airport currently so I can't rigourously test this for you.

Follow up

Ram Dittakavi, October 17, 2017 - 5:02 am UTC

Thanks Connor. One last question:

Suppose, our retention is 1 hour and FRA size allocated is 100GB. Now, let’s say 120 GB flashback logs got generated in last 30 mins.

At this point, flashback is 100% full and no logs can be purged. Note that the archives are not placed in FRA.

In this situation, what happens to the database? Does flashback gets disabled and database operates normally or it hangs or crashes?
Connor McDonald
October 19, 2017 - 4:28 am UTC

I just did a test where

- I had a tiny FRA (2G)
- put flashback logs in there
- put archivelogs elsewhere
- did continuous large delete/insert operations

The FRA eventually fills:

2017-10-19T12:07:46.533753+08:00
Errors in file C:\ORACLE\diag\rdbms\np122\np122\trace\np122_m000_9708.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.


but DML operations are allowed to continue without interruption. The flashback logs were 200MB each, so I had 10 in my FRA. The oldest ones simply get deleted and overwritten by newer ones.

This is because 'db_flashback_retention_target' is exactly that - a target, not a guarantee. I then tried to do a flashback to a time within my target (default of 1440):

SQL> FLASHBACK DATABASE TO TIMESTAMP SYSDATE-0.4/24;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-0.4/24
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


which makes sense because I threw away some of those logs due to space pressure.

Follow up

Ram Dittakavi, October 19, 2017 - 6:14 am UTC

Thanks a lot, Connor.

Suppose, we have flashback logs pertaining to a guaranteed restore point (GRT) and then FRA gets 100% full.

Suppose, FRA is 2G.

Case1: GRT logs occupy 1.5G

Now, the DMLs are allowed after purging the logs that are there in the remaining 0.5G (ofcourse 200MB at a time) and this repeats?

Case2: GRT logs occupy 2G

Now, what happens?

Follow up

Ram Dittakavi, October 19, 2017 - 6:41 am UTC

"_MINIMUM_DB_FLASHBACK_RETENTION" -- does this play any role in this scenario or this comes into picture only when archive logs are also sharing FRA?
Connor McDonald
October 20, 2017 - 1:04 am UTC

From the MOS note:

Some flashback logs that will not be deleted when the fast recovery area sees space pressure and archiver will fail. There is a coded hard limit on how much flashback logs can be deleted to make space for new archivelogs.

When archiver wants disk space to archive online redo log and there is no files to purged from FRA, then instead of making the database hang, flashback logs (that is not required for GRP) will be purged to hard limit. Once that hard limit is reached, no more flashback logs can be purged and the database will hang.


Follow up

Ram Dittakavi, October 20, 2017 - 6:13 am UTC

I don't understand why Oracle doesn't enforce the hard limit "_MINIMUM_DB_FLASHBACK_RETENTION" of retaining 1 hour worth logs when space is required for the new flashback logs allowing the oldest ones to be deleted so that the database is available where as for the archives which are one of the most important files, it won't allow beyond the hard limit and let's the database to go to hang state.

It just doesn't feel right.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.