重庆思庄Oracle、Redhat认证学习论坛

标题: 归档日志until time与all completed before的区别（二） [打印本页]

作者: jiawang 时间: 2020-9-25 10:02
标题: 归档日志until time与all completed before的区别（二）
删除N天以前的归档日志，有两个命令：
delete archivelog until time 'sysdate-N'
delete archivelog all completed before 'sysdate-N'

看下两者的区别：
RMAN> list archivelog until time 'sysdate-1';
List of Archived Log Copies
Key    Thrd Seq    S Low Time Name
------- ---- ------- - ---------- ----
7    1 5    A 13-7月 -11 +RECOVERY/edu/archivelog/2011_07_13/thread_1_seq_5.259.756405771
10    1 6    A 13-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_6.263.756453607
16    1 7    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_7.268.756467343
17    1 8    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_8.269.756467345
18    1 9    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_9.270.756467399
19    1 10    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_10.271.756467403
21    1 11    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_11.272.756467511
22    1 12    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_12.274.756467515
23    1 13    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_13.275.756467521
25    1 14    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_14.276.756467527
26    1 15    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_15.278.756467635
27    1 16    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_16.279.756484801
29    1 17    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_15/thread_1_seq_17.256.756576859
5    2 2    A 13-7月 -11 +RECOVERY/edu/archivelog/2011_07_13/thread_2_seq_2.257.756405175
8    2 3    A 13-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_3.260.756442807
9    2 4    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_4.261.756442823
11    2 5    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_5.262.756453607
12    2 6    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_6.264.756453623
13    2 7    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_7.265.756467273
14    2 8    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_8.266.756467279
15    2 9    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_9.267.756467279
20    2 10    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_10.273.756467511
24    2 11    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_11.277.756467533
28    2 12    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_15/thread_2_seq_12.280.756566433
最大sequence为17和12
再看：
RMAN> list archivelog all completed before 'sysdate-1';

List of Archived Log Copies
Key    Thrd Seq    S Low Time Name
------- ---- ------- - ---------- ----
7    1 5    A 13-7月 -11 +RECOVERY/edu/archivelog/2011_07_13/thread_1_seq_5.259.756405771
10    1 6    A 13-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_6.263.756453607
16    1 7    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_7.268.756467343
17    1 8    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_8.269.756467345
18    1 9    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_9.270.756467399
19    1 10    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_10.271.756467403
21    1 11    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_11.272.756467511
22    1 12    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_12.274.756467515
23    1 13    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_13.275.756467521
25    1 14    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_14.276.756467527
26    1 15    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_15.278.756467635
27    1 16    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_1_seq_16.279.756484801
5    2 2    A 13-7月 -11 +RECOVERY/edu/archivelog/2011_07_13/thread_2_seq_2.257.756405175
8    2 3    A 13-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_3.260.756442807
9    2 4    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_4.261.756442823
11    2 5    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_5.262.756453607
12    2 6    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_6.264.756453623
13    2 7    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_7.265.756467273
14    2 8    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_8.266.756467279
15    2 9    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_9.267.756467279
20    2 10    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_10.273.756467511
24    2 11    A 14-7月 -11 +RECOVERY/edu/archivelog/2011_07_14/thread_2_seq_11.277.756467533

详细看下这些日志的区别：
QL> select THREAD#,sequence#,FIRST_TIME,NEXT_TIME,COMPLETION_TIME
2  from v$archived_log order by thread#,sequence#;
THREAD#  SEQUENCE# FIRST_TIME       NEXT_TIME       COMPLETION_TIME
---------- ---------- ----------------- ----------------- -----------------
      1       1 20110713 14:57:58 20110713 14:58:42 20110713 14:58:49
      1       2 20110713 14:58:42 20110713 14:59:00 20110713 14:59:18
      1       3 20110713 14:59:00 20110713 15:02:31 20110713 15:02:36
      1       4 20110713 15:02:31 20110713 16:32:50 20110713 16:32:57
      1       5 20110713 16:32:50 20110713 16:41:40 20110713 16:42:51
      1       6 20110713 16:41:40 20110714 06:00:07 20110714 06:00:08
      1       7 20110714 06:00:07 20110714 09:26:57 20110714 09:49:04
      1       8 20110714 09:26:57 20110714 09:46:55 20110714 09:49:04
      1       9 20110714 09:46:55 20110714 09:49:58 20110714 09:50:06
      1       10 20110714 09:49:58 20110714 09:50:02 20110714 09:50:09
      1       11 20110714 09:50:02 20110714 09:51:49 20110714 09:51:59
THREAD#  SEQUENCE# FIRST_TIME       NEXT_TIME       COMPLETION_TIME
---------- ---------- ----------------- ----------------- -----------------
      1       12 20110714 09:51:49 20110714 09:51:55 20110714 09:52:05
      1       13 20110714 09:51:55 20110714 09:52:01 20110714 09:52:10
      1       14 20110714 09:52:01 20110714 09:52:07 20110714 09:52:13
      1       15 20110714 09:52:07 20110714 09:53:54 20110714 09:53:59
      1       16 20110714 09:53:54 20110714 14:40:01 20110714 14:40:05
      1       17 20110714 14:40:01 20110715 16:14:19 20110715 16:14:20
      1       18 20110715 16:14:19 20110715 16:15:46 20110715 16:15:47
      2       1 20110713 15:14:43 20110713 16:15:41 20110713 16:17:14
      2       2 20110713 16:15:41 20110713 16:32:50 20110713 16:32:54
      2       3 20110713 16:32:50 20110714 03:00:07 20110714 03:00:12
      2       4 20110714 03:00:07 20110714 03:00:23 20110714 03:00:27
THREAD#  SEQUENCE# FIRST_TIME       NEXT_TIME       COMPLETION_TIME
---------- ---------- ----------------- ----------------- -----------------
      2       5 20110714 03:00:23 20110714 06:00:06 20110714 06:00:11
      2       6 20110714 06:00:06 20110714 06:00:22 20110714 06:00:27
      2       7 20110714 06:00:22 20110714 06:00:37 20110714 09:47:56
      2       8 20110714 06:00:37 20110714 09:26:56 20110714 09:48:05
      2       9 20110714 09:26:56 20110714 09:47:56 20110714 09:48:05
      2       10 20110714 09:47:56 20110714 09:51:51 20110714 09:51:52
      2       11 20110714 09:51:51 20110714 09:52:12 20110714 09:52:12
      2       12 20110714 09:52:12 20110715 13:20:33 20110715 13:20:37

区别已经很明显：
  list archivelog until time 'sysdate-1'; 此时是按照归档日志开始时间即fisrt_time作为截止时间
  list archivelog all completed before 'sysdate-1' 是按照归档日志完成时间即completion_time作为截止时间
试想如果删除归档日志，还是采用delete archivelog all completed before 'sysdate-N'

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)