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

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



理解在執行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個。

摘要:

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的記錄。