清理FLASHBACK LOG

2021年2月2日 1054点热度 0人点赞 1条评论

19.9 ADG环境,查看Flash Recovery Area Usage发现FLASHBACK LOG占用了90%多的FRA空间,而ARCHIVED LOG仅占用不到10%,这还了得,这是一个繁忙的生产环境,一般是不需要长时间的Flash Back的,得想办法清理一下,以免影响归档。【注:其实是不会影响归档的,具体的实践观察见后】
如下是清理前的占用情况:

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES          CON_ID
----------------------- ------------------ ------------------------- --------------- ---------------
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                           7.1                         0             135               0
FLASHBACK LOG                        91.12                         0            1624               0
---以下是过一会儿之后的情况
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          7.63                         0             145               0
FLASHBACK LOG                        91.31                         0            1627               0

经查阅资料,发现没有很直接的删除闪回日志的方法,只能关闭数据库的闪回功能,但是本集群由于开启了DG_BROKER以实现Auto FailOver,所以这个闪回功能不能关,只能偶尔关一下释放一下空间,然后再打开。
如下先查看数据库闪回状态、恢复点,确认没有恢复点才能关闭闪回。

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
select FLASHBACK_ON from v$database;
SELECT NAME,TO_CHAR(TIME,'YYYY/MM/DD HH24:MI:SS'),GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;

打开、关闭闪回的命令:

alter database flashback on;
alter database flashback off;

执行关闭操作以后,时间很长,如下是期间的占用查询输出:

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES          CON_ID
----------------------- ------------------ ------------------------- --------------- ---------------
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                           7.1                         0             135               0
FLASHBACK LOG                        91.12                         0            1624               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          7.63                         0             145               0
FLASHBACK LOG                        91.31                         0            1627               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          7.85                         0             149               0
FLASHBACK LOG                        91.18                       .19            1625               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          8.61                         0             164               0
FLASHBACK LOG                        90.42                         0            1610               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          9.19                         0             177               0
FLASHBACK LOG                        89.89                         0            1595               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                         10.45                         0             203               0
FLASHBACK LOG                        58.67                     58.67             980               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                         10.67                         0             207               0
FLASHBACK LOG                        23.06                     23.06             373               0
■■■■■■■■■■■■■■■■■■■■■■■■48分钟以后
---12:05
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          4.82                         0              95               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                            0                         0               0               0

如下是数据库日志输出:

2021-02-02T11:14:50.960201+08:00
alter database flashback off
,,,
2021-02-02T11:27:33.515243+08:00
Deleted Oracle managed file +FRA/WYDB/FLASHBACK/log_444.1445.1063365911
开始删除log文件,一直到12:00左右才开始大规模迅速删除!
2021-02-02T12:05:26.192800+08:00
Deleted Oracle managed file +FRA/WYDB/FLASHBACK/log_1642.819.1063441923
Deleted Oracle managed file +FRA/WYDB/FLASHBACK/log_1643.1449.1063442187
Deleted Oracle managed file +FRA/WYDB/FLASHBACK/log_1644.1646.1063442281
Flashback Database Disabled
2021-02-02T12:05:26.357207+08:00
Errors in file /u01/app/oracle/diag/rdbms/wydb/wydb1/trace/wydb1_ora_326690.trc:
ORA-38885: WARNING: Flashback database has been disabled while fast-start failover is enabled.
*************************************************************
  WARNING: Flashback Database has been disabled while
           Fast-Start Failover is enabled. With Flashback
           disabled on a primary database, Data Guard Broker
           will be unable to reinstate the primary database
           in the event of a Fast-Start Failover to the
           target standby database.
*************************************************************
Completed: alter database flashback off

然后开启flashback
2021-02-02T12:09:41.888497+08:00
alter database flashback on
2021-02-02T12:09:41.914560+08:00
Starting background process RVWR
2021-02-02T12:09:41.938946+08:00
RVWR started with pid=357, OS id=389870
2021-02-02T12:09:51.552802+08:00
Already allocated 255008576 bytes in shared pool for flashback generation buffer.
2021-02-02T12:09:57.985644+08:00
Thread 1 advanced to log sequence 12654 (LGWR switch),  current SCN: 16801625346276
  Current log# 11 seq# 12654 mem# 0: +DATA1/WYDB/ONLINELOG/group_11.343.1062849321
  Current log# 11 seq# 12654 mem# 1: +FRA/WYDB/ONLINELOG/group_11.954.1062849331
2021-02-02T12:10:00.987550+08:00
ARC2 (PID:16741): Archived Log entry 62254 added for T-1.S-12653 ID 0x4dbccd0a LAD:1
2021-02-02T12:10:07.037247+08:00
Flashback Database Enabled at SCN 16801625379439
Completed: alter database flashback on

可见期间自动删除了大量的闪回日志文件:
Deleted Oracle managed file +FRA/WYDB/FLASHBACK/log

后续对FRA的占用情况做了观察统计,以下的输出表明,FLASHBACK log虽然占用了大量的FRA空间,但是会根据需要自动清理,一般不会影响ArchiveLog的使用。

---20210205 01:30
SYS@10.231.236.21:1521/pdbcol> SELECT * FROM v$flash_recovery_area_usage;
FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES          CON_ID
----------------------- ------------------ ------------------------- --------------- ---------------
CONTROL FILE                             0                         0               1               0
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          6.55                         0             123               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        92.49                         0             312               0
FOREIGN ARCHIVED LOG                     0                         0               0               0
AUXILIARY DATAFILE COPY                  0                         0               0               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          6.66                         0             125               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        92.18                         0             311               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          6.79                         0             128               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        92.18                         0             311               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          6.84                         0             129               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        92.18                         0             311               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                           6.9                         0             130               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        91.87                         0             310               0
---20210205 01:50
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          7.01                         0             132               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        91.87                         0             310               0
---
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          7.01                         0             132               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        92.02                         0             311               0
--- 8:43
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          3.84                         0              73               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        94.29                       .29             323               0
--- 9:00
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          4.52                         0              86               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                         94.3                         0             323               0
--- 9:50
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          3.78                         0              73               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        94.48                         0             324               0
--- 11:54
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          4.64                         0              88               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                         94.2                         0             327               0
--- 12:06
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          2.31                         0              44               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        93.76                         0             326               0                            
--- 12:37
REDO LOG                                .9                         0              14               0
ARCHIVED LOG                          4.37                         0              82               0
BACKUP PIECE                             0                         0               0               0
IMAGE COPY                               0                         0               0               0
FLASHBACK LOG                        94.52                         0             329               0

liking

我是雪人

文章评论

  • liking

    关于闪回日志占用的另外一个总结:
    闪回日志占用空间太多,如果直接使用OS命令rm删除,会导致启动时报错。
    这是因为,在数据库open阶段,rvwr进程需要加载闪回日志文件,由于找不到所以无法正常启动,而在关闭flashback后,rvwr进程被关闭,相应的闪回文件也会被删除。
    目前oracle还没有针对FLASHBACK LOG的手动维护操作,FLASHBACK LOG满了之后会自动覆盖之前的内容,手工清除办法就是关闭闪回。

    2021年2月5日