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