暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / DELETE ARCHIVELOG ALL COMPLETED BEFORE/AFTER 'SYSDATE-7'与DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7'的区别

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

Leo 2024-05-06
590

文档课题：DELETE ARCHIVELOG ALL COMPLETED BEFORE/AFTER 'SYSDATE-7'与DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7'的区别.

数据库：oracle 11.2.0.4

1、理论知识

1.1、v$archived_log视图解析

为了解这2个命令细微的差别，先来看视图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的情况

 

sys@ORCL 2024-05-06 11:35:26> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

 

sys@ORCL 2024-05-06 11:53:09> select * from global_name;

 

GLOBAL_NAME

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

ORCL

 

sys@ORCL 2024-05-06 11:53:42> show parameter log_archive_max_process

 

NAME                                 TYPE        VALUE

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

log_archive_max_processes            integer     4

sys@ORCL 2024-05-06 11:53:52> host ps -ef | grep arc | grep -v grep

oracle     2082      1  0 09:01 ?        00:00:00 ora_arc0_orcl

oracle     2084      1  0 09:01 ?        00:00:00 ora_arc1_orcl

oracle     2086      1  0 09:01 ?        00:00:00 ora_arc2_orcl

oracle     2089      1  0 09:01 ?        00:00:00 ora_arc3_orcl

 

sys@ORCL 2024-05-06 11:54:06> alter system set log_archive_max_processes=1;

 

System altered.

 

sys@ORCL 2024-05-06 11:54:35> host ps -ef | grep arc | grep -v grep

oracle     2082      1  0 09:01 ?        00:00:00 ora_arc0_orcl

oracle     2084      1  0 09:01 ?        00:00:00 ora_arc1_orcl

 

sys@ORCL 2024-05-06 11:55:38> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

Session altered.

 

sys@ORCL 2024-05-06 11:56:20> select sequence#,first_change# from v$log where status='CURRENT';

 

 SEQUENCE# FIRST_CHANGE#

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

        47       3211687

 

说明：current logfile当前在线日志的SEQUENCE#=47,FIRST_CHANGE#=3211687。

 

现运用oradebug suspend命令将arc0和arc1归档后台进程强制挂起，使归档长时间无法完成。

sys@ORCL 2024-05-06 11:56:43> oradebug setospid 2082

Oracle pid: 22, Unix process pid: 2082, image: oracle@leo-oracle-11g (ARC0)

sys@ORCL 2024-05-06 12:00:34> oradebug suspend

Statement processed.

sys@ORCL 2024-05-06 12:00:54> oradebug setospid 2084

Oracle pid: 23, Unix process pid: 2084, image: oracle@leo-oracle-11g (ARC1)

sys@ORCL 2024-05-06 12:01:14> oradebug suspend

Statement processed.

 

sys@ORCL 2024-05-06 12:01:21> alter system switch logfile;

 

System altered.

 

sys@ORCL 2024-05-06 12:01:50> alter system switch logfile;

 

System altered.

 

sys@ORCL 2024-05-06 12:03:16> 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

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

        46 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_05_06/o1_mf_1_46_m3jbyrbd_.arc 2024-05-05 21:02:21 2024-05-06 09:01:12 2024-05-06 09:01:12

 

可以看到suspend arc0和arc1后switch logfile，归档日志文件没有照常发生，v$archived_log中最大的sequence#仍是46。然后resume arc0和arc1.

sys@ORCL 2024-05-06 13:05:52> exec dbms_lock.sleep(60);

 

PL/SQL procedure successfully completed.

 

sys@ORCL 2024-05-06 13:10:13> oradebug resume;

Statement processed.

sys@ORCL 2024-05-06 13:12:51> set linesize 80 pagesize 1400

sys@ORCL 2024-05-06 13:13:04> 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

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

        48 /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_05_06/o1_mf_1_48_m3jspn12_.arc 2024-05-06 12:01:49 2024-05-06 12:01:55 2024-05-06 13:12:52

 

说明：next_time=2024-05-06 12:01:55，而completion_time=2024-05-06 13:12:52，相差71分钟.

 

