备库未应用的归档日志在主库端的delete测试

文档课题：备库未应用的归档日志在主库端的delete测试.
主库：CentOS 7.9 64位 + oracle 11.2.0.4 64位
备库：CentOS 7.9 64位 + oracle 11.2.0.4 64位
1、场景模拟
1.1、主库数据
主库最近归档日志.
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 13','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        21 2022-09-26 11:57:26          0 /u01/app/oracle/fast_recovery_area/archivelog/1_21_1101942938.dbf
        20 2022-09-26 11:57:22          0 /u01/app/oracle/fast_recovery_area/archivelog/1_20_1101942938.dbf
        19 2022-08-28 23:27:25          0 /u01/app/oracle/fast_recovery_area/archivelog/1_19_1101942938.dbf
        18 2022-08-28 23:27:22          0 /u01/app/oracle/fast_recovery_area/archivelog/1_18_1101942938.dbf
        17 2022-08-28 23:07:19          0 /u01/app/oracle/fast_recovery_area/archivelog/1_17_1101942938.dbf
        16 2022-08-28 23:06:07          0 /u01/app/oracle/fast_recovery_area/archivelog/1_16_1101942938.dbf
        15 2022-08-28 23:05:52          0 /u01/app/oracle/fast_recovery_area/archivelog/1_15_1101942938.dbf
        14 2022-08-28 23:00:19          0 /u01/app/oracle/fast_recovery_area/archivelog/1_14_1101942938.dbf

8 rows selected.
1.2、停止应用
备库停止实时应用.
> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY
> alter database recover managed standby database cancel;
1.3、产生归档
主库建表insert数据，产生新归档日志.
> create table t1 as select * from all_objects;
> insert into t1 select * from t1;
84395 rows created.
……(省略若干insert)
> select count(*) from t1;
  COUNT(*)
----------
   2700640
> commit;
1.4、归档确认
1.4.1、主库查看
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        30 2022-09-26 13:14:39         39 /u01/app/oracle/fast_recovery_area/archivelog/1_30_1101942938.dbf
        29 2022-09-26 13:14:35         39 /u01/app/oracle/fast_recovery_area/archivelog/1_29_1101942938.dbf
        28 2022-09-26 13:14:32         39 /u01/app/oracle/fast_recovery_area/archivelog/1_28_1101942938.dbf
        27 2022-09-26 13:12:41         39 /u01/app/oracle/fast_recovery_area/archivelog/1_27_1101942938.dbf
        26 2022-09-26 13:12:40         33 /u01/app/oracle/fast_recovery_area/archivelog/1_26_1101942938.dbf
        25 2022-09-26 13:09:32         39 /u01/app/oracle/fast_recovery_area/archivelog/1_25_1101942938.dbf
        24 2022-09-26 13:07:10         19 /u01/app/oracle/fast_recovery_area/archivelog/1_24_1101942938.dbf
        23 2022-09-26 13:07:08         39 /u01/app/oracle/fast_recovery_area/archivelog/1_23_1101942938.dbf
        22 2022-09-26 11:57:44         31 /u01/app/oracle/fast_recovery_area/archivelog/1_22_1101942938.dbf
        21 2022-09-26 11:57:26          0 /u01/app/oracle/fast_recovery_area/archivelog/1_21_1101942938.dbf
        20 2022-09-26 11:57:22          0 /u01/app/oracle/fast_recovery_area/archivelog/1_20_1101942938.dbf

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        19 2022-08-28 23:27:25          0 /u01/app/oracle/fast_recovery_area/archivelog/1_19_1101942938.dbf
        18 2022-08-28 23:27:22          0 /u01/app/oracle/fast_recovery_area/archivelog/1_18_1101942938.dbf
        17 2022-08-28 23:07:19          0 /u01/app/oracle/fast_recovery_area/archivelog/1_17_1101942938.dbf
        16 2022-08-28 23:06:07          0 /u01/app/oracle/fast_recovery_area/archivelog/1_16_1101942938.dbf
        15 2022-08-28 23:05:52          0 /u01/app/oracle/fast_recovery_area/archivelog/1_15_1101942938.dbf
        14 2022-08-28 23:00:19          0 /u01/app/oracle/fast_recovery_area/archivelog/1_14_1101942938.dbf

