1、环境信息
11g RAC + 单节点ADG
2、目的
清理部分已应用过的归档，且清理之前保证主库所有归档已被应用
3、异常信息
--定时任务在清理过期归档时出现，但DG日志应用是正常的
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+ARCH/testdb_dg/archivelog/2023_02_04/thread_2_seq_33410.981.1127923471 thread=2 sequence=33410
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+ARCH/testdb_dg/archivelog/2023_02_04/thread_2_seq_33411.983.1127929941 thread=2 sequence=33411
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+ARCH/testdb_dg/archivelog/2023_02_04/thread_2_seq_33412.984.1127937443 thread=2 sequence=33412
specification does not match any archived log in the repository
4、过程分析
--主库参数配置
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name testdb_DG are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+arch/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
--这里配置了 APPLIED ON STANDBY
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/testdb/snapcf_testdb1.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/testdb/snapcf_testdb1.f';
RMAN>
--此次没配置 MANDATORY ，如果配置在远程归档路径中，一旦出现断网，则会影响主库，导致主库hang住。网上有类似分析
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_2 string service=testdbdg async valid_f
or=(online_logfiles,primary_ro
le) db_unique_name=testdbdg ne
t_timeout=10
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
--说明：是说archivelog在standby目录中接收生产了，并不是被实际应用了，standby正常开启，但MPR不开启应用redo，并没有被应用，并没有被应用
--原来清理脚本
...
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-7';
delete noprompt expired archivelog all;
...
--检查备库查询日志应用是否正常,只有 IN-MEMORY 属于正常
SELECT A.THREAD#, A.SEQUENCE#, A.APPLIED
FROM V\$ARCHIVED_LOG A, V\$DATABASE D
WHERE A.ACTIVATION# = D.ACTIVATION#
AND A.APPLIED <> 'YES';
5、处理措施
--先修改删除归档策略
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
或者
注：如果带上 FORCE 参数可以忽略 RMAM-08120直接删除 archivelog
--delete force archivelog until time 'sysdate-3';
delete noprompt force archivelog all completed before 'sysdate-7';