PURPOSE
Starting with Oracle Database Release 19c, the management of space in the fast recovery area is simplified. Oracle Database monitors flashback logs in the fast recovery area and automatically deletes flashback logs that are beyond the retention period. When the retention target is reduced, flashback logs that are beyond the retention period are deleted immediately.
In scenarios where a sudden workload spike causes a large number of flashback logs to be created, the workload is monitored for a few days before deleting flashback logs that are beyond the retention period. This avoids the overhead of recreating the flashback logs, if another peak workload occurs soon after.
SCOPE
The COMPATIBLE initialization parameter must be set to 19.0.0 or higher for flashback logs to be automatically deleted.
DETAILS
Demo of space management of Flashback logs in FRA
===================================
Case 1 (Demo :changing retention)
=================================
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 11.58 0 32 0
BACKUP PIECE .18 0 2 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 18.75 0 17 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> select NAME,PDB_RESTORE_POINT,CLEAN_PDB_RESTORE_POINT,SCN,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
no rows selected
SQL> show parameter flash
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 4096
SQL> alter system set db_flashback_retention_target=2048;
System altered.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 11.58 0 32 0
BACKUP PIECE .18 0 2 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 2.34 1.17 2 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL>
SQL>
SQL>
SQL> alter session set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS';
Session altered.
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE CON_ID
-------------------- ----------------- ---------------- -------------- ------------------------ ----------
2867919 25-10-18 02:56:27 2048 503316480 1018027485 0
Alert
=====
2018-10-25T05:55:06.769584+00:00
Deleted Oracle managed file /<path>/o1_mf_fwv27fht_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv27jfp_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8gco9_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8jgph_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8m43o_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8ozz3_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8rll9_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8vtdg_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8yzt5_.flb
2018-10-25T05:55:07.886981+00:00
Deleted Oracle managed file /<path>/o1_mf_fwv92klo_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv96csg_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv9p7ln_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv9qoqt_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv9s0gn_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv9th7q_.flb
2018-10-25T05:55:08.560934+00:00
ALTER SYSTEM SET db_flashback_retention_target=2048 SCOPE=BOTH;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 11.58 0 32 0
BACKUP PIECE .18 0 2 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 18.75 0 17 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> select NAME,PDB_RESTORE_POINT,CLEAN_PDB_RESTORE_POINT,SCN,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
no rows selected
SQL> show parameter flash
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 4096
SQL> alter system set db_flashback_retention_target=2048;
System altered.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 11.58 0 32 0
BACKUP PIECE .18 0 2 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 2.34 1.17 2 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL>
SQL>
SQL>
SQL> alter session set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS';
Session altered.
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE CON_ID
-------------------- ----------------- ---------------- -------------- ------------------------ ----------
2867919 25-10-18 02:56:27 2048 503316480 1018027485 0
Alert
=====
2018-10-25T05:55:06.769584+00:00
Deleted Oracle managed file /<path>/o1_mf_fwv27fht_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv27jfp_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8gco9_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8jgph_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8m43o_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8ozz3_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8rll9_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8vtdg_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv8yzt5_.flb
2018-10-25T05:55:07.886981+00:00
Deleted Oracle managed file /<path>/o1_mf_fwv92klo_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv96csg_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv9p7ln_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv9qoqt_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv9s0gn_.flb
Deleted Oracle managed file /<path>/o1_mf_fwv9th7q_.flb
2018-10-25T05:55:08.560934+00:00
ALTER SYSTEM SET db_flashback_retention_target=2048 SCOPE=BOTH;
Case 2 (Demo: change in time)
==============================
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 22.42 0 71 0
BACKUP PIECE .62 .36 13 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 3.52 0 5 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> alter session set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS';
Session altered.
SQL> select sysdate, min(first_time), (sysdate-min(first_time))*24*60 from v$flashback_database_logfile;
SYSDATE MIN(FIRST_TIME) (SYSDATE-MIN(FIRST_TIME))*24*60
----------------- ----------------- -------------------------------
26-10-18 14:36:05 25-10-18 10:22:42 1693.38333
SQL>select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 23.59 0 73 0
BACKUP PIECE .62 .36 13 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 4.82 0 7 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> select sysdate from dual;
SYSDATE
-----------------
26-10-18 14:38:01
<<Changed the system date by 1 day ahead>>
SQL> select sysdate from dual;
SYSDATE
-----------------
27-10-18 14:39:36
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 24.75 0 75 0
BACKUP PIECE .62 .36 13 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 5.47 4.17 8 0 << Space reclaimable
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> select sysdate, min(first_time), (sysdate-min(first_time))*24*60 from v$flashback_database_logfile;
SYSDATE MIN(FIRST_TIME) (SYSDATE-MIN(FIRST_TIME))*24*60
----------------- ----------------- -------------------------------
27-10-18 14:40:51 25-10-18 10:22:42 3138.15
SQL> select sysdate, min(first_time), (sysdate-min(first_time))*24*60 from v$flashback_database_logfile;
SYSDATE MIN(FIRST_TIME) (SYSDATE-MIN(FIRST_TIME))*24*60
----------------- ----------------- -------------------------------
27-10-18 14:43:47 26-10-18 14:32:31 1451.26667
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 25.3 0 76 0
BACKUP PIECE .62 .36 13 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 5.47 1.95 8 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 22.42 0 71 0
BACKUP PIECE .62 .36 13 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 3.52 0 5 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> alter session set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS';
Session altered.
SQL> select sysdate, min(first_time), (sysdate-min(first_time))*24*60 from v$flashback_database_logfile;
SYSDATE MIN(FIRST_TIME) (SYSDATE-MIN(FIRST_TIME))*24*60
----------------- ----------------- -------------------------------
26-10-18 14:36:05 25-10-18 10:22:42 1693.38333
SQL>select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 23.59 0 73 0
BACKUP PIECE .62 .36 13 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 4.82 0 7 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> select sysdate from dual;
SYSDATE
-----------------
26-10-18 14:38:01
<<Changed the system date by 1 day ahead>>
SQL> select sysdate from dual;
SYSDATE
-----------------
27-10-18 14:39:36
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 24.75 0 75 0
BACKUP PIECE .62 .36 13 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 5.47 4.17 8 0 << Space reclaimable
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> select sysdate, min(first_time), (sysdate-min(first_time))*24*60 from v$flashback_database_logfile;
SYSDATE MIN(FIRST_TIME) (SYSDATE-MIN(FIRST_TIME))*24*60
----------------- ----------------- -------------------------------
27-10-18 14:40:51 25-10-18 10:22:42 3138.15
SQL> select sysdate, min(first_time), (sysdate-min(first_time))*24*60 from v$flashback_database_logfile;
SYSDATE MIN(FIRST_TIME) (SYSDATE-MIN(FIRST_TIME))*24*60
----------------- ----------------- -------------------------------
27-10-18 14:43:47 26-10-18 14:32:31 1451.26667
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 25.3 0 76 0
BACKUP PIECE .62 .36 13 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 5.47 1.95 8 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
No comments:
Post a Comment