17 rows selected.
说明：sequence#从22至30均是新产生的归档日志.
1.4.2、备库查看
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        30 2022-09-26 13:14:39         39 /u01/app/oracle/fast_recovery_area/archivelog1_30_1101942938.dbf
        29 2022-09-26 13:14:35         39 /u01/app/oracle/fast_recovery_area/archivelog1_29_1101942938.dbf
        28 2022-09-26 13:14:32         39 /u01/app/oracle/fast_recovery_area/archivelog1_28_1101942938.dbf
        27 2022-09-26 13:12:41         39 /u01/app/oracle/fast_recovery_area/archivelog1_27_1101942938.dbf
        26 2022-09-26 13:12:40         33 /u01/app/oracle/fast_recovery_area/archivelog1_26_1101942938.dbf
        25 2022-09-26 13:09:32         39 /u01/app/oracle/fast_recovery_area/archivelog1_25_1101942938.dbf
        24 2022-09-26 13:07:10         19 /u01/app/oracle/fast_recovery_area/archivelog1_24_1101942938.dbf
        23 2022-09-26 13:07:08         39 /u01/app/oracle/fast_recovery_area/archivelog1_23_1101942938.dbf
        22 2022-09-26 11:57:44         31 /u01/app/oracle/fast_recovery_area/archivelog1_22_1101942938.dbf
        21 2022-09-26 11:57:26          0 /u01/app/oracle/fast_recovery_area/archivelog1_21_1101942938.dbf
        19 2022-08-28 23:27:25          0 /u01/app/oracle/fast_recovery_area/archivelog1_19_1101942938.dbf

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        18 2022-08-28 23:27:22          0 /u01/app/oracle/fast_recovery_area/archivelog1_18_1101942938.dbf
        17 2022-08-28 23:07:19          0 /u01/app/oracle/fast_recovery_area/archivelog1_17_1101942938.dbf
        16 2022-08-28 23:06:07          0 /u01/app/oracle/fast_recovery_area/archivelog1_16_1101942938.dbf

14 rows selected.
1.5、应用确认
备库查看最新归档应用情况.
> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log order by 1,2;

   THREAD#  SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
         1         14 2022-08-28 23:00:19 2022-08-28 23:05:52 2022-08-28 23:07:19 YES
         1         15 2022-08-28 23:05:52 2022-08-28 23:06:07 2022-08-28 23:07:50 YES
         1         16 2022-08-28 23:06:07 2022-08-28 23:07:19 2022-08-28 23:07:19 YES
         1         17 2022-08-28 23:07:19 2022-08-28 23:27:22 2022-08-28 23:27:24 YES
         1         18 2022-08-28 23:27:22 2022-08-28 23:27:25 2022-08-28 23:27:25 YES
         1         19 2022-08-28 23:27:25 2022-09-26 11:57:22 2022-09-26 11:57:43 YES
         1         20 2022-09-26 11:57:22 2022-09-26 11:57:26 2022-09-26 11:57:43 YES
         1         21 2022-09-26 11:57:26 2022-09-26 11:57:44 2022-09-26 11:57:44 YES
         1         22 2022-09-26 11:57:44 2022-09-26 13:07:08 2022-09-26 13:07:08 NO
         1         23 2022-09-26 13:07:08 2022-09-26 13:07:10 2022-09-26 13:07:11 NO
         1         24 2022-09-26 13:07:10 2022-09-26 13:09:32 2022-09-26 13:09:32 NO

   THREAD#  SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
         1         25 2022-09-26 13:09:32 2022-09-26 13:12:40 2022-09-26 13:12:41 NO
         1         26 2022-09-26 13:12:40 2022-09-26 13:12:41 2022-09-26 13:12:42 NO
         1         27 2022-09-26 13:12:41 2022-09-26 13:14:32 2022-09-26 13:14:33 NO
         1         28 2022-09-26 13:14:32 2022-09-26 13:14:35 2022-09-26 13:14:36 NO
         1         29 2022-09-26 13:14:35 2022-09-26 13:14:39 2022-09-26 13:14:39 NO
         1         30 2022-09-26 13:14:39 2022-09-26 13:14:42 2022-09-26 13:14:42 NO

