DELETE ARCHIVELOG ALL COMPLETED BEFORE/after 'SYSDATE-7'与DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7'区别

有网友在T.ASKMACLEAN.COM上 提问关于”DELETE ARCHIVELOG ALL COMPLETED BEFORE” 与 “DELETE ARCHIVELOG UNTIL TIME “的区别。为了了解这2个命令细微的差别，我们先来温习一些 ARCHIVED LOG的知识。

 

V$ARCHIVED_LOG:FIRST_TIME             DATE     Timestamp of the first changeNEXT_TIME              DATE     Timestamp of the next changeCOMPLETION_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 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE    10.2.0.5.0      ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - ProductionSQL>SQL> select * from global_name;GLOBAL_NAME--------------------------------------------------------------------------------www.oracledatabase12g.comSQL> alter system set log_archive_max_processes=1;System altered.[oracle@vrh8 udump]$ ps -ef|grep arc|grep -v greporacle   21777     1  0 07:22 ?        00:00:00 ora_arc0_G10R25SQL> 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: [e-mail] (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_.arc2012-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_.arc2012-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.trcSQL> !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=  3114867949SELECT :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 a20col 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:2636 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_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=140 devtype=DISKRMAN> DELETE ARCHIVELOG  UNTIL TIME "to_timestamp('2012-05-17 07:40:00','YYYY-MM-DD hh24:mi:ss')";released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=140 devtype=DISKList of Archived Log CopiesKey     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_.arcDo you really want to delete the above objects (enter YES or NO)? ydeleted archive logarchive log filename=/s01/flash_recovery_area/G10R25/archivelog/2012_05_17/o1_mf_1_232_7v9s3dcf_.arc recid=39 stamp=783503020Deleted 1 objects

--本篇文章转自：Archivelog Completed Before VS UNTIL TIME