ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131028]理解archivelog completed,before,after,until-time.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-10-28 16:46:20 0 删除 编辑
[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的记录。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-775193/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2457
  • 访问量
    6262208