1.2、dump logfile

dump logfile了解更多信息：

sys@ORCL 2024-05-06 13:13:19> alter system dump logfile '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_05_06/o1_mf_1_48_m3jspn12_.arc';

 

System altered.

 

sys@ORCL 2024-05-06 14:04:14> oradebug setmypid;

Statement processed.

sys@ORCL 2024-05-06 14:04:25> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3532.trc

 

sys@ORCL 2024-05-06 14:04:36> !view /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3532.trc

......

Low  scn: 0x0000.00311aa4 (3218084) 05/06/2024 12:01:49

Next scn: 0x0000.00311aa7 (3218087) 05/06/2024 12:01:55

Enabled scn: 0x0000.000e2006 (925702) 12/23/2023 22:18:09

Thread closed scn: 0x0000.00311aa4 (3218084) 05/06/2024 12:01:49

 

说明：以上为关于archived_log的first_time和completion_time的知识，接下来实际了解"delete archivelog all completed before"与"delete archivelog until time"的区别。

 

1.3、recover.bsq

rman会通过$ORACLE_HOME/rdbms/admin/recover.bsq将rman命令解析成pl/sql包的调用，包括：dbms_rcvman和dbms_backup_restore等内置package。

 

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视图查找.

 

2、相关测试

2.1、delete archivelog until time 'sysdate-7'

当执行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小于指定的时间变量的归档日志.

 

2.2、delete archivelog all completed before 'sysdate-7'

当执行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，即实际归档操作完成时间，其意思为找出所有归档完成时间小于等于指定时间变量的归档日志。

 

2.3、delete archivelog all completed after 'sysdate-7'

当执行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                 05/10/12 07:23:03  => 'sysdate-7'

SQL中的相关条件:B14 IS NULL OR COMPLETION_TIME >= :B14 即：COMPLETION_TIME >= 'sysdate-7',after操作仅仅是从小于等于变成大于等于.

completed after指的是archivelog的completion_time，即实际归档操作完成时间，其意思为找出所有归档完成时间大于等于指定时间变量的归档日志。

 

3、总结

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

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

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

 

4、使用场景

Question:

搞清楚这些细节对实际工作有什么意义？

 

Answer:

ARCHIVELOG相关过滤条件UNTIL TIME和COMPLETED BEFORE是存在区别的，平时备份时可能感受不到这种区别。

 

试想如下场景：

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时间段的数据不可恢复；

 

若使用DELETE ARCHIVELOG ALL COMPLETED BEFORE 08:00，因为SENQUENCE A的COMPLETION_TIME>08:00，所以SEQUENCE A将不会被删除。

 

5、实际测试

测试sequence 48的归档日志文件：

FIRST_TIME= 2024-05-06 12:01:49

NEXT_TIME= 2024-05-06 12:01:55

COMPLETION_TIME= 2024-05-06 13:12:52

 

RMAN> delete noprompt archivelog all completed before "to_timestamp('2024-05-06 12:30:00','yyyy-mm-dd hh24:mi:ss')";

 

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

 

RMAN> delete noprompt archivelog until time "to_timestamp('2024-05-06 12:30:00','yyyy-mm-dd hh24:mi:ss')";

 

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

List of Archived Log Copies for database with db_unique_name ORCL

=====================================================================

 

Key     Thrd Seq     S Low Time

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

33      1    47      A 06-MAY-24

        Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_05_06/o1_mf_1_47_m3jspmyo_.arc

 

34      1    48      A 06-MAY-24

        Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_05_06/o1_mf_1_48_m3jspn12_.arc

 

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_05_06/o1_mf_1_47_m3jspmyo_.arc RECID=33 STAMP=1168261972

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_05_06/o1_mf_1_48_m3jspn12_.arc RECID=34 STAMP=1168261972

Deleted 2 objects

 

特别说明：以上内容均来自以下网址，笔者只是实际操作一遍.

参考文档：https://blog.csdn.net/yabingshi_tech/article/details/43966033

 

归档清理
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论