DO WE NEED TO HOUSEKEEP FLASHBACK LOGFILES MANUALLY?

Read the article until the end. There is small tip for you. Dont miss it!

Answer for the topic is no. Oracle has some internal mechanism which automatically wipes out the flashback logs when we disable flashback. Let us see how it works

I enable flashback in my database and perform some changes

kish@STDBY>alter database flashback off;

Database altered.

kish@STDBY>insert into t values('it_worked',5);

1 row created.

kish@STDBY>commit;

Commit complete.

Check the contribution of space occupation by flashback log in the below output. 3% of the space is utilized by flashback logfiles in disk

kish@STDBY>select * from v$recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                           .36                         0               1          0
REDO LOG                                 8                         0               8          0
ARCHIVED LOG                         20.11                         0              60          0
BACKUP PIECE                          2.18                      1.82               6          0
IMAGE COPY                            2.89                      2.89               8          0
FLASHBACK LOG                            3                         0               3          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

Disable flashback

kish@STDBY>alter database flashback off;

Database altered.

The moment we disable flashback, RVWR process is stopped and all the flashback logfiles are deleted

alter database flashback off
Stopping background process RVWR
Sat Oct 02 18:47:15 2021
Deleted Oracle managed file /apps01/FRA/DBX01/flashback/o1_mf_jodf2btl_.flb
Deleted Oracle managed file /apps01/FRA/DBX01/flashback/o1_mf_jodf2fop_.flb
Deleted Oracle managed file /apps01/FRA/DBX01/flashback/o1_mf_joj0lxh1_.flb
Deleted Oracle managed file /apps01/FRA/DBX01/flashback/o1_mf_joj6tsr7_.flb
Deleted Oracle managed file /apps01/FRA/DBX01/flashback/o1_mf_jojmm5j2_.flb
Deleted Oracle managed file /apps01/FRA/DBX01/flashback/o1_mf_jojpvkgk_.flb
Flashback Database Disabled
Completed: alter database flashback off

Check the FRA now. 3 turned to 0 in PERCENT_SPACE_USED

kish@STDBY>select * from v$flash_recovery_area_usage where FILE_TYPE='FLASHBACK LOG';

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
FLASHBACK LOG                            0                         0               0          0

Thus we dont need to manually housekeep flashback logs if we switch off flashback.

If flashback is turned on, then based on retention period (default – 1440 min ~ 24 hour ~ 1 day), logs are removed.

kish@STDBY>show parameter flashback

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

Trick: If flashback logs need to deleted with flashback on, then reduce the db_flashback_retention_target to less time and reduce FRA size. This can be performed dynamically

kish@STDBY>alter system set db_flashback_retention_target=1;

System altered.

kish@STDBY>show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 5000M

kish@STDBY>alter system set db_recovery_file_dest_size=1000M;

System altered.

kish@STDBY>select * from v$recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                          1.82                         0               1          0
REDO LOG                                60                         0              12          0
ARCHIVED LOG                         11.09                      9.67               8          0
BACKUP PIECE                          3.65                         0               2          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                           20                         5               4          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

Summary:We dont need to manually delete the flashback logfiles

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s