有网友在T.askmac.cn上 提问关于”DELETE ARCHIVELOG ALL COMPLETED BEFORE” 与 “DELETE ARCHIVELOG UNTIL TIME “的区别。为了了解这2个命令细微的差别，我们先来温习一些 ARCHIVED LOG的知识。
V$ARCHIVED_LOG: FIRST_TIME DATE Timestamp of the first change NEXT_TIME DATE Timestamp of the next change COMPLETION_TIME DATE Time when the archiving completed
FIRST_TIME代表该归档日志中LOW SCN对应的时间戳， 而NEXT_TIME代表HIGH SCN对应的时间戳； COMPLETION_TIME指该日志实际归档成功的时间，当归档可以快速完成时 NEXT_TIME往往等于COMPLETION_TIME，但是也存在因为LOGFILE SIZE尺寸较大导致archive归档操作持续较长时间，导致 NEXT_TIME << COMPLETION_TIME的情况存在。
我们来看一个例子：
首先通过修改log_archive_max_processes=1，保证实例只拥有一个ARC0归档进程:
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL>
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn
SQL> alter system set log_archive_max_processes=1;
System altered.
[oracle@vrh8 udump]$ ps -ef|grep arc|grep -v grep
oracle 21777 1 0 07:22 ? 00:00:00 ora_arc0_G10R25
SQL> alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';
Session altered.
SQL> select SEQUENCE#, FIRST_CHANGE# from v$log where status='CURRENT';
SEQUENCE# FIRST_CHANGE#
---------- -------------
232 2476748
CURRENT LOGFILE当前在线日志的SEQUENCE#=232， FIRST_CHANGE#=2476748。
我们利用oradebug suspend 命令将ARC0归档后台进程强制挂起，这将导致归档长时间无法完成； 注意不要在生产环境做这样的操作！！
SQL> oradebug setospid 21777;
Oracle pid: 20, Unix process pid: 21777, image: oracle@vrh8.oracle.com (ARC0)
SQL> oradebug suspend;
Statement processed.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
WAIT A MOMENT;
SQL> select sequence#,name,first_time,next_time,COMPLETION_TIME from v$archived_log where sequence# =( select max(sequence#) from v$archived_log);
SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
FIRST_TIME COMPLETION_TIME
------------------- -------------------
231
/s01/flash_recovery_area/G10R25/archivelog/2012_05_17/o1_mf_1_231_7v9rh2rg_.arc
2012-05-17 07:32:44 2012-05-17 07:32:50
可以看到手动suspend ARC0后switch logfile，归档没有照常发生，V$ARCHIVED_LOG中最大的SEQUENCE#仍是 231。 之后我们resume ARC0：
SQL> exec dbms_lock.sleep(60);
SQL> oradebug resume;
Statement processed.
SQL> set linesize 80 pagesize 1400;
SQL> select sequence#,name,first_time,next_time,COMPLETION_TIME from v$archived_log where sequence# =( select max(sequence#) from v$archived_log);
SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
FIRST_TIME NEXT_TIME COMPLETION_TIME
------------------- ------------------- -------------------
232
/s01/flash_recovery_area/G10R25/archivelog/2012_05_17/o1_mf_1_232_7v9s3dcf_.arc
2012-05-17 07:32:50 2012-05-17 07:41:32 2012-05-17 07:43:40
NEXT_TIME=07:41:32 而 COMPLETION_TIME= 07:43:40，相差了2分钟左右。
DUMP LOGFILE可以了解更多信息：
SQL> alter system dump logfile '/s01/flash_recovery_area/G10R25/archivelog/2012_05_17/o1_mf_1_232_7v9s3dcf_.arc'; System altered. SQL> oradebug setmypid; Statement processed. SQL> oradebug tracefile_name /s01/admin/G10R25/udump/g10r25_ora_21848.trc SQL> !vi /s01/admin/G10R25/udump/g10r25_ora_21848.trc Low scn: 0x0000.0025cacc (2476748) 05/17/2012 07:32:50 Next scn: 0x0000.0025cb8f (2476943) 05/17/2012 07:41:32 Enabled scn: 0x0000.0005eca9 (388265) 11/07/2011 03:58:11 Thread closed scn: 0x0000.0025cacc (2476748) 05/17/2012 07:32:50
以上我们复习了 关于ARCHIVED LOG 的FIRST_TIME和 COMPLETION_TIME的知识，接下来我们实际来了解”DELETE ARCHIVELOG ALL COMPLETED BEFORE” 与 “DELETE ARCHIVELOG UNTIL TIME “的区别。
RMAN会通过$ORACLE_HOME/rdbms/admin/recover.bsq将RMAN命令解析成PL/SQL包的调用，包括：DBMS_RCVMAN和DBMS_BACKUP_RESTORE等内置PACKAGE。
当使用BACKUP/DELETE ARCHIVELOG “Completed Before”/”UNTIL TIME”时DBMS_RCVMAN会调用以下一个SQL语句，查询V$ARCHIVED_LOG视图中是否有满足条件的归档日志：
HASH_VALUE= 3114867949 SELECT :B20 TYPE_CON, RECID KEY_CON, RECID RECID_CON, STAMP STAMP_CON, TO_NUMBER(NULL) SETSTAMP_CON, TO_NUMBER(NULL) SETCOUNT_CON, TO_NUMBER(NULL) BSRECID_CON, TO_NUMBER(NULL) BSSTAMP_CON, TO_NUMBER(NULL) BSKEY_CON, TO_NUMBER(NULL) BSLEVEL_ CON, TO_CHAR(NULL) BSTYPE_CON, TO_NUMBER(NULL) ELAPSESECS_CON, TO_NUMBER(NULL) P IECECOUNT_CON, NAME FILENAME_CON, TO_CHAR(NULL) TAG_CON, TO_NUMBER(NULL) COPYNUM BER_CON, STATUS STATUS_CON, BLOCKS BLOCKS_CON, BLOCK_SIZE BLOCKSIZE_CON, 'DISK' DEVICETYPE_CON, COMPLETION_TIME COMPTIME_CON, TO_DATE(NULL) CFCREATIONTIME_CON, TO_NUMBER(NULL) PIECENUMBER_CON, TO_DATE(NULL) BPCOMPTIME_CON, TO_CHAR(NULL) BPC OMPRESSED_CON, :B19 TYPE_ACT, TO_NUMBER(NULL) FROMSCN_ACT, TO_NUMBER(NULL) TOSCN _ACT, TO_DATE(NULL) TOTIME_ACT, TO_NUMBER(NULL) RLGSCN_ACT, TO_DATE(NULL) RLGTIM E_ACT, TO_NUMBER(NULL) DBINCKEY_ACT, TO_NUMBER(NULL) LEVEL_ACT, TO_NUMBER(NULL) DFNUMBER_OBJ, TO_NUMBER(NULL) DFCREATIONSCN_OBJ, TO_NUMBER(NULL) CFSEQUENCE_OBJ, TO_DATE(NULL) CFDATE_OBJ, SEQUENCE# LOGSEQUENCE_OBJ, THREAD# LOGTHREAD_OBJ, RES ETLOGS_CHANGE# LOGRLGSCN_OBJ, RESETLOGS_TIME LOGRLGTIME_OBJ, FIRST_CHANGE# LOGLO WSCN_OBJ, FIRST_TIME LOGLOWTIME_OBJ, NEXT_CHANGE# LOGNEXTSCN_OBJ, NEXT_TIME LOGN EXTTIME_OBJ, DECODE(END_OF_REDO_TYPE, 'TERMINAL', 'YES', 'NO') LOGTERMINAL_OBJ, T O_CHAR(NULL) CFTYPE_OBJ, TO_NUMBER(NULL) KEEP_OPTIONS, TO_DATE(NULL) KEEP_UNTIL, TO_NUMBER(NULL) AFZSCN_ACT, TO_DATE(NULL) RFZTIME_ACT, TO_NUMBER(NULL) RFZSCN_A CT, TO_CHAR(NULL) MEDIA_CON, IS_RECOVERY_DEST_FILE ISRDF_CON FROM V$ARCHIVED_LOG WHERE (:B18 IS NULL OR THREAD# = :B18) AND (:B17 IS NULL OR SEQUENCE# = :B17) AND (:B16 IS NULL OR FIRST_CHANGE# = :B16) AND (:B15 IS NULL OR NAME LIKE :B15) AND (:B14 IS NULL OR COMPLETION_TIME >= :B14) AND (:B13 IS NULL OR COMPLETION_TIME <= :B13) AND DECODE(:B10, :B12, DECODE(STATUS, 'A', :B9, :B11), DBMS _RCVMAN.ISSTATUSMATCH(STATUS, :B10)) = :B9 AND STANDBY_DEST = 'NO' AND (ARCHIVE D = 'YES') AND (:B8 IS NULL OR THREAD# = :B8) AND (:B7 IS NULL OR SEQUENCE# >= :B7) AND (:B6 IS NULL OR SEQUENCE# <= :B6) AND (:B5 IS NULL OR NEXT_CHANGE# > :B5) AND (:B4 IS NULL OR FIRST_CHANGE# < :B4) AND (:B3 IS NULL OR NAME LIKE :B 3) AND (:B2 IS NULL OR NEXT_TIME > :B2) AND (:B1 IS NULL OR FIRST_TIME <= :B1) ORDER BY RESETLOGS_CHANGE#, RESETLOGS_TIME, THREAD#, SEQUENCE#, LOGTERMINAL_OB J DESC, STAMP_CON DESC
已知该语句的HASH_VALUE=3114867949 ，虽然该语句使用了绑定变量且似乎10046 TRACE capture不到其BIND VALUE，但仍可以通过v$sql_bind_capture视图找到：
当DELETE ARCHIVELOG UNTIL TIME ‘SYSDATE-7’;执行时：
col name for a20 col value_string for a50 SQL> select name,value_string from v$sql_bind_capture where hash_value='3114867949'; :B20 :B19 :B18 NULL :B18 NULL :B17 NULL :B17 NULL :B16 NULL :B16 NULL :B15 NULL :B15 NULL :B14 NULL :B14 NULL :B13 NULL :B13 NULL :B10 27 :B12 1 :B9 1 :B11 0 :B10 27 :B9 1 :B8 NULL :B8 NULL :B7 NULL :B7 NULL :B6 NULL :B6 NULL :B5 NULL :B5 NULL :B4 NULL :B4 NULL :B3 NULL :B3 NULL :B2 NULL :B2 NULL :B1 05/10/12 07:15:26 :B1 05/10/12 07:15:26 36 rows selected.
其中有意义的绑定值为：
:B1 05/10/12 07:15:26 =》即SYSDATE – 7
可以在上述SQL中找到相关条件:B1 IS NULL OR FIRST_TIME <= :B1，即 FIRST_TIME <= ‘SYSDATE-7’;
即 UNTIL TIME 的TIME指的是 ARCHIVELOG的FIRST_TIME ，即归档日志中LOW SCN对应的时间戳；其意思为找出所有LOW SCN TIMESTAMP小于等于指定的时间变量的归档日志。
当DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7’;执行时：
SQL> select name,value_string from v$sql_bind_capture where hash_value='3114867949'; :B20 :B19 :B18 NULL :B18 NULL :B17 NULL :B17 NULL :B16 NULL :B16 NULL :B15 NULL :B15 NULL :B14 NULL :B14 NULL :B13 05/10/12 07:21:00 :B13 05/10/12 07:21:00 :B10 27 :B12 1 :B9 1 :B11 0 :B10 27 :B9 1 :B8 NULL :B8 NULL :B7 NULL :B7 NULL :B6 NULL :B6 NULL :B5 0 :B5 0 :B4 281474976710656 :B4 281474976710656 :B3 NULL :B3 NULL :B2 NULL :B2 NULL :B1 NULL :B1 NULL
其中有意义的绑定值为 :B13 05/10/12 07:21:00=> ‘SYSDATE-7′
SQL中的相关条件:B13 IS NULL OR COMPLETION_TIME <= :B13 即 COMPLETION_TIME <=’SYSDATE=7’;
COMPLETED BEFORE指的是ARCHIVELOG的COMPLETION_TIME，即实际归档操作完成的时间；其意思为找出所有归档完成时间小于指定的时间变量的归档日志。
当DELETE ARCHIVELOG ALL COMPLETED AFTER ‘SYSDATE-7’;执行时：
SQL> select name,value_string from v$sql_bind_capture where hash_value='3114867949'; :B20 :B19 :B18 NULL :B18 NULL :B17 NULL :B17 NULL :B16 NULL :B16 NULL :B15 NULL :B15 NULL :B14 05/10/12 07:23:03 :B14 05/10/12 07:23:03 :B13 NULL :B13 NULL :B10 27 :B12 1 :B9 1 :B11 0 :B10 27 :B9 1 :B8 NULL :B8 NULL :B7 NULL :B7 NULL :B6 NULL :B6 NULL :B5 0 :B5 0 :B4 281474976710656 :B4 281474976710656 :B3 NULL :B3 NULL :B2 NULL :B2 NULL :B1 NULL :B1 NULL
:B14 IS NULL OR COMPLETION_TIME >= :B14，类似的AFTER操作仅仅是从小于等于变成了大于等于。
COMPLETED AFTER指的是ARCHIVELOG的COMPLETION_TIME，即实际归档操作完成的时间；其意思为找出所有归档完成时间大于等于指定的时间变量的归档日志。
Summary :
UNTIL TIME的TIME 指的是 ARCHIVELOG的FIRST_TIME ，即归档日志中LOW SCN对应的时间戳；其意思为找出所有LOW SCN TIMESTAMP小于等于指定的时间变量的归档日志。
COMPLETED BEFORE 指的是ARCHIVELOG的COMPLETION_TIME，即实际归档操作完成的时间；其意思为找出所有归档完成时间小于指定的时间变量的归档日志。
COMPLETED AFTER 指的是ARCHIVELOG的COMPLETION_TIME，即实际归档操作完成的时间；其意思为找出所有归档完成时间大于等于指定的时间变量的归档日志。
Question:
搞清楚这些细节对实际的工作由什么意义？
Answer:
ARCHIVELOG相关过滤条件 UNTIL TIME 和 COMPLETED BEFORE是存在区别的，在平时备份BACKUP时可能感受不到这种区别。
试想这样一个场景，
SEQUENCE A 的ARCHIVELOG 的First TIME为 07:45 、 NEXT TIME为08:10、归档操作耗费了1分钟即COMPLETION_TIME为08:11
SEQUENCE A+1即后续的一个ARCHIVELOG的 FIRST TIME为08:10，NEXT TIME为08:30……..
我们以08:00为时间变量，
若使用DELETE ARCHIVELOG UNTIL TIME 08:00 ，因为SENQUENCE A的FIRST_TIME <08:00，所以SEQUENCE A将被删除，若没有相应的归档备份或COPY，则意味着08:00~08:10 这段时间将变成unrecoverable；
若使用DELETE ARCHIVELOG ALL COMPLETED BEFORE 08:00，因为SENQUENCE A的COMPLETION_TIME>08:00，所以SEQUENCE A将不被删除。
来实际体验一下这个结论，SEQUENCE 232的
FIRST_TIME=2012-05-17 07:32:50,
NEXT TIME=2012-05-17 07:41:32,
COMPLETION_TIME=2012-05-17 07:43:40
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE "to_timestamp('2012-05-17 07:40:00','YYYY-MM-DD hh24:mi:ss')";
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
RMAN> DELETE ARCHIVELOG UNTIL TIME "to_timestamp('2012-05-17 07:40:00','YYYY-MM-DD hh24:mi:ss')";
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
39 1 232 A 17-MAY-12 /s01/flash_recovery_area/G10R25/archivelog/2012_05_17/o1_mf_1_232_7v9s3dcf_.arc
Do you really want to delete the above objects (enter YES or NO)? y
deleted archive log
archive log filename=/s01/flash_recovery_area/G10R25/archivelog/2012_05_17/o1_mf_1_232_7v9s3dcf_.arc recid=39 stamp=783503020
Deleted 1 objects
