参考文档：
V$FOREIGN_ARCHIVED_LOG (oracle.com)
Foreign Archived Logs Not Being Deleted On Logical Standby (Doc ID 2304461.1)
今天在测试环境中，查询V$RECOVERY_AREA_USAGE的时候，发现foreign archived log 占用的空间较多。
- SYS@test PROD1>/
-
- FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
- ---------------------------------------- ------------------ ------------------------- ---------------
- CONTROL FILE 0 0 0
- REDO LOG .98 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 74.97 0 699
-
- 7 rows selected.
-
- SYS@test PROD1>
查询到的具体的文件信息如下：
- SYS@test PROD1>select name from V$FOREIGN_ARCHIVED_LOG where rownum <4;
-
- NAME
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- +ARC/prod/archivelog/2021_11_12/thread_2_seq_180.761.1088382915
- +ARC/prod/archivelog/2021_11_12/thread_2_seq_181.762.1088386897
- +ARC/prod/archivelog/2021_11_12/thread_2_seq_182.763.1088389127
-
- SYS@test PROD1>
-- 解决方法
CROSSCHECK FOREIGN archivelog all;
DELETE EXPIRED FOREIGN ARCHIVelog all;
部分过程如下：
foreign archived log file name=+ARC/prod/archivelog/2021_11_12/thread_2_seq_192.774.1088430487 RECID=13 STAMP=1114294303
validation failed for foreign archived log
foreign archived log file name=+ARC/prod/archivelog/2021_11_12/thread_2_seq_193.775.1088435813 RECID=14 STAMP=1114294303
validation failed for foreign archived log
foreign archived log file name=+ARC/prod/archivelog/2021_11_12/thread_2_seq_194.776.1088441877 RECID=15 STAMP=1114294303
Crosschecked 699 objects
RMAN>
deleted foreign archived log
foreign archived log file name=+ARC/prod/archivelog/2021_11_12/thread_2_seq_192.774.1088430487 RECID=13 STAMP=1114294303
deleted foreign archived log
foreign archived log file name=+ARC/prod/archivelog/2021_11_12/thread_2_seq_193.775.1088435813 RECID=14 STAMP=1114294303
deleted foreign archived log
foreign archived log file name=+ARC/prod/archivelog/2021_11_12/thread_2_seq_194.776.1088441877 RECID=15 STAMP=1114294303
Deleted 699 EXPIRED objects
再次查看，不存在了
- SYS@test PROD1>select * from V$RECOVERY_AREA_USAGE;
-
- FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
- ---------------------------------------- ------------------ ------------------------- ---------------
- CONTROL FILE 0 0 0
- REDO LOG .98 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.
-
- SYS@test PROD1>
这个环境不是一个逻辑备库环境。之前同一套库还原过多次。2次的归档日志，放在了同一个地方。所以被认为是foreign archived log .使用catalog注册这些归档的时候，其实还不认识这些归档。
END。