17 rows selected.
说明：sequence#从22至30均未应用,场景成功模拟出来.
2、delete测试
主库测试delete操作.
2.1、delete all input
2.1.1、备份语句
delete all input包含在备份语句中.
RMAN> backup as compressed backupset full database format '/home/oracle/rmanbak/2dbf_%d_%u_%p_%s' plus archivelog format '/home/oracle/rmanbak/3arc_%d_%u_%p_%s' delete all input;

Starting backup at 26-SEP-22
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=1101981177
input archived log thread=1 sequence=4 RECID=2 STAMP=1102023824
input archived log thread=1 sequence=5 RECID=3 STAMP=1102024493
input archived log thread=1 sequence=6 RECID=4 STAMP=1102024496
input archived log thread=1 sequence=7 RECID=5 STAMP=1102024586
input archived log thread=1 sequence=8 RECID=6 STAMP=1102024590
input archived log thread=1 sequence=9 RECID=7 STAMP=1102024819
input archived log thread=1 sequence=10 RECID=8 STAMP=1102026657
input archived log thread=1 sequence=11 RECID=9 STAMP=1102026729
input archived log thread=1 sequence=12 RECID=12 STAMP=1102026774
input archived log thread=1 sequence=13 RECID=14 STAMP=1113951619
input archived log thread=1 sequence=14 RECID=16 STAMP=1113951952
input archived log thread=1 sequence=15 RECID=17 STAMP=1113951967
input archived log thread=1 sequence=16 RECID=19 STAMP=1113952039
input archived log thread=1 sequence=17 RECID=22 STAMP=1113953243
input archived log thread=1 sequence=18 RECID=24 STAMP=1113953245
input archived log thread=1 sequence=19 RECID=26 STAMP=1116417443
input archived log thread=1 sequence=20 RECID=27 STAMP=1116417446
input archived log thread=1 sequence=21 RECID=30 STAMP=1116417464
input archived log thread=1 sequence=22 RECID=33 STAMP=1116421628
input archived log thread=1 sequence=23 RECID=35 STAMP=1116421630
input archived log thread=1 sequence=24 RECID=37 STAMP=1116421772
input archived log thread=1 sequence=25 RECID=39 STAMP=1116421960
input archived log thread=1 sequence=26 RECID=41 STAMP=1116421961
input archived log thread=1 sequence=27 RECID=43 STAMP=1116422073
input archived log thread=1 sequence=28 RECID=45 STAMP=1116422076
input archived log thread=1 sequence=29 RECID=47 STAMP=1116422079
input archived log thread=1 sequence=30 RECID=49 STAMP=1116422082
input archived log thread=1 sequence=31 RECID=51 STAMP=1116423749
input archived log thread=1 sequence=32 RECID=52 STAMP=1116423749
input archived log thread=1 sequence=33 RECID=53 STAMP=1116423750
input archived log thread=1 sequence=34 RECID=54 STAMP=1116423753
input archived log thread=1 sequence=35 RECID=55 STAMP=1116423757
input archived log thread=1 sequence=36 RECID=56 STAMP=1116423760
input archived log thread=1 sequence=37 RECID=57 STAMP=1116423763
input archived log thread=1 sequence=38 RECID=58 STAMP=1116423766
input archived log thread=1 sequence=39 RECID=59 STAMP=1116424094
input archived log thread=1 sequence=40 RECID=69 STAMP=1116425177
channel ORA_DISK_1: starting piece 1 at 26-SEP-22
channel ORA_DISK_1: finished piece 1 at 26-SEP-22
piece handle=/home/oracle/rmanbak/3arc_ORCL150_0b18miup_1_11 tag=TAG20220926T140617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL150/archivelog/2022_04_14/o1_mf_1_3_k5gzms5f_.arc RECID=1 STAMP=1101981177
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL150/archivelog/2022_04_14/o1_mf_1_4_k5j98jlm_.arc RECID=2 STAMP=1102023824
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL150/archivelog/2022_04_14/o1_mf_1_5_k5j9xfwx_.arc RECID=3 STAMP=1102024493
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL150/archivelog/2022_04_14/o1_mf_1_6_k5j9xjw9_.arc RECID=4 STAMP=1102024496
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_7_1101942938.dbf RECID=5 STAMP=1102024586
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_8_1101942938.dbf RECID=6 STAMP=1102024590
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_9_1101942938.dbf RECID=7 STAMP=1102024819
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_10_1101942938.dbf RECID=8 STAMP=1102026657
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_11_1101942938.dbf RECID=9 STAMP=1102026729
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_12_1101942938.dbf RECID=12 STAMP=1102026774
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_13_1101942938.dbf RECID=14 STAMP=1113951619
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_14_1101942938.dbf RECID=16 STAMP=1113951952
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_15_1101942938.dbf RECID=17 STAMP=1113951967
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_16_1101942938.dbf RECID=19 STAMP=1113952039
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_17_1101942938.dbf RECID=22 STAMP=1113953243
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_18_1101942938.dbf RECID=24 STAMP=1113953245
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_19_1101942938.dbf RECID=26 STAMP=1116417443
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_20_1101942938.dbf RECID=27 STAMP=1116417446
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_21_1101942938.dbf RECID=30 STAMP=1116417464
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_22_1101942938.dbf RECID=33 STAMP=1116421628
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_23_1101942938.dbf RECID=35 STAMP=1116421630
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_24_1101942938.dbf RECID=37 STAMP=1116421772
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_25_1101942938.dbf RECID=39 STAMP=1116421960
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_26_1101942938.dbf RECID=41 STAMP=1116421961
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_27_1101942938.dbf RECID=43 STAMP=1116422073
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_28_1101942938.dbf RECID=45 STAMP=1116422076
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_29_1101942938.dbf RECID=47 STAMP=1116422079
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_30_1101942938.dbf RECID=49 STAMP=1116422082
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_31_1101942938.dbf RECID=51 STAMP=1116423749
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_32_1101942938.dbf RECID=52 STAMP=1116423749
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_33_1101942938.dbf RECID=53 STAMP=1116423750
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_34_1101942938.dbf RECID=54 STAMP=1116423753
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_35_1101942938.dbf RECID=55 STAMP=1116423757
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_36_1101942938.dbf RECID=56 STAMP=1116423760
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_37_1101942938.dbf RECID=57 STAMP=1116423763
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_38_1101942938.dbf RECID=58 STAMP=1116423766
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_39_1101942938.dbf RECID=59 STAMP=1116424094
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_40_1101942938.dbf RECID=69 STAMP=1116425177
Finished backup at 26-SEP-22

