计划统计一下这个生产库的归档空间占用，节日期间由于孩子上网课，一起憋在家里哪里也不去了，边学习边统计，也留作参考。
■ select * from v$flash_recovery_area_usage; ■start FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE 0 0 0 REDO LOG .47 0 12 ARCHIVED LOG 15.32 4.91 551 ■09-30 22:40 ARCHIVED LOG 64.59 42.74 2295 ■10-01 08:50 ARCHIVED LOG 60.82 32.51 2185 ■10-01 12:45 ARCHIVED LOG 60.44 45.77 2191 ■10-01 18:27 ARCHIVED LOG 64.37 51.02 2337 ■10-01 20:07 ARCHIVED LOG 58.81 46.92 2142 ■10-02 11:05 ARCHIVED LOG 65.24 53.88 2394 ■10-02 12:26 ARCHIVED LOG 59.64 45.62 2190 ■10-02 15:10 ARCHIVED LOG 64.89 45.62 2399 ■10-02 16:52 ARCHIVED LOG 59.65 51.02 2206 ■10-02 22:00 ARCHIVED LOG 62.17 47.22 2308 ■10-03 11:36 ARCHIVED LOG 65.75 52.33 2407 ■10-03 12:37 ARCHIVED LOG 59.16 43.81 2141 ■10-03 21:36 ARCHIVED LOG 62.62 47.75 2214 ■10-04 11:57 ARCHIVED LOG 65.23 51.85 2294 ■10-04 12:03 ARCHIVED LOG 65.52 51.85 2305 ■10-04 19:24 ARCHIVED LOG 64.74 49.88 2287 ■10-04 21:22 ARCHIVED LOG 60.05 46.02 2144 ■10-05 12:48 ARCHIVED LOG 58.44 44.26 2134 ■10-05 20:37 ARCHIVED LOG 58.79 45.68 2157 ■10-06 09:45 ARCHIVED LOG 61.3 44.87 2219 ■10-06 22:21 ARCHIVED LOG 63.99 41.83 2308 ■10-08 19:34 ARCHIVED LOG 66.1 51.07 2391 ■10-09 09:01 ARCHIVED LOG 59.94 53.06 2172 ■ 归档位置及空间大小 db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 5000G
根据中秋国庆期间的以上统计数据，当前每4小时清理1天前的归档，归档基本保留1天时间，按照占比62%计算，则平均所需存储空间3T以上。
可以想见，工作日忙时所需空间更大。
文章评论
v$flash_recovery_area_usage里percent_space_used与percent_space_reclaimable应该一致，需要设置相关policy/archivelog/2020_05_30/o1_mf_1_151_6y71q675_.arc
建议主库归档日志的删除策略设置如下：
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED/APPLIED TO ALL STANDBY;
在视图v$flash_recovery_area_usage中的PERCENT_SPACE_RECLAIMABLE字段反映可以被自动删除的归档日志的百分比。
自动删除后在数据库的 alert.log 中将会看到类似下面的提示：
Deleted Oracle managed file
/opt/app/oracle/FRA/