Oracle闪回空间满处理Unable to allocate flashback log of_unable to allocate flashback log of 8800 blocks fr-CSDN博客

本文链接：https://blog.csdn.net/m0_37625564/article/details/130185118

文章描述了一次Oracle数据库闪回空间满导致的告警情况，通过查询发现闪回日志使用率达到99.99%，存在多个闪回点。为了解决问题，执行了删除过期闪回点的操作，之后检查发现闪回区空间释放，数据库告警消除。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle数据库闪回空间满告警：

2023-04-16T16:55:34.328765+08:00
*************************************************************
Unable to allocate flashback log of 4097 blocks from
current recovery area of size 536870912000 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
2023-04-16T16:56:34.575164+08:00
*************************************************************
Unable to allocate flashback log of 4097 blocks from
current recovery area of size 536870912000 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
2023-04-16T16:57:34.884968+08:00
*************************************************************
Unable to allocate flashback log of 4097 blocks from
current recovery area of size 536870912000 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.

查看闪回区使用率

SQL> col file_type for a50
SQL> set linesize 200
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                                                             0                         0               0          0
BACKUP PIECE                                                             0                         0               1          0
IMAGE COPY                                                             .01                         0               1          0
FLASHBACK LOG                                                        99.99                         0             284          0
FOREIGN ARCHIVED LOG                                                     0                         0               0          0
AUXILIARY DATAFILE COPY                                                  0                         0               0          0

8 rows selected.

可以看到flashback log使用率99.99%。
检查闪回点

SQL> select name from v$restore_point;

...
POINT_2023_04_01_00_04_58
POINT_2023_04_01_00_36_03
POINT_2023_04_01_01_06_32
POINT_2023_04_01_01_37_27
POINT_2023_04_01_02_07_57
POINT_2023_04_01_02_38_28
POINT_2023_04_01_03_08_59
POINT_2023_04_01_03_39_29
POINT_2023_04_01_04_09_59
POINT_2023_04_01_04_40_29
POINT_2023_04_01_05_11_00
POINT_2023_04_01_05_41_32
POINT_2023_04_01_06_12_03
POINT_2023_04_01_06_42_37
POINT_2023_04_01_07_13_32
POINT_2023_04_01_07_44_04
POINT_2023_04_01_08_14_33
POINT_2023_04_01_08_45_05
POINT_2023_04_01_09_15_35
POINT_2023_04_01_09_46_05
POINT_2023_04_01_10_16_35
POINT_2023_04_01_10_47_08
...

删除闪回点

SQL> select 'drop restore point '||name||';' from v$restore_point where name like 'POINT_2023_03%';

删除闪回点后再检查闪回区使用率

SQL> col file_type for a50
SQL> set linesize 200
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                                                        0                         0               0          0
BACKUP PIECE                                                        0                         0               1          0
IMAGE COPY                                                        .01                         0               1          0
FLASHBACK LOG                                                   38.76                         0             131          0
FOREIGN ARCHIVED LOG                                                0                         0               0          0
AUXILIARY DATAFILE COPY                                             0                         0               0          0

8 rows selected.

闪回区空间释放，数据库alert告警消失，问题解决。