Starting backup at 26-SEP-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl150/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl150/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl150/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl150/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-SEP-22
channel ORA_DISK_1: finished piece 1 at 26-SEP-22
piece handle=/home/oracle/rmanbak/2dbf_ORCL150_0c18mivj_1_12 tag=TAG20220926T140643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 26-SEP-22
channel ORA_DISK_1: finished piece 1 at 26-SEP-22
piece handle=/home/oracle/rmanbak/2dbf_ORCL150_0d18mj10_1_13 tag=TAG20220926T140643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-SEP-22

Starting backup at 26-SEP-22
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=41 RECID=70 STAMP=1116425250
channel ORA_DISK_1: starting piece 1 at 26-SEP-22
channel ORA_DISK_1: finished piece 1 at 26-SEP-22
piece handle=/home/oracle/rmanbak/3arc_ORCL150_0e18mj12_1_14 tag=TAG20220926T140730 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_41_1101942938.dbf RECID=70 STAMP=1116425250
Finished backup at 26-SEP-22
2.1.2、归档确认
含delete all input的rman备份语句执行后，查看归档情况.
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        40 2022-09-26 13:48:14         11
        39 2022-09-26 13:42:46         14
        38 2022-09-26 13:42:43         39
        37 2022-09-26 13:42:40         39
        36 2022-09-26 13:42:36         39
        35 2022-09-26 13:42:33         39
        34 2022-09-26 13:42:30         39
        33 2022-09-26 13:42:29         39
        32 2022-09-26 13:42:28         29
        31 2022-09-26 13:14:42         39
        30 2022-09-26 13:14:39         39

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        29 2022-09-26 13:14:35         39
        28 2022-09-26 13:14:32         39
        27 2022-09-26 13:12:41         39
        26 2022-09-26 13:12:40         33
        25 2022-09-26 13:09:32         39
        24 2022-09-26 13:07:10         19
        23 2022-09-26 13:07:08         39
        22 2022-09-26 11:57:44         31
        21 2022-09-26 11:57:26          0
        20 2022-09-26 11:57:22          0
        19 2022-08-28 23:27:25          0

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        18 2022-08-28 23:27:22          0
        17 2022-08-28 23:07:19          0

