今天早晨一个ADG的灾备库预警。提示闪回恢复区占用了97%以上的空间。

登陆服务器查询V$RECOVERY_AREA_USAGE的时候，发现foreign archived log 占用的空间较多。

SQL> select * from v$recovery_area_usage;
FILE_TYPE                                PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------------------------------- ------------------ ------------------------- ---------------
CONTROL FILE                                              0                         0               0
REDO LOG                                              10.36                         0               6
ARCHIVED LOG                                              0                         0               0
BACKUP PIECE                                              0                         0               0
IMAGE COPY                                                0                         0               0
FLASHBACK LOG                                             0                         0               0
FOREIGN ARCHIVED LOG                                  87.00                         0             459
 
7 rows selected.

出现FOREIGN ARCHIVED LOG占用过大的原因是：

这个ADG环境之前同一套库之前同事搭建还原过多次，在搭建过程中多次的归档日志，放在了同一个地方。所以被认为是foreign archived log .使用catalog注册这些归档的时候，其实还不认识这些归档。

解决方案：

在备库上登陆rman执行如下命令即可。

CROSSCHECK FOREIGN archivelog all;
DELETE EXPIRED FOREIGN ARCHIVelog all;

执行完毕后，再检查 V$RECOVERY_AREA_USAGE。这次空间就释放了。

SQL> select * from v$recovery_area_usage;
FILE_TYPE                                PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------------------------------- ------------------ ------------------------- ---------------
CONTROL FILE                                              0                         0               0
REDO LOG                                              10.36                         0               6
ARCHIVED LOG                                              0                         0               0
BACKUP PIECE                                              0                         0               0
IMAGE COPY                                                0                         0               0
FLASHBACK LOG                                             0                         0               0
FOREIGN ARCHIVED LOG                                      0                         0               0
 
7 rows selected.