[20131028]理解archivelog completed,before,after,until-time.txt

[20131028]理解archivelog completed,before,after,until-time.txt

http://www.askmaclean.com/archives/archivelog-completed-before-vs-until-time.html

理解在执行rman的"DELETE ARCHIVELOG ALL COMPLETED BEFORE" 与 "DELETE ARCHIVELOG UNTIL TIME "的区别.

RMAN> list archivelog all ;
...

498     1    555     A 2013-10-25 11:32:09
        Name: /u01/app/oracle11g/archivelog/1_555_798551880.dbf

499     1    556     A 2013-10-25 11:32:15
        Name: /u01/app/oracle11g/archivelog/1_556_798551880.dbf

500     1    557     A 2013-10-28 08:58:36
        Name: /u01/app/oracle11g/archivelog/1_557_798551880.dbf

501     1    558     A 2013-10-28 10:09:22
        Name: /u01/app/oracle11g/archivelog/1_558_798551880.dbf


--删除一些记录：
RMAN> delete archivelog sequence between 1 and 555;


RMAN> list archivelog all ;

List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
499     1    556     A 2013-10-25 11:32:15
        Name: /u01/app/oracle11g/archivelog/1_556_798551880.dbf

500     1    557     A 2013-10-28 08:58:36
        Name: /u01/app/oracle11g/archivelog/1_557_798551880.dbf

501     1    558     A 2013-10-28 10:09:22
        Name: /u01/app/oracle11g/archivelog/1_558_798551880.dbf

SCOTT@test> SELECT recid, first_time, next_time, completion_time FROM v$archived_log where recid between 499 and 501;

     RECID FIRST_TIME          NEXT_TIME           COMPLETION_TIME

---------- ------------------- ------------------- -------------------

       499 2013-10-25 11:32:15 2013-10-28 08:58:36 2013-10-28 08:58:38

       500 2013-10-28 08:58:36 2013-10-28 10:09:22 2013-10-28 10:09:23

       501 2013-10-28 10:09:22 2013-10-28 16:23:13 2013-10-28 16:23:14

--仅仅保留3个。

摘要:http://www.askmaclean.com/archives/archivelog-completed-before-vs-until-time.html

Summary :

UNTIL TIME的TIME  指的是 ARCHIVELOG的FIRST_TIME ，即归档日志中LOW SCN对应的时间戳；其意思为找出所有LOW SCN TIMESTAMP小于
等于指定的时间变量的归档日志。

COMPLETED BEFORE  指的是ARCHIVELOG的COMPLETION_TIME，即实际归档操作完成的时间；其意思为找出所有归档完成时间小于指定的时
间变量的归档日志。

COMPLETED AFTER   指的是ARCHIVELOG的COMPLETION_TIME，即实际归档操作完成的时间；其意思为找出所有归档完成时间大于等于指定
的时间变量的归档日志。

--我的环境定义如下:
$ env | grep NLS
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
--不然下面的日期时间不能这样写！

RMAN> list archivelog  until time '2013-10-25 11:32:16';

List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
499     1    556     A 2013-10-25 11:32:15
        Name: /u01/app/oracle11g/archivelog/1_556_798551880.dbf


RMAN> list archivelog all completed before '2013-10-25 11:32:16';

RMAN> list archivelog all completed before '2013-10-28 08:58:36';

RMAN> list archivelog all completed before '2013-10-28 08:58:37';

RMAN> list archivelog all completed before '2013-10-28 08:58:38';

List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
499     1    556     A 2013-10-25 11:32:15
        Name: /u01/app/oracle11g/archivelog/1_556_798551880.dbf

--before '2013-10-28 08:58:38' 这样seq=556才显示出来。

--这样的话：
delete archivelog  until time '2013-10-25 11:32:16'; =>可以删除seq=556的记录。
delete archivelog all completed before '2013-10-28 08:58:38'; =>可以删除seq=556的记录。