我们在搭建完ADG灾备库后，我们经常会遇到归档日志满的预警信息，那么备库的归档日志是怎么删除的？怎么管理的？搭建过dg的同学肯定非常清楚归档日志和备份的重要性，这些归档日志从主库传输到备库，在备库被应用apply。思考一个问题：主库的归档日志是怎么管理呢，备库的归档日志又该怎么管理呢？
-
不能够随意手动删除归档日志，归档日志丢失会导致Data Guard需要重新搭建。
-
不能随意使用RMAN删除归档日志，否则同样会导致Data Guard需要重新搭建。
-
在使用RMAN备份后，如果归档没有被传送或应用到备库上，那么RMAN不应该删除归档日志，否则Data Guard需要的归档就必须从备份里面还原出来，增加了维护工作量。
-
归档应尽量长时间的保存在磁盘上，以避免Data Guard长时间维护时归档被删除。
-
备库的归档日志不需要花精力去维护，自动删除已经应用过的归档日志。 CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
-
在主备库上需要指定归档路径为 快速恢复区，这样好让rman来管理闪回区。 log_archive_dest_size_1='location=USE_DB_RECOVERY_FILE_DEST'
-
归档日志如果没有应用到备库，那么在RMAN中使用备份命令backup .... delete inputs all和删除归档命令delete archivelog all不会将归档日志删除。 请注意如果是使用delete force命令则会删除掉归档，不管归档有没有被应用到备库。
-
如果归档日志已经应用到了备库，那么在RMAN中使用backup .... delete inputs all和delete archivelog all可以删除归档日志，在正常情况下，由于归档日志可能很快应用到Data Guard，所以在RMAN备份之后可以正常删除归档日志。也不必担心人为不小心使用。delete archivelog all命令删除了归档。
- 备库的归档日志存储到快速恢复区中，备库的快速恢复区空间紧张时，会自动删除已经应用过的较早的归档日志以释放空间，这样便可以实现备库的归档日志完全自动管理。
-
如果由于备份异常或Data Guard异常，在快速恢复区空间紧张时，Oracle在切换日志时，会自动删除掉已经应用过的归档日志，以释放空间。但是如果归档日志没有应用到Data Guard，那么归档日志不会被删除。这种情况下，快速恢复区的归档可能会增加到空间耗尽，最后就会出现数据库不能归档，数据库挂起的问题。
首先说一下日志的重要性， 归档日志是非常重要的，备份恢复需要它，而Data Guard也需要它。
上文提到快速恢复区空间紧张，具体是指什么时间？也就是快速恢复区的空间消耗多少百分比的时候才算是空间紧张？在MOS文章《Files being deleted in the flash recovery area, messages in the alert log Deleted Oracle managed file <filename> (Doc ID 1369341.1)》里面有提到，空间使用率达到80%以后就开始删除文件（归档日志）。 Oracle在往快速恢复区存储文件时，其步骤大概是这样的：Oracle估计需要的空间大小（切换日志时就是归档日志大小），然后将这个大小与当前的占用空间大小相加，看是否超过了80%，如果超过了，那么就回收空间（回收的空间应大于等于新建文件需要的空间大小，也就是回收的空间以够用为原则）。如果不能回收空间（比如归档日志没有被应用到备库），那就只能继续占用新的空间，直到空间耗尽。 这里的问题是，假设快速恢复区设定了30G空间，那么在使用到80%，也就是24G的时候就开始回收空间。那么我们在估算空间时，就应该上浮20%。比如我们要求保留24小时归档，这24小时之内归档量最大是30G，那么我们应该为快速恢复区设置36G左右的容量。
SQL> show parameter recover NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /oradata/fast_recovery_area db_recovery_file_dest_size big integer 30G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 remote_recovery_file_dest string SQL>
[oracle@testdba fast_recovery_area]$ du -hs 24G . [oracle@testdba fast_recovery_area]$
SQL> select * from V$RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 69.93 69.93 183 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 9.11 0 14 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected.
我们可以看到现在的使用情况已经达到了79.54%,已经处于触发的临界点了。我们看一下alter日志中。似乎每次接受一次归档，都会触发一次自动的删除就归档的操作。
2024-08-05T15:58:44.065888+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18125 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-05T15:58:44.146998+08:00 ARC0 (PID:23131): Archived Log entry 4032 added for T-1.S-18124 ID 0xf18fc702 LAD:1 2024-08-05T16:58:34.395153+08:00 rfs (PID:23240): Selected LNO:4 for T-1.S-18126 dbid 4052763138 branch 1071049602 2024-08-05T16:58:34.403024+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_25/o1_mf_1_17937_mb3y8k4o_.arc 2024-08-05T16:58:34.561566+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18126 (in transit) 2024-08-05T16:58:34.561852+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18126 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-05T16:58:34.632797+08:00 ARC2 (PID:23139): Archived Log entry 4033 added for T-1.S-18125 ID 0xf18fc702 LAD:1 2024-08-05T18:12:31.688797+08:00 rfs (PID:23240): Selected LNO:5 for T-1.S-18127 dbid 4052763138 branch 1071049602 2024-08-05T18:12:31.696440+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_25/o1_mf_1_17938_mb415mnm_.arc 2024-08-05T18:12:31.891584+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18127 (in transit) 2024-08-05T18:12:31.891872+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18127 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-05T18:12:31.930030+08:00 ARC0 (PID:23131): Archived Log entry 4034 added for T-1.S-18126 ID 0xf18fc702 LAD:1 2024-08-05T19:34:41.999619+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_25/o1_mf_1_17939_mb44fnbj_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_25/o1_mf_1_17940_mb48nxvf_.arc 2024-08-05T19:40:03.170431+08:00 PL/SQL package SYS.DBMS_BACKUP_RESTORE version is not current 2024-08-05T19:54:53.246247+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18128 (in transit) 2024-08-05T19:54:53.246598+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18128 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-05T19:54:53.247275+08:00 ARC2 (PID:23139): Archived Log entry 4035 added for T-1.S-18127 ID 0xf18fc702 LAD:1 2024-08-05T21:46:17.704134+08:00 rfs (PID:23240): Selected LNO:5 for T-1.S-18129 dbid 4052763138 branch 1071049602 2024-08-05T21:46:17.713044+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/flashback/o1_mf_mbn1flm6_.flb 2024-08-05T21:46:17.798605+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18129 (in transit) 2024-08-05T21:46:17.798897+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18129 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-05T21:46:17.938454+08:00 ARC0 (PID:23131): Archived Log entry 4036 added for T-1.S-18128 ID 0xf18fc702 LAD:1 2024-08-05T22:00:06.781946+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_25/o1_mf_1_17941_mb4gqbl8_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_25/o1_mf_1_17942_mb4nz61h_.arc 2024-08-05T22:54:31.431765+08:00 rfs (PID:23240): Standby controlfile consistent with primary 2024-08-05T22:54:31.445461+08:00 rfs (PID:23240): Selected LNO:4 for T-1.S-18130 dbid 4052763138 branch 1071049602 2024-08-05T22:54:31.565560+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18130 (in transit) 2024-08-05T22:54:31.565839+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18130 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-05T22:54:31.631812+08:00 ARC2 (PID:23139): Archived Log entry 4037 added for T-1.S-18129 ID 0xf18fc702 LAD:1 2024-08-06T00:27:04.605051+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_25/o1_mf_1_17943_mb4qm2r9_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17944_mb558l8c_.arc 2024-08-06T05:00:05.669861+08:00 rfs (PID:23240): Standby controlfile consistent with primary 2024-08-06T05:00:05.685127+08:00 rfs (PID:23240): Selected LNO:5 for T-1.S-18131 dbid 4052763138 branch 1071049602 2024-08-06T05:00:05.691471+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17946_mb5h6ohw_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17945_mb5h6obl_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17947_mb5osz1x_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17948_mb5ot6x7_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17949_mb5ot8sr_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17950_mb5wnkro_.arc 2024-08-06T05:00:05.802598+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18131 (in transit) 2024-08-06T05:00:05.802906+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18131 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-06T05:00:05.920505+08:00 ARC0 (PID:23131): Archived Log entry 4038 added for T-1.S-18130 ID 0xf18fc702 LAD:1 2024-08-06T05:00:06.012330+08:00 rfs (PID:23240): Standby controlfile consistent with primary 2024-08-06T05:00:06.026701+08:00 rfs (PID:23240): Selected LNO:4 for T-1.S-18132 dbid 4052763138 branch 1071049602 2024-08-06T05:00:06.040806+08:00 ARC2 (PID:23139): Archived Log entry 4039 added for T-1.S-18131 ID 0xf18fc702 LAD:1 2024-08-06T05:00:06.110556+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18132 (in transit) 2024-08-06T05:00:06.110858+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18132 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-06T07:02:57.562054+08:00 rfs (PID:23240): Selected LNO:5 for T-1.S-18133 dbid 4052763138 branch 1071049602 2024-08-06T07:02:57.681227+08:00 ARC0 (PID:23131): Archived Log entry 4040 added for T-1.S-18132 ID 0xf18fc702 LAD:1 2024-08-06T07:02:57.693067+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18133 (in transit) 2024-08-06T07:02:57.693369+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18133 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-06T07:03:05.511853+08:00 rfs (PID:23240): Standby controlfile consistent with primary 2024-08-06T07:03:05.525198+08:00 rfs (PID:23240): Selected LNO:4 for T-1.S-18134 dbid 4052763138 branch 1071049602 2024-08-06T07:03:05.525279+08:00 ARC2 (PID:23139): Archived Log entry 4041 added for T-1.S-18133 ID 0xf18fc702 LAD:1 2024-08-06T07:03:05.613538+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18134 (in transit) 2024-08-06T07:03:05.613799+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18134 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-06T07:03:09.663166+08:00 rfs (PID:23240): Standby controlfile consistent with primary 2024-08-06T07:03:09.676067+08:00 rfs (PID:23240): Selected LNO:5 for T-1.S-18135 dbid 4052763138 branch 1071049602 2024-08-06T07:03:09.676155+08:00 ARC0 (PID:23131): Archived Log entry 4042 added for T-1.S-18134 ID 0xf18fc702 LAD:1 2024-08-06T07:03:09.770498+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18135 (in transit) 2024-08-06T07:03:09.770755+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18135 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-06T09:13:09.079979+08:00 rfs (PID:23240): Selected LNO:4 for T-1.S-18136 dbid 4052763138 branch 1071049602 2024-08-06T09:13:09.087989+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17951_mb5zs6ob_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17952_mb62fm05_.arc 2024-08-06T09:13:09.318956+08:00 ARC2 (PID:23139): Archived Log entry 4043 added for T-1.S-18135 ID 0xf18fc702 LAD:1 2024-08-06T09:13:09.393542+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18136 (in transit) 2024-08-06T09:13:09.393816+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18136 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-06T10:01:04.981164+08:00 rfs (PID:23240): Selected LNO:5 for T-1.S-18137 dbid 4052763138 branch 1071049602 2024-08-06T10:01:05.185009+08:00 ARC0 (PID:23131): Archived Log entry 4044 added for T-1.S-18136 ID 0xf18fc702 LAD:1 2024-08-06T10:01:05.530544+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18137 (in transit) 2024-08-06T10:01:05.530826+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18137 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-06T10:43:48.180213+08:00 rfs (PID:23240): Selected LNO:4 for T-1.S-18138 dbid 4052763138 branch 1071049602 2024-08-06T10:43:48.203078+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17953_mb65chz4_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17954_mb69c4s7_.arc 2024-08-06T10:43:48.336605+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18138 (in transit) 2024-08-06T10:43:48.336920+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18138 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-06T10:43:48.425563+08:00 ARC2 (PID:23139): Archived Log entry 4045 added for T-1.S-18137 ID 0xf18fc702 LAD:1 2024-08-06T11:24:18.638346+08:00 rfs (PID:23240): Selected LNO:5 for T-1.S-18139 dbid 4052763138 branch 1071049602 2024-08-06T11:24:18.840449+08:00 ARC0 (PID:23131): Archived Log entry 4046 added for T-1.S-18138 ID 0xf18fc702 LAD:1 2024-08-06T11:24:18.912542+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18139 (in transit) 2024-08-06T11:24:18.912841+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18139 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-06T12:35:40.170591+08:00 rfs (PID:23240): Selected LNO:4 for T-1.S-18140 dbid 4052763138 branch 1071049602 2024-08-06T12:35:40.178222+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17955_mb6gmb8y_.arc 2024-08-06T12:35:40.312573+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18140 (in transit) 2024-08-06T12:35:40.312884+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18140 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-06T12:35:40.401490+08:00 ARC2 (PID:23139): Archived Log entry 4047 added for T-1.S-18139 ID 0xf18fc702 LAD:1 2024-08-06T13:56:38.200957+08:00 rfs (PID:23240): Standby controlfile consistent with primary 2024-08-06T13:56:38.216618+08:00 rfs (PID:23240): Selected LNO:5 for T-1.S-18141 dbid 4052763138 branch 1071049602 2024-08-06T13:56:38.224208+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17956_mb6loqxx_.arc 2024-08-06T13:56:38.407590+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18141 (in transit) 2024-08-06T13:56:38.407908+08:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 18141 Reading mem 0 Mem# 0: /oradata/STD/std_redo05.log 2024-08-06T13:56:38.445818+08:00 ARC0 (PID:23131): Archived Log entry 4048 added for T-1.S-18140 ID 0xf18fc702 LAD:1 2024-08-06T15:04:38.187570+08:00 rfs (PID:23240): Standby controlfile consistent with primary 2024-08-06T15:04:38.201371+08:00 rfs (PID:23240): Selected LNO:4 for T-1.S-18142 dbid 4052763138 branch 1071049602 2024-08-06T15:04:38.210675+08:00 Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17957_mb6p69xt_.arc Deleted Oracle managed file /oradata/fast_recovery_area/STD/archivelog/2024_07_26/o1_mf_1_17958_mb6syq4o_.arc 2024-08-06T15:04:38.356563+08:00 PR00 (PID:23256): Media Recovery Waiting for T-1.S-18142 (in transit) 2024-08-06T15:04:38.356858+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 18142 Reading mem 0 Mem# 0: /oradata/STD/std_redo04.log 2024-08-06T15:04:38.440265+08:00 ARC2 (PID:23139): Archived Log entry 4049 added for T-1.S-18141 ID 0xf18fc702 LAD:1 2024-08-06T15:10:09.669315+08:00
那么，这个80%的比率能够更改吗以便延迟Oracle删除归档日志的时间吗？答案是肯定的。没有相应的数据库参数来设定，但是可以通过事件来设置，事件号是19823：
[oracle@testdba trace]$ oerr ora 19823 19823, 00000, "soft limit recovery area space pressure percentage" // *Document: NO // *Cause: Set on all instances to alter recovery area space pressure // trigger percentage. // *Action: level 1 to 100 indicates the percentage when the space // pressure has to be triggered.
现在我们利用事件19823将这个比率调到95%看看会是什么样子：
SQL> alter system set event='19823 trace name context forever,level 95' scope=spfile sid='*';
再运行上面的测试代码，发现Oracle不再删除归档日志，而是到接近95%的空间使用率时再开始删除归档日志：
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 15.33 0 13 ARCHIVED LOG 78.62 59.9 55 BACKUP PIECE .24 0 1 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0
从上面的最后一次对v$recovery_area_usage的查询数据可以看到，此时空间利用率达到了94.19%，离95%已经很接近（在线日志的大小是512MB，占快速恢复区的3.1%，如果在快速恢复区里面多一个文件就会超过95%）。