27 rows selected.
结论：备库未应用的归档日志被delete all input语句全部删除.
2.2、delete archivelog all
现测试delete archivelog all是否能删除备库未应用的归档日志.
2.2.1、新归档
主库再次产生新归档日志.
> insert into t1 select * from t1;

2700640 rows created.
> commit;
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 15','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        57 2022-09-26 14:17:01         39 /u01/app/oracle/fast_recovery_area/archivelog/1_57_1101942938.dbf
        56 2022-09-26 14:16:57         39 /u01/app/oracle/fast_recovery_area/archivelog/1_56_1101942938.dbf
        55 2022-09-26 14:16:54         39 /u01/app/oracle/fast_recovery_area/archivelog/1_55_1101942938.dbf
        54 2022-09-26 14:16:51         39 /u01/app/oracle/fast_recovery_area/archivelog/1_54_1101942938.dbf
        53 2022-09-26 14:16:48         39 /u01/app/oracle/fast_recovery_area/archivelog/1_53_1101942938.dbf
        52 2022-09-26 14:16:45         39 /u01/app/oracle/fast_recovery_area/archivelog/1_52_1101942938.dbf
        51 2022-09-26 14:16:41         39 /u01/app/oracle/fast_recovery_area/archivelog/1_51_1101942938.dbf
        50 2022-09-26 14:16:38         39 /u01/app/oracle/fast_recovery_area/archivelog/1_50_1101942938.dbf
        49 2022-09-26 14:16:35         39 /u01/app/oracle/fast_recovery_area/archivelog/1_49_1101942938.dbf
        48 2022-09-26 14:16:32         39 /u01/app/oracle/fast_recovery_area/archivelog/1_48_1101942938.dbf
        47 2022-09-26 14:16:29         39 /u01/app/oracle/fast_recovery_area/archivelog/1_47_1101942938.dbf

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        46 2022-09-26 14:16:25         39 /u01/app/oracle/fast_recovery_area/archivelog/1_46_1101942938.dbf
        45 2022-09-26 14:16:22         39 /u01/app/oracle/fast_recovery_area/archivelog/1_45_1101942938.dbf
        44 2022-09-26 14:16:17         39 /u01/app/oracle/fast_recovery_area/archivelog/1_44_1101942938.dbf
        43 2022-09-26 14:16:15         36 /u01/app/oracle/fast_recovery_area/archivelog/1_43_1101942938.dbf
        42 2022-09-26 14:07:30         39 /u01/app/oracle/fast_recovery_area/archivelog/1_42_1101942938.dbf
        41 2022-09-26 14:06:17          1
        40 2022-09-26 13:48:14         11
        39 2022-09-26 13:42:46         14
        38 2022-09-26 13:42:43         39
        37 2022-09-26 13:42:40         39
        36 2022-09-26 13:42:36         39

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        35 2022-09-26 13:42:33         39
        34 2022-09-26 13:42:30         39
        33 2022-09-26 13:42:29         39
        32 2022-09-26 13:42:28         29
        31 2022-09-26 13:14:42         39
        30 2022-09-26 13:14:39         39
        29 2022-09-26 13:14:35         39
        28 2022-09-26 13:14:32         39
        27 2022-09-26 13:12:41         39
        26 2022-09-26 13:12:40         33
        25 2022-09-26 13:09:32         39

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        24 2022-09-26 13:07:10         19
        23 2022-09-26 13:07:08         39
        22 2022-09-26 11:57:44         31
        21 2022-09-26 11:57:26          0
        20 2022-09-26 11:57:22          0
        19 2022-08-28 23:27:25          0
        18 2022-08-28 23:27:22          0
        17 2022-08-28 23:07:19          0
        16 2022-08-28 23:06:07          0
        15 2022-08-28 23:05:52          0
        14 2022-08-28 23:00:19          0

