Flashback Logs-Space management [ID 369755.1]（ flashback 笔记-02）

最新推荐文章于 2025-06-07 19:24:53 发布

转载最新推荐文章于 2025-06-07 19:24:53 发布 · 529 阅读

文章标签：

#database #delete #flash #server #disk #file

oracle 专栏收录该内容

59 篇文章

订阅专栏

本文介绍了解决Oracle数据库中闪回日志空间不足的方法。通过禁用和重新启用闪回功能来清理旧的日志文件，并提供了一个绕过Bug 5106952导致的空间无法回收的问题方案。

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

Applies to:

Oracle Server Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1
Information in this document applies to any platform.
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1

Goal

The recovery file dest is full of flashback logs. There are ORA-19815 errors in the production alert log, then its necessary to remove or reuse the flashback logs (FBL).

Solution

Flashback logs are handled internally and we have no control over them. This means
that we cannot delete flashback logs manually, the ONLY way to delete then
is disabling FLASHBACK.

The FlashBack Logs can ONLY be deleted by DISABLING FLASHBACK
on database level

SQL> startup mount
alter database flashback off;
alter database open;

During space pressure, the Flashback logs are deleted under two
conditions:

1) When an archivelog is deleted, it would also delete the flashback logs
    that are dependent on the archived log.
2) When quota is shrunk, then flashback logs is deleted to reclaim disk
    space. This is decreasing the DB_RECOVERY_FILE_DEST_SIZE to such a value
     that the FLB itself will run into a space pressure and than the old
     FLB's will get deleted.

We can hit bellow bug and then the flashback logs are not deleted
even there is space pressure in the Flash backup disk space.

Bug 5106952
Hdr: 5106952 10.2.0.1.0 RDBMS 10.2.0.1.0 FLASHBACK_DB PRODID-5 PORTID-23
Abstract: FLASHBACK LOG SPACE NOT BEING RECLAIMED
Fixed In Ver: 11.0
Also the bug is fixed in patch set 10.1.0.6 and 10.2.0.3 .

The workaround to this bugs is to disable flashback database option then it will be possible to remove the FBL.

1.- First of all we need to check if there is any guaranteed restore point defined:

select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;

2.- Remove all the restore points

Drop restore point <name>;

3.- Check how much space is used before turning off Flashback

select * from v$flash_recovery_area_usage;

4.- ALTER DATABASE FLASHBACK OFF;

5.- Now you can delete the Flashback logs manually

6.- ALTER DATABASE FLASHBACK ON;

7.- Check that there are no flashback logs before turning back on

select * from v$flash_recovery_area_usage;