44 rows selected.
说明：sequence# 42-57备库肯定未应用.
2.2.2、delete archivelog
主库delete archivelog all测试.
RMAN> delete archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL150
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
73      1    42      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_42_1101942938.dbf

75      1    43      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_43_1101942938.dbf

77      1    44      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_44_1101942938.dbf

79      1    45      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_45_1101942938.dbf

81      1    46      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_46_1101942938.dbf

83      1    47      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_47_1101942938.dbf

85      1    48      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_48_1101942938.dbf

87      1    49      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_49_1101942938.dbf

89      1    50      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_50_1101942938.dbf

91      1    51      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_51_1101942938.dbf

93      1    52      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_52_1101942938.dbf

95      1    53      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_53_1101942938.dbf

97      1    54      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_54_1101942938.dbf

99      1    55      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_55_1101942938.dbf

101     1    56      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_56_1101942938.dbf

102     1    57      A 26-SEP-22
        Name: /u01/app/oracle/fast_recovery_area/archivelog/1_57_1101942938.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_42_1101942938.dbf RECID=73 STAMP=1116425776
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_43_1101942938.dbf RECID=75 STAMP=1116425778
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_44_1101942938.dbf RECID=77 STAMP=1116425783
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_45_1101942938.dbf RECID=79 STAMP=1116425786
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_46_1101942938.dbf RECID=81 STAMP=1116425790
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_47_1101942938.dbf RECID=83 STAMP=1116425793
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_48_1101942938.dbf RECID=85 STAMP=1116425796
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_49_1101942938.dbf RECID=87 STAMP=1116425799
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_50_1101942938.dbf RECID=89 STAMP=1116425802
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_51_1101942938.dbf RECID=91 STAMP=1116425805
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_52_1101942938.dbf RECID=93 STAMP=1116425808
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_53_1101942938.dbf RECID=95 STAMP=1116425811
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_54_1101942938.dbf RECID=97 STAMP=1116425814
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_55_1101942938.dbf RECID=99 STAMP=1116425817
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_56_1101942938.dbf RECID=101 STAMP=1116425821
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/archivelog/1_57_1101942938.dbf RECID=102 STAMP=1116425825
Deleted 16 objects
结论：主库通过delete archivelog all可以删除备库未应用的归档日志.